Adding Zero Values - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Adding Zero Values

Author

Topic

jamesingamells
Starting Member

11 Posts

Posted - 2013-10-09 : 11:05:45

HI,

I have the below query set up. What i am trying to do is create a line that populates as value to be zero if there is no count of unit in a given month like below. Is this possible?

There are 4 units, so for each month i want 4 lines, but currently if there are no actuals in a month its giving 3 lines.

SELECT
count([Unit]) as Actual,
unit,
[1st_of_month],
last_of_month
FROM [lchs_live].[dbo].[Full_Referrals_Dataset_live]
where RfR1 =
'18 month project'

group by unit,
[1st_of_month],
[last_of_month]

Results
6 NW 2013-08-01 2013-08-31
4 SE 2013-08-01 2013-08-31
5 SW 2013-08-01 2013-08-31

Required Result
6 NW 2013-08-01 2013-08-31
4 SE 2013-08-01 2013-08-31
5 SW 2013-08-01 2013-08-31
0 NE 2013-08-01 2013-08-31

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-10-09 : 11:44:16

Do you have a tables with domain values for Units and the First of the month?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-09 : 13:25:52

Ideally you need to do cross join between unit table and date table and then use that as the base. Then take a LEFT JOIn with your table. I hope you've units and calendar table.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

jamesingamells
Starting Member

11 Posts

Posted - 2013-10-10 : 11:31:11

HI,

yes i have a units table here:

lchs_ref.dbo.FHL_Units

and a calendar table:

[lchs_ref].[dbo].[Date_Lookup].

Unfortunately my skills dont cover cross joins. Any help very much appreciated!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-10 : 14:47:03

[code]
DECLARE @RangeStart datetime,@RangeEnd datetime
SELECT @RangeStart=<value1>,@RangeEnd=<value2>

--Choose value1 and value2 based on dates for which you want output to come for (18 months in your above example)

SELECT m.*,COALESCE(n.Actual,0) AS Actual
FROM
(
SELECT u.Unit,Start,End
FROM lchs_ref.dbo.FHL_Units u
CROSS JOIN (SELECT MIN(DateField) AS Start,MAX(DateField) AS End
FROM [lchs_ref].[dbo].[Date_Lookup]
WHERE dateField BETWEEN @RangeStart AND @RangeEnd
GROUP BY DATEDIFF(mm,0,Datefield)
)d
)m
LEFT JOIN (SELECT
count([Unit]) as Actual,
unit,
[1st_of_month],
last_of_month
FROM [lchs_live].[dbo].[Full_Referrals_Dataset_live]
where RfR1 =
'18 month project'

group by unit,
[1st_of_month],
[last_of_month])n
ON n.unit=m.unit
AND n.[1st_of_month] = m.Start
AND n.[last_of_month] = m.End
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

jamesingamells
Starting Member

11 Posts

Posted - 2013-10-11 : 04:38:04

Hi Visakh16, many thanks for your assistance. I have ammended to have the following, but i am now producing all the lines, but they all have an actual of 0?

DECLARE @RangeStart datetime,@RangeEnd datetime
SELECT @RangeStart='01-apr-2013',@RangeEnd='31-mar-2014'

--Choose value1 and value2 based on dates for which you want output to come for (18 months in your above example)

SELECT m.*,COALESCE(n.Actual,0) AS Actual
FROM
(
SELECT u.Unit,Start,[End]
FROM lchs_ref.dbo.FHL_Units u
CROSS JOIN (SELECT MIN([1st_of_month]) AS Start,MAX([1st_of_month]) AS [End]
FROM [lchs_ref].[dbo].[Date_Lookup]
WHERE [1st_of_month] BETWEEN @RangeStart AND @RangeEnd
GROUP BY DATEDIFF(mm,0,[1st_of_month])
)d
)m
LEFT JOIN (SELECT
count([Unit]) as Actual,
unit,
[1st_of_month],
last_of_month
FROM [lchs_live].[dbo].[Full_Referrals_Dataset_live]
where RfR1 =
'18 month project'

group by unit,
[1st_of_month],
[last_of_month])n
ON n.unit=m.unit
AND n.[1st_of_month] = m.Start
AND n.[last_of_month] = m.[End]
order by 3

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-11 : 08:21:50

does [1st_of_month],
last_of_month fields in [lchs_live].[dbo].[Full_Referrals_Dataset_live] have time part too?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

jamesingamells
Starting Member

11 Posts

Posted - 2013-10-11 : 09:57:52

Hi Vikash,
The data in the referrals table looks like:

1st_of_month last_of_month
2012-08-01 00:00:00.000 2012-08-31 00:00:00.000

James

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-11 : 13:50:56

does dateLookup have records for all days of the month?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

jamesingamells
Starting Member

11 Posts

Posted - 2013-10-25 : 06:42:46

Hi,
Sorry for the late reply and thanks for your help so far.

Yes it does have a record for every day of the month.

James

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-25 : 08:43:53

quote:
Originally posted by jamesingamells

Hi,
Sorry for the late reply and thanks for your help so far.

Yes it does have a record for every day of the month.

James

Only other possibility is [1st_of_month] and [last_of_month] not corresponding to 1st and last day of month

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources