• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 419
  • Last Modified:

SQL query construction with date ranges

Hi Experts,

I have a question for a SQL query I am trying to build. I have the following tables

Timesheet
Stores Employee ID, week ending date and the times worked by an employee

Booking
Stores Employee ID and holidays booked by an employee

I am working on a summary report that should show the total hours worked by an employee in a week and the holidays taken.

I this example employee 546 worked 56 hours but was on holiday on the 22nd and between the 24th and 27th of January

The summary report shows 5 days total holiday BUT I want it to show 4 days because the 27th of January holiday was outside week ending 26/01/2014

Any ideas?

current queries
0
takwirirar
Asked:
takwirirar
  • 3
  • 3
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
Is the data stored in columns like that, or this a view/report?  If it is, you may have to UNPIVOT the data first.  Otherwise, another solution is to add a CASE statement to addition of each day's total.

e.g., CASE WHEN tm.[Sun Date] <= tm.[w/e] THEN tm.[Sun Total] ELSE 0 END

This way a day only adds to total if it falls within the week ending date.
0
 
takwirirarAuthor Commented:
Hi Kevin, thanks for that. The data is stored as that. The totals of what an employee has worked is not an issue its the holidays that are the issue. The record in the timesheet is stored by [w/e] so thats not giving me issues at the moment.

My issue is finding the dates between the range in the holiday
0
 
Kevin CrossChief Technology OfficerCommented:
Maybe I am mistaken, but I think it is the same solution.  If you have a table with dates of holidays, you essentially join to table and filter the daily total based on the date range of the holiday.  If you have DDL scripts with sample data as well as expected results for the sample, we can help you further with developing the solution.  However, I think the proposed methods will work --- i.e., UNPIVOT your data, so you can do date manipulation OR use 7 CASE statements on each day of the week.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
takwirirarAuthor Commented:
Thank you. Please find attached. The expected result is 4 days holiday for employee 546. The current query is showing 5
create-bookings.txt
create-timesheet.txt
sample-data.xlsx
current-query.txt
0
 
Kevin CrossChief Technology OfficerCommented:
Okay.  I guess I did misunderstand the issue a bit as the 5 units is a result of joining both bookings records to the one timesheet record.  Therefore, you may be able to fix this by making sure that the start and stop dates of the timesheet fall within the boundaries of the booking record like the below.
SELECT CONVERT(VARCHAR(10),tm.[w/e],103) as [w/e]
     , tm.[Shop ID]
     , tm.[Employee ID]
     , COALESCE(tm.[Sun Total]+tm.[Mon Total]+tm.[Tue Total]+tm.[Wed Total]+tm.[Thu Total]+tm.[Fri Total]+tm.[Sat Total],0) as [Total Hours]
     , bk.[Total Holiday]
from timesheet tm
OUTER APPLY (
    SELECT SUM(bk.units) as [Total Holiday]
    FROM dbo.bookings bk 
    WHERE bk.[Employee ID]=tm.[Employee ID]
    AND bk.[to] BETWEEN tm.[Sun Date] AND tm.[Sat Date]
) bk
;

Open in new window


The example is the date filter with BETWEEN, but I also showed with OUTER APPLY to illustrate how you can avoid the outer GROUP BY.

Your data does not seem consistent with my original thought as it is not the sum of the time sheet columns that were your issue (unless the 56 hours is not correct).
0
 
takwirirarAuthor Commented:
Wow! This is fantastic! Thank you so much!

Yes apologies all the other hours were cut out of the image. 56 is correct
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now