Want to get the last quater - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Want to get the last quater

Author

Topic

divyaram
Posting Yak Master

180 Posts

Posted - 2012-01-12 : 04:55:00

Hi all,

I want to get the last quater ....
i have a query to get this quater of this year like this
select to_char(sysdate, 'YYYY " Q"Q')Quater from dual

Output is

Quater
-------
2012 Q1

But i want to get the last to quater like this

Quater
------
2011 Q4

Thanks,
Divya

Regards,
Divya

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-12 : 06:22:43

the easiest way to do that is to have a calendar table storing quarters against dates and then apply simple date functions like DATEADD to go forward or back and get corresponding quarter

if you dont have one, you need to do calculation on the fly like below


DECLARE @CurrentQr varchar(7),@LastQrDt datetime
SELECT @CurrentQr='2012 Q1',@LastQrDt= DATEADD(qq,RIGHT(@CurrentQr,1)-2,DATEADD(yy,LEFT(@CurrentQr,4)-1900,0))
SELECT DATENAME(yy,@LastQrDt) + ' Q' + DATENAME(qq,@LastQrDt) AS lastQuarterValue

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-01-12 : 06:42:16

Simpler?

DECLARE	@Today DATE = GETDATE()

-- Current Quarter
SELECT	DATEADD(QUARTER, DATEDIFF(QUARTER, '19000101', @Today), '19000101'),
	DATEADD(QUARTER, DATEDIFF(QUARTER, '19000101', @Today), '19000331')

-- Previous Quarter
SELECT	DATEADD(QUARTER, DATEDIFF(QUARTER, '19000101', @Today), '18991001'),
	DATEADD(QUARTER, DATEDIFF(QUARTER, '19000101', @Today), '18991231')

-- Next Quarter
SELECT	DATEADD(QUARTER, DATEDIFF(QUARTER, '19000101', @Today), '19000401'),
	DATEADD(QUARTER, DATEDIFF(QUARTER, '19000101', @Today), '19000630')

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-01-12 : 06:44:11

[code]DECLARE @Today DATE = GETDATE()

-- Current Quarter
SELECT DATENAME(YEAR, @Today) + ' Q' + DATENAME(QUARTER, @Today)

-- Previous Quarter
SELECT DATENAME(YEAR, DATEADD(QUARTER, -1, @Today)) + ' Q' + DATENAME(QUARTER, DATEADD(QUARTER, -1, @Today))

-- Next Quarter
SELECT DATENAME(YEAR, DATEADD(QUARTER, 1, @Today)) + ' Q' + DATENAME(QUARTER, DATEADD(QUARTER, 1, @Today))[/code]

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources