THIS IS A TIME SLIP SCENARIO...using SQL SERVER 2000
I have a table (exempt) with the days of the week represented as columns (MONam, MONpm, TUESam, TUESpm, WEDam, WEDpm and so on).
For each day (am and pm) I am allowing a FORM to pass a value of P or S or H (p=present, S=Sick, H=holiday)
Edning up with...
MONam: P MONpm: P
TUEam: P TUEpm: P
WEDam: H WEDpm: H
THURam: P THURpm: P
FRIam: S FRIpm: S
I would like to COUNT the number of Ps, Ss and Hs across the column (for each record) and then SUM that COUNT.
Ending up with a summary for the week/username
This is what I have so far:
SELECT COUNT(MONam,MONpm,TUESam,TUESpm,WEDam,WEDpm, THURam,THURpm,FRIam,FRIpm) AS hours, SUM(hours * .5) AS days FROM exempt WHERE (username = '::username::')
Any assistance is MUCH appreciated!!