[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SQL query construction with date ranges

Posted on 2014-02-25
6
Medium Priority
?
408 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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 2000 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

656 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