Parsing Data into Additional Colums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Parsing Data into Additional Colums

Author

Topic

dougancil
Posting Yak Master

217 Posts

Posted - 2011-05-24 : 15:04:54

I have the following query:


SELECT
[ScratchPad5].EmployeeNumber,
SUM( case when [sumhours]>40 
THEN 40
ELSE cast([sumhours] as numeric(12,2))
END ) AS TotalRegHours, 
SUM( case when [sumhours]>40 
THEN cast([sumhours]-40 as numeric(12,2))
ELSE 0 
END ) AS TotalOT 
FROM ScratchPad5
GROUP BY 
[ScratchPad5].EmployeeNumber, 
sumhours

union 
select employeenumber, null, null
from scratchpad3 where code in ('Vacation','Holiday','Sicktime', 'ETO')

order by employeenumber asc

and what I'm trying to do is that if I have any values in my query that match Vacation, Holiday, Sicktime or ETO, that this will show in a seperate columns that are designated as such. If there are no values in my query that match those, then the columns would just show 0.0.

right now what I'm seeing is this:


EmployeeNumber      TotalRegHours        TotalOT
    8244	           NULL	           NULL
    8247	           12.99	   .00
    8330	            7.64	   .00
    8389	           18.67	   .00
    8433	            9.74	   .00
    8442	           17.91	   .00
    8451	            3.72	   .00
    8455                  4.72        .00
    8467	           37.48	   .00
    8471	           40.00	  8.68 
    8472	           27.07	   .00
    8475	           25.55	   .00
    8477	           28.74	   .00
    8482	            6.69	   .00

thank you

dougancil
Posting Yak Master

217 Posts

Posted - 2011-05-24 : 15:09:16

what I'd like to see is this


EmployeeNumber      TotalRegHours    TotalOT       Vacation      Holiday    Sicktime    ETO
    8244	         NULL	       NULL          .00          .00         .00       .00
    8247	           12.99	   .00           .00          .00         .00       .00
    8330	            7.64	   .00           .00          .00         .00       .00
    8389	           18.67	   .00           .00          .00         .00       .00
    8433	            9.74	   .00           .00          .00         .00       .00
    8442	           17.91	   .00           .00          .00         .00       .00
    8451	            3.72	   .00           .00          .00         .00       .00
    8455                  4.72        .00           .00          .00         .00       .00
    8467	           37.48	   .00           .00          .00         .00       .00
    8471	           40.00	  8.68           .00          .00         .00       .00
    8472	           27.07	   .00           .00          .00         .00       .00
    8475	           25.55	   .00           .00          .00         .00       .00
    8477	           28.74	   .00           .00          .00         .00       .00
    8482	            6.69	   .00           .00          .00         .00       .00

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources