SQL Row Placeholder

I have the following query:

SELECT
	
	 DISTINCT o.Name AS [OrgName]
	,MAX(op.ValueText) AS [Classification]
	,MAX(c.FirstName + ' ' + c.LastName) AS [Coordinator]
	,MAX(ISNULL(CASE WHEN DeathR.Month = 1 THEN DeathR.ActualDeaths ELSE 0 END,0)) AS 'Jan'
	,DeathR.Year

FROM
	 dbo.ITX_vw_tbl_DeathReferrals AS DeathR LEFT JOIN
     dbo.ITX_vw_tbl_Organization AS o ON DeathR.OrganizationId = o.Id LEFT JOIN
     dbo.ITX_vw_tbl_ContactOrganizationRelationship AS cor ON o.Id = cor.OrganizationId LEFT JOIN
     dbo.ITX_vw_tbl_Contact AS c ON cor.ContactId = c.Id LEFT JOIN
     dbo.ITX_vw_tbl_OrganizationProfile AS op ON o.Id = op.OrganizationId
     
WHERE   o.Name = 'Alaska Regional Hospital' AND
		op.ValueText in ('A','B','C')
		
GROUP BY
	 o.Name
	,DeathR.Year

Open in new window


It creates 2 rows in the result set:

Alaska Regional Hospital        B      Pete Jones   13      2012
Alaska Regional Hospital        B      Pete Jones   17      2013

I want  placeholder rows for any empty years that would appear as such (3rd Row):

Alaska Regional Hospital        B      Pete Jones   13      2012
Alaska Regional Hospital        B      Pete Jones   17      2013
Alaska Regional Hospital        B      Pete Jones    0      2014

I tried ISNULL but this didn't work as there is no entry for Jan 2014 in the table.
LVL 1
LCNWAsked:
Who is Participating?
 
QlemoConnect With a Mentor DeveloperCommented:
I reckon hard-codeing the "group" value doesn't make much sense for real-life data.

Something like that CTE was on my mind. But to get the necessary results ("groups") we need to permute thru all groups pre joining with the data table.
with yearlist as 
(
    select 2010 as year
    union all
    select yl.year + 1 as year
    from yearlist yl
    where yl.year + 1 <= YEAR(GetDate())
)
select h.hospital, h.value, y.year from 
(select y.year, h.hospital from yearlist y, (select distinct hospital from hospitals) h) y
left join hospitals h on y.year = h.myyear and y.hospital = h.hospital

Open in new window

assuming the table is HOSPITALS for making it more simple, but I'm certain you get the idea. Your query might be best used in another CTE generating the HOSPITALS stuff I refer to.
0
 
QlemoConnect With a Mentor DeveloperCommented:
You will need a table containing all years, or something generating the years, and join with it. Something like
select ... from years left join data on ...
0
 
LCNWAuthor Commented:
Could you elaborate?
0
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
Steve WalesConnect With a Mentor Senior Database AdministratorCommented:
I don't claim to be an expert on CTE's but you could generate a CTE on the fly by doing something like this (you can modify it to change the range of dates generated (so you could specify the start as current year - 10 and end current year +5 or something):

with yearlist as 
(
    select 2004 as year
    union all
    select yl.year + 1 as year
    from yearlist yl
    where yl.year + 1 <= YEAR(GetDate())
)
select year from yearlist

Open in new window


Then, you can use that in your query:

create table mydata (myyear char(4), mytext char(50));
go
insert into mydata values('2013', 'my text');
go
insert into mydata values('2011','my other text');
go
with yearlist as 
(
    select 2004 as year
    union all
    select yl.year + 1 as year
    from yearlist yl
    where yl.year + 1 <= YEAR(GetDate())
)
select a.year, b.mytext from yearlist a 
left join mydata b on a.year = b.myyear

Open in new window


Gives:
year        mytext
----------- ---------------
2004        NULL
2005        NULL
2006        NULL
2007        NULL
2008        NULL
2009        NULL
2010        NULL
2011        my other text                                     
2012        NULL
2013        my text                                           
2014        NULL

(11 row(s) affected)

Open in new window


For full disclosure I had to do a little search for the CTE syntax since I don't use them a whole lot (see here)
0
 
LCNWAuthor Commented:
I've done lots of CTEs, I'll give this a look. Thanks.
0
 
LCNWAuthor Commented:
This works to an extent. My result set looks like this:

NULL                               0      2010
NULL                               0      2011
Alaska Regional Hospital      13      2012
Alaska Regional Hospital      17      2013
NULL                               0      2014

I did INSULL to handle the Jan column. How do I replicate the Name column so it isn't NULL? There are numerous Names I'll be working with.
0
 
Steve WalesSenior Database AdministratorCommented:
Can't you wrap an ISNULL around that too ?  Or do you want it to be the name of the Hospital / other name ?  

I'm afraid I don't have answer for that one - however since you're specifically selecting where name = 'Alaska...', can't you hard code that in the query as a literal for that column ?
0
 
LCNWAuthor Commented:
I want it to be the name of the hospital. I had the hard code in the where to keep the result set smaller.
0
 
Steve WalesSenior Database AdministratorCommented:
Can you modify your query to do something like this ?

Have an "inner" query that returns  your dates and some key lookup column that you can then join on to get the names ?

select name1, name2, name3
from (inner query / result set here)
where (condition)

Not knowing your data it's hard to get specifics - perhaps there's another expert who's more of a developer than me with a bright idea ...

The problem is that since you "master" table on the join is the year, the whole point of an outer join is to return nulls when data isn't found.  If you don't have the data there for a particular year .... then it's missing (NULL).
0
 
LCNWAuthor Commented:
Qlemo,

I worked out your code but the hospital name is still NULL.
0
 
QlemoDeveloperCommented:
Sorry. Use y.hospital instead of h.hospital in the outermost select list. That should never be NULL.
0
 
LCNWAuthor Commented:
Ok. I got it working. Can you explain what this

select y.hospital, h.value, y.year from
(select y.year, h.hospital from yearlist y, (select distinct hospital from hospitals) h) y
left join hospitals h on y.year = h.myyear and y.hospital = h.hospital

is doing?
0
 
LCNWAuthor Commented:
When I add an additonal column of dynamic data it doesn't line up. Perhaps we could close out the question and I could message you Qlemo?
0
 
QlemoConnect With a Mentor DeveloperCommented:
You can always use a subselect as table, as long as you supply (result) names for all columns and an alias for the result table:
select * from (select distinct hospital from hospitals) h

Open in new window

You can write the subselect as a CTE, of course, which is exactly the same:
;with h as (
  select distinct hospital from hospitals
)
select * from h;

Open in new window

Whether you use the former or a CTE is a matter of taste most of the time.
(select y.year, h.hospital from yearlist y, (select distinct hospital from hospitals) h) y

Open in new window

will perform a cross join (no restriction), mixing all years with all hospital names. And so you have a reference table containing both, where we can join in the results of the real data table if found.

Adding a column (derived from the Hospitals CTE/table) should be no problem. But you have to take care of correct grouping, and might need to add something to the join condition. Could you post your current query, and what you would expect as result (short example sufficient)?
0
 
LCNWAuthor Commented:
Sure. Here's my query.
WITH BASEDATA AS (

SELECT DISTINCT 
	 o.Name AS [Facility]
	,op.ValueText AS [Class]
	,c.FirstName + ' ' + c.LastName AS [Coordinator] 
	,dr.[Month] AS [DMonth]
	,dr.[Year] AS [DYear]
	,dr.ActualDeaths AS [ActualDeaths]
	,ISNULL(dr.ReportedDeaths,0) AS [ReportedDeaths]
	,ISNULL(dr.LateReferrals,0) AS [LateReferrals]

FROM
	dbo.ITX_vw_tbl_DeathReferrals AS dr FULL OUTER JOIN
	dbo.ITX_vw_tbl_Organization AS o ON dr.OrganizationId = o.Id FULL OUTER JOIN
	dbo.ITX_vw_tbl_ContactOrganizationRelationship AS cor ON o.Id = cor.OrganizationId FULL OUTER JOIN
	dbo.ITX_vw_tbl_Contact AS c ON cor.ContactId = c.Id FULL OUTER JOIN
	dbo.ITX_vw_tbl_OrganizationProfile AS op ON o.Id = op.OrganizationId
     
WHERE   o.Name = 'Alaska Regional Hospital' AND
		op.ValueText in ('A','B','C')
		
),

-------------------------------------------------------------------------------------------------------------

YEARLIST AS (
	 
    SELECT 2010 AS [Year]
    UNION ALL
    SELECT yl.[Year] + 1 AS [Year]
    FROM YEARLIST yl
    WHERE yl.[Year] + 1 <= YEAR(GetDate())
)

-------------------------------------------------------------------------------------------------------------

SELECT y.Class, y.Facility, y.Coordinator, y.[Year], y.DMonth
FROM (SELECT yl.[Year], f.Facility, f.Class, f.Coordinator, f.DMonth
FROM yearlist yl,
	 
	  
(SELECT DISTINCT Facility, Class ,Coordinator, DMonth
FROM BASEDATA) AS f) AS y LEFT JOIN
BASEDATA BD on y.year = BD.DYear
			
GROUP BY y.Facility, y.Class, y.[Year], y.DMonth, y.Coordinator

Open in new window

Here's the result:
B	Alaska Regional Hospital	Mary Dawson	2013	1
B	Alaska Regional Hospital	Mary Dawson	2013	2
B	Alaska Regional Hospital	Mary Dawson	2013	3
B	Alaska Regional Hospital	Mary Dawson	2013	4
B	Alaska Regional Hospital	Mary Dawson	2013	5
B	Alaska Regional Hospital	Mary Dawson	2013	6
B	Alaska Regional Hospital	Mary Dawson	2013	7
B	Alaska Regional Hospital	Mary Dawson	2013	8
B	Alaska Regional Hospital	Mary Dawson	2013	9
B	Alaska Regional Hospital	Mary Dawson	2013	10
B	Alaska Regional Hospital	Mary Dawson	2013	11
B	Alaska Regional Hospital	Mary Dawson	2013	12
B	Alaska Regional Hospital	Mary Dawson	2014	1
B	Alaska Regional Hospital	Mary Dawson	2014	2
B	Alaska Regional Hospital	Mary Dawson	2014	3
B	Alaska Regional Hospital	Mary Dawson	2014	4
B	Alaska Regional Hospital	Mary Dawson	2014	5
B	Alaska Regional Hospital	Mary Dawson	2014	6
B	Alaska Regional Hospital	Mary Dawson	2014	7
B	Alaska Regional Hospital	Mary Dawson	2014	8
B	Alaska Regional Hospital	Mary Dawson	2014	9
B	Alaska Regional Hospital	Mary Dawson	2014	10
B	Alaska Regional Hospital	Mary Dawson	2014	11
B	Alaska Regional Hospital	Mary Dawson	2014	12

Open in new window

I've added a few columns and they worked, but when I try to add the column [ActualDeaths] The results are skewed as  such:
B	Alaska Regional Hospital	Mary Dawson	2013	1	13
B	Alaska Regional Hospital	Mary Dawson	2013	1	14
B	Alaska Regional Hospital	Mary Dawson	2013	1	17
B	Alaska Regional Hospital	Mary Dawson	2014	1	13
B	Alaska Regional Hospital	Mary Dawson	2014	1	14
B	Alaska Regional Hospital	Mary Dawson	2014	1	17
B	Alaska Regional Hospital	Mary Dawson	2013	2	12
B	Alaska Regional Hospital	Mary Dawson	2013	2	15
B	Alaska Regional Hospital	Mary Dawson	2013	2	8
B	Alaska Regional Hospital	Mary Dawson	2014	2	12
B	Alaska Regional Hospital	Mary Dawson	2014	2	15
B	Alaska Regional Hospital	Mary Dawson	2014	2	8
B	Alaska Regional Hospital	Mary Dawson	2013	3	14
B	Alaska Regional Hospital	Mary Dawson	2013	3	15
B	Alaska Regional Hospital	Mary Dawson	2014	3	14
B	Alaska Regional Hospital	Mary Dawson	2014	3	15
B	Alaska Regional Hospital	Mary Dawson	2013	4	14
B	Alaska Regional Hospital	Mary Dawson	2013	4	6
B	Alaska Regional Hospital	Mary Dawson	2014	4	14
B	Alaska Regional Hospital	Mary Dawson	2014	4	6
B	Alaska Regional Hospital	Mary Dawson	2013	5	13
B	Alaska Regional Hospital	Mary Dawson	2013	5	7
B	Alaska Regional Hospital	Mary Dawson	2014	5	13
B	Alaska Regional Hospital	Mary Dawson	2014	5	7
B	Alaska Regional Hospital	Mary Dawson	2013	6	13
B	Alaska Regional Hospital	Mary Dawson	2013	6	15
B	Alaska Regional Hospital	Mary Dawson	2014	6	13
B	Alaska Regional Hospital	Mary Dawson	2014	6	15
B	Alaska Regional Hospital	Mary Dawson	2013	7	11
B	Alaska Regional Hospital	Mary Dawson	2013	7	14
B	Alaska Regional Hospital	Mary Dawson	2014	7	11
B	Alaska Regional Hospital	Mary Dawson	2014	7	14
B	Alaska Regional Hospital	Mary Dawson	2013	8	15
B	Alaska Regional Hospital	Mary Dawson	2013	8	9
B	Alaska Regional Hospital	Mary Dawson	2014	8	15
B	Alaska Regional Hospital	Mary Dawson	2014	8	9
B	Alaska Regional Hospital	Mary Dawson	2013	9	14
B	Alaska Regional Hospital	Mary Dawson	2013	9	17
B	Alaska Regional Hospital	Mary Dawson	2014	9	14
B	Alaska Regional Hospital	Mary Dawson	2014	9	17
B	Alaska Regional Hospital	Mary Dawson	2013	10	14
B	Alaska Regional Hospital	Mary Dawson	2014	10	14
B	Alaska Regional Hospital	Mary Dawson	2013	11	18
B	Alaska Regional Hospital	Mary Dawson	2013	11	8
B	Alaska Regional Hospital	Mary Dawson	2014	11	18
B	Alaska Regional Hospital	Mary Dawson	2014	11	8
B	Alaska Regional Hospital	Mary Dawson	2013	12	16
B	Alaska Regional Hospital	Mary Dawson	2013	12	17
B	Alaska Regional Hospital	Mary Dawson	2014	12	16
B	Alaska Regional Hospital	Mary Dawson	2014	12	17

Open in new window

Those results are not correct. It should only display 24 rows with appropriate numbers, which these are not.

Here's what my adjusted query looks like:
WITH BASEDATA AS (

SELECT DISTINCT 
	 o.Name AS [Facility]
	,op.ValueText AS [Class]
	,c.FirstName + ' ' + c.LastName AS [Coordinator] 
	,dr.[Month] AS [DMonth]
	,dr.[Year] AS [DYear]
	,dr.ActualDeaths AS [ActualDeaths]
	,ISNULL(dr.ReportedDeaths,0) AS [ReportedDeaths]
	,ISNULL(dr.LateReferrals,0) AS [LateReferrals]

FROM
	dbo.ITX_vw_tbl_DeathReferrals AS dr FULL OUTER JOIN
	dbo.ITX_vw_tbl_Organization AS o ON dr.OrganizationId = o.Id FULL OUTER JOIN
	dbo.ITX_vw_tbl_ContactOrganizationRelationship AS cor ON o.Id = cor.OrganizationId FULL OUTER JOIN
	dbo.ITX_vw_tbl_Contact AS c ON cor.ContactId = c.Id FULL OUTER JOIN
	dbo.ITX_vw_tbl_OrganizationProfile AS op ON o.Id = op.OrganizationId
     
WHERE   o.Name = 'Alaska Regional Hospital' AND
		op.ValueText in ('A','B','C') 
		
),

-------------------------------------------------------------------------------------------------------------

YEARLIST AS (
	 
    SELECT 2013 AS [Year]
    UNION ALL
    SELECT yl.[Year] + 1 AS [Year]
    FROM YEARLIST yl
    WHERE yl.[Year] + 1 <= YEAR(GetUTCDate())
)

-------------------------------------------------------------------------------------------------------------

SELECT y.Class, y.Facility, y.Coordinator, y.[Year], y.DMonth, y.ActualDeaths
FROM (SELECT yl.[Year], f.Facility, f.Class, f.Coordinator, f.DMonth, f.ActualDeaths
FROM yearlist yl,
	 
	  
(SELECT DISTINCT Facility, Class ,Coordinator, DMonth, ActualDeaths
FROM BASEDATA) AS f) AS y LEFT JOIN
BASEDATA BD on y.year = BD.DYear
			
GROUP BY y.Facility, y.Class, y.Coordinator, y.DMonth, y.[Year], y.ActualDeaths
		

Open in new window

If I can get this going for the one hospital, I'll remove the 'WHERE' restriction to allow all of them.

I'm feeling like maybe I should PIVOT out the Months and then display the Actual Deaths somehow.
0
 
LCNWAuthor Commented:
I believe I have it worked out. I'll post the query shortly.
0
All Courses

From novice to tech pro — start learning today.