query problem

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
--

Query on Performance Point

Hi,

Wonder why there is no seperate thread for Performance point or maybe I missed out on it.

Can anyone help me on this error I m getting while I m trying to publish the scorecards that I have done in Performance Point 2007?

When I export the scorecard to sharepoint this is the error I get:

The Microsoft.PerformancePoint.Scorecards.WebParts, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35 assembly specified in a Register directive of this page could not be found.

Can anyone please help me on this? Is it the problem with the sharepoint site I created or is it related to some folder?

Thanks in advance

See this http://forums.asp.net/thread/1510627.aspx post is any help.