February 13, 2002 at 12:32 pm
I'm hoping someone can give me a nudge in the right direction on this one - I can't even figure out where to start. Right now I have a query where I'm trying to figure out which records fall within a certain amount of days:
... and datediff(minute, btbin, importdate) >= 1440 and datediff(minute, btbin, importdate) < 2880...
...but I need to find a way to exclude weekends and if possible, holidays listed in a table. Any ideas, anyone?
February 13, 2002 at 12:34 pm
you can use datepart() with a parameter to find out which days are Sat and Sun. Hint: it returns a number. Exclude these by doing a datepart() in (weekdday numbers).
As far as holidays, you'd have to outer join with the table and exclude matching rows.
Steve Jones
February 13, 2002 at 12:46 pm
Ok, I kind of get it... but I'm still a little confused. What I'm doing in this case, is selecting how many of our records fall outside given service level agreements. In this case, how many records took less than 2 days to complete. I guess the part I don't get is how to use datepart in this case. Please help as I am an idiot.
full example:
set @day1 = (select sum(btbpg) as day1 from batchtracker_import where btbactive = 1 and datediff(minute, btbin, importdate) >= 1440 and datediff(minute, btbin, importdate) < 2880 and (impstat = 'X' OR impstat = 'S') and (btbin >= @date2 and btbin < @dateadd))
February 13, 2002 at 1:15 pm
My bad, I misunderstood. Try this:
create table MyTable
( myid int
, my1 datetime
, My2 datetime
)
go
insert MyTable select 1, '01/09/02', '01/14/02'
insert MyTable select 1, '01/10/02', '01/23/02'
insert MyTable select 1, '01/11/02', '01/22/02'
insert MyTable select 1, '01/11/02', '01/29/02'
insert MyTable select 1, '01/14/02', '01/21/02'
insert MyTable select 1, '01/14/02', '01/30/02'
select a.Myid
, datediff( day, a.my1, a.my2) 'days'
, datediff( day, a.my1, a.my2) / 7 'days2'
, datepart( dw, a.my1) 'start day'
, datepart( dw, a.my2) 'end day'
, case
when datepart( dw, a.my1) > datepart( dw, a.my2)
then case
when datediff( day, a.my1, a.my2)< 7
then datediff( day, a.my1, a.my2) - 2
else datediff( day, a.my1, a.my2) - (((datediff( day, a.my1, a.my2) / 7) + 1) * 2)
end
when datepart( dw, a.my1) = datepart( dw, a.my2)
then datediff( day, a.my1, a.my2) - ((datediff( day, a.my1, a.my2) / 7) * 2)
when datepart( dw, a.my1) < datepart( dw, a.my2)
then datediff( day, a.my1, a.my2) - ((datediff( day, a.my1, a.my2) / 7) * 2)
end 'weekdays'
from MyTable a
-- drop table MyTable
Steve Jones
February 13, 2002 at 4:29 pm
Thanks!! That looks like it will do the trick. Any ideas for how I might be able to exclude holidays listed in a table?
February 14, 2002 at 10:49 am
Holidays have alsways caused me an issue. Mainly because each country and sometimes each company has different ones. The way I've handled it in the past is to make a "holidays" table and joined that where I needed. Here you could join to the holidays table and get a count of holdays in the interval. Then subtract that from the days.
Steve Jones
February 14, 2002 at 10:54 am
I had that idea, but I wasn't sure how to join it exactly, since I'm using a date range. Could I join it on the month?
something like this:
on datepart(month, @date1) = datepart(month, holidaydate)
February 14, 2002 at 11:26 am
you need to include day and year, or convert to characters and strip off the times.
Steve Jones
February 15, 2002 at 2:50 pm
Steve -
Thanks for your help. You got me on the right track, but my report is kind of a wierd one. I tried your method along with a brute force attempt (dump report records into temp table, loop through and if/then/else, but this took 50 minutes!) and I finally came up with a pretty quick and easy way to do it. I post it here in the case someone else runs into something like I'm doing.
The solution is to create a table that has precalculated business day values in it based on the day of the week starting and ending (7x7 = 49 rows):
SDow | EDow | BusDays
1 1 0
1 2 1
1 3 2
1 4 3
etc, for all possible starting and ending days of the week. Then I just joined my query with the table based on the datepart(dw) of the starting and ending days. Then I just subtract the amount of holidays (from a table) between the two dates.
My example:
select btbbcn, btbin, sdow, importdate, edow,
case
when datediff(week, btbin, importdate) = 0
then elapsed - (select count(hday) from fdiholidays where btbin <= hdate and hdate <= importdate)
else elapsed * (datediff(week, btbin, importdate)) - (select count(hday) from fdiholidays where btbin <= hdate and hdate <= importdate)
end 'SLA'
from btbatch left outer join bat2import on btbtfbatch = batchno, btreportdays
where datepart(dw, btbatch.btbin) = sdow and datepart(dw, importdate) = edow
order by btbin
This works very cleanly and most importantly, QUICKLY. What do you think?
February 15, 2002 at 3:44 pm
Having such a table is definitely the fastest way to do it, but you have to maintain that table. I built a system where we did this and the first week (or last) of evry year, an accounting had to enter in all the holidays and non-weekend, non-business days. We then populated a table for every day of the year. Slow for that 15 minutes, but the rest of the year was fast!!!!
Steve Jones
February 15, 2002 at 3:59 pm
Well, the beauty of the business day lookup table is that it *never* has to change. Monday is always 3 business days (depending on how you want to count it) from Thursday. That's why there's only 49 rows and the rows will never change. The first two columns are just the numerical values of given days of the week and the last column is just the given amount of business days between the two.
I do have to keep the holiday table updated, but I took the liberty of populating the table for the next 5 years, so that's pretty much taken care of.
February 15, 2002 at 4:31 pm
February 15, 2002 at 4:34 pm
Thanks again for your help, Steve!
March 4, 2002 at 8:04 pm
I got a good one for you guys. I too have developed a string that eliminates weekends and holidays. But the twist I've just discovered is this. We sometimes have actities occurring on the system on weekends when someone comes in to process data. Currently with the '5-business day rule', the weekend data is being omitted from the results in reporting. I need to figure out a way to take a Saturday or a Sunday activity and make it look like a 'weekday' activity so it can be included into the calculations etc.
Anyone have any ideas? In addition to posting here, please please please forward any suggestions to mgr at avsdata.com (the at is acutally @)
thanks
Reno
AVSData.com
March 5, 2002 at 9:36 am
You should probably post whatever your current solution is so we can look at it.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply