HELP on SELECT Statement

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.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
"magix" <magix@.asia.com> wrote in message news:449bef96_2@.news.tm.net.my...
>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.
>