Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
52326 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
52326 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
52326 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
52326 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
52326 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  
 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