Display Null Months - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Display Null Months

Author

Topic

bielen
Yak Posting Veteran

97 Posts

Posted - 2010-03-12 : 11:04:12

I'm trying to display null months for chart data. I tried inserting the second outer join to group by the available months, but the overall output never changed.

SELECT DISTINCT tsk.TASK_NAME, COUNT(*) AS total, MONTH(ts.WORK_DT) AS workMonth, YEAR(ts.WORK_DT) AS workYear, SUM(ts.TS_HOURS) AS total_hours FROM DCSC_TIMESHEET_DATA ts
LEFT OUTER JOIN DCSC_TASK tsk ON ts.TASK_NO = tsk.TASK_NO AND ts.PROJ_NO = tsk.PROJ_NO AND ts.PROJ_NO IN (10774, 10775)

LEFT OUTER JOIN (SELECT MONTH(WORK_DT) AS pdrMonth, YEAR(WORK_DT) AS pdrYear FROM DCSC_TIMESHEET_DATA WHERE (PROJ_NO IN (10774, 10775)) AND (WORK_DT <= '12/31/2009')
GROUP BY MONTH(WORK_DT), YEAR(WORK_DT)) monthList ON MONTH(ts.WORK_DT) = monthList.pdrMonth

WHERE (ts.PROJ_NO IN (10774, 10775)) AND (ts.WORK_DT <= '12/31/2009')
GROUP BY tsk.TASK_NAME, MONTH(ts.WORK_DT), YEAR(ts.WORK_DT)
ORDER BY tsk.TASK_NAME, m.workYear, m.workMonth

Sample output is: I want "Item 2" to display 0 for months 6-10:

TASK_NAME Total workMonth workYear total_hours
Item 1 9 6 2009 38.5
Item 1 97 7 2009 408.75
Item 1 62 8 2009 234.5
Item 1 62 9 2009 199.5
Item 1 90 10 2009 242.6
Item 1 88 11 2009 198.95
Item 1 78 12 2009 224.1
Item 2 61 11 2009 127
Item 2 22 12 2009 37

Any suggestions. I also tried isNull and COALESCE, but can't get it to display the results properly.

Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-12 : 11:16:33

may be this?


SELECT tsk.TASK_NAME, COUNT(*) AS total, monthList.pdrMonth, monthList.pdrYear, SUM(ts.TS_HOURS) AS total_hours FROM (SELECT MONTH(WORK_DT) AS pdrMonth, YEAR(WORK_DT) AS pdrYear FROM DCSC_TIMESHEET_DATA 
WHERE (PROJ_NO IN (10774, 10775)) AND (WORK_DT <= '12/31/2009') 
GROUP BY MONTH(WORK_DT), YEAR(WORK_DT)) monthList
LEFT OUTER JOIN DCSC_TIMESHEET_DATA ts 
ON MONTH(ts.WORK_DT) = monthList.pdrMonth 
AND YEAR(ts.WORK_DT)= monthList.pdrYear 
LEFT OUTER JOIN DCSC_TASK tsk 
ON ts.TASK_NO = tsk.TASK_NO 
AND ts.PROJ_NO = tsk.PROJ_NO 
GROUP BY tsk.TASK_NAME, monthList.pdrYear, monthList.pdrMonth 
ORDER BY tsk.TASK_NAME, monthList.pdrYear, monthList.pdrMonth

EDIT: cleared messed up code
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bielen
Yak Posting Veteran

97 Posts

Posted - 2010-03-12 : 11:53:59

I'm not clear what you are doing with the following. It gives syntax error by having the two stacked GROUP BYs.

GROUP BY tsk.TASK_NAME, WHERE (ts.PROJ_NO IN (10774, 10775)) AND (ts.WORK_DT <= '12/31/2009')
GROUP BY tsk.TASK_NAME, MONTH(ts.WORK_DT), YEAR(ts.WORK_DT)
ORDER BY tsk.TASK_NAME, m.workYear, m.workMonth, WHERE (ts.PROJ_NO IN (10774, 10775)) AND (ts.WORK_DT <= '12/31/2009')

My code without the group bys is:

SELECT tsk.TASK_NAME, COUNT(*) AS total, monthList.pdrMonth, monthList.pdrYear, SUM(ts.TS_HOURS) AS total_hours
FROM (SELECT MONTH(WORK_DT) AS pdrMonth, YEAR(WORK_DT) AS pdrYear
FROM DCSC_TIMESHEET_DATA
WHERE (PROJ_NO IN (10774, 10775)) AND (WORK_DT <= '12/31/2009')
GROUP BY MONTH(WORK_DT), YEAR(WORK_DT)) monthList LEFT OUTER JOIN
DCSC_TIMESHEET_DATA ts ON MONTH(ts.WORK_DT) = monthList.pdrMonth AND YEAR(ts.WORK_DT) = monthList.pdrYear LEFT OUTER JOIN
DCSC_TASK tsk ON ts.TASK_NO = tsk.TASK_NO AND ts.PROJ_NO = tsk.PROJ_NO AND ts.PROJ_NO IN (10774, 10775)
WHERE (ts.PROJ_NO IN (10774, 10775)) AND (ts.WORK_DT <= '12/31/2009')
GROUP BY tsk.TASK_NAME, monthList.pdrMonth, monthList.pdrYear
ORDER BY tsk.TASK_NAME, monthList.pdrYear, monthList.pdrMonth

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-12 : 11:59:03

quote:
Originally posted by bielen

I'm not clear what you are doing with the following. It gives syntax error by having the two stacked GROUP BYs.

GROUP BY tsk.TASK_NAME, WHERE (ts.PROJ_NO IN (10774, 10775)) AND (ts.WORK_DT <= '12/31/2009')
GROUP BY tsk.TASK_NAME, MONTH(ts.WORK_DT), YEAR(ts.WORK_DT)
ORDER BY tsk.TASK_NAME, m.workYear, m.workMonth, WHERE (ts.PROJ_NO IN (10774, 10775)) AND (ts.WORK_DT <= '12/31/2009')

My code without the group bys is:

SELECT tsk.TASK_NAME, COUNT(*) AS total, monthList.pdrMonth, monthList.pdrYear, SUM(ts.TS_HOURS) AS total_hours
FROM (SELECT MONTH(WORK_DT) AS pdrMonth, YEAR(WORK_DT) AS pdrYear
FROM DCSC_TIMESHEET_DATA
WHERE (PROJ_NO IN (10774, 10775)) AND (WORK_DT <= '12/31/2009')
GROUP BY MONTH(WORK_DT), YEAR(WORK_DT)) monthList LEFT OUTER JOIN
DCSC_TIMESHEET_DATA ts ON MONTH(ts.WORK_DT) = monthList.pdrMonth AND YEAR(ts.WORK_DT) = monthList.pdrYear LEFT OUTER JOIN
DCSC_TASK tsk ON ts.TASK_NO = tsk.TASK_NO AND ts.PROJ_NO = tsk.PROJ_NO AND ts.PROJ_NO IN (10774, 10775)
WHERE (ts.PROJ_NO IN (10774, 10775)) AND (ts.WORK_DT <= '12/31/2009')
GROUP BY tsk.TASK_NAME, monthList.pdrMonth, monthList.pdrYear
ORDER BY tsk.TASK_NAME, monthList.pdrYear, monthList.pdrMonth

sorry i messed it up while copy pasted. I've edited last suggestion

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bielen
Yak Posting Veteran

97 Posts

Posted - 2010-03-12 : 12:52:01

Viskah:

Thanks for your help, but the output is exactly the same. I added an additional where clause:

SELECT DISTINCT
tsk.TASK_NAME, COUNT(*) AS total, MONTH(ts.WORK_DT) AS workMonth, YEAR(ts.WORK_DT) AS workYear, SUM(ts.TS_HOURS) AS total_hours
FROM DCSC_TIMESHEET_DATA ts LEFT OUTER JOIN
DCSC_TASK tsk ON ts.TASK_NO = tsk.TASK_NO AND ts.PROJ_NO = tsk.PROJ_NO AND ts.PROJ_NO IN (10774, 10775) LEFT OUTER JOIN
(SELECT MONTH(WORK_DT) AS pdrMonth, YEAR(WORK_DT) AS pdrYear
FROM DCSC_TIMESHEET_DATA
WHERE (PROJ_NO IN (10774, 10775)) AND (WORK_DT <= '12/31/2009')
GROUP BY MONTH(WORK_DT), YEAR(WORK_DT)) monthList ON MONTH(ts.WORK_DT) = monthList.pdrMonth
WHERE (ts.PROJ_NO IN (10774, 10775)) AND (ts.WORK_DT <= '12/31/2009')
GROUP BY tsk.TASK_NAME, MONTH(ts.WORK_DT), YEAR(ts.WORK_DT)
ORDER BY tsk.TASK_NAME, m.workYear, m.workMonth

NULL value months do not appear in the listing. Shouldn't I be using isNull OR COALESCE to assign a zero value to the row?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-12 : 12:58:16

quote:
Originally posted by bielen

Viskah:

Thanks for your help, but the output is exactly the same. I added an additional where clause:

SELECT DISTINCT
tsk.TASK_NAME, COUNT(*) AS total, MONTH(ts.WORK_DT) AS workMonth, YEAR(ts.WORK_DT) AS workYear, SUM(ts.TS_HOURS) AS total_hours
FROM DCSC_TIMESHEET_DATA ts LEFT OUTER JOIN
DCSC_TASK tsk ON ts.TASK_NO = tsk.TASK_NO AND ts.PROJ_NO = tsk.PROJ_NO AND ts.PROJ_NO IN (10774, 10775) LEFT OUTER JOIN
(SELECT MONTH(WORK_DT) AS pdrMonth, YEAR(WORK_DT) AS pdrYear
FROM DCSC_TIMESHEET_DATA
WHERE (PROJ_NO IN (10774, 10775)) AND (WORK_DT <= '12/31/2009')
GROUP BY MONTH(WORK_DT), YEAR(WORK_DT)) monthList ON MONTH(ts.WORK_DT) = monthList.pdrMonth
WHERE (ts.PROJ_NO IN (10774, 10775)) AND (ts.WORK_DT <= '12/31/2009')
GROUP BY tsk.TASK_NAME, MONTH(ts.WORK_DT), YEAR(ts.WORK_DT)
ORDER BY tsk.TASK_NAME, m.workYear, m.workMonth

NULL value months do not appear in the listing. Shouldn't I be using isNull OR COALESCE to assign a zero value to the row?

the extra where clause is culprit. remove it. how do you think it will return NULL value when you put condition ts.PROJ_NO IN (10774, 10775)) AND (ts.WORK_DT <= '12/31/2009') as those fields also have NULL values in that case

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bielen
Yak Posting Veteran

97 Posts

Posted - 2010-03-12 : 13:13:09

Even without the WHERE clause, NULL values are not being returned. I'm going to try a different approach at this point. Thanks for your help. It is appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-12 : 13:26:16

you're not using it in same order. see my suggestion in 03/12/2010 : 11:16:33 you should start from MonthList and then left join others with it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bielen
Yak Posting Veteran

97 Posts

Posted - 2010-03-12 : 13:30:56

That's the script I am using. I can copy/paste and it runs. The output appears at:

Item 1 2.0 1 9 2009 2
Item 2 2 8 2009 7
Item 3 23 6 2009 214
Item 3 6 7 2009 48
Item 3 7 8 2009 56
Item 3 12 9 2009 96
Item 3 17 10 2009 130
Item 3 8 11 2009 59
Item 3 7 12 2009 56

Item 1 and Item 2 should have the same number rows as Item 3.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-12 : 13:35:00

show your used query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bielen
Yak Posting Veteran

97 Posts

Posted - 2010-03-12 : 13:39:49

Here you go:

SELECT tsk.TASK_NAME, COUNT(*) AS total, monthList.pdrMonth, monthList.pdrYear, SUM(ts.TS_HOURS) AS total_hours
FROM (SELECT MONTH(WORK_DT) AS pdrMonth, YEAR(WORK_DT) AS pdrYear
FROM DCSC_TIMESHEET_DATA
WHERE (PROJ_NO IN (10774, 10775)) AND (WORK_DT <= '12/31/2009')
GROUP BY MONTH(WORK_DT), YEAR(WORK_DT)) monthList LEFT OUTER JOIN
DCSC_TIMESHEET_DATA ts ON MONTH(ts.WORK_DT) = monthList.pdrMonth AND YEAR(ts.WORK_DT) = monthList.pdrYear LEFT OUTER JOIN
DCSC_TASK tsk ON ts.TASK_NO = tsk.TASK_NO AND ts.PROJ_NO = tsk.PROJ_NO
GROUP BY tsk.TASK_NAME, monthList.pdrYear, monthList.pdrMonth
ORDER BY tsk.TASK_NAME, monthList.pdrYear, monthList.pdrMonth

bielen
Yak Posting Veteran

97 Posts

Posted - 2010-03-12 : 14:58:50

Viskah:

I'm still not getting it using a cross join approach I found. I have the following two queries. I've been trying everything to cross join them using the following example:

http://weblogs.sqlteam.com/jeffs/archive/2005/09/12/7755.aspx

SELECT MONTH(WORK_DT) AS pdrMonth, YEAR(WORK_DT) AS pdrYear
FROM DCSC_TIMESHEET_DATA
WHERE (PROJ_NO IN (10774, 10775)) AND (WORK_DT <= '12/31/2009')
GROUP BY MONTH(WORK_DT), YEAR(WORK_DT)
ORDER BY pdrYear, pdrMonth

The output is:

pdrMonth pdrYear
6 2009
7 2009
8 2009
9 2009
10 2009
11 2009
12 2009

SELECT tsk.TASK_NAME, MONTH(ts.WORK_DT) AS workMonth, YEAR(ts.WORK_DT) AS workYear, SUM(ts.TS_HOURS) AS total_hours
FROM DCSC_TIMESHEET_DATA ts LEFT OUTER JOIN
DCSC_TASK tsk ON ts.TASK_NO = tsk.TASK_NO AND ts.PROJ_NO = tsk.PROJ_NO AND ts.PROJ_NO IN (10774, 10775)
WHERE (ts.PROJ_NO IN (10774, 10775)) AND (ts.WORK_DT <= '12/31/2009')
GROUP BY tsk.TASK_NAME, MONTH(ts.WORK_DT), YEAR(ts.WORK_DT)
ORDER BY tsk.TASK_NAME, m.workYear, m.workMonth

The output is:

Item 1 7 2009 146.45
Item 1 8 2009 844.7
Item 1 9 2009 724
Item 1 10 2009 634.6
Item 1 11 2009 716.4
Item 1 12 2009 538
Item 2 6 2009 38.5
Item 2 7 2009 408.75
Item 2 8 2009 234.5
Item 2 9 2009 199.5
Item 2 10 2009 242.6
Item 2 11 2009 198.95
Item 2 12 2009 224.1
Item 3 12 2009 37
Item 4 7 2009 28
Item 4 8 2009 27.5
Item 4 9 2009 16
Item 4 10 2009 27
Item 4 11 2009 46.5

I'm trying to JOIN/CROSS JOIN both tables so the output appears as equal months. (7 rows per item in this example).

Item 1 6 2009 0
Item 1 7 2009 146.45
Item 1 8 2009 844.7
Item 1 9 2009 724
Item 1 10 2009 634.6
Item 1 11 2009 716.4
Item 1 12 2009 538
Item 2 6 2009 38.5
Item 2 7 2009 408.75
Item 2 8 2009 234.5
Item 2 9 2009 199.5
Item 2 10 2009 242.6
Item 2 11 2009 198.95
Item 2 12 2009 224.1
Item 3 6 2009 0
Item 3 7 2009 0
Item 3 8 2009 0
Item 3 9 2009 0
Item 3 10 2009 0
Item 3 11 2009 0
Item 3 12 2009 37
Item 4 7 2009 0
Item 4 7 2009 28
Item 4 8 2009 27.5
Item 4 9 2009 16
Item 4 10 2009 27
Item 4 11 2009 46.5
Item 4 12 2009 0

Any suggestion or is the above approach a better method to try to get working?

Thanks for your time.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-13 : 01:14:13

[code]
SELECT tsk.TASK_NAME, MONTH(ts.WORK_DT) AS workMonth, YEAR(ts.WORK_DT) AS workYear, SUM(ts.TS_HOURS) AS total_hours INTO #Temp
FROM DCSC_TIMESHEET_DATA ts LEFT OUTER JOIN
DCSC_TASK tsk ON ts.TASK_NO = tsk.TASK_NO AND ts.PROJ_NO = tsk.PROJ_NO AND ts.PROJ_NO IN (10774, 10775)
WHERE (ts.PROJ_NO IN (10774, 10775)) AND (ts.WORK_DT <= '12/31/2009')
GROUP BY tsk.TASK_NAME, MONTH(ts.WORK_DT), YEAR(ts.WORK_DT)

SELECT r.TASK_NAME, r.pdrYear, r.pdrMonth,
COALESCE(tmp.total_hours,0) AS total_hours
FROM
(
SELECT t.TASK_NAME,cal.pdrMonth,cal.pdrYear
FROM (SELECT DISTINCT tsk.TASK_NAME FROM #Temp)t
CROSS JOIN
(
SELECT MONTH(WORK_DT) AS pdrMonth, YEAR(WORK_DT) AS pdrYear
FROM DCSC_TIMESHEET_DATA
WHERE (PROJ_NO IN (10774, 10775)) AND (WORK_DT <= '12/31/2009')
GROUP BY MONTH(WORK_DT), YEAR(WORK_DT)
)cal
)r
LEFT JOIN #Temp tmp
ON tmp.TASK_NAME=r.TASK_NAME
AND r.pdrMonth=tmp.workMonth
AND r.pdrYear=tmp.workYear
ORDER BY r.TASK_NAME, r.pdrYear, r.pdrMonth

DROP TABLE #Temp
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bielen
Yak Posting Veteran

97 Posts

Posted - 2010-03-15 : 14:03:55

Viskah:

Thank you. I now have it fully working. I made some updates and now have it working as a stored procedure. Here's the final output.

CREATE PROCEDURE sp_GetChartData (@IDs varchar(500), @snapshot_date datetime) AS

SELECT tsk.TASK_NAME, MONTH(ts.WORK_DT) AS pdrMonth, YEAR(ts.WORK_DT) AS pdrYear, SUM(ts.TS_HOURS) AS total_hours INTO #TempChartData
FROM DCSC_TIMESHEET_DATA ts LEFT OUTER JOIN
DCSC_TASK tsk ON ts.TASK_NO = tsk.TASK_NO AND ts.PROJ_NO = tsk.PROJ_NO
WHERE (ts.PROJ_NO IN (Select ID From fnSplitter(@IDs))) AND (ts.WORK_DT <= @snapshot_date)
GROUP BY tsk.TASK_NAME, MONTH(ts.WORK_DT), YEAR(ts.WORK_DT)

SELECT r.TASK_NAME, r.workYear, r.workMonth,
COALESCE(tmp.total_hours,0) AS total_hours
FROM
(
SELECT t.TASK_NAME,cal.workMonth,cal.workYear
FROM (SELECT DISTINCT TASK_NAME FROM #TempChartData)t
CROSS JOIN
(
SELECT MONTH(WORK_DT) AS workMonth, YEAR(WORK_DT) AS workYear
FROM DCSC_TIMESHEET_DATA
WHERE (PROJ_NO IN (Select ID From fnSplitter(@IDs))) AND (WORK_DT <= @snapshot_date)
GROUP BY MONTH(WORK_DT), YEAR(WORK_DT)
)cal
)r
LEFT JOIN #TempChartData tmp
ON tmp.TASK_NAME=r.TASK_NAME
AND r.workMonth=tmp.pdrMonth
AND r.workYear=tmp.pdrYear
ORDER BY r.TASK_NAME, r.workYear, r.workMonth

DROP TABLE #TempChartData
GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-15 : 14:06:17

cool
you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources