Adapting SQL to work over multiple weeks

Question

I have a report that determines employees that have entered less than 40 hours on their timesheets for a specific week:

With Under40s as (
Select Distinct Entry.UUID, Entry.FirstName, Entry.LastName, Team.PersonUnit, Team.TeamName
From Entry as Entry
Inner Join TeamOrganization as Team on Team.PersonUUID = Entry.UUID and @EnteredDate     between Team.PeriodBeginDate and Team.PeriodEndDate
Where 
(
    Select sum(Entry2.Hours) From Entry as Entry2
    Where Entry2.UUID = Entry.UUID and Entry2.Date Between @StartDate and @EndDate
) < 40
Or not exists 
(
    Select 1 From Entry as Entry2
    Where Entry2.UUID = Entry.UUID and Entry2.Date Between @StartDate and @EndDate
)
)
Select distinct Under40s.UUID, Under40s.FirstName, Under40s.LastName, Under40s.PersonUnit, Under40s.TeamName, Case
    When Sum(Entry.Hours) is null then 0
    Else Sum(Entry.Hours)
    End as TotalHours 
From Under40s
Left Join Entry as Entry on Entry.UUID = Under40s.UUID and Entry.Date Between @StartDate and @EndDate
Where Under40s.PersonUnit in (@PersonUnit) and Under40s.TeamName in (@Teams)
Group By Under40s.UUID, Under40s.FirstName, Under40s.LastName, Under40s.PersonUnit, Under40s.TeamName
Order By Under40s.LastName, Under40s.FirstName

The client now wants to be able to enter a date range of more than a week. I'm thinking they want to see for each week within that date range, who's reporting less than 40 hours. I am not sure how, or if, I can modify the SQL to deliver such a report. Can anyone give me a hint?

Thanks!

Are you using reporting services to display the output of this cte? — JsonStatham, Nov 18 '13 at 15:55

score 0 · Answer 1 · edited May 23 '17 at 12:28

0

Assuming every working day is 8 hours, one option would be to get the the day differences between start date and end date and then multiplying that by 8 and using the product as the criteria. For example: startdate: 20/11/2013 and enddate: 31/12/2013 is 41 days including weekends (so you also you have to filter weekends or non-working days), but just as example 41*8 = 328. So instead of less than 40 it will be less than 328. Here's a link to a working solution of excluding weekends and other specified dates: Number of days in date range, excluding weekends and other dates, in C#

edited May 23 '17 at 12:28

Community

1
1

answered Nov 18 '13 at 16:35

zulqarnain

1,695
1
16
33

I'm thinking that what they really want is this: for the period 10/28/2013 to 11/17/2013, employee 1 reported 20 hours on week 1, employee 2 reported 35 hours on week 2, and employee 3 reported 15 hours on week 1 and 29 hours on week 2. I'm just not sure how to deliver this. I'm not sure it's even possible really. – Michael Robinson Nov 18 '13 at 16:43
Ah I see if that's what they want then I think he can get the weeks, then each week the start and end date and so on. – zulqarnain Nov 18 '13 at 17:14
How would you do that with straight SQL? – Michael Robinson Nov 18 '13 at 18:10

Adapting SQL to work over multiple weeks

1 Answers1