CODEWITHSUNDEEP
sqlsql-server
Randy
Randy

Reputation: 1287

I need guidance formating output with an sql query

My current SQL looks like this:

Declare @Start datetime;
Declare @End datetime;

set @Start = '2016-04-4';
set @End = '2016-04-10';


Select pr.PR_FIELD03 AS EENo, pr.PR_Name AS EEName, lo.LocationName AS [State], SUM(ts.TotalMins/60) AS Hours, ts.OTFlag from TimeSheets as ts
join BWSPRMSD as pr on ts.EmployeeSSN = pr.PR_SSN
join Locations as lo on ts.LocationID = lo.LocationID
where ts.TransDate >= @start AND ts.TransDate <= @End AND ts.ActiveFlag = 1 AND HolidayFlag = 0 AND VacationFlag = 0
group by pr.PR_FIELD03, pr.PR_Name, lo.LocationName, ts.OTFlag;

UPDATED

That will give me:

EENo    EEName      State    Hours    OTFlag
118     Randy       TX       31       1
118     Randy       TX       40       0
118     Randy       MS       40       0
125     Bill        OK       40       0

What I need is:

EENo    EEName      Reg Hours      O.T. Hours    State
118     Randy       40             31            TX
118     Randy       40             0             MS
125     Bill        40             0             OK

Any suggestions you may have would be appreciated.

Thank you in advance

UPDATE

I apologize because my original post wasn't very clear. No, I don't want to just change column names, I need Hours split on the same line by if the OTFlag is set.

As an aside, If linq or Lambda is your cup of tea. Those answers will work just as well.

Upvotes: 0

Views: 56

Answers (1)

WickedFan
WickedFan

Reputation: 356

Proceed with a CTE. I haven't double checked my code, but it should be pretty close! Assuming here that you have a flag on your timesheet table named OTFlag set to 0 when regular hours are entered and set to 1 when they are OT hours. This way, you will have one entry for each employee for each state they worked in.

Declare @Start datetime;
Declare @End datetime;

set @Start = '2016-04-4';
set @End = '2016-04-10';

with regHours as (SELECT ts.employeeSSN, CAST(decimal(5,2),SUM(ts.totalMins/60) AS RegHours , lo.locationName as State
FROM TimeSheets ts
join Locations as lo on ts.LocationID = lo.LocationID
where ts.TransDate >= @start 
AND ts.TransDate <= @End 
AND ts.ActiveFlag = 1 
AND ts.OTFlag = 0
AND HolidayFlag = 0 
AND VacationFlag = 0
Group BY ts.employeeSSN, lo.locationName)



 Select pr.PR_FIELD03 AS EENo
       , pr.PR_Name AS EEName           
       , rh.regHours as 'Regular Hours'
       , SUM(ts.TotalMins/60) AS 'O.T. Hours'
       , lo.LocationName AS [State]
from TimeSheets as ts
join BWSPRMSD as pr on ts.EmployeeSSN = pr.PR_SSN
join Locations as lo on ts.LocationID = lo.LocationID
join regHours rh ON rh.employeeSSN = ts.employeeSSN AND rh.State = ts.LocationID
where ts.TransDate >= @start 
AND ts.TransDate <= @End 
AND ts.ActiveFlag = 1 
AND HolidayFlag = 0 
AND VacationFlag = 0
group by pr.PR_FIELD03, pr.PR_Name, lo.LocationName, rh.RegHours

Upvotes: 1

Related Questions