SQL, days in a time period

Question

0.00/5 (No votes)

See more:

A group of sales guys on the road, have a SQL table that lists the date they left and the date they returned on each trip and their ID. Want to determine how many days out of the last 120 that each guy was on the road, then 120 to 240 then 240 to 360. It has been harder than I thought, any ideas? Do I need to do the logic in C# or can a query answer this?

Posted 27-Jul-11 11:07am

Member 8105842

Add a Solution

Comments

wizardzz 27-Jul-11 17:11pm

I'm pretty sure you can do it either way. What part exactly are you having trouble with?

Al Moje 27-Jul-11 21:30pm

Yes... that is true; you can do it either of the two. If you choice C# coding use Lambda expression within a list

Member 8105842 27-Jul-11 23:40pm

Yes, I imagine that it's possible either way. Does anyone have a sql query that will do this?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

s_magus · Answer 1 · 2011-07-27T18:15:00

Since you did not give any information about what you are having problems with or what you have currently done. I will give a few ideas and tips on how to solve your problem. This is only one possible way to complete this problem, there are other methods to do it all with their own pros and cons, but I will go over how to do it with a scalar-valued function. I decided against just writing out the entire query for you without you showing at least a little effort in providing us with what you have done so far, so we can help answer specific problems you might be having with your query.

You can start out by writing a scalar-valued function (lets call it DaysOuts) that you pass in a Id, StartRange, and EndRange that you will return the days out for that Range. This will allow you query the table of salesmen table and pass in their Id and Range (ex. DaysOut(Salesmen.Id, 0, 120) As Range1, DaysOut(Salesmen.Id, 120, 240) As Range2, etc.).

The key to writing the query is the DateDiff function. DateDiff(day, DateLeft, GetDate()) will get you the number of days ago they left for the trip. So if they left 6 months ago for a trip then DateDiff would return ~180 days. So you could compare that to your StartRange and EndRange to determine if that is within the correct range. You can then use DateDiff to Sum up the number of days they were out based on the DateLeft and DateReturned.

Once you get the basic function working you will probable need to take edge conditions into consideration. Such as when someone left 130 days ago and returned 110 days ago. Since 10 of those days will fall with the 0 to 120 range and the other 10 days would fall in the 120 to 240 range. This is probable the trickier part of the query since it involves finding the days between DateLeft and DateReturned that actually fall within the Range. Basically you need to determine if the DateLeft is outside the EndRange and you need to figure out the date that ends the range we are searching between and use that instead of the DateLeft. You will need to do the same thing for DateReturned.