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
LVL 1
David CIT Projects ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
David CIT Projects ManagerAuthor 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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

David CIT Projects ManagerAuthor 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
David CIT Projects ManagerAuthor 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.