Calculating the Weekend Days(Saturaday,Sunday)

[Manikgisl]

HI,

I have two different dates

26-12-2008 to 26-01-2009
these are the two dates ..

i want calculate number of saturday and sundays in between two date


anybody having an idea....
Pls help me

With Regards,

Mani

[ck9663]

What's the biggest (in days) the possible range of your FromDate and ToDate?


-- CK

[Jibran]

You can do a count() or have a countter for each type of day for the number of entries that return Saturday and Sunday using DATENAME() function.

[Manikgisl]

hi
I Having the 26 27 28 29 30 as Fields in our tables ..
i want marked 'O' in the Saturday and Sunday Coloumn\


Thanks In advance

With regards
Mani

[Delerna]

HI,
I have two different dates 26-12-2008 to 26-01-2009
i want calculate number of saturday and sundays in between two date

Hi Mani
This is purely a matematics exercise
Here is one method (I think it is right) :-}

[code=sql]
declare @Start datetime,@End Datetime
set @Start='2009-03-14'
set @End='2009-03-29'


select WkDay,
TotDays,
DaysTillNextSat ,
DaysTillNextSun ,
(7-DaysTillNextSat )/7+(TotDays-DaysTillNextSat )/7+case when DaysTillNextSat <=totdays and DaysTillNextSat <>0 then 1 else 0 end as NumSats,
(8-DaysTillNextSun )/7+(TotDays-DaysTillNextSun )/7 as NumSuns
from
( select WkDay,TotDays,
7-WkDay as DaysTillNextSat ,case when 7-WkDay+1=7 then 0 else 7-WkDay+1 end as DaysTillNextSun
from( select datepart(dw,@St art) as WkDay,
datediff(dd,@St art,@End)as TotDays
)a
)a
[/code]

I have tried to write the query so you can follow my logic (hopefully)
rather than giving you a concice query

Take the query, analyse it and reduce it so that it returns only the fields you need.
When done, make a user defined function out of it.

I hope it helps you

If anyone can come up with a mathematically neater way then I for one would like to see it

Regards

[Delerna]

I thought I might try and explain this line a little bit
[code=sql]
(7-DaysTillNextSat )/7+(TotDays-DaysTillNextSat )/7+case when DaysTillNextSat <=totdays and DaysTillNextSat <>0 then 1 else 0 end as NumSats
[/code]

TotDays is the number of days in the date range

(7-DaysTillNextSat )/7 will be 1 if @StartDate is a saturday otherwise 0

This bit
(TotDays-DaysTillNextSat )/7
gives the number of saturdays (one per week)
over and above @StartDate being a saturday

However, if
Totdays-DaysTillNextSat
is less than 7 then it will be 0
but there still might be a saturday somwere in there

so this bit
case when DaysTillNextSat <=totdays and DaysTillNextSat <>0 then 1 else 0 end
corrects the result in that case

The DaysTillNextSat <>0 criteria is there because
@Startdate being saturday has already been covered

[stijn0507]

this function is close but seems not to work 100% ( at least on my end)
Please try these DateTime Stamps

2009-04-03 11:42:36 (yyyy-mm-dd)
2009-04-06 09:16:49

It should come up with 1 Sat and 1 Sun, but for me it only return 1 Sat and 0 Sun.

[ck9663]

Let me try.......

This query will return all the days from your start date to end date....

declare @startdate as datetime, @enddate as datetime

select  @startdate = '12-26-2008', @enddate = '01-26-2009'

select    
   dateadd(dd,days,@startdate), datename(dw,dateadd(dd,days,@startdate))
from
(select top 365 colorder - 1 as days from master..syscolumns where id = -519536829 order by colorder) x
where datediff(dd,dateadd(dd,days,@startdate),@enddate) >= 0

Here's the code that will count all weekends..

select
   sum(       
   case 
      when datename(dw,dateadd(dd,days,@startdate)) in ('SATURDAY', 'SUNDAY') then 1
      else 0
   end)
from
(select top 365 colorder - 1 as days from master..syscolumns where id = -519536829 order by colorder) x
where datediff(dd,dateadd(dd,days,@startdate),@enddate) >= 0

so is this...

select
   count(*)
from
(select top 365 colorder - 1 as days from master..syscolumns where id = -519536829 order by colorder) x
where datediff(dd,dateadd(dd,days,@startdate),@enddate) >= 0 and datename(dw,dateadd(dd,days,@startdate)) in ('SATURDAY', 'SUNDAY')

All queries will run if your dates are one year apart. If you need more, adjust the TOP 365 portion.


--- CK

[Delerna]

Yea, you're right
In my explanation of the query I said

However, if
Totdays-DaysTillNextSat
is less than 7 then it will be 0
but there still might be a saturday somewere in there

so this bit
case when DaysTillNextSat <=totdays and DaysTillNextSat <>0 then 1 else 0 end
corrects the result in that case

The DaysTillNextSat <>0 criteria is there because
@Startdate being saturday has already been covered

I didn't do the same thing for sunday
so in the above explanation substitute sunday for saturday
and add the code being described to the NumSuns calculation

specifically, this bit

+case when DaysTillNextSun <=totdays and DaysTillNextSun <>0 then 1 else 0 end


[code=sql]
declare @Start datetime,@End Datetime
set @Start='2009-04-3 11:42:36'
set @End='2009-04-06 09:16:49'


select WkDay,
TotDays,
DaysTillNextSat ,
DaysTillNextSun ,
(7-DaysTillNextSat )/7+(TotDays-DaysTillNextSat )/7+case when DaysTillNextSat <=totdays and DaysTillNextSat <>0 then 1 else 0 end as NumSats,
(8-DaysTillNextSun )/7+(TotDays-DaysTillNextSun )/7+case when DaysTillNextSun <=totdays and DaysTillNextSun <>0 then 1 else 0 end as NumSuns
from
(
select WkDay,TotDays,
7-WkDay as DaysTillNextSat ,case when 7-WkDay+1=7 then 0 else 7-WkDay+1 end as DaysTillNextSun
from( select datepart(dw,@St art) as WkDay,
datediff(dd,@St art,@End)as TotDays
)a
)a
[/code]