November 21, 2011 at 11:44 am
I am kind stuck with fuction .
I want to write a fuction that calcualte no. of weekdays per month.
Like in @fromdate= '2011-01-01' and @todate='2011-01-31'
And I want to calculate no. of mondays in this month......answer is 5.
tuesday in this month ..........answer is 4
This is my create function scripty
create FUNCTION [dbo].[f_WeekdaysPerMonth] (@fromDate datetime, @toDate datetime,@dayoftheweek varchar(100))
RETURNS INT
AS
BEGIN
.....(I need here a script )
end
I am considering @dayoftheweek =1 then sunday
@dayoftheweek =2 then Monday
@dayoftheweek =3then tuesday
@dayoftheweek =4 then wed.
@dayoftheweek =5 then thus
@dayoftheweek =6 then friday
@dayoftheweek =7 then saturday.
Please help me.
Thanks
P
November 21, 2011 at 11:54 am
You are much better off creating a dates table. This is just one of it's many uses.
http://www.sqlservercentral.com/scripts/Date/68389/
While you're at it, you might as well create a Tally table too.
http://www.sqlservercentral.com/articles/T-SQL/62867/
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 21, 2011 at 11:56 am
Second the motion on using a calendar table. They're tremendously useful, and will be MUCH faster than the kind of function you built.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 21, 2011 at 11:59 am
yet another who concurs on the value of a Calendar table;
then, depending on the columns /construction you add, you could do somethign as simple as this:
SELECT COUNT(*) FROM TallyCalendar
Where [Weekday]='Monday'
AND TheDate between @fromdate AND @ToDate
Lowell
November 21, 2011 at 12:02 pm
Thanks For reply but Can you provide me exact Solution...
I do not want to create calender table.
Thanks
Bhavesh
November 21, 2011 at 12:06 pm
bhaveshp.dba (11/21/2011)
Thanks For reply but Can you provide me exact Solution...
I do not want to create calender table.
Thanks
Bhavesh
ugg; to me, that sounds like "i don't want to use multiplication. can you show me how to repetitively add these numbers together instead"
the right tool for the job was suggested, anything else is a slower version, or a build-it-on-the fly variation of the same idea.
Lowell
November 21, 2011 at 12:08 pm
Provided Sunday is your DATEFIRST
SELECT
sum(CASE WHEN datepart(dw,TheDate) BETWEEN 2 AND 6 THEN 1 ELSE 0 END)
WHERE TheDate between @fromdate AND @ToDate
EDIT: Typo corrected.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 21, 2011 at 12:09 pm
Thanks Lowell,
But I have some condition with this fuction so i want to create this fuction instead of using another.
Thanks
Bhavesh
November 21, 2011 at 12:10 pm
Jason Selburg (11/21/2011)
Provided Sunday is your DATEFIRST
SELECT
sum(CASE WHEN datepart(dw,getdate()) BETWEEN 2 AND 6 THEN 1 ELSE 0 END)
WHERE TheDate between @fromdate AND @ToDate
OOPS, change the getdate() to TheDate
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 21, 2011 at 12:16 pm
Here's a solution that doesn't use a Calendar table:
CREATE PROC dbo.DeleteMe
(@WeekDay INT,
@FromDate DATE,
@ToDate DATE)
AS
SET NOCOUNT ON ;
DECLARE @Date DATE = @FromDate,
@Days INT = 0 ;
WHILE @Date <= @ToDate
BEGIN
IF DATEPART(WeekDay, @Date) = @WeekDay
SET @Days = @Days + 1 ;
SET @Date = DATEADD(DAY, 1, @Date) ;
END ;
SELECT @Days AS [WeekDays] ;
Note: DON'T USE THIS SOLUTION!!!!!
It will work, but it's a really, really, really bad idea to use something like this. A Calendar table is simpler, easier, works better, and does more.
First, this function is completely dependent on the DateFirst and Language settings of the server, and on the language of the connection to the server. That means it will give the wrong results if the settings are changed. There is NO easy way to fix that. A Calendar table won't have that problem at all.
Second, this will be very slow compared to querying a Calendar table. Won't matter if you run it once or twice in a dev environment. Will matter a lot if it gets used in production code with lots of concurrency.
There's a long list after that of reasons to not do this programmatically. That's just the biggest issue.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 21, 2011 at 12:25 pm
GSquared (11/21/2011)
Here's a solution that doesn't use a Calendar table:
CREATE PROC dbo.DeleteMe(@WeekDay INT,
@FromDate DATE,
@ToDate DATE)
AS
SET NOCOUNT ON ;
DECLARE @Date DATE = @FromDate,
@Days INT = 0 ;
WHILE @Date <= @ToDate
BEGIN
IF DATEPART(WeekDay, @Date) = @WeekDay
SET @Days = @Days + 1 ;
SET @Date = DATEADD(DAY, 1, @Date) ;
END ;
SELECT @Days AS [WeekDays] ;
Note: DON'T USE THIS SOLUTION!!!!!
It will work, but it's a really, really, really bad idea to use something like this. A Calendar table is simpler, easier, works better, and does more.
First, this function is completely dependent on the DateFirst and Language settings of the server, and on the language of the connection to the server. That means it will give the wrong results if the settings are changed. There is NO easy way to fix that. A Calendar table won't have that problem at all.
Second, this will be very slow compared to querying a Calendar table. Won't matter if you run it once or twice in a dev environment. Will matter a lot if it gets used in production code with lots of concurrency.
There's a long list after that of reasons to not do this programmatically. That's just the biggest issue.
Yep, and DONT USE MY SOLUTION EITHER. I wrote it way to fast and didn't even think it through. IT WONT PROVIDE THE RESULTS YOU WANT ANYWAY.
Now I wish I could DELETE my post... **going back into my hole now**
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 22, 2011 at 7:59 am
🙂
Thanks You so Much.....
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply