Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Sum All Rows

Posted on 2013-11-25
4
Medium Priority
?
257 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 25

Accepted Solution

by:
chaau earned 2000 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 2000 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

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
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 backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

876 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