June 16, 2012 at 6:46 am
how to calculate 4 quarter when year starting from april(financial year).
means '01/05/2012' lying in first quarter within 4 quarters.
June 16, 2012 at 10:56 am
Is this what you require? Post edited 4:13 PM
DECLARE @D DATETIME
DECLARE @Q INT
SET @Q = 1
SET @D = '04/01/2012'
SELECT @D as 'Quarter Start',@Q as 'Quarter',DATEADD(dd,-1,DATEADD(mm,3,@D))AS 'Quarter end date'
SET @Q = @Q + 1
WHILE @Q < 5
BEGIN
SELECT DATEADD(mm,3,@D) AS 'Quarter Start',@Q AS 'Quarter',DATEADD(dd,-1,DATEADD(mm,6,@D))AS 'Quarter end date'
SET @D = DATEADD(mm,3,@D)
SET @Q = @Q + 1
END
/*
Quarter Start Quarter Quarter end date
2012-04-01 00:00:00.000 1 2012-06-30 00:00:00.000
2012-07-01 00:00:00.000 2 2012-09-30 00:00:00.000
2012-10-01 00:00:00.000 3 2012-12-31 00:00:00.000
2013-01-01 00:00:00.000 4 2013-03-31 00:00:00.000
June 16, 2012 at 1:05 pm
The following will return a single result.
DECLARE @StartFY DATETIME,
@EndFY DATETIME
;
SELECT @StartFY = '2012',
@EndFY = '2013'
;
WITH
cteStartDate AS
(
SELECT TOP ((DATEDIFF(yy,@StartFY,@EndFY)+1)*4-1)
QtrStartDate = DATEADD(qq,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),@StartFY)
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
)
SELECT QtrStartDate,
NextQtrStartDate = DATEADD(qq,1,QtrStartDate),
Qtr = (ROW_NUMBER() OVER (ORDER BY QtrStartDate)-1)%4+1
FROM cteStartDate
;
The "NextQtrStart" is there because it makes it REAL easy to group dates by quarter using the ol' WHERE SomeDate >= QtrStartDate and SomeDate < NextQtrStartDate trick.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 16, 2012 at 2:55 pm
If you want to create a table defining your quarters you could use:
CREATE TABLE #Quarters(QuarterStart DATETIME,QuarterEnd DATETIME,[Quarter] INT)
DECLARE @D DATETIME
DECLARE @Q INT,@C INT
SET @Q = 1
SET @C = 1
SET @D = '04/01/2012'
INSERT INTO #Quarters
SELECT @D,DATEADD(dd,-1,DATEADD(mm,3,@D)),@Q
SET @Q = @Q + 1
WHILE @C < 20
BEGIN
INSERT INTO #Quarters
SELECT DATEADD(mm,3,@D),DATEADD(dd,-1,DATEADD(mm,6,@D)),@Q
SET @D = DATEADD(mm,3,@D)
SET @Q = @Q + 1
IF @Q > 4
SET @Q = 1
END
#Quarters contents
QuarterStart QuarterEnd Quarter
----------------------- ----------------------- -----------
2012-04-01 00:00:00.000 2012-06-30 00:00:00.000 1
2012-07-01 00:00:00.000 2012-09-30 00:00:00.000 2
2012-10-01 00:00:00.000 2012-12-31 00:00:00.000 3
2013-01-01 00:00:00.000 2013-03-31 00:00:00.000 4
2013-04-01 00:00:00.000 2013-06-30 00:00:00.000 1
2013-07-01 00:00:00.000 2013-09-30 00:00:00.000 2
2013-10-01 00:00:00.000 2013-12-31 00:00:00.000 3
2014-01-01 00:00:00.000 2014-03-31 00:00:00.000 4
2014-04-01 00:00:00.000 2014-06-30 00:00:00.000 1
2014-07-01 00:00:00.000 2014-09-30 00:00:00.000 2
2014-10-01 00:00:00.000 2014-12-31 00:00:00.000 3
2015-01-01 00:00:00.000 2015-03-31 00:00:00.000 4
2015-04-01 00:00:00.000 2015-06-30 00:00:00.000 1
2015-07-01 00:00:00.000 2015-09-30 00:00:00.000 2
2015-10-01 00:00:00.000 2015-12-31 00:00:00.000 3
2016-01-01 00:00:00.000 2016-03-31 00:00:00.000 4
2016-04-01 00:00:00.000 2016-06-30 00:00:00.000 1
2016-07-01 00:00:00.000 2016-09-30 00:00:00.000 2
2016-10-01 00:00:00.000 2016-12-31 00:00:00.000 3
2017-01-01 00:00:00.000 2017-03-31 00:00:00.000 4
June 16, 2012 at 4:49 pm
BWAA-HAAA!!!! Sorry. I'm an idiot. :blush: This is an SQL Server 2000 forum and I used things for 2k5 and above. My most sincere apologies.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 16, 2012 at 5:27 pm
What a refreshing reminder. So many people have moved away from the simplicity of a Tally Table in favor of ROW_NUMBER() that I've also gotten caught up in steering away from my favorite tool of all time.
If you don't know what a Tally Table is, it's a wonderful little helper table that acts like a Swiss Army Knife to quickly solve some of the more difficult problems and to eliminate the need for many types of loops. It also makes code really, really simple to write, read, and maintain. And, it works in ALL versions of T-SQL and always will. Here's an article to introduce you to how a Tally Table replaces loops.
http://www.sqlservercentral.com/articles/T-SQL/62867/
For convenience sake, here's how to quickly build a Tally Table (it should be a permanent table. It takes almost no room).
--===== Create an populate the Tally Table
SELECT TOP 11001
N = IDENTITY(INT,0,1)
INTO dbo.Tally
FROM master.dbo.syscolumns sc1
CROSS JOIN master.dbo.syscolumns sc2
;
--===== Index it for performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally
PRIMARY KEY CLUSTERED(N)
WITH FILLFACTOR = 100
;
--===== Let other people use it
GRANT SELECT ON dbo.Tally TO PUBLIC
;
Once that computational bit of heaven is in place, problems like this "quarters" problem become absolute child's play.
DECLARE @StartFY DATETIME,
@EndFY DATETIME
;
SELECT @StartFY = '2012',
@EndFY = '2013'
;
SELECT QtrStartDate = DATEADD(qq,t.N+1,@StartFY),
NextQtrStartDate = DATEADD(qq,t.N+2,@StartFY),
Qtr = t.N%4+1
FROM dbo.Tally t
WHERE t.N BETWEEN 0 AND (DATEDIFF(yy,@StartFY,@EndFY)+1)*4-1
ORDER BY t.N
;
--Jeff Moden
Change is inevitable... Change for the better is not.
June 18, 2012 at 2:49 am
Jeff Moden (6/16/2012)
--===== Create an populate the Tally Table
SELECT TOP 11001
N = IDENTITY(INT,0,1)
INTO dbo.Tally
FROM master.dbo.syscolumns sc1
CROSS JOIN master.dbo.syscolumns sc2
;
--===== Index it for performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally
PRIMARY KEY CLUSTERED(N)
WITH FILLFACTOR = 100
;
--===== Let other people use it
GRANT SELECT ON dbo.Tally TO PUBLIC
;
I didn't realize that CROSS JOIN is supported in SQL 2000.
This BOL reference seems to suggest that it is not:
http://msdn.microsoft.com/en-us/library/ms190690(v=sql.105).aspx
Clearly for the scope of Tally n's needed for this request, you could easily build the necessary Tally table with (or simply remove the CROSS JOIN from your Tally table create script):
SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
But my main curiosity is that there seems to be no other good way to generate larger Tally tables on the fly for SQL 2000!?
Just asking while hoping I never have to work in SQL 2000 again anyway. 😛
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 18, 2012 at 2:52 am
Then again, I suppose you could do something silly like this:
SELECT n=16*t3.n+4*t1.n+t2.n
FROM (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) t1(n)
INNER JOIN (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) t2(n) ON 1=1
INNER JOIN (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) t3(n) ON 1=1
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 20, 2012 at 4:22 am
dwain.c (6/18/2012)I didn't realize that CROSS JOIN is supported in SQL 2000.
This BOL reference seems to suggest that it is not:
http://msdn.microsoft.com/en-us/library/ms190690(v=sql.105).aspx
For the record, I am a moron. Of course CROSS JOIN works in SQL 2000 based on this link: http://msdn.microsoft.com/en-us/library/aa213229(v=sql.80).aspx, which I could have determined before posting this with just a little more research.
Jeff - will you ever forgive my questioning your prowess?
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 20, 2012 at 7:27 am
Jeff Moden (6/16/2012)
Once that computational bit of heaven is in place, problems like this "quarters" problem become absolute child's play.
DECLARE @StartFY DATETIME,@EndFY DATETIME
;
SELECT @StartFY = '2012',
@EndFY = '2013'
;
SELECT QtrStartDate = DATEADD(qq,t.N+1,@StartFY),
NextQtrStartDate = DATEADD(qq,t.N+2,@StartFY),
Qtr = t.N%4+1
FROM dbo.Tally t
WHERE t.N BETWEEN 0 AND (DATEDIFF(yy,@StartFY,@EndFY)+1)*4-1
ORDER BY t.N
;
What happend to Q1 2012 Jeff 😛
My answer
DECLARE @Year int
SET @Year = 2012
SELECT
Offset / 3 AS [Qtr],
DATEADD(month,Offset,DATEADD(year,@Year-1900,0)) AS [QtrStart],
DATEADD(day,-1,DATEADD(month,Offset+3,DATEADD(year,@Year-1900,0))) AS [QtrEnd]
FROM (SELECT 3 AS [Offset] UNION ALL SELECT 6 UNION ALL SELECT 9 UNION ALL SELECT 12) q
Far away is close at hand in the images of elsewhere.
Anon.
June 20, 2012 at 8:37 pm
David Burrows (6/20/2012)
What happend to Q1 2012 Jeff 😛
Considering that the fiscal year for the OP starts in April of each year, nothing happened to Q1. Here's the output that I get. Are you getting something different?
QtrStartDateNextQtrStartDateQtr
2012-04-01 00:00:00.0002012-07-01 00:00:00.0001
2012-07-01 00:00:00.0002012-10-01 00:00:00.0002
2012-10-01 00:00:00.0002013-01-01 00:00:00.0003
2013-01-01 00:00:00.0002013-04-01 00:00:00.0004
2013-04-01 00:00:00.0002013-07-01 00:00:00.0001
2013-07-01 00:00:00.0002013-10-01 00:00:00.0002
2013-10-01 00:00:00.0002014-01-01 00:00:00.0003
2014-01-01 00:00:00.0002014-04-01 00:00:00.0004
--Jeff Moden
Change is inevitable... Change for the better is not.
June 21, 2012 at 1:52 am
Jeff Moden (6/20/2012)
David Burrows (6/20/2012)
Considering that the fiscal year for the OP starts in April of each year, nothing happened to Q1. Here's the output that I get. Are you getting something different?
Nah, twas me. Seem to have a tally table without zero in it, doh! :hehe:
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply