Re: How to calculate category value - perhaps use subqueries?

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