October 9, 2013 at 9:07 am
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
October 9, 2013 at 9:14 am
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]
October 9, 2013 at 11:09 am
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
Consultant - Straight Path Solutions
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
October 9, 2013 at 7:26 pm
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.
SELECTcount(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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 9, 2013 at 7:43 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 10, 2013 at 9:27 am
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'.
October 10, 2013 at 9:40 am
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
Consultant - Straight Path Solutions
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
October 10, 2013 at 9:47 am
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?
October 10, 2013 at 9:53 am
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.
SELECTcount(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. :hehe:
October 10, 2013 at 6:11 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 11, 2013 at 1:51 am
Hi Dwain,
I did have a go at your attempt, however if I want to be able to use this on a monthly basis i would have to do the manual insert statement every time??
James
October 11, 2013 at 2:10 am
james.ingamells (10/11/2013)
Hi Dwain,I did have a go at your attempt, however if I want to be able to use this on a monthly basis i would have to do the manual insert statement every time??
James
Not sure what you mean by the manual insert as mine doesn't do any inserts.
I took your original code and embedded it in the first CTE. You should uncomment that and use it instead of the SELECT/UNION ALL SELECT query inside that CTE (you said that's what your query produced).
The rest should stand pretty much on its own.
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 11, 2013 at 2:31 am
HI Dwain,
Sorry to be a pain, but i am struggling to make this work and i know its probably quite simple. Here is what i have changed and the results attached it produces:
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]
--union all
--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
October 11, 2013 at 3:32 am
That looks like my query but I don't see any results attached.
BTW. There is the possibility that mine might not return what you expect if your base query returns more projects than in your sample data.
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply