Replace a null query with zeros? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Replace a null query with zeros?

Author

Topic

mattboy_slim
Yak Posting Veteran

72 Posts

Posted - 2012-02-12 : 13:12:40

I hope this isn’t an oddball request, because I can’t find any information through Google searches. This may be because my terminology is incorrect, so please bear with me. I can’t imagine that this issue is something that has never been tackled before, and this forum has always been an amazing resource.

Is it possible to replace a null query with zeros? Let me explain a little bit more. Imagine you have a query that pulls billing information for the last 3 weeks for each employee. Now, say you have one employee who has only been employed for one week, so you need the oldest weeks to simply return as a zero, instead of no results?

This is what I currently get, as you would exactly expect:

This is what I would like to get, if possible:

If this isn’t possible, I can live with it. I will just create “x” rows with “zero” values for each new employee. If it is possible though, it would be neat, it just isn’t the end of the world if it isn’t.

Thanks in advance,
Matt

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-12 : 13:30:00

you need to have a calendar table for that.
see an example function below

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

then use it like


SELECT  m.*,COALESCE(t.yourvaluefield,0) AS Value
FROM
(
SELECT Employee,[Date]
FROM dbo.CalendarTable (@StartDate,@EndDate,0,0)f
CROSS JOIN (SELECT DISTINCT Employee FROM Table) e
)m
LEFT JOIN YourTable t
ON t.Employee = m.Employee
AND t.YourDateField = m.[Date]

I've put place holder columns so make sure you replace the code in blue with actual column names

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

mattboy_slim
Yak Posting Veteran

72 Posts

Posted - 2012-02-12 : 14:34:43

That's a bit of a downer. The periods do not currently have dates attached to them (a period is actually two weeks). The knowledge, however, is useful. I might see how this can be adapted in a differnet way.

Thanks,
Matt

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-12 : 14:47:22

quote:
Originally posted by mattboy_slim

That's a bit of a downer. The periods do not currently have dates attached to them (a period is actually two weeks). The knowledge, however, is useful. I might see how this can be adapted in a differnet way.

Thanks,
Matt

ok...let us know how you got on!

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources