May 23, 2012 at 5:27 pm
Hello All,
I have a vendor provided stored procedure that is essentially a calculation engine. I can pass this procedure two parameters: an identifier and a month-end date. The calculation generated is an annual (12 month) calculation describing the previous year prior to the month-end date parameter.
I need to extract historical data out of this system so I intend to pass a data set to a cursor and then do the INSERT INTO EXEC myProc thing.
My companies have a start date. For each company I want to generate a series of records starting with the previous month-end date (based on current date) and going back to one year after the companies start date.
Example. Say my start date is 1/3/2011. The first full month after the start date would be February 2011. I would want to generate three dates: 4/30/2012, 3/31/2012 and 2/29/2012.
I would not want 1/31/2012 because subtracting one year from 1/31/2012 would give me 1/31/2011 which is before the first full month after the start date.
I hope this is clear. None of my logic in this project is holiday or weekend based and I don't have a calendar so I'd prefer a solution without one
Thanks if you want to help.
CREATE TABLE #Companies
(
Company varchar(1),
StartDate datetime
)
INSERT INTO #Companies
SELECT 'A',' 4/3/2009' UNION
SELECT 'B',' 3/31/2010' UNION
SELECT 'C',' 1/1/2009'
SELECT * FROM #Companies
DROP TABLE #Companies
Expected results:
A5/31/2010
A6/30/2010
A7/31/2010
A8/31/2010
A9/30/2010
A10/31/2010
A11/30/2010
A12/31/2010
A1/31/2011
A2/28/2011
A3/31/2011
A4/30/2011
A5/31/2011
A6/30/2011
A7/31/2011
A8/31/2011
A9/30/2011
A10/31/2011
A11/30/2011
A12/31/2011
A1/31/2012
A2/29/2012
A3/31/2012
A4/30/2012
B4/30/2011
B5/31/2011
B6/30/2011
B7/31/2011
B8/31/2011
B9/30/2011
B10/31/2011
B11/30/2011
B12/31/2011
B1/31/2012
B2/29/2012
B3/31/2012
B4/30/2012
C2/28/2010
C3/31/2010
C4/30/2010
C5/31/2010
C6/30/2010
C7/31/2010
C8/31/2010
C9/30/2010
C10/31/2010
C11/30/2010
C12/31/2010
C1/31/2011
C2/28/2011
C3/31/2011
C4/30/2011
C5/31/2011
C6/30/2011
C7/31/2011
C8/31/2011
C9/30/2011
C10/31/2011
C11/30/2011
C12/31/2011
C1/31/2012
C2/29/2012
C3/31/2012
C4/30/2012
May 23, 2012 at 5:55 pm
This?
DECLARE @Now DATETIME = DATEADD(DD, DATEDIFF(DD, 0 , GETDATE() ), 0)
SELECT C.Company
, C.StartDate
, CrsApp1.NextYearMonthEnd
, CrsApp2.MonthDiff
, T.N
, Months =DATEADD (DD, -1, DATEADD(MM, (T.N + 1 ), CrsApp1.NextYearMonthEnd ))
FROM #Companies C
CROSS APPLY (SELECT DATEADD(YY,1, DATEADD(DD , (-1 * (DAY(C.StartDate) -1)) ,C.StartDate ))) CrsApp1 (NextYearMonthEnd)
CROSS APPLY (SELECT DATEDIFF(MM, CrsApp1.NextYearMonthEnd ,@Now ) ) CrsApp2 (MonthDiff)
CROSS JOIN Tally T
WHERE T.N < ( CrsApp2.MonthDiff )
AND T.N <> 0
You need a tally table for this ( My tally was zero based, so i added T.N <> 0 in my where clause; not needed if ur tally is based off 1)
May 23, 2012 at 7:07 pm
Chrissy,
Yes you do need a Tally table, but I don't think you need that many CROSS APPLYs.
CREATE TABLE #Companies (Company varchar(1), StartDate datetime)
INSERT INTO #Companies
SELECT 'A',' 4/3/2009' UNION
SELECT 'B',' 3/31/2010' UNION
SELECT 'C',' 1/1/2009'
;WITH Tally (n) AS (
SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1 FROM sys.all_columns),
StartAndEnd AS (
SELECT Company, StartDate
-- Last day of starting month
,SDate = DATEADD(day, -1, DATEADD(year, 1, DATEADD(month, 2, DATEADD(month, DATEDIFF(month, 0, StartDate), 0))))
-- Last day of ending month
,EDate = DATEADD(day, -1, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0))
FROM #Companies)
SELECT Company, MyDate = DATEADD(day, -1, DATEADD(month, n, DATEADD(day, 1, SDate)))
FROM StartAndEnd
CROSS APPLY (SELECT n FROM Tally WHERE DATEADD(day, -1, DATEADD(month, n, DATEADD(day, 1, SDate))) BETWEEN SDate AND EDate) x
ORDER BY Company, MyDate
DROP TABLE #Companies
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
May 23, 2012 at 7:13 pm
dwain.c (5/23/2012)
Chrissy,Yes you do need a Tally table, but I don't think you need that many CROSS APPLYs.
Dwain, if you watch closely, those CROSS APPLYs are not on Tally tables; rather those are used to minimize typing the same DATE functions multiple times. It is like , if your query needs 3 columns that uses the same CONVERT/CAST function, i wrap them around CROSS APPLY so that i can just alias them in those 3 columns rather than typing them thrice.
May 23, 2012 at 8:03 pm
ColdCoffee (5/23/2012)
dwain.c (5/23/2012)
Chrissy,Yes you do need a Tally table, but I don't think you need that many CROSS APPLYs.
Dwain, if you watch closely, those CROSS APPLYs are not on Tally tables; rather those are used to minimize typing the same DATE functions multiple times. It is like , if your query needs 3 columns that uses the same CONVERT/CAST function, i wrap them around CROSS APPLY so that i can just alias them in those 3 columns rather than typing them thrice.
ColdCoffee - True indeed. Are you doing this to improve readability?
I guess if you do it often and are used to it, perhaps it does. Wonder which performs better? 🙂
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
May 23, 2012 at 8:41 pm
dwain.c (5/23/2012)
Wonder which performs better? 🙂
DWAIN FOR PRESIDENT! 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
May 23, 2012 at 8:51 pm
Jeff Moden (5/23/2012)
dwain.c (5/23/2012)
Wonder which performs better? 🙂DWAIN FOR PRESIDENT! 😀
Shoot man! Talk is cheap!
Saying and doing are two different things.
In this thread: http://www.sqlservercentral.com/Forums/Topic1303878-392-2.aspx#bm1304951 I dived right in (should do you proud). Wish I had proposed a better solution to test against though.
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
May 23, 2012 at 9:22 pm
So let's begin the campaign!
Here's a test harness to compare ColdCoffee's submission vs. mine over roughly 65,000 rows. ColdCoffee will need to excuse me but I didn't have a Tally table already defined so I had to use my (actually Jeff's) CTE to generate one.
CREATE TABLE #Companies (Company varchar(8), StartDate datetime)
;WITH Tally (n) AS (
SELECT TOP 50000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1 FROM sys.all_columns)
INSERT INTO #Companies
SELECT 'A' + RIGHT('000000' + CAST(n AS VARCHAR(7)), 7)
,DATEADD(day, CHECKSUM(NEWID()) % 500, '2010-01-01')
FROM Tally
SET STATISTICS TIME ON
SET STATISTICS TIME ON
;WITH Tally (n) AS (
SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1 FROM sys.all_columns),
StartAndEnd AS (
SELECT Company, StartDate
-- Last day of starting month
,SDate = DATEADD(day, -1, DATEADD(year, 1, DATEADD(month, 2, DATEADD(month, DATEDIFF(month, 0, StartDate), 0))))
-- Last day of ending month
,EDate = DATEADD(day, -1, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0))
FROM #Companies)
SELECT Company, MyDate = DATEADD(day, -1, DATEADD(month, n, DATEADD(day, 1, SDate)))
FROM StartAndEnd
CROSS APPLY (SELECT n FROM Tally WHERE DATEADD(day, -1, DATEADD(month, n, DATEADD(day, 1, SDate))) BETWEEN SDate AND EDate) x
ORDER BY Company, MyDate
DECLARE @Now DATETIME
SELECT @Now = DATEADD(DD, DATEDIFF(DD, 0 , GETDATE() ), 0)
;WITH Tally (n) AS (
SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1 FROM sys.all_columns)
SELECT C.Company
-- , C.StartDate
-- , CrsApp1.NextYearMonthEnd
-- , CrsApp2.MonthDiff
-- , T.N
, Months =DATEADD (DD, -1, DATEADD(MM, (T.N + 1 ), CrsApp1.NextYearMonthEnd ))
FROM #Companies C
CROSS APPLY (SELECT DATEADD(YY,1, DATEADD(DD , (-1 * (DAY(C.StartDate) -1)) ,C.StartDate ))) CrsApp1 (NextYearMonthEnd)
CROSS APPLY (SELECT DATEDIFF(MM, CrsApp1.NextYearMonthEnd ,@Now ) ) CrsApp2 (MonthDiff)
CROSS JOIN Tally T
WHERE T.N < ( CrsApp2.MonthDiff )
AND T.N <> 0
ORDER BY Company, Months
SET STATISTICS TIME OFF
SET STATISTICS TIME OFF
DROP TABLE #Companies
And then the results:
(68955 row(s) affected)
SQL Server Execution Times:
CPU time = 2558 ms, elapsed time = 3136 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
(68955 row(s) affected)
SQL Server Execution Times:
CPU time = 1685 ms, elapsed time = 2263 ms.
Showing ColdCoffee wins by a landslide!
But this was just the first of many campaign stops so Dwain.C is hoping for a comeback in late polling!
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
May 23, 2012 at 9:33 pm
dwain.c (5/23/2012)
Jeff Moden (5/23/2012)
dwain.c (5/23/2012)
Wonder which performs better? 🙂DWAIN FOR PRESIDENT! 😀
Shoot man!
That's why I was making you president... I was going to "shoot" you if you didn't follow through. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
May 23, 2012 at 9:46 pm
BWAAA-HAAAA!!!! Careful now... Do you gents feel compelled to use "0" or "1" in the Tally Table? Did you add a Clustered Index to the Tally Table in vain? 😀
SELECT c.Company,
DATEADD(mm,DATEDIFF(mm,0,c.StartDate)+t.N,0)-1
FROM #Companies c,
dbo.Tally t
WHERE t.N BETWEEN 14 AND DATEDIFF(mm,c.StartDate,GETDATE())
ORDER BY c.Company, t.N
;
Test results from my old machine.
(4195 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
(64965 row(s) affected)
SQL Server Execution Times:
CPU time = 10000 ms, elapsed time = 13419 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
(64965 row(s) affected)
SQL Server Execution Times:
CPU time = 5656 ms, elapsed time = 8607 ms.
(64965 row(s) affected)
SQL Server Execution Times:
CPU time = 266 ms, elapsed time = 3105 ms.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 23, 2012 at 10:01 pm
Thank you all again. I had never used a tally table.
Please allow me to ask a question which will clearly demonstrate I am still trying to understand the solution.
What's the relationship between how far back in time I can go and the number of records in the tally table? I am assuming there is one.
May 23, 2012 at 10:01 pm
Jeff Moden (5/23/2012)
BWAAA-HAAAA!!!! Careful now... Do you gents feel compelled to use "0" or "1" in the Tally Table? Did you add a Clustered Index to the Tally Table in vain? 😀
SELECT c.Company,DATEADD(mm,DATEDIFF(mm,0,c.StartDate)+t.N,0)-1
FROM #Companies c,
dbo.Tally t
WHERE t.N BETWEEN 14 AND DATEDIFF(mm,c.StartDate,GETDATE())
ORDER BY c.Company, t.N
;
Test results from my old machine.
(4195 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
(64965 row(s) affected)
SQL Server Execution Times:
CPU time = 10000 ms, elapsed time = 13419 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
(64965 row(s) affected)
SQL Server Execution Times:
CPU time = 5656 ms, elapsed time = 8607 ms.
(64965 row(s) affected)
SQL Server Execution Times:
CPU time = 266 ms, elapsed time = 3105 ms.
As the dark horse candidate in the race, it appears Jeff Moden has pulled into the lead!
Are those results ordered as #1 mine, #2 ColdCoffee and #3 Jeff?
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
May 23, 2012 at 10:05 pm
Chrissy321 (5/23/2012)
Thank you all again. I had never used a tally table.Please allow me to ask a question which will clearly demonstrate I am still trying to understand the solution.
What's the relationship between how far back in time I can go and the number of records in the tally table? I am assuming there is one.
The tally table is just a number from 1...n. If n is around 65000 (I'm assuming Jeff and ColdCoffee both go beyond that), it ties to a month so you've got about 170 years you can go back in time.
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
May 23, 2012 at 10:05 pm
dwain.c (5/23/2012)
As the dark horse candidate in the race, it appears Jeff Moden has pulled into the lead!Are those results ordered as #1 mine, #2 ColdCoffee and #3 Jeff?
Yes. And thanks for making my day. Except for "BitBucket", I thought I was the only one left in the world that knew what a "dark horse" was. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
May 23, 2012 at 10:10 pm
dwain.c (5/23/2012)
Chrissy321 (5/23/2012)
Thank you all again. I had never used a tally table.Please allow me to ask a question which will clearly demonstrate I am still trying to understand the solution.
What's the relationship between how far back in time I can go and the number of records in the tally table? I am assuming there is one.
The tally table is just a number from 1...n. If n is around 65000 (I'm assuming Jeff and ColdCoffee both go beyond that), it ties to a month so you've got about 170 years you can go back in time.
My code for this is based on month. For my standard 11,001 row zero based Tally Table and based on the fact that I start 13 months later, I can get 915 years, 7 months out of this bad boy. That's more than the "beginning of time" for DATETIME. My code won't actually work for DATETIME2 because I used -1 to subtract a day. You'd have to convert that to a DATEADD if you want it to work with DATETIME2.
65,000 months is more than 5,416 years.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply