Albert Vincent Albert Vincent - 1 month ago 12
SQL Question

Joining CTE_1 to CTE_2 if CTE_2 has data, if not just give CTE_1 Data

Is there a way to join CTE_1 to CTE_2 if CTE_2 has data? With this code, it returns nothing if CTE_2 has no data. The common columns are Names and Locations. The issue that I am having is CTE_1 will always have data, but CTE_2 won't get data until around 4-6 hours into the shift.

Begin

Create table #Punches
(
[AEmployeeID] [varchar](255) NULL,
[APERSONFULLNAME] [varchar](255) NULL,
[ACUSTOMER] [varchar](255) NULL,
[ADEPARTMENT] [varchar](255) NULL,
[AEVENTDATE] [varchar](255) NULL,
[AINPUNCHDTM1] [varchar](255) NULL,
[AOUTPUNCHDTM1] [varchar](255) NULL,
[AINPUNCHDTM2] [varchar](255) NULL,
[AOUTPUNCHDTM2] [varchar](255) NULL,
[AINPUNCHDTM3] [varchar](255) NULL,
[AOUTPUNCHDTM3] [varchar](255) NULL,
[ASUPERVISORNAME] [varchar](255) NULL,
[ASUPERVISORID] [varchar](255) NULL,
[ASHIFTSTARTTIME] [varchar](255) NULL,
[ASHIFTENDTIME] [varchar](255) NULL,
[ASITE] [varchar](255) NULL,
[ABadge#] [varchar] (255) NULL
)
insert into #Punches
EXECUTE ('BEGIN FX_RPTSP_MEMPHIS_PUNCHES(); END;') at KR1P1;
end
Begin
Create table #1stScans
(
ID int identity ,
[AEmployee Name] [varchar](255) NULL,
[Location] [varchar](25) NULL,
[Dispatch] [varchar](30) NULL,
[TimeStamp] [varchar](255) NULL
)
insert into #1stScans
Select * from Openquery(FFLOW,'Execute rudpBenchLocations_today');
end



--Selects Tech Name, Bench Location, 1st Punch, First Scan, and Dwell (COMPLETED and ACCURATE)
BEGIN

WITH CTE_1 AS(
Select
[AEmployee Name] Name1,
Location Location1,
#Punches.AINPUNCHDTM1 as FirstPunch,
Timestamp From
(Select [AEmployee Name],Location,TimeStamp,
ROW_NUMBER()OVER (PARTITION By [AEmployee Name] ORDER BY Timestamp ASC)Dup from #1stScans,#Punches)as FSD

Join #Punches ON [AEmployee Name]=APERSONFULLNAME
where FSD.dup = 1
AND Convert(Date,AINPUNCHDTM1) = Convert(Date,Getdate()))
,CTE_2 AS
(
Select [AEmployee Name] Name2,Location Location2,TimeStamp Timestamp2,AINPUNCHDTM2 LunchPunch,(ROW_NUMBER()OVER(PARTITION By [AEmployee Name] ORDER BY TimeStamp))as DUP from #1stScans
INNER JOIN #Punches ON [AEmployee Name]=APERSONFULLNAME
AND CONVERT(TIME,Timestamp)>=CONVERT(TIME,AINPUNCHDTM2)
)


***Select
Name1 as Name,
Location1 as Location,
TimeStamp as [1st TimeStamp],
FirstPunch,
TimeStamp2 as [2nd TimeStamp],
LunchPunch
FROM CTE_1
Join CTE_2 on Name1 = NAME2
WHERE DUP=1
AND Name2 <> ''***



END

Drop table #Punches
Drop table #1stScans

Answer

With a left join, conditions on the right hand table go in the JOIN conditions, not the where clause

Select 
       Name1 as Name,
       Location1 as Location,
       TimeStamp as [1st TimeStamp],
       FirstPunch,
       TimeStamp2 as [2nd TimeStamp],
       LunchPunch 
FROM CTE_1
LEFT Join CTE_2 
    on Name1 = NAME2
    AND DUP=1
    AND Name2 <> ''
Comments