November 3, 2011 at 11:32 am
Hi Guys,
So here's a question: When I add an extra WHERE clause in my query, the result set excludes rows with NULLS. Why does that happen and how do I fix my query?
Query
SELECT Calendar.CalendarDate, TimeTracking.UserName, SUM(TimeTracking.TimeLength) AS TotalTime
FROM Calendar LEFT JOIN
TimeTracking ON Calendar.CalendarDate = TimeTracking.Date1
WHERE (Calendar.CalendarDate BETWEEN '01/01/2011' AND '11/03/2011') AND (TimeTracking.UserName = 'someguy')
GROUP BY Calendar.CalendarDate, TimeTracking.UserName
HAVING (SUM(TimeTracking.TimeLength) < 390) OR (SUM(TimeTracking.TimeLength) IS NULL)
ORDER BY CalendarDate
When I remove 'AND (TimeTracking.UserName = 'someguy')' the result set includes NULLs again. Why does this happen and how do I fix this query?
Thanks in advance!
Allan
November 3, 2011 at 11:41 am
Is that column from the table you're left-joining to?
If so, it needs to go in Join On clause, not the Where clause.
Otherwise, in the outer table with no match in the left-join table won't have a value in that column, and the Where clause says they have to.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 3, 2011 at 11:43 am
Can you provide us sample of your data ( not your original data, but a mock-up)
November 3, 2011 at 11:47 am
Can't sum a NULL... SUM(ISNULL(columnName,0)) nevertheless I assume that TimeTracking.UserName = 'someguy' filter contains no nulls in that column.
Jared
Jared
CE - Microsoft
November 3, 2011 at 11:51 am
allan.madriaga (11/3/2011)
Hi Guys,So here's a question: When I add an extra WHERE clause in my query, the result set excludes rows with NULLS. Why does that happen and how do I fix my query?
Query
SELECT Calendar.CalendarDate, TimeTracking.UserName, SUM(TimeTracking.TimeLength) AS TotalTime
FROM Calendar LEFT JOIN
TimeTracking ON Calendar.CalendarDate = TimeTracking.Date1
WHERE (Calendar.CalendarDate BETWEEN '01/01/2011' AND '11/03/2011') AND (TimeTracking.UserName = 'someguy')
GROUP BY Calendar.CalendarDate, TimeTracking.UserName
HAVING (SUM(TimeTracking.TimeLength) < 390) OR (SUM(TimeTracking.TimeLength) IS NULL)
ORDER BY CalendarDate
When I remove 'AND (TimeTracking.UserName = 'someguy')' the result set includes NULLs again. Why does this happen and how do I fix this query?
Thanks in advance!
Allan
Which field(s) contain NULLs? It sounds like you have some records where UserName is null but when you add the where clause they go away???
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 3, 2011 at 11:58 am
Sean Lange (11/3/2011)
allan.madriaga (11/3/2011)
Hi Guys,So here's a question: When I add an extra WHERE clause in my query, the result set excludes rows with NULLS. Why does that happen and how do I fix my query?
Query
SELECT Calendar.CalendarDate, TimeTracking.UserName, SUM(TimeTracking.TimeLength) AS TotalTime
FROM Calendar LEFT JOIN
TimeTracking ON Calendar.CalendarDate = TimeTracking.Date1
WHERE (Calendar.CalendarDate BETWEEN '01/01/2011' AND '11/03/2011') AND (TimeTracking.UserName = 'someguy')
GROUP BY Calendar.CalendarDate, TimeTracking.UserName
HAVING (SUM(TimeTracking.TimeLength) < 390) OR (SUM(TimeTracking.TimeLength) IS NULL)
ORDER BY CalendarDate
When I remove 'AND (TimeTracking.UserName = 'someguy')' the result set includes NULLs again. Why does this happen and how do I fix this query?
Thanks in advance!
Allan
Which field(s) contain NULLs? It sounds like you have some records where UserName is null but when you add the where clause they go away???
TimeTracking.TimeLength must have the nulls as it is in the HAVING clause...
Do:
SELECT Calendar.CalendarDate, TimeTracking.UserName, TimeTracking.TimeLength
FROM Calendar LEFT JOIN
TimeTracking ON Calendar.CalendarDate = TimeTracking.Date1
WHERE (Calendar.CalendarDate BETWEEN '01/01/2011' AND '11/03/2011') AND (TimeTracking.UserName = 'someguy')
AND TimeTracking.TimeLength IS NULL
ORDER BY CalendarDate
My guess is it will return 0 rows.
Jared
Jared
CE - Microsoft
November 3, 2011 at 12:59 pm
Yes, you are correct. There will be rows in the Left Table but none on the right.
Basically, what I have is an auxiliary table called Calendar which contains rows of all the dates in the calendar year. My second table called TimeTracking contains rows from users who input their hours on a day to day basis. My query needs to find any 'SUM(TimeTracking.TimeLength)' that are less than 390 minutes (any people who have entered less than 6.5 hours a day). This should include NULLs (any days where people forget to enter their times). That is why I have Calendar LEFT JOIN TimeTracking.
I've been working on this for the past 2 days and I'm sure I'm missing something.
November 3, 2011 at 1:02 pm
Then as suggested you need to add your additional filter to the join condition. Otherwise it will only return those rows where there is a match.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 3, 2011 at 1:04 pm
EDIT:
You can't sum a NULL... That is your problem I think.
SELECT 1 + NULLReturns NULL. You must do a case or an SUM(ISNULL(Timething.timecolumn,0))
Sorry... Losing it... Don't look at that...
Jared
Jared
CE - Microsoft
November 3, 2011 at 1:12 pm
Not sure if you got the jist of what others said so I will give another answer.
In your WHERE clause, you have the following:
AND (TimeTracking.UserName = 'someguy')
The WHERE clause filters are applied AFTER the LEFT OUTER JOIN, and since TimeTracking.UserName is the in the table on the RIGHT, you are filtering for all records after the join in which this column has the value 'someguy' (whoever that may be).
November 3, 2011 at 1:13 pm
allan.madriaga (11/3/2011)
Yes, you are correct. There will be rows in the Left Table but none on the right.Basically, what I have is an auxiliary table called Calendar which contains rows of all the dates in the calendar year. My second table called TimeTracking contains rows from users who input their hours on a day to day basis. My query needs to find any 'SUM(TimeTracking.TimeLength)' that are less than 390 minutes (any people who have entered less than 6.5 hours a day). This should include NULLs (any days where people forget to enter their times). That is why I have Calendar LEFT JOIN TimeTracking.
I've been working on this for the past 2 days and I'm sure I'm missing something.
Like this:
SELECT Calendar.CalendarDate, TimeTracking.UserName, SUM(TimeTracking.TimeLength) AS TotalTime
FROM Calendar LEFT JOIN
TimeTracking ON Calendar.CalendarDate = TimeTracking.Date1 AND TimeTracking.UserName = 'someguy'
WHERE (Calendar.CalendarDate BETWEEN '01/01/2011' AND '11/03/2011')
GROUP BY Calendar.CalendarDate, TimeTracking.UserName
HAVING (SUM(TimeTracking.TimeLength) < 390) OR (SUM(TimeTracking.TimeLength) IS NULL)
ORDER BY CalendarDate
Thanks,
Jared
Jared
CE - Microsoft
November 3, 2011 at 1:14 pm
Hi Jared. The sum of NULLs actually do appear if I take out the following where clause:
WHERE ... AND (TimeTracking.UserName = 'someguy')
But when I insert that where clause back into the query it takes out the NULLs.
November 3, 2011 at 1:23 pm
allan.madriaga (11/3/2011)
Hi Jared. The sum of NULLs actually do appear if I take out the following where clause:WHERE ... AND (TimeTracking.UserName = 'someguy')
But when I insert that where clause back into the query it takes out the NULLs.
Disregard my sum of nulls... It has been a long night/morning with deployments. Look at the query I last posted. It sums up what everyone has been saying.
Jared
Jared
CE - Microsoft
November 3, 2011 at 1:26 pm
allan.madriaga (11/3/2011)
Hi Jared. The sum of NULLs actually do appear if I take out the following where clause:WHERE ... AND (TimeTracking.UserName = 'someguy')
But when I insert that where clause back into the query it takes out the NULLs.
Read my post above for another take on what is happening.
November 3, 2011 at 1:27 pm
Thanks for all your responses guys. Thanks for the clarification Lynn, I understand now. That makes sense.
Now...how do I work around this.......hmmm....I need to include 'someguy' in the where clause. Maybe I can talk my supervisor out of narrowing it down to 'someguy' and just show everyone. 😀
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply