15,906,766 members
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
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?

## Solution 1

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.

Member 8105842 28-Jul-11 0:28am
Well, I've actually put a lot of time into working with datediff, and there are a number of tables involved. But it can be simplified to this: Suppose there is just 1 table with a salesman's ID and maybe dozens of trips for each salesman over the last several years of anywhere from 1 day to several weeks, with the start and end date. I need to add up all of the trips and determine the total days gone just for the last 120 days. Not that familiar with a scalar value function. I can count widgets sold during a time perion but the days have eluded me.
s_magus 28-Jul-11 1:37am
You never specified what database you were using so I don't know if the database has them or what they may be called. Scalar-Valued functions are part of MS SQL Server which is the database I use on a day to day basis so it is what I used as the database system to write my solution. A scalar-valued function allows you to pass in parameters and return scalar (single) value such as a date, int, string, etc. In my solution the function DaysOut would return an int representing the number of days out in the time period.

DateDiff returns a number representing the count of the specified datepart for the date range. DateDiff(day, DateLeft, DateReturned) would return the number of days they were gone. Since it returns a number you can Sum the DateDiff to get the total days gone. Sum(DateDiff(day, DateLeft, DateReturned)) As DaysOut