sql server programming: HELP on SELECT Statement -- sql server programming

Re: HELP on SELECT Statement

Mike C#
6/23/2006 10:25:35 AM

sql server programming: Something like this? Note that my output came out a little different from
your specified output:

Year | TypeA | TypeB
1999 | 6 | 3
2000 | 5 | 7
2001 | 0 | 4

CREATE TABLE #Widgets ([Year] INT NOT NULL,
ModelYr INT NOT NULL,
Type INT NOT NULL,
PRIMARY KEY ([Year], ModelYr, Type));

INSERT INTO #Widgets([Year], ModelYr, Type)
SELECT 1999, 1994, 2
UNION SELECT 1999, 1999, 3
UNION SELECT 2000, 1999, 4
UNION SELECT 2001, 2000, 2
UNION SELECT 1999, 1996, 4
UNION SELECT 2000, 1996, 5
UNION SELECT 2000, 1998, 3
UNION SELECT 2001, 2001, 2

SELECT [Year], SUM(CASE WHEN [Year] - [ModelYr] > 2 THEN Type ELSE 0 END) AS
TypeA,
SUM(CASE WHEN [Year] - [ModelYr] <= 2 THEN Type ELSE 0 END) AS TypeB
FROM #Widgets
WHERE ModelYr <= [Year]
GROUP BY [Year]
ORDER BY [Year]

DROP TABLE #Widgets

[quoted text, click to view]

HELP on SELECT Statement

magix
6/23/2006 9:41:42 PM

I have following table record

Year ModelYr Type
1999 1994 2
1999 1999 3
2000 1999 4
2001 2000 2
1999 1996 4
2000 1996 5
2000 1998 3
2001 2001 2

Here are the rules:
1. In display output, it will have the following column name:
Year TypeA TypeB

2. "ModelYr" is equal or less than "Year"

3. if ("Year" - "ModelYr") < 2 then
TypeA = "Type"
else
TypeB = "Type"

3. The output should be "Group by Year Order by Year"
4. For each "Year", the each type will be SUM UP.

So the expected output will be:
Year TypeA TypeB
1999 6 3
2000 7 5
2001 4 0

Could you kindly advise how to make the SELECT statement to achieve the
output above ? I'm really not too sure.

Any help is very much appreciated.

Thank you.

Regards.