Solved

SQL Row Placeholder

Posted on 2014-03-11
16
681 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 68

Assisted Solution

by:Qlemo
Qlemo earned 375 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 22

Assisted Solution

by:Steve Wales
Steve Wales earned 125 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
 
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 22

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 68

Accepted Solution

by:
Qlemo earned 375 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 22

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 68

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 68

Assisted Solution

by:Qlemo
Qlemo earned 375 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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

762 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now