Inserting Zero data Rows to the query result

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

SQL Server 2005 Forums

Transact-SQL (2005)

Inserting Zero data Rows to the query result

Author

Topic

coagulance
Yak Posting Veteran

78 Posts

Posted - 2010-06-03 : 08:39:34

Hi ,

The below query gives me the results when there are CastIDs for a ProductionDay , I would like to have the results indicate for days of the month when there is no production as '0'.

[Code]

Declare
@dtProductionDay datetime

set @dtProductionDay = GETDATE()-45

-- Define the CTE expression name and column list.
;WITH Casts_CTE (CastID, ProductionDay, ProductionMonth)
AS
-- Define the CTE query.
(
SELECT DISTINCT CastID, ProductionDay, ProductionMonth
FROM List_Jobs
WHERE CastID IS NOT NULL
)
-- Define the outer query referencing the CTE name.
SELECT COUNT(CastID) AS Total, ProductionDay AS Dy,ProductionMonth AS Mn
FROM Casts_CTE
WHERE ProductionMonth = DATEPART(MONTH,@dtProductionDay)
GROUP BY ProductionMonth,ProductionDay
ORDER BY ProductionMonth,ProductionDay ;
GO

Result:

Total Dy Mn
25 1 4
15 3 4
7 4 4
25 5 4
17 6 4
1 7 4
33 8 4
13 9 4
10 10 4
15 12 4
1 13 4
15 14 4
1 15 4

Result Needed:
Total Dy Mn
25 1 4
0 2 4
15 3 4
7 4 4
25 5 4
17 6 4
1 7 4
33 8 4
13 9 4
10 10 4
15 12 4
1 13 4
15 14 4
1 15 4
0 16 4
0 17 4
....SO ON
0 30 4
[/CODE]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-06-05 : 07:20:35

you need to have a calendar table for that. left join your table with it and use ISNULL to convert null values to 0

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources