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

Popular posts from this blog

java.util.scanner - How to read and add only numbers to array from a text file -

rewrite - Trouble with Wordpress multiple custom querystrings -

php - Accessing static methods using newly created $obj or using class Name -