Solved

Sum All Rows

Posted on 2013-11-25
4
241 Views
Last Modified: 2013-11-25
I have the following SP:

WITH OrgansByTxC AS (
      SELECT
            p.CrossClampedOn,
            DATEPART(yyyy,p.CrossClampedOn) AS 'Year', 
            dbo.fx_OrgIsExport(ot.TransplantOrgName) AS 'Facility',
            dbo.fx_Organ_SimpleName(ot.OrganName) AS 'OrganType',
            dbo.fx_Organ_Count(ot.OrganName) AS 'OrganCount',
            ot.DispositionOutcomeDesc AS 'Outcome'
            
      FROM
            LifeCenterReporting.dbo.rpt_patient p
      INNER JOIN
            LifeCenterReporting.dbo.rpt_organ_transplant AS ot ON p.PatientId = ot.PatientId
), CTE1 AS (
SELECT  p.CrossClampedOn, 
        DATEPART(yyyy,p.CrossClampedOn) AS 'Year',
		Facility,
          ISNULL(Heart,0) AS 'Heart'
         ,ISNULL(Intestine,0) AS 'Intestine'
         ,ISNULL(Kidney,0) AS 'Kidney'
         ,ISNULL(Liver,0) AS 'Liver'
         ,ISNULL(Lung,0) AS 'Lung'
         ,ISNULL(Pancreas,0) AS 'Pancreas'
FROM
      OrgansByTxC
      PIVOT(SUM(OrganCount) FOR OrganType IN (Heart,Intestine,Kidney,Liver,Lung,Pancreas)) P
WHERE Year >= 2009 AND Outcome = 'Transplanted')

SELECT CrossClampedOn,[Year],Facility,Heart,Intestine,Kidney,Liver,Lung,Pancreas
  FROM CTE1
 UNION ALL
SELECT CrossClampedOn,[Year],'Totals' Facility,Heart,Intestine,Kidney,Liver,Lung,Pancreas
  FROM CTE1 
 GROUP BY CrossClampedOn,[Year],Heart,Intestine,Kidney,Liver,Lung,Pancreas

Open in new window


I've been tasked to get an overall total for each facility for each year of all organtypes. So, not broken apart. Can that be done within this code or will I need to create something separate?

Thanks.
0
Comment
Question by:LCNW
  • 2
  • 2
4 Comments
 
LVL 24

Accepted Solution

by:
chaau earned 500 total points
ID: 39676089
Yes, it is possible. What about the sorting? Are you going to sort it inside the program, or you want it in the stored procedure? This code is doing the totals you need without the sorting:
WITH OrgansByTxC AS (
      SELECT
            p.CrossClampedOn,
            DATEPART(yyyy,p.CrossClampedOn) AS 'Year', 
            dbo.fx_OrgIsExport(ot.TransplantOrgName) AS 'Facility',
            dbo.fx_Organ_SimpleName(ot.OrganName) AS 'OrganType',
            dbo.fx_Organ_Count(ot.OrganName) AS 'OrganCount',
            ot.DispositionOutcomeDesc AS 'Outcome'
            
      FROM
            LifeCenterReporting.dbo.rpt_patient p
      INNER JOIN
            LifeCenterReporting.dbo.rpt_organ_transplant AS ot ON p.PatientId = ot.PatientId
), CTE1 AS (
SELECT  p.CrossClampedOn, 
        DATEPART(yyyy,p.CrossClampedOn) AS 'Year',
		Facility,
          ISNULL(Heart,0) AS 'Heart'
         ,ISNULL(Intestine,0) AS 'Intestine'
         ,ISNULL(Kidney,0) AS 'Kidney'
         ,ISNULL(Liver,0) AS 'Liver'
         ,ISNULL(Lung,0) AS 'Lung'
         ,ISNULL(Pancreas,0) AS 'Pancreas'
FROM
      OrgansByTxC
      PIVOT(SUM(OrganCount) FOR OrganType IN (Heart,Intestine,Kidney,Liver,Lung,Pancreas)) P
WHERE Year >= 2009 AND Outcome = 'Transplanted')

SELECT CrossClampedOn,[Year],Facility,Heart,Intestine,Kidney,Liver,Lung,Pancreas
  FROM CTE1
 UNION ALL
SELECT CrossClampedOn,[Year],'Totals' Facility,Heart,Intestine,Kidney,Liver,Lung,Pancreas
  FROM CTE1 
 GROUP BY CrossClampedOn,[Year],Heart,Intestine,Kidney,Liver,Lung,Pancreas
 UNION ALL
SELECT 'Overall Totals' CrossClampedOn, [Year], Facility, SUM(Heart) Heart, SUM(Intestine) Intestine, SUM(Kidney) Kidney, SUM(Liver) Liver, SUM(Lung) Lung, SUM(Pancreas) Pancreas
  FROM CTE1 
 GROUP BY [Year],Facility

Open in new window

And this one is with the proper sorting (but with an extra column at the back, that can be ignored)
WITH OrgansByTxC AS (
      SELECT
            p.CrossClampedOn,
            DATEPART(yyyy,p.CrossClampedOn) AS 'Year', 
            dbo.fx_OrgIsExport(ot.TransplantOrgName) AS 'Facility',
            dbo.fx_Organ_SimpleName(ot.OrganName) AS 'OrganType',
            dbo.fx_Organ_Count(ot.OrganName) AS 'OrganCount',
            ot.DispositionOutcomeDesc AS 'Outcome'
            
      FROM
            LifeCenterReporting.dbo.rpt_patient p
      INNER JOIN
            LifeCenterReporting.dbo.rpt_organ_transplant AS ot ON p.PatientId = ot.PatientId
), CTE1 AS (
SELECT  p.CrossClampedOn, 
        DATEPART(yyyy,p.CrossClampedOn) AS 'Year',
		Facility,
          ISNULL(Heart,0) AS 'Heart'
         ,ISNULL(Intestine,0) AS 'Intestine'
         ,ISNULL(Kidney,0) AS 'Kidney'
         ,ISNULL(Liver,0) AS 'Liver'
         ,ISNULL(Lung,0) AS 'Lung'
         ,ISNULL(Pancreas,0) AS 'Pancreas'
FROM
      OrgansByTxC
      PIVOT(SUM(OrganCount) FOR OrganType IN (Heart,Intestine,Kidney,Liver,Lung,Pancreas)) P
WHERE Year >= 2009 AND Outcome = 'Transplanted')
SELECT CrossClampedOn,[Year],Facility,Heart,Intestine,Kidney,Liver,Lung,Pancreas FROM
(SELECT CrossClampedOn,[Year],Facility,Heart,Intestine,Kidney,Liver,Lung,Pancreas, 1 as SortOrder
  FROM CTE1
 UNION ALL
SELECT CrossClampedOn,[Year],'Totals' Facility,Heart,Intestine,Kidney,Liver,Lung,Pancreas, 3 as SortOrder
  FROM CTE1 
 GROUP BY CrossClampedOn,[Year],Heart,Intestine,Kidney,Liver,Lung,Pancreas
 UNION ALL
SELECT 'Overall Totals' CrossClampedOn, [Year], Facility, SUM(Heart) Heart, SUM(Intestine) Intestine, SUM(Kidney) Kidney, SUM(Liver) Liver, SUM(Lung) Lung, SUM(Pancreas) Pancreas, 2 as SortOrder
  FROM CTE1 
 GROUP BY [Year],Facility) x
ORDER BY SortOrder, CrossClampedOn,[Year],Facility

Open in new window

0
 
LVL 1

Author Comment

by:LCNW
ID: 39676212
I'm getting a conversion error:

Conversion failed when converting date and/or time from character string.
0
 
LVL 24

Assisted Solution

by:chaau
chaau earned 500 total points
ID: 39676219
OK then. I expected something like this:
Replace then this line in both of my examples
SELECT 'Overall Totals' CrossClampedOn, [Year], Facility, SUM(Heart) Heart, SUM(Intestine) Intestine, SUM(Kidney) Kidney, SUM(Liver) Liver, SUM(Lung) Lung, SUM(Pancreas) Pancreas, 2 as SortOrder

Open in new window

to
SELECT NULL CrossClampedOn, [Year], Facility, SUM(Heart) Heart, SUM(Intestine) Intestine, SUM(Kidney) Kidney, SUM(Liver) Liver, SUM(Lung) Lung, SUM(Pancreas) Pancreas, 2 as SortOrder

Open in new window

You just need to know that when there is an empty "CrossClampedOn" column, it is the Overall total
0
 
LVL 1

Author Closing Comment

by:LCNW
ID: 39676683
Thanks, this helped.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

911 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

16 Experts available now in Live!

Get 1:1 Help Now