Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Adding Zero Expand / Collapse
Author
Message
Posted Wednesday, October 9, 2013 9:07 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 8, 2013 9:24 AM
Points: 16, Visits: 23
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
Post #1503162
Posted Wednesday, October 9, 2013 9:14 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 10:33 PM
Points: 3,374, Visits: 7,296
You need a Units table or at least a Units set to look for all units. after that the rest is easy, just add a RIGHT JOIN.

SELECT 
count(fr.[Unit]) as Actual,
fr.unit,
[1st_of_month],
last_of_month
FROM [lchs_live].[dbo].[Full_Referrals_Dataset_live] fr
RIGHT JOIN Units u ON fr.unit = u.unit
where RfR1 = '18 month project'
group by fr.unit,
[1st_of_month],
[last_of_month]




Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1503167
Posted Wednesday, October 9, 2013 11:09 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:07 AM
Points: 11,157, Visits: 12,899
Something like this would work:

With units AS
(
Select Distinct
unit
FROM
dbo.Full_Referrals_Dataset_live
)
SELECT
count(FR.[Unit]) as Actual,
U.unit,
FR.[1st_of_month],
FR.[last_of_month]
FROM
units as U LEFT JOIN
dbo.Full_Referrals_Dataset_live as FR
WHERE
FR.RfR1 = '18 month project'
GROUP BY
U.unit,
FR.[1st_of_month],
FR.[last_of_month];





Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1503210
Posted Wednesday, October 9, 2013 7:26 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 10:07 PM
Points: 3,617, Visits: 5,236
Luis Cazares (10/9/2013)
You need a Units table or at least a Units set to look for all units. after that the rest is easy, just add a RIGHT JOIN.

SELECT 
count(fr.[Unit]) as Actual,
fr.unit,
[1st_of_month],
last_of_month
FROM [lchs_live].[dbo].[Full_Referrals_Dataset_live] fr
RIGHT JOIN Units u ON fr.unit = u.unit
where RfR1 = '18 month project'
group by fr.unit,
[1st_of_month],
[last_of_month]



Uh, Luis. Isn't that RIGHT JOIN going to leave you with NULLs in the resulting [1st_of_month], last_of_month columns? And also I think you'd want to refer to u.unit and not fr.unit.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1503346
Posted Wednesday, October 9, 2013 7:43 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 10:07 PM
Points: 3,617, Visits: 5,236
Here is what I propose:

WITH StartingPoint AS
(
--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]

SELECT Actual=6,unit='NW',[1st_of_month]='2013-08-01',last_of_month='2013-08-31'
UNION ALL SELECT 4,'SE','2013-08-01','2013-08-31'
UNION ALL SELECT 5,'SW','2013-08-01','2013-08-31'
),
Units (unit) AS
(
SELECT 'NW' UNION ALL SELECT 'SE' UNION ALL SELECT 'SW' UNION ALL SELECT 'NE'
)
SELECT Actual=ISNULL(Actual, 0), b.unit, a.[1st_of_month], a.last_of_month
FROM
(
SELECT DISTINCT [1st_of_month], last_of_month
FROM StartingPoint a
) a
CROSS JOIN Units b
LEFT JOIN StartingPoint c ON b.unit = c.unit





My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1503347
Posted Thursday, October 10, 2013 9:27 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 8, 2013 9:24 AM
Points: 16, Visits: 23
hi all,

many thanks for your assistance so far. I have created a units table as mentioned called lchs_ref.dbo.FHL_Units. I have tried to replicate what Jack Corbett has suggested but i keep coming up with an error. Here is my adapted query and the error i get. Any suggestions on what i am missing? I have never used the top part of the query before so apologies if its a simple error.


With Units AS
(
Select Distinct
unit
FROM
dbo.Full_Referrals_Dataset_live
)
SELECT
count(FR.[Unit]) as Actual,
U.unit,
FR.[1st_of_month],
FR.[last_of_month]
FROM
lchs_ref.dbo.FHL_Units as U
LEFT JOIN
dbo.Full_Referrals_Dataset_live as FR

WHERE
FR.RfR1 = '18 month project'
GROUP BY
U.unit,
FR.[1st_of_month],
FR.[last_of_month];

Msg 156, Level 15, State 1, Line 18
Incorrect syntax near the keyword 'WHERE'.
Post #1503655
Posted Thursday, October 10, 2013 9:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:07 AM
Points: 11,157, Visits: 12,899
james.ingamells (10/10/2013)
hi all,

many thanks for your assistance so far. I have created a units table as mentioned called lchs_ref.dbo.FHL_Units. I have tried to replicate what Jack Corbett has suggested but i keep coming up with an error. Here is my adapted query and the error i get. Any suggestions on what i am missing? I have never used the top part of the query before so apologies if its a simple error.


With Units AS
(
Select Distinct
unit
FROM
dbo.Full_Referrals_Dataset_live
)
SELECT
count(FR.[Unit]) as Actual,
U.unit,
FR.[1st_of_month],
FR.[last_of_month]
FROM
lchs_ref.dbo.FHL_Units as U
LEFT JOIN
dbo.Full_Referrals_Dataset_live as FR

WHERE
FR.RfR1 = '18 month project'
GROUP BY
U.unit,
FR.[1st_of_month],
FR.[last_of_month];

Msg 156, Level 15, State 1, Line 18
Incorrect syntax near the keyword 'WHERE'.


Oops. My bad. I forgot the ON clause for the JOIN. You need to add ON U.unit = FR.unit before the WHERE.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1503671
Posted Thursday, October 10, 2013 9:47 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 8, 2013 9:24 AM
Points: 16, Visits: 23
THanks Jack,

It now runs, but does not pull the zero values i was hoping for. Do i need to join a calendar table on to it?
Post #1503679
Posted Thursday, October 10, 2013 9:53 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 10:33 PM
Points: 3,374, Visits: 7,296
dwain.c (10/9/2013)
Luis Cazares (10/9/2013)
You need a Units table or at least a Units set to look for all units. after that the rest is easy, just add a RIGHT JOIN.

SELECT 
count(fr.[Unit]) as Actual,
fr.unit,
[1st_of_month],
last_of_month
FROM [lchs_live].[dbo].[Full_Referrals_Dataset_live] fr
RIGHT JOIN Units u ON fr.unit = u.unit
where RfR1 = '18 month project'
group by fr.unit,
[1st_of_month],
[last_of_month]



Uh, Luis. Isn't that RIGHT JOIN going to leave you with NULLs in the resulting [1st_of_month], last_of_month columns? And also I think you'd want to refer to u.unit and not fr.unit.


You're absolutely right, I didn't think the query throughly and got distracted.



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1503682
Posted Thursday, October 10, 2013 6:11 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 10:07 PM
Points: 3,617, Visits: 5,236
james.ingamells (10/10/2013)
THanks Jack,

It now runs, but does not pull the zero values i was hoping for. Do i need to join a calendar table on to it?


Guess you didn't like my try?



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1503837
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse