Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
MBeal
Posting Yak Master
110 Posts |
 Posted - 2009-09-04 : 11:09:54
|
| This is an odd question...How do you count "Sales Days" (not business days).Example -- you sell a widget and you attend an event where you setup at 9 am on 9/1/09. You sell all day the 1st, all day the 2nd, and until 4pm on the 3rd day.So a DATEDIFF statement might look like this:DateDiff(DD, StartDate, EndDate)NOTE: StartDate = '2009-09-01 09:00:00.000' and EndDate = '2009-09-03 16:00:00.000' in the data)The result will be 2. From a sales perspective, they sold for 3 days.Do I simply do a DATEADD function?DateDiff(DD, Min(Start_Date), DateAdd(D, 1, Max(End_Date)))I'm just wondering if there is another method that is more accurate?MBeal |
|
|
gavakie
Posting Yak Master
221 Posts |
Posted - 2009-09-04 : 11:11:53
|
| why not add a column for sales days 1 for sales days 0 for not then do a count. |
 |
|
|
MBeal
Posting Yak Master
110 Posts |
Posted - 2009-09-04 : 11:16:05
|
| Because I need it to be more intuitive. Great idea though but in this application it won't work. If I create something that calculates the number of hours between Start and End dates is greater than 4 or 8, I could add 1 (in a CASE statement)??? Something to consider. Any other ideas?MBeal |
|
|
|
gavakie
Posting Yak Master
221 Posts |
Posted - 2009-09-04 : 11:31:45
|
| do a datediff of hours instead of days, divide by number of days and use the round function. |
|
|
|
gavakie
Posting Yak Master
221 Posts |
Posted - 2009-09-04 : 11:36:11
|
| Select Round((DateDiff(HH, GetDate(), GetDate()+4))/24,1) |
|
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2009-09-04 : 11:38:18
|
this?select ceiling((datediff(hh,@StartDate,@EndDate) * 1.0 / 24) |
|
|
|
MBeal
Posting Yak Master
110 Posts |
Posted - 2009-09-04 : 12:57:42
|
| Sorry guys... it's not working... Here is the exact query I am running:Select Distinct Location_Name [Location_Name], Min(Start_Date) [StartDate], Max(End_Date) [EndDate], Round((DateDiff(HH, Min(Start_Date), Max(End_Date)+4))/24,1) [Duration] From Kiosk_HistoryThe result for the Start_Date, End_Date, and Duration are as follows:2009-08-27 09:06:02.000 2009-08-29 10:14:31.000 6I am going to need to research "Ceiling" because I've never used or heard of that before. My goal was to represent the result as a fraction. ExampleIf I identify the difference between the two dates is greater than 2 days (or whatever number) but not enough to be calculated as another full day, I will represent it as a decimal -- so in this case 49 hours = 2.04 duration. Any ideas?Group by Location_NameMBeal |
|
|
|
MBeal
Posting Yak Master
110 Posts |
Posted - 2009-09-04 : 13:11:39
|
| Figured it out... Select Distinct Location_Name [Location_Name], Min(Start_Date) [StartDate], Max(End_Date) [EndDate], Cast(DateDiff(HH, Min(Start_Date), Max(End_Date))as decimal)/24 [Duration]From Kiosk_HistoryGroup by Location_NameThanks for the information folks!!!!MBeal |
|
|
|
|
|
|
|
|
Sales Days using Datediff