Forcing blank columns to be returned in select

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

Forcing blank columns to be returned in select

Author

Topic

mcab21
Starting Member

2 Posts

Posted - 2011-04-24 : 17:37:26

I have a sql select dilemma. I want to pull back a list of totals within a certain set of dates.

The select I use is as follows:

SELECT StartOfWeek,Totals,Name FROM [dbo].[View_Sample]
WHERE StartOfWeek >= '2011-03-27 00:00:00.000' and StartOfWeek <= '2011-04-28 00:00:00.000'

If this returns me the following:

Week Beginning Totals Name
07/03/2011 3277 Sample 1
07/03/2011 193 Sample 2
07/03/2011 52 Sample 3
14/03/2011 3578 Sample 1
14/03/2011 120 Sample 3
21/03/2011 4 Sample 1
21/03/2011 46 Sample 2
21/03/2011 60 Sample 3
28/03/2011 1983 Sample 1
28/03/2011 125 Sample 2
04/04/2011 21 Sample 1

It doesnt always have records for all Sample 1, 2 and 3 for each week beginning. How do I force this so I get the following data returned?

Week Beginning Totals Name
07/03/2011 3277 Sample 1
07/03/2011 193 Sample 2
07/03/2011 52 Sample 3
14/03/2011 3578 Sample 1
14/03/2011 0 Sample 2
14/03/2011 120 Sample 3
21/03/2011 4 Sample 1
21/03/2011 46 Sample 2
21/03/2011 60 Sample 3
28/03/2011 1983 Sample 1
28/03/2011 125 Sample 2
28/03/2011 0 Sample 3
04/04/2011 21 Sample 1
04/04/2011 0 Sample 2
04/04/2011 0 Sample 3

Forcing zero counts where there is no data to return so each week has the same number of records returned (in this case 3 records for each week beginning).

Thanks for the help in advance.

Regards,

mcab21

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-24 : 18:43:12

SELECT wk.StartOfWeek,Totals=coalesce(v.Totals,0),name.Name
FROM (select distint StartOfWeek from [View_Sample] WHERE StartOfWeek >= '2011-03-27 00:00:00.000' and StartOfWeek <= '2011-04-28 00:00:00.000') wk
cross join (select distinct Name from [View_Sample]) name
left join [View_Sample] v
on v.StartOfWeek = wk.StartOfWeek
and v.name = name.name

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mcab21
Starting Member

2 Posts

Posted - 2011-04-25 : 05:35:42

That is absolutely fantastic that is exactly what I needed, thank you so much!

your a sql whizz!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources