Number of Sat, Sun in a year[Answered] RSS

• • Reply
  winseealn@ho...

  Member

  289 Points

  1337 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

  1. to find how many Saturdays, Sundays in a year?
  2. what is the last date of Saturday in a year?
  3. what is the last date of Sunday in a year?if i pass the year then query has to return the above output
• • Reply
  limno

  All-Star

  123252 Points

  10024 Posts

  Moderator

  Re: Number of Sat, Sun in a year

  Jan 06, 2020 06:45 PM|limno|LINK

  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

  Jingyang Li
  Format your SQL query with instant sql formatter:
  http://www.dpriver.com/pp/sqlformat.htm
• • Reply
  limno

  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.

  Jingyang Li
  Format your SQL query with instant sql formatter:
  http://www.dpriver.com/pp/sqlformat.htm
• • Reply
  Yang Shen

  Contributor

  3130 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:

  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:

  

  Best Regard,

  Yang Shen