Query help - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Query help

Author

Topic

harry123
Starting Member

13 Posts

Posted - 2011-08-11 : 16:44:36

My Query should generate last 3 quarters data (Excluding current quarter).

Quarters
---------
Q1 - 1/1 (00:00:00.000) thru 3/31 (11:59:59.000)
Q2 - 4/1 (00:00:00.000) thru 6/30 (11:59:59.000)
Q3 - 7/1 (00:00:00.000) thru 9/30 (11:59:59.000)
Q4 - 10/1(00:00:00.000) thru 12/31(11:59:59.000)

Example : Today's Date : 08/11/2011. This is Quarter:Q3 (exclude current Quarter)
So my query should generate data Q4-2010,Q1-2011,Q2-2011
(i,e:01/10/2010 00:00:00.000 to 30/06/2011 11:59:59.000)

Desired Output Format:

Table:
------
CustomerName State Amount
------------ ----- --------

Any help would be appreciated. Thanks.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-08-11 : 18:24:51

[CODE]declare @MyDate datetime

set @MyDate = GETDATE()

select Year(DateAdd(Quarter, -1, @MyDate)), DATEPART(Quarter, DateAdd(Quarter, -1, @MyDate))
union all
select Year(DateAdd(Quarter, -2, @MyDate)), DATEPART(Quarter, DateAdd(Quarter, -2, @MyDate))
union all
select Year(DateAdd(Quarter, -3, @MyDate)), DATEPART(Quarter, DateAdd(Quarter, -3, @MyDate))[/CODE]There is probably a cleverer way using a Calendar table.

=======================================
I have never met a man so ignorant that I couldn't learn something from him. -Galileo Galilei, physicist and astronomer (1564-1642)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-12 : 02:18:49

[code]declare @MyDate datetime

set @MyDate = GETDATE()

SELECT 'Q' + DATENAME(qq,DATEADD(qq,-1 * Num,DATEADD(qq,DATEDIFF(qq,0,GETDATE()),0))) + ' ' + DATENAME(yyyy,DATEADD(qq,-1 * Num,DATEADD(qq,DATEDIFF(qq,0,GETDATE()),0)))
FROM (SELECT 1 AS Num UNION ALL SELECT 2 UNION ALL SELECT 3) t
ORDER BY DATEADD(qq,-1 * Num,DATEADD(qq,DATEDIFF(qq,0,GETDATE()),0)) ASC
[/code]

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

harry123
Starting Member

13 Posts

Posted - 2011-08-12 : 12:16:45

Thanks for the info.

But its not generating the past 3 quarters(excluding present quarter)
data.I am getting all the data thats in the table.
Any Suggestions?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources