HELP on SELECT Statement

23 Jun 2006 1:41 PM

magix

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.

23 Jun 2006 2:25 PM

Mike C#

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

Show quote

"magix" <ma***@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.
>