Hi, I have an existing query with ambulances and how many patient drops each day for the month. I need to add a total column at the end for each of the row. I also need to add a total row for each of the colmns.
thank you for your help!
this is my query:
(DATEPART(DAY, t1.[PatientVisitDate])) AS 'DAY',
sum(case when t1.AmbulanceName = 'AI' then 1 end) as 'Amb1',
sum(case when t1.AmbulanceName = 'AM' then 1 end) as 'Amb2',
sum(case when t1.AmbulanceName = 'AS' then 1 end) as 'Amb3',
sum(case when t1.AmbulanceName = 'BE' then 1 end) as 'Amb4',
sum(case when t1.AmbulanceName = 'BR' then 1 end) as 'Amb5',
sum(case when t1.AmbulanceName = 'CI' then 1 end) as 'Amb6',
sum(case when t1.AmbulanceName = 'CO' then 1 end) as 'Amb7',
sum(case when t1.AmbulanceName = 'EM' then 1 end) as 'Amb8',
sum(case when t1.AmbulanceName = 'FR' then 1 end) as 'Amb9',
sum(case when t1.AmbulanceName = 'HA' then 1 end) as 'Amb10',
sum(case when t1.AmbulanceName = 'HB' then 1 end) as 'Amb11',
sum(case when t1.AmbulanceName = 'HC' then 1 end) as 'Amb12',
sum(case when t1.AmbulanceName = 'HF' then 1 end) as 'Amb13',
sum(case when t1.AmbulanceName = 'HG' then 1 end) as 'Amb14',
sum(case when t1.AmbulanceName = 'HH' then 1 end) as 'Amb15',
sum(case when t1.AmbulanceName = 'HP' then 1 end) as 'Amb16',
sum(case when t1.AmbulanceName = 'HR' then 1 end) as 'Amb17',
sum(case when t1.AmbulanceName = 'HS' then 1 end) as 'Amb18',
sum(case when t1.AmbulanceName = 'HW' then 1 end) as 'Amb19',
sum(case when t1.AmbulanceName = 'IN' then 1 end) as 'Amb20',
sum(case when t1.AmbulanceName = 'KB' then 1 end) as 'Amb21',
sum(case when t1.AmbulanceName = 'LU' then 1 end) as 'Amb22',
sum(case when t1.AmbulanceName = 'MA' then 1 end) as 'Amb23',
sum(case when t1.AmbulanceName = 'ME' then 1 end) as 'Amb24',
sum(case when t1.AmbulanceName = 'MI' then 1 end) as 'Amb25',
sum(case when t1.AmbulanceName = 'MV' then 1 end) as 'Amb26',
sum(case when t1.AmbulanceName = 'NA' then 1 end) as 'Amb27',
sum(case when t1.AmbulanceName = 'NP' then 1 end) as 'Amb28',
sum(case when t1.AmbulanceName = 'RI' then 1 end) as 'Amb29',
sum(case when t1.AmbulanceName = 'SC' then 1 end) as 'Amb30',
sum(case when t1.AmbulanceName = 'TR' then 1 end) as 'Amb31',
sum(case when t1.AmbulanceName = 'UN' then 1 end) as 'UAmb32',
sum(case when t1.AmbulanceName = 'VI' then 1 end) as 'Amb33',
sum(case when t1.AmbulanceName = 'ZZ' then 1 end) as 'Amb34'
from
[Visit_History] t1
WHERE
t1.PatientVisit >= DATEADD(DAY,1,EOMONTH(GETDATE(),-1)) AND
t1.PatientVisit < CAST(CAST(GETDATE() AS DATE) AS DATETIME)
