sql - Dense_rank and sum -
i have common table expression
with total_hour ( select employee_id, sum(round(cast(datediff(minute, start_time, finish_time) numeric(18, 0)) / 60, 2)) total_h timesheet t t.employee_id = @employee_id , dense_rank() on ( order datediff(day, '20130925', date_worked) / 7 desc ) = @rank group t.personnel_id )
this sample data:
id employee_id worked_date start_time finish_time 1 1 2013-09-25 09:00:00 17:30:00 2 1 2013-09-26 07:00:00 17:00:00 8 1 2013-10-01 09:00:00 17:00:00 9 1 2013-10-04 09:00:00 17:00:00 12 1 2013-10-07 09:00:00 17:00:00 13 1 2013-10-30 09:00:00 17:00:00 14 1 2013-10-28 09:00:00 17:00:00 15 1 2013-11-01 09:00:00 17:00:00
supposed wednesday first day of week , based date 2013-09-25. want total number of hours worked 09-25 10-01 when @rank 1 , total hour 10-02 10-08 when @rank=2 , on.
thanks
to number of hours worked employee within particular week, use suitable criteria. no need use dense_rank or similar windowed functions this.
assuming have @week parameter, contains integer (0 current week, 1 last week, 2 week before that, etc.):
select employee_id sum(round(cast(datediff(minute, start_time, finish_time) numeric(18, 0)) / 60, 2)) total_h timesheet t t.employee_id = @employee_id , date_worked between dateadd(ww, datediff(ww,0,getdate()) - @week, 0) , dateadd(ww, datediff(ww,0,getdate()) - @week, 0) + 7
here, i've used current date (getdate()
) base date, replace 20130925
, if that's need.
Comments
Post a Comment