Link to home
Start Free TrialLog in
Avatar of Clif
ClifFlag for United States of America

asked on

A Query To Convert a Two Week Time Frame To A One Week Time Frame

I'm using SQL Server 2008 R2

I have a table similar to this:
Employee_Name        Pay_Period_End    Work_Date     Pay_Type     Hours
George Washington    2012-11-03        2012-10-22    R            8
George Washington    2012-11-03        2012-10-23    R            8
George Washington    2012-11-03        2012-10-24    R            8
George Washington    2012-11-03        2012-10-25    R            8
George Washington    2012-11-03        2012-10-26    R            8
George Washington    2012-11-03        2012-10-29    R            8
George Washington    2012-11-03        2012-10-30    R            8
George Washington    2012-11-03        2012-10-31    R            6.5
George Washington    2012-11-03        2012-10-31    H            1.5
George Washington    2012-11-03        2012-11-01    R            8
George Washington    2012-11-03        2012-11-02    R            8

Open in new window

Basically the Pay Period is a two week span (Beginning on a Sunday and ending on a Saturday).  What I need is a query that would break this down to single week groups such as this:
Employee_Name        Pay_Period_End    Work_Date     Pay_Type     Hours
George Washington    2012-10-27        2012-10-22    R            8
George Washington    2012-10-27        2012-10-23    R            8
George Washington    2012-10-27        2012-10-24    R            8
George Washington    2012-10-27        2012-10-25    R            8
George Washington    2012-10-27        2012-10-26    R            8
George Washington    2012-11-03        2012-10-29    R            8
George Washington    2012-11-03        2012-10-30    R            8
George Washington    2012-11-03        2012-10-31    R            6.5
George Washington    2012-11-03        2012-10-31    H            1.5
George Washington    2012-11-03        2012-11-01    R            8
George Washington    2012-11-03        2012-11-02    R            8

Open in new window


Or, ultimately, total all the hours for each workweek so that I end up with this:
Employee_Name        Work_Week_End     Hours
George Washington    2012-10-27        40
George Washington    2012-11-03        40

Open in new window


Any ideas on how this can be done with a query/view?

TIA
ASKER CERTIFIED SOLUTION
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
you can work on same line.......


   set dateformat ymd
   Declare @tab table (Work_Date datetime,         Hours int)
insert into @tab
select 
'2012-10-22'        ,        8
union all
select
'2012-10-23'    ,            8
union all
select '2012-10-24'  ,             8
union all
select '2012-10-25'    ,            8
union all
select '2012-10-31'      ,          6
union all
select '2012-10-31'  ,            1
union all
select '2012-11-01'    ,            8
union all
select '2012-11-02'      ,          8

select DATEADD(dd, 7-(DATEPART(dw, Work_Date)), Work_Date)as Week_end,Work_Date from @tab


select DATEADD(dd, 7-(DATEPART(dw, Work_Date)), Work_Date) Work_date, sum(Hours) as total_hours from @tab
group by DATEADD(dd, 7-(DATEPART(dw, Work_Date)), Work_Date)

Open in new window

Avatar of Clif

ASKER

Thanks.

I'm not going to use a separate table, though.  I have recently discovered CTEs.  :-)