How to calculate category value - perhaps use subqueries?

Hello all - I am trying to create a view that will calculate a category field
value based on several conditions regarding another field.
i.e. having a table PayCodes (employeeID, paycode)
employeeID paycode
1 01
1 02
1 02S
1 03S
1 71
2 01
2 02S
2 71
3 02
3 03H
4 01
4 02
I need to create a view that will output employeeID and overtimeType where
overtimeType = 1 if an employeeID has 02S or 02H
overtimeType = 2 if an employeeID has 03S or 03H
overtimeType = 3 if an employeeID has (02S or 02H) and (03S or 03H)
overtimeType = 0 if an employeeID has none of 02S, 02H, 03S, 03H
So given the above table, the view should return:
employeeID overtimeType
1 3
2 1
3 2
4 0
Any ideas?
Thanks in advance!!
Hellman.
On Tue, 4 Oct 2005 10:36:02 -0700, Hellman wrote:

>Hello all - I am trying to create a view that will calculate a category field
>value based on several conditions regarding another field.
>i.e. having a table PayCodes (employeeID, paycode)
>employeeID paycode
>--
> 1 01
> 1 02
> 1 02S
> 1 03S
> 1 71
> 2 01
> 2 02S
> 2 71
> 3 02
> 3 03H
> 4 01
> 4 02
>I need to create a view that will output employeeID and overtimeType where
> overtimeType = 1 if an employeeID has 02S or 02H
> overtimeType = 2 if an employeeID has 03S or 03H
> overtimeType = 3 if an employeeID has (02S or 02H) and (03S or 03H)
> overtimeType = 0 if an employeeID has none of 02S, 02H, 03S, 03H
>So given the above table, the view should return:
>employeeID overtimeType
>--
> 1 3
> 2 1
> 3 2
> 4 0
>Any ideas?
>Thanks in advance!!
>Hellman.
>
Hi Hellman,
I believe that the following will work:
SELECT employeeID,
SUM(overtimeType) AS overtimeType
FROM (SELECT DISTINCT
employeeID,
CASE
WHEN paycode LIKE '02[HS]' THEN 1
WHEN paycode LIKE '03[HS]' THEN 2
ELSE 0
END AS overtimeType
FROM PayCodes) AS d
GROUP BY employeeID
(untested - see www.aspfaq.com/5006 if you prefer tested results)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)