Answered by:
Number of Sat, Sun in a year
Question

User1471881183 posted
Hello all,
May i know is there any short way
 to find how many Saturdays, Sundays in a year?
 what is the last date of Saturday in a year?
 what is the last date of Sunday in a year?if i pass the year then query has to return the above output
Monday, January 6, 2020 6:10 PM
Answers

User77042963 posted
declare @yr int=2019 **** create a Number table ;WITH Num1 (n) AS ( SELECT 1 as n UNION ALL SELECT n+1 as n FROM Num1 Where n <101), Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y), Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2) ,mydates as ( select dateadd(day,n1,datefromparts(@yr,1,1)) dt from Nums where dateadd(day,n1,datefromparts(@yr,1,1)) <datefromparts(@yr+1,1,1) ) select Sum(Case When datepart(weekday,dt)=7 then 1 else null end) TotalSaturdays ,Sum(Case When datepart(weekday,dt)=1 then 1 else null end) TotalSundays ,Max(Case When datepart(weekday,dt)=7 then dt else null end) maxSaturday ,Max(Case When datepart(weekday,dt)=1 then dt else null end) maxSunday from mydates
 Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Monday, January 6, 2020 6:45 PM 
User719153870 posted
Hi winseealn,
In addition to @limno's reply, you can also refer to below demo which is built based on very basic TransactSQL and simple logic:
declare @year int=2019 declare @startdate date=convert(date,convert(varchar(50),@year)+'0101') declare @enddate date=convert(date,convert(varchar(50),@year)+'1231') declare @maxsaturday date declare @maxsunday date declare @totalSaturday int=0 declare @totalSunday int=0 while @startdate<=@enddate begin if(DATENAME(DW,@startdate)='Saturday') begin set @totalSaturday+=1 set @maxsaturday=@startdate end else if(DATENAME(DW,@startdate)='Sunday') begin set @totalSunday+=1 set @maxsunday=@startdate end set @startdate=DATEADD(DD,1,@startdate) end select @totalSaturday as totalSaturday,@totalSunday as totalSunday,@maxsaturday as maxsaturday,@maxsunday as maxsunday
set any value for the parameter @year and run the query, below is the result when @year = 2019:
Best Regard,
Yang Shen
 Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Tuesday, January 7, 2020 1:59 AM
All replies

User77042963 posted
declare @yr int=2019 **** create a Number table ;WITH Num1 (n) AS ( SELECT 1 as n UNION ALL SELECT n+1 as n FROM Num1 Where n <101), Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y), Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2) ,mydates as ( select dateadd(day,n1,datefromparts(@yr,1,1)) dt from Nums where dateadd(day,n1,datefromparts(@yr,1,1)) <datefromparts(@yr+1,1,1) ) select Sum(Case When datepart(weekday,dt)=7 then 1 else null end) TotalSaturdays ,Sum(Case When datepart(weekday,dt)=1 then 1 else null end) TotalSundays ,Max(Case When datepart(weekday,dt)=7 then dt else null end) maxSaturday ,Max(Case When datepart(weekday,dt)=1 then dt else null end) maxSunday from mydates
 Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Monday, January 6, 2020 6:45 PM 
User77042963 posted
If you have a calendar table in your database, you have use a simple select query with aggregate functions to get your result.
Monday, January 6, 2020 7:00 PM 
User719153870 posted
Hi winseealn,
In addition to @limno's reply, you can also refer to below demo which is built based on very basic TransactSQL and simple logic:
declare @year int=2019 declare @startdate date=convert(date,convert(varchar(50),@year)+'0101') declare @enddate date=convert(date,convert(varchar(50),@year)+'1231') declare @maxsaturday date declare @maxsunday date declare @totalSaturday int=0 declare @totalSunday int=0 while @startdate<=@enddate begin if(DATENAME(DW,@startdate)='Saturday') begin set @totalSaturday+=1 set @maxsaturday=@startdate end else if(DATENAME(DW,@startdate)='Sunday') begin set @totalSunday+=1 set @maxsunday=@startdate end set @startdate=DATEADD(DD,1,@startdate) end select @totalSaturday as totalSaturday,@totalSunday as totalSunday,@maxsaturday as maxsaturday,@maxsunday as maxsunday
set any value for the parameter @year and run the query, below is the result when @year = 2019:
Best Regard,
Yang Shen
 Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Tuesday, January 7, 2020 1:59 AM