Solved

SQL query construction with date ranges

Posted on 2014-02-25
6
378 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 60

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 60

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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
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 60

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

Technology Partners: 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!

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

726 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