pivot table, calculated rows?

Hi,
Using SQL Server 2000 and I have a pivot table query that I'd now like to
total the rows making a calculated column. I've done this before on other
queries but can't seem to get it to work now. Any suggestions? Here's my
query as it stands now: (if you see something that could be improved in my
code feel free to suggest.)
SELECT p1.patient_id, p1.Patient,
SUM(CASE WHEN transaction_date BETWEEN (GETDATE() - 30) AND
(GETDATE()) THEN balance ELSE 0 END) AS R0,
SUM(CASE WHEN transaction_date BETWEEN (GETDATE() - 60) AND
(GETDATE() - 31) THEN balance ELSE 0 END) AS R30,
SUM(CASE WHEN transaction_date BETWEEN (GETDATE() - 90) AND
(GETDATE() - 61) THEN balance ELSE 0 END) AS R60,
SUM(CASE WHEN transaction_date BETWEEN (GETDATE() - 120) AND
(GETDATE() - 91) THEN balance ELSE 0 END) AS R90,
SUM(CASE WHEN transaction_date BETWEEN (GETDATE() - 150) AND
(GETDATE() - 121) THEN balance ELSE 0 END) AS R120,
SUM(CASE WHEN transaction_date BETWEEN (GETDATE() - 180) AND
(GETDATE() - 151) THEN balance ELSE 0 END) AS R150,
SUM(CASE WHEN transaction_date < (GETDATE() - 181)
THEN balance ELSE 0 END) AS R180
FROM
(SELECT DISTINCT claim.claim_number, pt.patient_id AS 'Patient_ID',
RTRIM(pt.last_name) + ', ' + ISNULL(RTRIM(pt.first_name), ' ') AS 'Patient',
amount.Balance,
( select max(posted_transaction_date)
from claim as c1
left join charge_on_claim as coc on coc.claim_sys_id = c1.sys_id
left join chg_item on chg_item.sys_id = coc.chg_item_sys_id
where c1.claim_number = claim.claim_number ) as 'Transaction_Date',
claim.status
FROM claim
LEFT JOIN ar_detail ON ar_detail.claim_sys_id = claim.sys_id
LEFT JOIN pt_policy ON pt_policy.sys_id = ar_detail.pt_policy_sys_id
LEFT JOIN patient AS pt ON pt.patient_id = pt_policy.patient_id
LEFT JOIN (SELECT claim.claim_number, SUM(amount) AS 'Balance'
FROM claim
LEFT JOIN ar_detail ON ar_detail.claim_sys_id = claim.sys_id
group by claim.claim_number) AS Amount ON Amount.claim_number =
claim.claim_number
WHERE amount.balance is not null) AS P1
GROUP BY patient_id, patient
ORDER BY p1.patient
I'd like to add another column which is the total of R0 - R180. If I simply
add this line to the selection:
, (R0 + R30 + R60 + R90 + R120 + R150 + R180) AS 'Total'
I get "Invalid column name 'R0'." error messages for each column. Hope that
makes sense.
Thanks,
LinnLinn Kubler,
You can use your query as a derived table.
select r0,...,r180, r0 +...+r180 as total
from ("here put your query") as t
or you can duplicate the code that calculates each r? column.
SELECT p1.patient_id, p1.Patient,
SUM(CASE WHEN transaction_date BETWEEN (GETDATE() - 30) AND
(GETDATE()) THEN balance ELSE 0 END) AS R0,
SUM(CASE WHEN transaction_date BETWEEN (GETDATE() - 60) AND
(GETDATE() - 31) THEN balance ELSE 0 END) AS R30,
SUM(CASE WHEN transaction_date BETWEEN (GETDATE() - 90) AND
(GETDATE() - 61) THEN balance ELSE 0 END) AS R60,
SUM(CASE WHEN transaction_date BETWEEN (GETDATE() - 120) AND
(GETDATE() - 91) THEN balance ELSE 0 END) AS R90,
SUM(CASE WHEN transaction_date BETWEEN (GETDATE() - 150) AND
(GETDATE() - 121) THEN balance ELSE 0 END) AS R120,
SUM(CASE WHEN transaction_date BETWEEN (GETDATE() - 180) AND
(GETDATE() - 151) THEN balance ELSE 0 END) AS R150,
SUM(CASE WHEN transaction_date < (GETDATE() - 181)
THEN balance ELSE 0 END) AS R180,
(
SUM(CASE WHEN transaction_date BETWEEN (GETDATE() - 30) AND
(GETDATE()) THEN balance ELSE 0 END) +
... +
SUM(CASE WHEN transaction_date < (GETDATE() - 181)
THEN balance ELSE 0 END)
) as total
...
AMB
"Linn Kubler" wrote:

> Hi,
> Using SQL Server 2000 and I have a pivot table query that I'd now like to
> total the rows making a calculated column. I've done this before on other
> queries but can't seem to get it to work now. Any suggestions? Here's my
> query as it stands now: (if you see something that could be improved in m
y
> code feel free to suggest.)
> SELECT p1.patient_id, p1.Patient,
> SUM(CASE WHEN transaction_date BETWEEN (GETDATE() - 30) AND
> (GETDATE()) THEN balance ELSE 0 END) AS R0,
> SUM(CASE WHEN transaction_date BETWEEN (GETDATE() - 60) AND
> (GETDATE() - 31) THEN balance ELSE 0 END) AS R30,
> SUM(CASE WHEN transaction_date BETWEEN (GETDATE() - 90) AND
> (GETDATE() - 61) THEN balance ELSE 0 END) AS R60,
> SUM(CASE WHEN transaction_date BETWEEN (GETDATE() - 120) AND
> (GETDATE() - 91) THEN balance ELSE 0 END) AS R90,
> SUM(CASE WHEN transaction_date BETWEEN (GETDATE() - 150) AND
> (GETDATE() - 121) THEN balance ELSE 0 END) AS R120,
> SUM(CASE WHEN transaction_date BETWEEN (GETDATE() - 180) AND
> (GETDATE() - 151) THEN balance ELSE 0 END) AS R150,
> SUM(CASE WHEN transaction_date < (GETDATE() - 181)
> THEN balance ELSE 0 END) AS R180
> FROM
> (SELECT DISTINCT claim.claim_number, pt.patient_id AS 'Patient_ID',
> RTRIM(pt.last_name) + ', ' + ISNULL(RTRIM(pt.first_name), ' ') AS 'Patient
',
> amount.Balance,
> ( select max(posted_transaction_date)
> from claim as c1
> left join charge_on_claim as coc on coc.claim_sys_id = c1.sys_id
> left join chg_item on chg_item.sys_id = coc.chg_item_sys_id
> where c1.claim_number = claim.claim_number ) as 'Transaction_Date',
> claim.status
> FROM claim
> LEFT JOIN ar_detail ON ar_detail.claim_sys_id = claim.sys_id
> LEFT JOIN pt_policy ON pt_policy.sys_id = ar_detail.pt_policy_sys_id
> LEFT JOIN patient AS pt ON pt.patient_id = pt_policy.patient_id
> LEFT JOIN (SELECT claim.claim_number, SUM(amount) AS 'Balance'
> FROM claim
> LEFT JOIN ar_detail ON ar_detail.claim_sys_id = claim.sys_id
> group by claim.claim_number) AS Amount ON Amount.claim_number =
> claim.claim_number
> WHERE amount.balance is not null) AS P1
> GROUP BY patient_id, patient
> ORDER BY p1.patient
> I'd like to add another column which is the total of R0 - R180. If I simp
ly
> add this line to the selection:
> , (R0 + R30 + R60 + R90 + R120 + R150 + R180) AS 'Total'
> I get "Invalid column name 'R0'." error messages for each column. Hope th
at
> makes sense.
> Thanks,
> Linn
>
>