SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Adding Zero Values
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jamesingamells
Starting Member

11 Posts

Posted - 10/09/2013 :  11:05:45  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 10/09/2013 :  11:44:16  Show Profile  Reply with Quote
Do you have a tables with domain values for Units and the First of the month?

Edited by - Lamprey on 10/09/2013 11:45:31
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/09/2013 :  13:25:52  Show Profile  Reply with Quote
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
Go to Top of Page

jamesingamells
Starting Member

11 Posts

Posted - 10/10/2013 :  11:31:11  Show Profile  Reply with Quote
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!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/10/2013 :  14:47:03  Show Profile  Reply with Quote

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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

jamesingamells
Starting Member

11 Posts

Posted - 10/11/2013 :  04:38:04  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/11/2013 :  08:21:50  Show Profile  Reply with Quote
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
Go to Top of Page

jamesingamells
Starting Member

11 Posts

Posted - 10/11/2013 :  09:57:52  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/11/2013 :  13:50:56  Show Profile  Reply with Quote
does dateLookup have records for all days of the month?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

jamesingamells
Starting Member

11 Posts

Posted - 10/25/2013 :  06:42:46  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/25/2013 :  08:43:53  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000