|
|
Posted by Erland Sommarskog on 10/14/06 11:54
Radu (cuca_macaii2000@yahoo.com) writes:
> PIN#
> # of weeks when Overtime > 8
> Overtime Week 1
> Overtime Week 2
> Overtime Week 3
> .
> .
> .
> Overtime Week 52
>
>
> I need to calculate the # of times when in column "Overtime Week X" for
> the current PIN# appears a number >=8, and write in column [# of weeks
> when Overtime > 8] the number of times when this happens for the
> current PIN.
>
>
> Assume that for PIN 376 I have the following row:
>
> PIN Wk1 Wk2 Wk3 Wk4........
> 376 7 9 5 9
>
> I have to write in column [# of weeks when Overtime > 8] the value 2
> (the overtime for this guy is only twice greater than 8, as you can see
> above).
>
> How could I accomplish this, please ? I don't know how to attack this
> problem.
UPDATE tbl
SET [# of weeks when Overtime > 8] =
CASE WHEN [Overtime Week 1] > 8 THEN 1 ELSE 0 END +
CASE WHEN [Overtime Week 2] > 8 THEN 1 ELSE 0 END +
...
CASE WHEN [Overtime Week 52] > 8 THEN 1 ELSE 0 END
> Note - I think that if I had a transposed table (weekN being rows, not
> cols), my problem would be easy. But how do I transpose my table
INSERT redesignedtable (PIN, Week)
SELECT PIN, CASE n WHEN 1 THEN [Overtime Week 1]
WHEN 2 THEN [Ovettime Week 2]
...
WHEN 52 THEN [Overtime Week 52]
END
FROM old_badly_designed_table
CROSS JOIN (SELECT 1 AS n UNION SELECT 2 UNION SELECT 3 UNION ...
SELECT 52) AS n
If you have already have a table with contiguos numbers from 1 to 52
you can use that.
> (actually, I am using Access, not SQL) ?
The syntax I've posted for SQL Server. Whether it works on Access, I
have no idea. You should try an Access newsgroup for that.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|