June 10, 2010 at 2:45 pm
Scenario: If John Smith is to enter his time, he needs to be able to see his current total hours in the current pay period and his total hours from his previous pay period depending on the present date (or when he runs the report).
I need to be able to figure out which pay period the 'present' date belongs under if I already have a table that has all the pay period.
Pay Period: Table
SELECT payperiodsid, description, startdate, enddate
FROM dbo.payperiods
ORDER BY payperiodsid
payperioddescr startdate enddate
108 Period 12/20 - 01/02 12/20/20091/2/2010
109 Period 01/03 - 01/16 1/3/20101/16/2010
110 Period 01/17 - 01/30 1/17/20101/30/2010
111 Period 01/31 - 02/13 1/31/20102/13/2010
112 Period 02/14 - 02/27 2/14/20102/27/2010
113 Period 02/28 - 03/13 2/28/20103/13/2010
114 Period 03/14 - 03/27 3/14/20103/27/2010
115 Period 03/28 - 04/10 3/28/20104/10/2010
116 Period 04/11 - 04/24 4/11/20104/24/2010
117 Period 04/25 - 05/08 4/25/20105/8/2010
Total Hours By Date
SELECT CONVERT(varchar, startdate, 101) AS DateStart, laborcode
, SUM(regularhrs) AS TotalHrs
, CASE DATEPART(dw, startdate)
WHEN '1' THEN 'Sunday'
WHEN '2' THEN 'Monday'
WHEN '3' THEN 'Tuesday'
WHEN '4' THEN 'Wednesday'
WHEN '5' THEN 'Thursday'
WHEN '6' THEN 'Friday'
WHEN '7' THEN 'Saturday' END AS Day
FROM dbo.labtrans
WHERE (laborcode = '000173')
GROUP BY laborcode, CONVERT(varchar, startdate, 101), DATEPART(dw, startdate), startdate
ORDER BY startdate DESC
Results:
DateStartLaborCodeTotalHrs Day
4/15/2010173 10Thursday
4/14/2010173 10Wednesday
4/13/2010173 10Tuesday
4/12/2010173 10Monday
4/8/2010173 10Thursday
4/7/2010173 10Wednesday
4/6/2010173 10Tuesday
4/5/2010173 10Monday
4/1/2010173 10Thursday
3/31/2010173 10Wednesday
3/30/2010173 10Tuesday
3/29/2010173 10Monday
3/25/2010173 10Thursday
So if he ran the report on 4/23/10 the totals should be:
Previous Pay Period: 115 From: 3/28/10 - 4/10/10 Total Hours: 80
Current Pay Period: 116 From: 4/11/10 - 4/24/10 Total Hours: 40
June 10, 2010 at 3:15 pm
create table #PayPeriod
(
payPeriodint,
periodDescvarchar(50),
startDatedatetime,
endDatedatetime
)
insert into #PayPeriod (payPeriod, periodDesc, startDate, endDate)
select 108, 'Period 12/20 - 01/02', '12/20/2009', '1/2/2010' union
select 109, 'Period 01/03 - 01/16', '1/3/2010', '1/16/2010' union
select 110, 'Period 01/17 - 01/30', '1/17/2010', '1/30/2010' union
select 111, 'Period 01/31 - 02/13', '1/31/2010', '2/13/2010' union
select 112, 'Period 02/14 - 02/27', '2/14/2010', '2/27/2010' union
select 113, 'Period 02/28 - 03/13', '2/28/2010', '3/13/2010' union
select 114, 'Period 03/14 - 03/27', '3/14/2010', '3/27/2010' union
select 115, 'Period 03/28 - 04/10', '3/28/2010', '4/10/2010' union
select 116, 'Period 04/11 - 04/24', '4/11/2010', '4/24/2010' union
select 117, 'Period 04/25 - 05/08', '4/25/2010', '5/8/2010'
create table #LaborHours
(
payDatedatetime,
laborCodeint,
payHoursint--just using int for example to keep it simple
)
insert into #LaborHours (payDate, laborCode, payHours)
select '4/15/2010', 173, 10union
select '4/14/2010',173, 10union
select '4/13/2010',173, 10union
select '4/12/2010',173, 10union
select '4/8/2010',173, 10union
select '4/7/2010',173, 10union
select '4/6/2010',173, 10union
select '4/5/2010',173, 10union
select '4/1/2010',173, 10union
select '3/31/2010',173, 10union
select '3/30/2010',173, 10union
select '3/29/2010',173, 10union
select '3/25/2010',173, 10
And the query ...
declare @queryDate datetime
select @queryDate = '04/23/10' --in real life you'll use getDate()
select cur.payHours as CurrentPayPeriod,
prev.payHours as PreviousPayPeriod
from
(
select SUM(lh.payHours) as payHours,
pp.payPeriod
from #LaborHours lh
join #PayPeriod pp
on lh.payDate between pp.startDate and pp.endDate
where @queryDate between pp.startDate and pp.endDate
group by pp.payPeriod
) cur
join
(
select SUM(lh.payHours) as payHours,
pp.payPeriod
from #LaborHours lh
join #PayPeriod pp
on lh.payDate between pp.startDate and pp.endDate
group by pp.payPeriod
) prev
on prev.payPeriod = cur.payPeriod - 1
drop table #PayPeriod
drop table #LaborHours
June 10, 2010 at 4:17 pm
wow!!!! you are heaven sent. thanks!
June 11, 2010 at 6:46 am
canoedoceanprince (6/10/2010)
, CASE DATEPART(dw, startdate)WHEN '1' THEN 'Sunday'
WHEN '2' THEN 'Monday'
WHEN '3' THEN 'Tuesday'
WHEN '4' THEN 'Wednesday'
WHEN '5' THEN 'Thursday'
WHEN '6' THEN 'Friday'
WHEN '7' THEN 'Saturday' END AS Day
As a sidebar, please lookup DATENAME in "Books Online" so you don't have to write code a CASE statement for something like that in the future. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
June 11, 2010 at 1:50 pm
Jeff Moden (6/11/2010)
canoedoceanprince (6/10/2010)
, CASE DATEPART(dw, startdate)WHEN '1' THEN 'Sunday'
WHEN '2' THEN 'Monday'
WHEN '3' THEN 'Tuesday'
WHEN '4' THEN 'Wednesday'
WHEN '5' THEN 'Thursday'
WHEN '6' THEN 'Friday'
WHEN '7' THEN 'Saturday' END AS Day
As a sidebar, please lookup DATENAME in "Books Online" so you don't have to write code a CASE statement for something like that in the future. 😉
Not only that you'd have to write less code. You'd also avoid wrong results if someone decides to change the value of @@datefirst to any other value than 7...
Just try SET DATEFIRST 4 and run your query after that. See what I mean 😉
You could use a totally different apporach to get a consistant weekday regardless of the @@datefirst setting:
SELECT DATENAME(weekday,DATEDIFF(dd,0,startdate))
June 11, 2010 at 3:22 pm
Thank you all.
Another thing though...
How can I display this by total hours per day for both current pay period and previous pay period grouped by total hrs per day.
SELECT lh.startdate AS PayDate
, SUM(lh.regularhrs) AS PayHours
, DATENAME(weekday, lh.startdate) AS Day
, pp.pospayperiodsid
FROM dbo.labtrans AS lh INNER JOIN
dbo.pospayperiods AS pp
ON lh.startdate BETWEEN CONVERT(datetime, pp.posstartdate, 101) AND CONVERT(datetime, pp.posenddate,
101)
WHERE (@queryDate BETWEEN CONVERT(datetime, pp.posstartdate, 101)
AND CONVERT(datetime, pp.posenddate, 101)) AND
(lh.laborcode = '000173')
GROUP BY pp.pospayperiodsid, lh.startdate
So I want it to always deduct one period from the current pay period and pull the dates in between.
This is the result of the Current Pay Period using the @querydate = 4/23/2010
PayDate PayHoursDay PosPayPeriodsID
4/12/2010 0:0010Monday 116
4/13/2010 0:0010Tuesday 116
4/14/2010 0:0010Wednesday 116
4/15/2010 0:0010Thursday 116[/size]
June 11, 2010 at 3:39 pm
Please provide table def and some sample data together with your expected result and definition of the variable used in your query in a ready to use format as described in the first link in my signature.
This would allow us to test our solutions and to take everything that's required into consideration (e.g. column data type).
Edit: a perfect example of how to post sample data has been provided by bteraberry in a previous post...
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply