November 15, 2011 at 4:22 am
I have a table which has an ID (not an identity, a key to another table), a return value (just a decimal 18,2), and a date, so it might look a bit like this:
ID Return Date
123 1.25 31/10/2010
123 1.75 30/11/2010
123 2.01 31/01/2011
What I am looking to return from this table, for each ID would be:
'Oct 10 - Nov 10'
Essentially what's the longest period from the first date for which we have returns without missing a month.
I've been scratching my head on this for quite a while so I thought I'd throw it out and see what ideas you guys can come up with. So far I've created an additional table which has an 'ordinal' and has every month and year from 1900 to 2040 so Jan 1900 is 1, Feb 1900 is 2 etc. I can then join to this to see when the numbers have a break and aren't continuous, it's just how I do this!
Some form of join to its self seems to work to a degree but it seems this just gets a min and a max.
Thoughts much appreciated, this has already taken way longer than I thought it would!
November 15, 2011 at 7:49 am
I am not sure why ID is in your test data, but something like the following may help:
-- *** Test Data ***
CREATE TABLE #t
(
ReturnDate datetime NOT NULL
)
INSERT INTO #t
VALUES ('20101031')
,('20101130')
,('20110131');
-- *** End Test Data ***
WITH Grps
AS
(
SELECT ReturnDate
,DATEDIFF(month, 0, ReturnDate) - ROW_NUMBER() OVER (ORDER BY ReturnDate) AS Grp
FROM #t
)
, Ranges
AS
(
SELECT Grp
,MIN(ReturnDate) AS MinReturnDate
,MAX(ReturnDate) AS MaxReturnDate
FROM Grps
GROUP BY Grp
)
SELECT TOP (1) WITH TIES
MinReturnDate, MaxReturnDate
FROM Ranges
ORDER BY DATEDIFF(month, MinReturnDate, MaxReturnDate) DESC;
November 15, 2011 at 7:56 am
I'm just looking through your code now and trying to suss it out, many thanks for the posting.
The point in having ID in the test data (although perhaps it was poor test data on reflection!) is that the table will contain data for many IDs, so like this@
ID Date
123 31/01/11
123 28/02/22
123 30/05/11
334 30/09/11
334 31/10/11
334 31/11/11
So I'll be writing a function which returns the dates for a given ID basically.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply