add 0 values for missing date - 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

add 0 values for missing date

Author

Topic

learning_grsql
Posting Yak Master

230 Posts

Posted - 2012-09-02 : 11:15:07

I have a table similar to below


Date  | qty1 | qty2 | qty3 | 
1 Aug | 33   | 0    |  0   | 
1 Aug | 0    | 20   | 0    | 
1 Aug | 18   | 0    | 0    |
3 Aug | 0    | 0    | 0    | 
3 Aug | 23   | 0    |  0   | 
4 Aug | 0    | 40   | 0    | 
4 Aug | 57   | 0    | 0    | 
7 Aug | 0    | 12   | 0    |

I want to have result similar to below in sql server 2005 using pivot
or any other query


 Date  | qty1 | qty2 | qty3 | 
 1 Aug | 51   | 20   | 0    | 
 2 Aug | 0    | 0    | 0    |
 3 Aug | 23   | 0    | 0    |
 4 Aug | 57   | 40   | 0    | 
 5 Aug | 0    | 0    | 0    | 
 6 Aug | 0    | 0    | 0    | 
 7 Aug | 0    | 12   | 0    |

I want it to put automatically 0 values for missing date as above for 2nd, 5th and 6th)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-02 : 12:04:36

do you've calendar table? if not you need to generate one.


SELECT f.[Date],
SUM(t.qty1) AS qty1,
SUM(t.qty2) AS qty2,
SUM(t.qty3) AS qty3
FROM dbo.CalendarTable('20000101','20121231',0,0)f
LEFT JOIN YourTable t
ON t.datefield = f.[Date]
ORDER BY f.[Date]

calendar date can be seen from below link

http://visakhm.blogspot.com/2010/02/generating-calendar-table.html

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

learning_grsql
Posting Yak Master

230 Posts

Posted - 2012-09-02 : 14:12:16

Thanks visakh16...
but it's not working in my case. It's still not displaying missing dates.

Here is my exact code and I have two more fields which I want to specify in where conditions as below.


SELECT f.[Date],
SUM(t.qty1) AS qty1,
SUM(t.qty2) AS qty2,
SUM(t.qty3) AS qty3
FROM dbo.CalendarTable('20000101','20121231',0,0)f
LEFT JOIN YourTable t
ON t.datefield = f.[Date]
where field1 = 'xyz' and field2 = 'red'
ORDER BY f.[Date]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-02 : 15:10:47

quote:
Originally posted by learning_grsql

Thanks visakh16...
but it's not working in my case. It's still not displaying missing dates.

Here is my exact code and I have two more fields which I want to specify in where conditions as below.

SELECT f.[Date],
SUM(t.qty1) AS qty1,
SUM(t.qty2) AS qty2,
SUM(t.qty3) AS qty3
FROM dbo.CalendarTable('20000101','20121231',0,0)f
LEFT JOIN YourTable t
ON t.datefield = f.[Date]
where field1 = 'xyz' and field2 = 'red'
ORDER BY f.[Date]

the reason is your where. make it like below and see


SELECT f.[Date],
SUM(t.qty1) AS qty1,
SUM(t.qty2) AS qty2,
SUM(t.qty3) AS qty3
FROM dbo.CalendarTable('20000101','20121231',0,0)f
LEFT JOIN YourTable t
ON t.datefield = f.[Date]
AND field1 = 'xyz' 
AND field2 = 'red'
ORDER BY f.[Date]

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-02 : 15:12:40

see the reason here

http://weblogs.sqlteam.com/jeffs/archive/2007/05/14/criteria-on-outer-joined-tables.aspx

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

learning_grsql
Posting Yak Master

230 Posts

Posted - 2012-09-03 : 03:05:31

Thanks visakh16 again. It works great.
But i'm not sure where dbo.calendartable is created after i executed the calendar function in your blog. I cannot find it under table objects in my database.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-09-03 : 04:39:56

it is a function. Look under function

KH
[spoiler]Time is always against us[/spoiler]

learning_grsql
Posting Yak Master

230 Posts

Posted - 2012-09-03 : 06:53:04

Thanks. i have found it under "function".

functions in sql is new to me

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-03 : 15:10:25

quote:
Originally posted by learning_grsql

Thanks. i have found it under "function".

functions in sql is new to me

learn about different types of functions here

http://www.sqlteam.com/article/user-defined-functions

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources