Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 SQL Function : Find ‘X’ Business Days in the Future Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, February 5, 2009 2:08 PM
 SSC-Addicted Group: General Forum Members Last Login: Tuesday, July 7, 2015 10:24 AM Points: 440, Visits: 921
 :peterYou 'da man!scalar value results: 500 rows: CPU time = 844 ms, elapsed time = 879 ms.15,000 rows: CPU time = 25531 ms, elapsed time = 25603 ms.tabular results:500 rows: CPU time = 2703 ms, elapsed time = 2699 ms.15,000 rows: CPU time = 6656 ms, elapsed time = 6654 ms.i added another set size for the tabular results:500 rows: CPU time = 2734 ms, elapsed time = 2738 ms.5000 rows: CPU time = 3969 ms, elapsed time = 3967 ms.15000 rows: CPU time = 6656 ms, elapsed time = 6663 ms.So, the in-line tabular method takes a sec to get out the blocks, but once it gets going, it flies! There is an error in your calculation somewhere (it miscalculates the date) but I'm sure it's a simple oversight. Thanks for sharing your talent!
Post #651269
 Posted Thursday, February 5, 2009 2:14 PM
 Ten Centuries Group: General Forum Members Last Login: Yesterday @ 7:08 AM Points: 1,234, Visits: 374
 I really like that solution too Jim - really simple and yet so neat :)
Post #651275
 Posted Thursday, February 5, 2009 2:17 PM
 Ten Centuries Group: General Forum Members Last Login: Yesterday @ 7:08 AM Points: 1,234, Visits: 374
 scalar value results: 500 rows: CPU time = 844 ms, elapsed time = 879 ms.15,000 rows: CPU time = 25531 ms, elapsed time = 25603 ms.tabular results:500 rows: CPU time = 2703 ms, elapsed time = 2699 ms.15,000 rows: CPU time = 6656 ms, elapsed time = 6654 ms.So my question here would be do you assume worst case that it's going to be hit really hard and go for the tabular method and take a hit if only doing a few rows or vice versa?
Post #651279
 Posted Thursday, February 5, 2009 2:46 PM
 Hall of Fame Group: General Forum Members Last Login: Tuesday, November 29, 2016 2:42 PM Points: 3,370, Visits: 6,889
 So my question here would be do you assume worst case that it's going to be hit really hard and go for the tabular method and take a hit if only doing a few rows or vice versa? It won't be the same from system to system. I'm considering doing two versions, one for long batch jobs like reports and one for interactive. Before choosing, you really need to study your data and the nature of your workload. Reports shouldn't drag on for minutes that could be generated in seconds, but displays shouldn't take two seconds of DB time if they can be delivered in a fraction of a second. On the other hand, if the calculation is done for a small number of rows only a few times a day, then a couple of seconds isn't that big a deal. You just have to think about the ultimate consequences of your choice, whatever it may be.That said, do we have any performance numbers from precalculated table approaches? __________________________________________________Against stupidity the gods themselves contend in vain. -- Friedrich Schiller Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Post #651296
 Posted Friday, February 6, 2009 6:31 AM
 SSChampion Group: General Forum Members Last Login: Monday, August 29, 2016 1:09 PM Points: 13,999, Visits: 9,728
 Bob Hovious (2/5/2009)So my question here would be do you assume worst case that it's going to be hit really hard and go for the tabular method and take a hit if only doing a few rows or vice versa? It won't be the same from system to system. I'm considering doing two versions, one for long batch jobs like reports and one for interactive. Before choosing, you really need to study your data and the nature of your workload. Reports shouldn't drag on for minutes that could be generated in seconds, but displays shouldn't take two seconds of DB time if they can be delivered in a fraction of a second. On the other hand, if the calculation is done for a small number of rows only a few times a day, then a couple of seconds isn't that big a deal. You just have to think about the ultimate consequences of your choice, whatever it may be.That said, do we have any performance numbers from precalculated table approaches?Yes. 1-million rows in about 17 seconds. Less than 1 millisecond for a single-row calculation. Posted earlier in the thread. In other words, over 100 times faster than the second-place version. - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETCProperty of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #651566
 Posted Friday, February 6, 2009 1:09 PM
 Hall of Fame Group: General Forum Members Last Login: Tuesday, November 29, 2016 2:42 PM Points: 3,370, Visits: 6,889
 I was getting 1 million rows in 8.5-9.5 seconds on my laptop without precalculation, but 4 ms for a single row. So faster in batch, but slower in low volumes. Sounds like pre-calc might be the most versatile solution. __________________________________________________Against stupidity the gods themselves contend in vain. -- Friedrich Schiller Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Post #651945
 Posted Wednesday, February 18, 2009 5:21 AM
 Grasshopper Group: General Forum Members Last Login: Friday, September 9, 2016 8:09 AM Points: 12, Visits: 107
 Here's a quick 2005 example of a single select using a tally table. stage 1 uses a tally table to generate a set of dates.stage 2 adds a date enumeration after removing the non-working days (Sat/Sun)stage 3 gets the matching 'business days' using the enumeration.If you need to include a holiday table then include an outer join/is null or 'not in' at stage 2.`select max(dts) from ( select dts, ROW_NUMBER ( ) over (order by dts) as nRow from ( select dateadd(dd,datediff(dd,0,GETDATE ()),0)+n as dts from dbo.Tally where n < @requiredDays) x where DATENAME(dw, dts) not in ('Saturday','Sunday') ) ywhere nRow < @requiredDays;`G.
Post #659256
 Posted Tuesday, April 28, 2009 10:34 AM
 Forum Newbie Group: General Forum Members Last Login: Monday, May 11, 2009 5:15 AM Points: 7, Visits: 18
 I'm trying to use this function but need my begin date to be today's date, trying [dbo].fn_AddBizDays(GETDATE, 1) but not working does anyone know how to get this operational from todays date?
Post #706069
 Posted Tuesday, April 28, 2009 10:44 AM
 Hall of Fame Group: General Forum Members Last Login: Tuesday, November 29, 2016 2:42 PM Points: 3,370, Visits: 6,889
 GETDATE() is a function. You have to include the parens after it. [dbo].fn_AddBizDays(GETDATE(), 1)Be advised that GETDATE includes the current time as well as date, unless fn_AddBizDays strips it off. __________________________________________________Against stupidity the gods themselves contend in vain. -- Friedrich Schiller Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Post #706077
 Posted Wednesday, April 29, 2009 6:37 AM
 Forum Newbie Group: General Forum Members Last Login: Monday, May 11, 2009 5:15 AM Points: 7, Visits: 18
 thanks bob..
Post #706735

 Permissions