Finding weekdays of a given month
-
We'll see. (It's not like I haven't been wrong before...) I'll try and set something up and make a new thread asking for yours, Noel's, and sushila's help, (I never see Frank anymore, but hopefully he can put his $0.02 in as well).
Have a great weekend.
I wasn't born stupid - I had to study.
-
Looks like he's waiting for me to hit the 5K mark to start posting again
. -
I gotta agree with THAT! Between a tally table (you guys call it a numbers table) and/or a date table (mine are combined into one which is why I called it "Tally"), the solution to this problem is a breeze that runs in less than a millisecond or so.
As for DBA's that won't allow a WHILE loop of any kind... bravo... you should rarely, if at all, need anything that even resembles a loop even on the more complex stuff. Sometimes it takes a bit of thinking to come up with something set-based, but it's well worth the effort in the long run.
However, there's always that disk-stingy know-it-all BSOFH of a DBA that doesn't understand how little room a tally table takes or what the benefits are... so, you try to go with making a temp table (populated with the normal cross-join to make a temporary tally table) or some other reasonable method and the boy-wonder won't let you do that either. So whata'ya do? Well, the job still has to be done so ya gotta do something... (my least favorite answer here would be "something in the Presentation or Business Layer" despite what Farrell's wife found). Chances are that if the DBA doesn't understand the benefit of a Tally table, he/she won't spot the loop in the code below either, especially if you take out the few comments and smush it all into just a couple of lines like some folks do (left it readable here and meets the request of the original poster...)...
CREATE PROCEDURE dbo.WD4Month
@pMonth VARCHAR(9),
@pYear VARCHAR(4)
AS
--===== Presets
SET NOCOUNT ON
DECLARE @StartDate DATETIME
DECLARE @EndEnd DATETIME
SET @StartDate = CONVERT(DATETIME,LEFT(@pMonth,3)+' 01 '+@pYear,100)
SET @EndEnd = DATEADD(mm,1,@StartDate)
--===== Return the week days for the given month/year
SELECT CONVERT(CHAR(10),(@StartDate + i.n),101) AS [Date]
FROM (--Derived table "i" returns numbers from 0 to 30 (31 items)
SELECT (SELECT COUNT(*) FROM dbo.SYSOBJECTS so1 WHERE so1.ID < so2.ID) AS n
FROM dbo.SYSOBJECTS so2
WHERE (SELECT COUNT(*) FROM dbo.SYSOBJECTS so1 WHERE so1.ID < so2.ID) <=30
) i --End derived table "i"
WHERE (@StartDate + i.n) < @EndEnd
AND DATENAME(dw,@StartDate + i.n) NOT IN ('Saturday','Sunday')
ORDER BY (@StartDate + i.n)
Compared to the calendar table or tally table approaches, this takes a millenia to run (tested out to 0 to 16 milliseconds on a non-server quality box). I wouldn't call this a set-based solution
by any means but it gets it by the BSOFH and it get's the job done.So far as the BSOFH's that won't allow a tally table? Don't give up on them... most are just a bunch of regular joes that have been beat to death by the stupidity of inept developer wanna-be's and users. If you spend a little quality shoulder-to-shoulder time with them, show them what most would erroneously consider to be a set-based solution like the one above (with some timing code, it seems that SET STATISTICS TIME ON doesn't always report correctly for me) and THEN show them how a couple of MB worth of table can improve performance by more than 1,600% AND do it all without challenging THEIR authority or treating them as if THEY were stupid, they'll come around... works on RBAR (Row By Agonizing Row) Developer's, too. It's a real pain in the butt and it sometimes takes a lot of effort (I hate candy coating crap like this!), but it's worth it. Don't expect them to put away the Smith&Wesson though... it's still their server and it's still their data... I wouldn't hire a DBA that didn't have some junk-yard-dog qualities.
As some say, that's my two cent's worth... sorry it turn out to be a buck.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Nice workaround Jeff, I just don't understand why a guy can't spare 80K of space on the hd for a number table??? That's smaller than most of the spam I get these days!!
-
I know what you mean... It's only when they live up to the name of "BSOFH" and they've got that control thing going on... or, they're just plain old stupid.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I vote for a and b
. -
Very funny. I will not forgot this, but I have a bit much on plate right now to get to some type of psuedo-code to give you an idea. (Heck one of the articles in Todays newsletter uses a loop for Proper Casing of names, etc...).
BTW..., I must admit to more ignorance. I do not know what BSOFH means. When I googled it, I got this post. Too funny...
I am sure you cannot state its exact meaning, but maybe you can give me a better direction to look it up. Also, good posting Jeff! We have tried the going to lunch, nice talks, etc. route(s) with the DBA - many teams are having real problems with him. I think it is a gov't power thing... But, on the plus side - it does force you to think of better solutions....
I wasn't born stupid - I had to study.
-
> BSOFH:
>
> It's a term I havn't used (or heard, for that matter) in a couple
> years. Mainly back with "the internet" was just used by people who
> actually knew what it was. It means "bastard sysop from hell" and it's
> the type of person who inflicts pain on their users and friends just to
> see if it can be done. Stuff like removing their routes or setting their
> gateway to some path in Argentina. Not a good way to be. One of those
> old IRC things that comes from the same time as "RTFM". Anybody who
> remembers a while back would refere to a script or action like breaking IP
> headers as a "BSOFH" action. Any more you don't hear comments like that
> any more.
-
Ok, ok... you got me... I've got dirt in my garden that's younger than I am...
As you can tell, though, I've had to put up with more than my fair share of that ill-begotten breed and have had to create all sorts of work arounds to trick the buggers into letting my code in. I can learn new tricks, though... I guess that in this day and age, you can hardly find the "M" in "RTFM"... nowadays, it would be "RTFS". That's provided the BSOFH was kind enough to install the manuals where you can actually get at them. 
LONG LIVE FORUMS!--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Dang! I must be old! None of the newer online slang dictionaries has SOFH or BSOFH! Remi, where did you find that definition?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
It's a secret organisation... and I already said too much
. -
Hi friends,
can one tell me how to find the same result without using procedures.
i wanna find the no of working days between two given dates excluding sat an sun without using procedures.
thanks in advance.
-
http://www.sqlservercentral.com/columnists/jmoden/calculatingworkdays.asp
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 13 posts - 31 through 42 (of 42 total)
You must be logged in to reply to this topic. Login to reply