Like I say, I don’t generally post SQL specific stuff because, well there’s so many blogs out there that do. But what the heck.
Had a problem the other day. I needed to return the hours worked per timerange for a specific employee. And if they worked no hours, return 0. So basically had to deal with gaps.
There’s lots of solutions out there, this is mine:
Alter procedure GetEmployeeHoursByDate @startdate date, @enddate date , @userID varchar(25)
— Usage exec GetEmployeeHoursByDate ‘2018-01-07’, ‘2018-01-13’, ‘gmoore’
— Author: Greg D. Moore
— Date: 2018-02-12
— Version: 1.0
— Get the totals for the days in question
set NOCOUNT on
— First let’s create simple table that just has the range of dates we want
; WITH daterange AS (
SELECT @startdate AS WorkDate
SELECT DATEADD(dd, 1, WorkDate)
FROM daterange s
WHERE DATEADD(dd, 1, WorkDate) <= @enddate)
select dr.workdate as workdate, coalesce(a.dailyhours,0) as DailyHours from
— Here we get the hours worked and sum them up for that person.
select ph.WorkDate, sum(ph.Hours) as DailyHours from ProjectHours ph
and ph.workdate>= @startdate and ph.workdate <= @enddate
group by ph.workdate
) as a
right outer join daterange dr on dr.WorkDate=a.WorkDate — now join our table of dates to our hours and put in 0 for dates we don’t have hours for
order by workdate
There’s probably better ways, but this worked for me. What’s your solution?