Solved

Sum All Rows

Posted on 2013-11-25
4
247 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 25

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 25

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

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

710 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