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]

@Manikgisl

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

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,@Start) as WkDay,
                       datediff(dd,@Start,@End)as TotDays
        )a
)a
 

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

(7-DaysTillNextSat)/7+(TotDays-DaysTillNextSat)/7+case when DaysTillNextSat<=totdays and DaysTillNextSat<>0 then 1 else 0 end as NumSats
 

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

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,@Start) as WkDay, 
                       datediff(dd,@Start,@End)as TotDays 
        )a 
)a