Comparing figures based on relative date

Question

Comparing figures based on relative date

Paul Munter

Say Hey Kid

Points: 681
More actions
February 11, 2013 at 7:15 am

#270615

Hi All
I'm having trouble working out a solution to the following issue:
I have a table containing a date and a metric I wish to add another field which is based on the existing metric but shows the figure as at 12 months ago side by side with my current figure.
I want to store it on the table so I need code that can dynamically allocate the 12 months previous figure to the relative column. I have started with a cursor (it's been a while!) but have hit a brick wall, in theory or in my head it seems simple but I'm not sure if I'm over complicating the task or i've simply looked at it too long and am being thick.
The cursor below returns the corrent figure I need but I then require to add it to the correct line(this is where I'm struggling). Any advice appreciated
CREATE TABLE #mytable
(
P_KEY INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
LAST_DAY_OF_MONTH DATETIME,
Value int ,
PreviousYearValue INT
)
INSERT INTO #mytable
VALUES('20130131',20,NULL)
INSERT INTO #mytable
VALUES('20120131',5,NULL)
INSERT INTO #mytable
VALUES('20130331',40,NULL)
INSERT INTO #mytable
VALUES('20120331',2,NULL)
DECLARE @DATE DATETIME
DECLARE @TWLEVE_MONTHS_PREVIOUS DATETIME
DECLARE @MEASURE INT
DECLARE @P_KEY INT
DECLARE db_cursor CURSOR FOR
SELECT P_KEY,LAST_DAY_OF_MONTH FROM #mytable
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @P_KEY,@DATE
WHILE @@FETCH_STATUS = 0
BEGIN
SET @TWLEVE_MONTHS_PREVIOUS = dateadd(month, datediff(month, -1, @DATE) - 12, -1)
FETCH NEXT FROM db_cursor INTO @P_KEY,@DATE
SELECT P_KEY,Value FROM #mytable
WHERE LAST_DAY_OF_MONTH =@TWLEVE_MONTHS_PREVIOUS
END
CLOSE db_cursor
DEALLOCATE db_cursor

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

From what you have posted it is pretty much impossible to provide much of an answer. I don't think you need a cursor for this but it is hard to know for sure. Please take a few minutes and read the first link in my signature about best practices when posting questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Hi Paul

You have rows in your table which have a datetime column. You want to pick some rows which have a "recent" date, from the table. On the same output row, you want to show data from rows which are dated a year before the "recent" rows. Is this about right?

1. I can't see how your date arithmetic might help, the example matches GETDATE() (11th February 2013) to 31st March 2012.

2. What do you want to do when there's data for the "recent" rows but no data for "old" rows?

3. What do you want to do when there's data for "old" rows but not "recent" rows?

4. What datetime range do you plan to use for matching "old" rows to "recent" rows? A day would be matching old rows dated 11th February 2012 with new rows dated 11th February 2013.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Paul Munter Say Hey Kid Points: 681 More actions · Answer 3

Hi Sean, aplogies for the lack of standardisation I have updated my code sample in accordance with your post(hopefully)

Paul Munter Say Hey Kid Points: 681 More actions · Answer 4

Hi Chris,

I've ammended my code sample to hopefully clear up the date logic. In regards to the other questions I'm happy to leave the comparison column null where it doesn't have a figure based on 12 months previous. The grain of the data is at month level holding the last day of the month as the datetime. I've added a small screen grab, again any advice much appreciated.

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

Paul Munter (2/11/2013)
Hi Sean, aplogies for the lack of standardisation I have updated my code sample in accordance with your post(hopefully)

No problem. Now that you posted your sample data and desired result it is much easier to see what you are trying to do.

Do you really need this data to be persistent in the database or can you calculate this as you run a query?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Paul Munter Say Hey Kid Points: 681 More actions · Answer 6

I would like if possible to have it stored on the table, I may be able to push it onto the app but would be dependant on the query.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

Something like this?

DROP TABLE #mytable

CREATE TABLE #mytable

(P_KEY INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

LAST_DAY_OF_MONTH DATETIME,

Value int,

PreviousYearValue INT)

INSERT INTO #mytable

VALUES('20130131',20,NULL),('20120131',5,NULL),('20130331',40,NULL),('20120331',2,NULL)

SELECT

ty.*,

ly.*

FROM #mytable ty

LEFT JOIN #mytable ly

ON ly.LAST_DAY_OF_MONTH = DATEADD(yy,-1,ty.LAST_DAY_OF_MONTH)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Paul Munter Say Hey Kid Points: 681 More actions · Answer 8

Paul Munter

Say Hey Kid

Points: 681

February 11, 2013 at 8:16 am

#1586597

Exactly like that Chris, yes.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

Paul Munter (2/11/2013)
Exactly like that Chris, yes.

Cool, it was a lucky guess. Now what do you want to do with it?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Paul Munter Say Hey Kid Points: 681 More actions · Answer 10

I'd like to be able to write a cursor of similar code which does this dynamically for me based on a data set that I have. So I can store the values for this year and previous year side by side on the same row.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

Paul Munter (2/11/2013)
I'd like to be able to write a cursor of similar code which does this dynamically for me based on a data set that I have. So I can store the values for this year and previous year side by side on the same row.

You can see how easy it is to output "this year and previous year side by side", why would you want to store it?

What changes would you need to the query I posted which would meet this requirement?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Sean Lange SSC Guru Points: 286590 More actions · Answer 12

ChrisM@Work (2/11/2013)
Paul Munter (2/11/2013)
I'd like to be able to write a cursor of similar code which does this dynamically for me based on a data set that I have. So I can store the values for this year and previous year side by side on the same row.
You can see how easy it is to output "this year and previous year side by side", why would you want to store it?
What changes would you need to the query I posted which would meet this requirement?

To add to Chris's comment, what are you going to do when the values change? Storing calculated values in the database are notorious for being incorrect. This is why we now have computed columns. 😉 In the case of you example I would just do something like Chris demonstrated and calculate the values when you need them.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Paul Munter Say Hey Kid Points: 681 More actions · Answer 13

Thanks for the advice, I was hoping to store it to avoid the computations being done by an application. I like to have as much stored as possible via ETL rather than either having the app or the RDBMS do the work. Thanks for taking time to reply much appreciated.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 14

Paul Munter (2/11/2013)
Thanks for the advice, I was hoping to store it to avoid the computations being done by an application. I like to have as much stored as possible via ETL rather than either having the app or the RDBMS do the work. Thanks for taking time to reply much appreciated.

For a cheap and simple computation such as this, I'd always get SQL Server to do the work, on demand.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden