Quarter Calculation T-SQL


  

\CREATE TABLE mytable(
   Qtr         VARCHAR(20) NOT NULL  
  ,CurrentQtr  VARCHAR(2) NOT NULL
  ,CurrentYear INTEGER  NOT NULL
 
);
INSERT INTO mytable(Qtr,CurrentQtr,CurrentYear) VALUES
 ('CurrentQuarter','Q1',2019 )
,('CurrentQuarterMinus1','Q1',2019 )
,('CurrentQuarterMinus2','Q1',2019 )
,('CurrentQuarterMinus3','Q1',2019 )
,('CurrentQuarterMinus4','Q1',2019 )
,('CurrentQuarterMinus5','Q1',2019 )
,('CurrentQuarterMinus6','Q1',2019 )
,('CurrentQuarterMinus7','Q1',2019 )
,('CurrentQuarterMinus8','Q1',2019 );


;with myctedate as (
SELECT  Qtr,CurrentQtr,CurrentYear,
	Dateadd(Quarter,ISNULL(-try_cast(right(Qtr ,1) as int),0),
	Cast(CAST(CurrentYear AS CHAR(4))+'-'+
CASE WHEN CurrentQtr = 'Q1' THEN '01' 
WHEN CurrentQtr = 'Q2' THEN '04'
WHEN CurrentQtr = 'Q3' THEN '07'
WHEN CurrentQtr = 'Q4' THEN '10' Else NULL END +'-01' as Date) ) QuarterDate
From mytable
 )

 Select Qtr,CurrentQtr,CurrentYear,
 Concat(Format(QuarterDate,'yy'),'Q', Datepart(QUARTER,QuarterDate)) ans
 from myctedate

 drop table mytable

  

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/c5b3dd56-5781-41ad-82e5-89a9ab9d7050/tsql-query-help-to-compute-quarteryear?forum=transactsql

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s