Find previous date in a series

Question

Find previous date in a series

stevet 20325

SSC Veteran

Points: 225
More actions
April 11, 2011 at 7:27 am

#236498

We have a fairly standard table, A Customer table. PK is Customer_ID and End_Effective_Date. One of the non-key attributes is the statement Cutoff-Date.
We need to find out what the previous cutoff date is. IT's not stored in the table. Our BI reporting tool vendor needs to report on transactionsfrom last cutoff to current cutoff.
How do I find the previous cutoff date using sql?

Viewing 7 posts - 1 through 6 (of 6 total)

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

Dan.Humphries SSChampion Points: 12125 More actions · Answer 1

I was already with an answer when I got towards the end and you said "It's not stored in the table"

Are you saying the previous cutoff date is not stored in the table? if that is the case I can not imagine being able to get it through SQL unless there is some businness rule or furmals that can be applied to the current date to get the previous.

Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

stevet 20325 SSC Veteran Points: 225 More actions · Answer 2

Well maybe I misspoke. The cutoff date is in the table, on each record. But there is no distinct column for Previous cutoff date. You'd have to do something like locate the current record, find the cutoff date for the current record, then get a list of cutoff dates in order, and get the first previous record.

Dan.Humphries SSChampion Points: 12125 More actions · Answer 3

What I have done in this type of case is use the Row_number function. group by you ID and sort the function by the cutoff date in desc order. Then if you look at row 2 for each ID you will get the previous cutoff date.

Here is a link to BOL for the function to help get you started. http://msdn.microsoft.com/en-us/library/ms186734.aspx

Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

stevet 20325 (4/11/2011)
Well maybe I misspoke. The cutoff date is in the table, on each record. But there is no distinct column for Previous cutoff date. You'd have to do something like locate the current record, find the cutoff date for the current record, then get a list of cutoff dates in order, and get the first previous record.

Without seeing the data or the structure of the table(s), we're just guessing. If you'd like a proper coded answer, take a gander at the first link in my signature line below.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

tfifield SSCrazy Eights Points: 9655 More actions · Answer 5

I would use a calendar table for the cutoff dates. Have each row contain the start (last cutoff) and current cutoff to you have a BETWEEN type range for the records. I've written several articles here on using calendar tables for this type of thing. Here are 2 of them:

http://qa.sqlservercentral.com/articles/T-SQL/70482/

http://qa.sqlservercentral.com/articles/T-SQL/72345/

I hope I understand your requirements and that these articles help.

Todd Fifield

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 6

Steve I really don't understand how you can expect us to help you with so little information given, especially in your first post! Help us help you! 😉

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service