Solved

SQL query construction with date ranges

Posted on 2014-02-25
6
368 Views
Last Modified: 2014-02-25
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
Comment
Question by:takwirirar
  • 3
  • 3
6 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 39885499
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
 
LVL 1

Author Comment

by:takwirirar
ID: 39885573
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 39885587
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 1

Author Comment

by:takwirirar
ID: 39885620
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
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 39885746
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
 
LVL 1

Author Closing Comment

by:takwirirar
ID: 39885795
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question