Help with a Subquery

1

Thanks for looking at this. Subqueries always kick my ass. I think I have them and then poof, the logic escapes me. Here's what I'm attempting to accomplish. I'm looking for all hourly users that didn't have a time ticket today. Below is my query that shows all the hourly users.

SELECT DISTINCT 
ah.Employee_Name AS Employee_Name
FROM
dbo.Time_Ticket tt LEFT OUTER JOIN
dbo.Attendance_Header ah ON tt.Attendance_Header_ID = ah.Attendance_Header_ID LEFT OUTER 
JOIN dbo.Employee_Code ec ON ah.Employee_Code = ec.Employee_Code LEFT OUTER JOIN 
dbo.Rate r ON ec.Employee_Code_ID = r.Employee_Code_ID
WHERE
r.Hourly_Rate  <>  0

Here's what will get me all the hourly users that have a time ticket today.

SELECT DISTINCT 
ah.Employee_Name AS Employee_Name
FROM
dbo.Time_Ticket tt LEFT OUTER JOIN
dbo.Attendance_Header ah ON tt.Attendance_Header_ID = ah.Attendance_Header_ID LEFT OUTER 
JOIN dbo.Employee_Code ec ON ah.Employee_Code = ec.Employee_Code LEFT OUTER JOIN 
dbo.Rate r ON ec.Employee_Code_ID = r.Employee_Code_ID
WHERE
FORMAT(tt.Time_Start, 'd', 'en-us')  =  '{%Current Date%}' AND
r.Hourly_Rate  <>  0

I need to compare the two and show the users in the first query that AREN'T in the second query.

Any help would be appreciated.

Thanks

2

hi

Except will give you ...

SELECT DISTINCT 
ah.Employee_Name AS Employee_Name
FROM
dbo.Time_Ticket tt LEFT OUTER JOIN
dbo.Attendance_Header ah ON tt.Attendance_Header_ID = ah.Attendance_Header_ID LEFT OUTER 
JOIN dbo.Employee_Code ec ON ah.Employee_Code = ec.Employee_Code LEFT OUTER JOIN 
dbo.Rate r ON ec.Employee_Code_ID = r.Employee_Code_ID
WHERE
r.Hourly_Rate  <>  0

    except 

SELECT DISTINCT 
ah.Employee_Name AS Employee_Name
FROM
dbo.Time_Ticket tt LEFT OUTER JOIN
dbo.Attendance_Header ah ON tt.Attendance_Header_ID = ah.Attendance_Header_ID LEFT OUTER 
JOIN dbo.Employee_Code ec ON ah.Employee_Code = ec.Employee_Code LEFT OUTER JOIN 
dbo.Rate r ON ec.Employee_Code_ID = r.Employee_Code_ID
WHERE
FORMAT(tt.Time_Start, 'd', 'en-us')  =  '{%Current Date%}' AND
r.Hourly_Rate  <>  0
3

Also
not in
will give you

SELECT 
    DISTINCT ah.Employee_Name AS Employee_Name
FROM
    dbo.Time_Ticket tt 
	    LEFT OUTER JOIN
    dbo.Attendance_Header ah 
	    ON tt.Attendance_Header_ID = ah.Attendance_Header_ID LEFT OUTER 
        JOIN 
	dbo.Employee_Code ec 
	    ON ah.Employee_Code = ec.Employee_Code 
		LEFT OUTER JOIN 
    dbo.Rate r 
	    ON ec.Employee_Code_ID = r.Employee_Code_ID
WHERE
    r.Hourly_Rate  <>  0

AND     
   ah.Employee_Name  NOT IN 
	(
	SELECT 
	    DISTINCT ah.Employee_Name AS Employee_Name
	FROM
	    dbo.Time_Ticket tt 
            LEFT OUTER JOIN
	    dbo.Attendance_Header ah 
		    ON tt.Attendance_Header_ID = ah.Attendance_Header_ID 
		    LEFT OUTER JOIN 
	    dbo.Employee_Code ec ON ah.Employee_Code = ec.Employee_Code 
		    LEFT OUTER JOIN 
	    dbo.Rate r ON ec.Employee_Code_ID = r.Employee_Code_ID
	WHERE
	      FORMAT(tt.Time_Start, 'd', 'en-us')  =  '{%Current Date%}' 
		      AND
	      r.Hourly_Rate  <>  0
	)
4

Don't convert dates to char to compare. It's huge overhead because it prevents index seeks, not to mention the overhead of the conversion itself.

Do this comparison instead to find today's datetime:

WHERE
tt.Time_Start >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) AND
tt.Time_start < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) + 1, 0) AND
r.Hourly_Rate  <>  0
1 Like