Link to home
Create AccountLog in
Avatar of Kyliem
Kyliem

asked on

Need count No. of Sundays in a date range

Language:  MSSQL 2000

Hi, I have a query that gives returns me 52 weeks by date from my current date plus the start & end dates of the month.  I need from these results, count the No. of Sundays in the month.

eg:
DECLARE @Start_Date datetime
DECLARE @Month_Start datetime
DECLARE @Month_End datetime
DECLARE @CNTR int
DECLARE @No_Of_Wks int
set @CNTR=0
set @Start_Date=getdate()
while @CNTR <= 52 begin
set @Start_Date=DATEADD(dd,7,@Start_Date)
set @Month_Start =DATEADD(mm, DATEDIFF(mm,0,@Start_Date), 0)
set @Month_End =DATEADD(ms,-3,DATEADD(mm, DATEDIFF(m,0,@Start_Date  )+1, 0))
print @Start_Date
print @Month_Start
print @Month_End
set @CNTR= @CNTR +1
end

returns me: (when run today Thurs 06 Mar)
Mar 13 2008  2:13PM       (which is today plus 1 week)
Mar  1 2008 12:00AM      (start date of month)
Mar 31 2008 11:59PM      (end date of month)
 
Mar 20 2008  2:13PM    (which is today plus 2 week etc)
Mar  1 2008 12:00AM
Mar 31 2008 11:59PM  
 
...... etc

Apr  3 2008  2:13PM
Apr  1 2008 12:00AM
Apr 30 2008 11:59PM

what I want to do is be able to count the No. of Sundays between my start date of month & end date of month.  Some months are longer than others so I could have 4 or 5 etc.


Avatar of chapmandew
chapmandew
Flag of United States of America image

something like this....

SET DATEFIRST 1

DECLARE @Start_Date datetime
DECLARE @Month_Start datetime
DECLARE @Month_End datetime
DECLARE @CNTR int
DECLaRE @SundayCount INT
DECLARE @No_Of_Wks int
set @CNTR=0
SET @SundayCount = 0
set @Start_Date=getdate()
while @CNTR <= 52 begin
SET @SundayCount = @SundayCount + case when datepart(dw, dateadd(day, @CNTR, @Start_Date)) = 1 THEN 1 ELSE 0 END

set @Start_Date=DATEADD(dd,7,@Start_Date)
set @Month_Start =DATEADD(mm, DATEDIFF(mm,0,@Start_Date), 0)
set @Month_End =DATEADD(ms,-3,DATEADD(mm, DATEDIFF(m,0,@Start_Date  )+1, 0))
print @Start_Date
print @Month_Start
print @Month_End
set @CNTR= @CNTR +1
end
SELECT @SundayCount
ASKER CERTIFIED SOLUTION
Avatar of dportas
dportas

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Kyliem
Kyliem

ASKER

Hi, thanks for the postings.  
Chapmandew, I couldn't seem to get a correct coutn using your sql, it returned a value of 8 in the final select statement.  
dportas, whilst i have accepted your answer I didn't actually use it since in between posting I found the following url forum which gave me a very similar solution and which I am using.  As this and yours both pointed to using a calendar type table I have awarded pts on this basis rather than request a refund.
If either expert believe this to be unfair please advise & I will discuss with community support.  Thanks again
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519