LCNW
asked on
Sum All Rows
I have the following SP:
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.
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
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks, this helped.
ASKER
Conversion failed when converting date and/or time from character string.