October 19, 2007 at 5:35 am
Jeff Moden (10/18/2007)
Ok... just ran Matt's code against the million row table... Ladies and Gentlemen, we have a tie! :w00t: Matt's function took the same amount of time as mine.One thing that everyone should note... we haven't tested either function for a negative number of days... but I'm pooped and I gotta get my beauty sleep 😛
See ya tomorrow...
Hehe - I sat bolt upright @ 2:30am with the SAME reaction..."I wonder if it works for subtraction":w00t:
If I get a chance this weekend - I will get it some time.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 19, 2007 at 6:37 am
Greg Snidow (10/19/2007)
Sergiy, wouldn't it be possible to have a small table of only hollidays, and if @DateEnd falls on one of those days just add 1?
Bloody site!
Wiped out my response!
Silly me - did not watch for it.
OK, short version:
If you need "Holiday table" anyway, if you need to scan for the holidays in that table - why scan table AND run UDF?
Why not just include all non-working days including weekends and perform one check instead of two?
_____________
Code for TallyGenerator
October 19, 2007 at 6:50 am
Sergiy (10/19/2007)
OK, short version:
If you need "Holiday table" anyway, if you need to scan for the holidays in that table - why scan table AND run UDF?
Why not just include all non-working days including weekends and perform one check instead of two?
I gues that makes sense. I have a very small database, so I have not really ever considered overhead costs of doing things. Got any suggestions on where to start looking in BOL to try to understand? I see a lot about scans vs seeks, so I am assuming that might be a good starting point.
Greg
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 19, 2007 at 6:58 am
Greg Snidow (10/19/2007)
I gues that makes sense. I have a very small database, so I have not really ever considered overhead costs of doing things. Got any suggestions on where to start looking in BOL to try to understand? I see a lot about scans vs seeks, so I am assuming that might be a good starting point.Greg
Did you try the code I posted?
There was no any scan involved when I tried it on my machine.
_____________
Code for TallyGenerator
October 19, 2007 at 7:00 am
Jeff Moden (10/18/2007)
In the mean time... It took me a couple of hours to figure it out, but I took quite a different approach... and, the function I built gives you the choice of whether to include or exclude the starting day... of course, I did it for U.S. applications... some mods would have to be made for non-U.S. apps...
First, Jeff, I like the functionality of being able to choose whether to count the start day. There are, of course, two trains of thought regarding that in my office.
Secondly, too all of you, I am amazed by how you guys can effictively do in a matter of hours, what it took me days, and sometimes weeks just to get to the point where I am totally confused. I wish now I could go back to '90, a fresh pup out of high school, unknowingly on the cusp of making some very foolish life decisions. Anyhow, I admire you all, and am very glad Jeff turned me on to this forum. Thank you.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 19, 2007 at 7:06 am
Sergiy (10/19/2007)
Greg Snidow (10/19/2007)
Sergiy, wouldn't it be possible to have a small table of only hollidays, and if @DateEnd falls on one of those days just add 1?
Bloody site!
Wiped out my response!
Silly me - did not watch for it.
OK, short version:
If you need "Holiday table" anyway, if you need to scan for the holidays in that table - why scan table AND run UDF?
Why not just include all non-working days including weekends and perform one check instead of two?
Agreed - would likely be faster. But that's a different requirement:) As to counting the startdate or not - that's simply a matter of decreasing the @daysToAdd by one or not? Also another another "new" requirement.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 19, 2007 at 8:59 am
Well, there seems to be a few different ways to implement this feature of 'workdays'...
Atm it seems the race is about millions of rows in batchmode, and 30 days. But didn't the op ask about 5 days, and it also seems that 'millions' isn't anywhere near his requirement?
Another thing that also may have some impact on how to implement it, is what is it for?
That is, how is it going to be used?
Inline with some transaction? (ie while a row is added)
In batch? Doing the 'random dates by the millions' thing? (or perhaps only 'by the thousands')
Is the number of days fixed, or should the 'feature' support dynamic range? Must it do so?
Depending on what is 'good enough' for the purpose, there's some different ways to do this.
But if you settle for a more strict solution, that is 'good enough', then that will likely be far less complex and complicated than any possible solution that will try to cover all corners...
Point is, sometimes (most of the times?) specialization is more efficient than generalization.
/Kenneth
October 19, 2007 at 6:20 pm
Greg Snidow (10/19/2007)
Secondly, too all of you, I am amazed by how you guys can effictively do in a matter of hours, what it took me days, and sometimes weeks just to get to the point where I am totally confused. I wish now I could go back to '90, a fresh pup out of high school, unknowingly on the cusp of making some very foolish life decisions. Anyhow, I admire you all, and am very glad Jeff turned me on to this forum. Thank you.
Heh... thanks for making me feel old, Greg... I'm from the class of '70 :w00t: Fun part about getting this old is people think I'm hard of hearing... when they ask me if I am, I say "No... I'm just tired of listening". 😛
Anyway, I'm tickled that you decided to sign up and even more tickled that you decided to stick around, Greg... there's lots of good folks and lots of good info here.
You've already run into one of my best friends on this forum and I should warn newbies like you about folks like him... he has absolutely no manners, has an extremely dry sense of humor, will ride rough shod over anyone who challenges him without the proper knowledge, "speaks" in a terribly short and condescending manner to all who deserve it, and will verbally kick your butt at any sign of weakness. I've seen him keep at least 2 threads going for over 300 posts each just because he loves to argue with people that don't know any better. He also happens to be one of the smartest and most practical buggers I know and will force you to teach yourself. You'll figure out who he is...
Then, there's other folks that have all the same attributes and are just plain stupid. You'll figure out who those are, too.
I just had to warn you about both these types... this isn't the nice quiet little forum that you and I originally came from... some of these guys and gals use some really sharp knives to get their point across and don't ever let that discourage you. The important part is that at the end, you'll know...
--Jeff Moden
Change is inevitable... Change for the better is not.
October 19, 2007 at 6:46 pm
Why not just include all non-working days including weekends and perform one check instead of two?
Larger number of rows? Correlated aggragate sub-query on large number of rows? Dunno...
I've not had to "future-cast" in this manner before but every solution I've seen on other posts and forums that uses a full date table or even a partial date table as you suggest just totally gags on performance in batches.
Date table works great for RBAR GUI.
Guess I have a little more research to do on this... if anyone finds or knows of date table code that performs well in million row batches, please step forward... even an old dog like me can learn a new trick. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
October 19, 2007 at 9:01 pm
Jeff Moden (10/19/2007)
You've already run into one of my best friends on this forum and I should warn newbies like you about folks like him... he has absolutely no manners, has an extremely dry sense of humor, will ride rough shod over anyone who challenges him without the proper knowledge, "speaks" in a terribly short and condescending manner to all who deserve it, and will verbally kick your butt at any sign of weakness. I've seen him keep at least 2 threads going for over 300 posts each just because he loves to argue with people that don't know any better. He also happens to be one of the smartest and most practical buggers I know and will force you to teach yourself. You'll figure out who he is...
While I agree with you about almost all of his attributes, I disagree with two of them that I bolded above. He's the same way whether they deserve it or not,ifthey disagree with him. He also loves to argue even with people who do know better, even ones who have absolutely proven him wrong. The rest, yep, he's grumpy as hell, and does indeed know his stuff. His only problem is that he thinks he also knows everything, including things that aren't really his "stuff".
He could be one of the 5 most valuable posters on this board (one of which is quite definitely you). As it is, especially with his treatment of new members who are just getting started, I haven't even decided if he brings more positive than negative, in my opinion. It's a shame, actually. Us old hands just laugh at him when he gets that way, so he has no negative impact on us, but he likely drives away some people who could benefit both themselves and the SQL community by sticking it out. We all were new to this at one point, even if it's been so long for some of us that we barely remember it. It would probably be in everyone's best interest if we tried to dig up that memory when dealing with new SQL folks.
October 20, 2007 at 10:58 pm
Jeff Moden (10/19/2007)
Larger number of rows? Correlated aggragate sub-query on large number of rows? Dunno...I've not had to "future-cast" in this manner before but every solution I've seen on other posts and forums that uses a full date table or even a partial date table as you suggest just totally gags on performance in batches.
Date table works great for RBAR GUI.
Guess I have a little more research to do on this... if anyone finds or knows of date table code that performs well in million row batches, please step forward... even an old dog like me can learn a new trick. 😀
Probably you never got into this task seriously.
I needed to do roster for organization with different rules for Saturdays, Sundays, "primary" holidays, "secondary" holidays, "overtimes" (working for > 8 hours per day or > 40 (38 is Aus) hours per week), etc.
It was about counting working days, replacing "ordinal" personal with those who work on "primary" holidays, different pay rates for working on different time spans.
Guys working with me created brilliant program on VB6 with heaps of hardcoded values which did the roster for entire month in less than 7 hours.
When I showed them single SELECT statement returning the whole roster in seconds they abandoned everything and were trying to prove it cannot be right for a week. 🙂
Believe me it was done using "Holidays" and some other time framing tables.
_____________
Code for TallyGenerator
October 21, 2007 at 9:22 am
Sergiy (10/20/2007)
Probably you never got into this task seriously.
Yeah... absolutely correct... and I guess I need to learn how to use a date table for these types of things with some performance in mind...
Any chance of you providing an example that I could learn from? What I'd really be interested in is seeing an example that, given a large table with columns for StartDate and EndDate (all of which are filled in and has simple PK of IDENTITY column), calculate and return the PK, StartDate, EndDate, and work days between the StartDate and EndDate using a date table.
Not challenging you in your knowledge... I really want to know because I just don't know how to do it with some good performance...
Thanks, Serqiy.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 21, 2007 at 7:07 pm
Kenneth Wilhelmsson (10/19/2007)
Another thing that also may have some impact on how to implement it, is what is it for?That is, how is it going to be used?
Inline with some transaction? (ie while a row is added)
In batch? Doing the 'random dates by the millions' thing? (or perhaps only 'by the thousands')
Is the number of days fixed, or should the 'feature' support dynamic range? Must it do so?
Depending on what is 'good enough' for the purpose, there's some different ways to do this.
But if you settle for a more strict solution, that is 'good enough', then that will likely be far less complex and complicated than any possible solution that will try to cover all corners...
Point is, sometimes (most of the times?) specialization is more efficient than generalization.
/Kenneth
Kenneth, I am using it for setting the delivery date for email notification in an email staging table. I am experimenting with a set up suggeted by Sergiy in my post a few days ago called 'trigger help'. Anyhow, I need to be able to set a delivery date of an email based on the enter date of a record. It just so happens this person wanted 5, 10, and 20 day notification using week days only. It just as well could have been 37 weekdays, or 63, or whatever it needs to be.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 22, 2007 at 2:19 am
How to you select/copy/paste the sample code in the gray background areas?
When I do this I don't get any line breaks.
October 22, 2007 at 2:29 am
Greg,
ok, and how are these mails staged?
one-by-one? By the thousands? (or millions?)
Are you looking for the 'batch-approach' that Jeff speaks of, or is it less?
Just trying to set the frames for where 'good enough' might be...
/Kenneth
Viewing 15 posts - 46 through 60 (of 117 total)
You must be logged in to reply to this topic. Login to reply