Improve company productivity with a Business Account.Sign Up

x
?
Solved

Sum All Rows

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

579 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