Hey,
Struggeling with a query, maybe someone can point me out.
Table structure : per contract per observation year the total payments
Contract Observationyear Paiments
A 1999 10
A 2000 4
A 2001 3
A 2002 2
B 1999 3
B 2000 1
B 2001 8
B 2002 2
Question : per contract (1line per contract) the sum of the payments on 2001
and 2002 taking the previous years into account.
Contract 2001 2002
A 17 19
B 12 14
I tried already several querys but no luck.
Thanks for help
JacSELECT contract,
SUM(CASE WHEN observationyear<=2001 THEN payment END) AS yr2001,
SUM(CASE WHEN observationyear<=2002 THEN payment END) AS yr2002,
SUM(CASE WHEN observationyear<=2003 THEN payment END) AS yr2003,
SUM(CASE WHEN observationyear<=2004 THEN payment END) AS yr2004,
SUM(CASE WHEN observationyear<=2005 THEN payment END) AS yr2005
FROM ContractPayments
GROUP BY contract
David Portas
SQL Server MVP
--|||Thanks david .
I did the following and it was quite wrong :
SELECT contract,
> SUM(CASE observationyear WHEN 2005 THEN 0
when 2004 then 0
when 2003 then 0
when 2002 then 0
else payment END) AS yr2001,
> SUM(CASE observationyear WHEN 2005 THEN 0
when 2004 then 0
when 2003 then 0
else payment END) AS yr2002
FROM ContractPayments
GROUP BY contract
That was the result :
Column 'observationyear is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.
"David Portas" wrote:
> SELECT contract,
> SUM(CASE WHEN observationyear<=2001 THEN payment END) AS yr2001,
> SUM(CASE WHEN observationyear<=2002 THEN payment END) AS yr2002,
> SUM(CASE WHEN observationyear<=2003 THEN payment END) AS yr2003,
> SUM(CASE WHEN observationyear<=2004 THEN payment END) AS yr2004,
> SUM(CASE WHEN observationyear<=2005 THEN payment END) AS yr2005
> FROM ContractPayments
> GROUP BY contract
> --
> David Portas
> SQL Server MVP
> --
>|||It works for me - see below. Please include CREATE and INSERT
statements like this with future posts so that both you and those who
reply can test solutions against some actual data:
CREATE TABLE ContractPayments (contract CHAR(1) NOT NULL,
observationyear INTEGER NOT NULL, payment INTEGER NOT NULL, PRIMARY KEY
(contract,observationyear))
INSERT INTO ContractPayments (contract, observationyear, payment)
SELECT 'A', 1999, 10 UNION ALL
SELECT 'A', 2000, 4 UNION ALL
SELECT 'A', 2001, 3 UNION ALL
SELECT 'A', 2002, 2 UNION ALL
SELECT 'B', 1999, 3 UNION ALL
SELECT 'B', 2000, 1 UNION ALL
SELECT 'B', 2001, 8 UNION ALL
SELECT 'B', 2002, 2
SELECT contract,
SUM(CASE observationyear WHEN 2005 THEN 0
WHEN 2004 THEN 0
WHEN 2003 THEN 0
WHEN 2002 THEN 0
ELSE payment END) AS yr2001,
SUM(CASE observationyear WHEN 2005 THEN 0
WHEN 2004 THEN 0
WHEN 2003 THEN 0
ELSE PAYMENT END) AS yr2002
FROM ContractPayments
GROUP BY contract
Result:
contract yr2001 yr2002
-- -- --
A 17 19
B 12 14
(2 row(s) affected)
David Portas
SQL Server MVP
--
