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,n-1,datefromparts(@yr,1,1)) dt from Nums
where dateadd(day,n-1,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
In addition to @limno's reply, you can also refer to below demo which is built based on very basic Transact-SQL and simple logic:
declare @year int=2019
declare @startdate date=convert(date,convert(varchar(50),@year)+'-01-01')
declare @enddate date=convert(date,convert(varchar(50),@year)+'-12-31')
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:
Member
297 Points
1356 Posts
Number of Sat, Sun in a year
Jan 06, 2020 06:10 PM|winseealn@hotmail.com|LINK
Hello all,
May i know is there any short way
All-Star
123252 Points
10024 Posts
Moderator
Re: Number of Sat, Sun in a year
Jan 06, 2020 06:45 PM|limno|LINK
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm
All-Star
123252 Points
10024 Posts
Moderator
Re: Number of Sat, Sun in a year
Jan 06, 2020 07:00 PM|limno|LINK
If you have a calendar table in your database, you have use a simple select query with aggregate functions to get your result.
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm
Contributor
3140 Points
983 Posts
Re: Number of Sat, Sun in a year
Jan 07, 2020 01:59 AM|Yang Shen|LINK
Hi winseealn,
In addition to @limno's reply, you can also refer to below demo which is built based on very basic Transact-SQL and simple logic:
set any value for the parameter @year and run the query, below is the result when @year = 2019:
Best Regard,
Yang Shen