Friday, August 23, 2013

Query to get No.Of specific Week days between two dates

The below query gives number of specific week days between two dates , the two dates can be in any order.

EX: Let us try to get number of Saturdays between two given dates.

 Declare @date1 datetime='20 aug 2013' ,
@date2 datetime='31 aug 2013'
 
select CASE when [PlanendDate]> [ActualEndDate] then (DATEDIFF(Day ,[ActualEndDate],[PlanendDate])/7)+1
                when [PlanendDate]< [ActualEndDate] then (DATEDIFF(Day ,[PlanendDate],[ActualEndDate])/7)+ CASE when datepart(dw,@date2)=7 then 1 else 0 end
                else 1
                end
               
                from (
select   case
                when datepart(dw,@date1)=7
                        then @date1
                else @date1+7-datepart(dw,@date1)
        end [PlanendDate],case
                when datepart(dw,@date2)=7
                        then @date2
                else @date2+7-datepart(dw,@date2)
        end [ActualEndDate]) A 

No comments: