How to calculate total number of sunday

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How to calculate total number of sunday

Author

Topic

Chandan
Starting Member

9 Posts

Posted - 2006-04-26 : 04:26:43

How to calculate total number of sunday between given date
for example
to calculate total number of sunday between(25/3/2005 ,25/10/2005)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-26 : 04:30:27

refer to similar thread here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65156

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-04-26 : 05:35:13

And to adapt what's in that thread for you...

--inputs
declare @startdate datetime, @enddate datetime
set @startdate = '20050325'
set @enddate = '20051025'

--calculation
declare @NumberOfDays int
set @NumberOfDays = datediff(d, @startdate, @enddate) + 1
set datefirst 7
set rowcount @NumberOfDays

declare @numbers table (i int identity(0,1), x bit)
insert into @numbers select null from master.dbo.sysobjects a, master.dbo.sysobjects b, master.dbo.sysobjects c
set rowcount 0

select count(*) as 'NumberOfSundays' from
    (select dateadd(dd, i, @startdate) d from @numbers) dates
where datepart(dw, d) = 1

It keeps it simple by creating a derived table (dates) of all the days between the 2 dates and then querying that.

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-26 : 05:53:32

That's very kind of you Ryan

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-04-26 : 07:35:15

That's the kind of guy I am, khtan

(Well, sometimes - when I'm not busy

)

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Chandan
Starting Member

9 Posts

Posted - 2006-04-26 : 07:50:36

Thanks khtan
i got the answer but this query is a bit complex to understand .Can u make me understand how this query goes

Chandan
Starting Member

9 Posts

Posted - 2006-04-26 : 07:54:22

Thanks Ryan Randall

i got the answer in one go from ur query.Can u make me it simpler how it works

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-04-26 : 08:08:10

Hi Chandan,

I've added in a few queries (in red) so you can see how it progresses...

--inputs
declare @startdate datetime, @enddate datetime
set @startdate = '20050325'
set @enddate = '20051025'

--calculation
declare @NumberOfDays int
set @NumberOfDays = datediff(d, @startdate, @enddate) + 1
set datefirst 7
set rowcount @NumberOfDays

select @NumberOfDays as 'Number of days between start and end date'

--create a table of numbers (very useful in all kinds of places) to help create a table of dates.
declare @numbers table (i int identity(0,1), x bit)
insert into @numbers select null from master.dbo.sysobjects a, master.dbo.sysobjects b, master.dbo.sysobjects c
set rowcount 0

-- table of every day between start and end date and whether it's a Sunday or not
select *, datepart(dw, d) as 'WeekDayNumber',
    case when datepart(dw, d) = 1 then 1 else 0 end as 'IsSunday' from
        (select dateadd(dd, i, @startdate) d from @numbers) dates

select count(*) as 'NumberOfSundays' from
    (select dateadd(dd, i, @startdate) d from @numbers) dates
where datepart(dw, d) = 1

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-04-26 : 09:12:02

I know it's cheating to use a date table for this, but the code is a little more compact.

Date Table Function F_TABLE_DATE:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519


declare @start datetime, @end datetime
set @start = '20050325'
set @end = '20051025'

select
	Sundays = count(*)
from
	dbo.F_TABLE_DATE(@start,@end)
where
	DAY_OF_WEEK = 1

Results:


Sundays     
----------- 
31

(1 row(s) affected)

CODO ERGO SUM

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-04-26 : 09:32:39

Yes that is cheating!

I'm going to have to start referencing your table myself, Michael, rather than re-creating (a small part of) it from scratch and then have you steal some of the glory!

(BTW - don't forget about datefirst issues...)

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-04-26 : 11:08:45

quote:
Originally posted by RyanRandall

Yes that is cheating!

I'm going to have to start referencing your table myself, Michael, rather than re-creating (a small part of) it from scratch and then have you steal some of the glory!

(BTW - don't forget about datefirst issues...)

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

The code in the F_TABLE_DATE function is independent of the setting of datefirst.

All the date related functions (F_START_OF_WEEK, F_END_OF_WEEK, etc.) I have posted in the Script Library forum are independent of the setting of date first.

CODO ERGO SUM

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-04-26 : 13:21:08

quote:
Originally posted by Michael Valentine Jones
The code in the F_TABLE_DATE function is independent of the setting of datefirst.

All the date related functions (F_START_OF_WEEK, F_END_OF_WEEK, etc.) I have posted in the Script Library forum are independent of the setting of date first.

CODO ERGO SUM

OK - makes sense. My apologies.

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-04-26 : 13:36:00

Chandan,

The sysobjects thing was just to get a table of numbers. But if you use Michael's table of dates you can do (for your latest requirement)...

select
  month, count(*) as sundays
from
  dbo.F_TABLE_DATE('20050101', '20051231')
where
  DAY_OF_WEEK = 1
group by month

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-04-26 : 13:45:35

quote:
Originally posted by RyanRandall

Chandan,

The sysobjects thing was just to get a table of numbers. But if you use Michael's table of dates you can do (for your latest requirement)...

select
month, count(*) as sundays
from
dbo.F_TABLE_DATE('20050101', '20051231')
where
DAY_OF_WEEK = 1
group by month

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

I think the question you are answering is actually on this thread:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65156

CODO ERGO SUM

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2006-04-26 : 15:52:00

You can also consider trying this query:

declare @startdate datetime, @enddate datetime
set @startdate = '20050325'
set @enddate = '20051025'

select
DATEDIFF(wk, DATEADD(d, CASE WHEN DATEPART(dw, @startdate) = 7 THEN 1 ELSE 0 END, @startdate), DATEADD(d, CASE WHEN DATEPART(dw, @enddate) = 7 THEN 1 ELSE 0 END, @enddate))

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-04-26 : 17:32:15

quote:
Originally posted by nosepicker

You can also consider trying this query:

declare @startdate datetime, @enddate datetime
set @startdate = '20050325'
set @enddate = '20051025'

select
DATEDIFF(wk, DATEADD(d, CASE WHEN DATEPART(dw, @startdate) = 7 THEN 1 ELSE 0 END, @startdate), DATEADD(d, CASE WHEN DATEPART(dw, @enddate) = 7 THEN 1 ELSE 0 END, @enddate))

Doesn't seem to work right with this date range:


declare @startdate datetime, @enddate datetime

set @startdate = '20050327'
set @enddate   = '20051025'

select SundayNP = 
DATEDIFF(wk, DATEADD(d, CASE WHEN DATEPART(dw, @startdate) = 7 THEN 1 ELSE 0 END, @startdate), DATEADD(d, CASE WHEN DATEPART(dw, @enddate) = 7 THEN 1 ELSE 0 END, @enddate)) 

select
	Sundays = count(*)
from
	dbo.F_TABLE_DATE(@startdate,@enddate)
where
	DAY_OF_WEEK = 1

Results:


SundayNP    
----------- 
30

(1 row(s) affected)

Sundays     
----------- 
31

(1 row(s) affected)

CODO ERGO SUM

Chandan
Starting Member

9 Posts

Posted - 2006-04-27 : 01:44:44

Thanks nosepicker for your code which helped me a lot.
Can i know how your code goes means a short dry run from you

Chandan
Starting Member

9 Posts

Posted - 2006-04-27 : 07:26:27

Thanks nosepicker for your code which helped me a lot.
Can i know how your code goes means a short dry run from you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources