[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Row Placeholder

Posted on 2014-03-11
16
Medium Priority
?
734 Views
Last Modified: 2014-03-18
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.
0
Comment
Question by:LCNW
  • 9
  • 4
  • 3
16 Comments
 
LVL 71

Assisted Solution

by:Qlemo
Qlemo earned 1500 total points
ID: 39921723
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
 
LVL 1

Author Comment

by:LCNW
ID: 39921735
Could you elaborate?
0
 
LVL 23

Assisted Solution

by:Steve Wales
Steve Wales earned 500 total points
ID: 39921746
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 1

Author Comment

by:LCNW
ID: 39922032
I've done lots of CTEs, I'll give this a look. Thanks.
0
 
LVL 1

Author Comment

by:LCNW
ID: 39922112
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
 
LVL 23

Expert Comment

by:Steve Wales
ID: 39922148
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
 
LVL 71

Accepted Solution

by:
Qlemo earned 1500 total points
ID: 39922169
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
 
LVL 1

Author Comment

by:LCNW
ID: 39922177
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
 
LVL 23

Expert Comment

by:Steve Wales
ID: 39922179
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
 
LVL 1

Author Comment

by:LCNW
ID: 39922221
Qlemo,

I worked out your code but the hospital name is still NULL.
0
 
LVL 71

Expert Comment

by:Qlemo
ID: 39922287
Sorry. Use y.hospital instead of h.hospital in the outermost select list. That should never be NULL.
0
 
LVL 1

Author Comment

by:LCNW
ID: 39922292
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
 
LVL 1

Author Comment

by:LCNW
ID: 39922422
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
 
LVL 71

Assisted Solution

by:Qlemo
Qlemo earned 1500 total points
ID: 39924451
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
 
LVL 1

Author Comment

by:LCNW
ID: 39924534
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
 
LVL 1

Author Comment

by:LCNW
ID: 39924672
I believe I have it worked out. I'll post the query shortly.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question