June 6, 2011 at 12:19 am
Hi Experts,
I am trying to get the start date and end date based on the date ranking.
i.e. based on the date ranking, return the start date and end dates.
If first date is 31/05/2010 and 2nd ranked date is 30/06/2010 then start date willb e 31/05/2010 and end date will be 29/06/2010.
I got up to doing the ranking but can't really get the start, end date columns.
Please help T_T
Source data contains dates and ID.
Date ID
31/05/2010 57
31/05/2010 57
30/06/2010 57
31/07/2010 57
31/08/2010 57
30/09/2010 57
31/10/2010 57
31/12/2010 57
31/01/2011 57
31/03/2011 57
30/04/2011 57
31/05/2011 57
31/05/2010 4004
31/07/2010 4004
30/11/2010 4004
30/04/2011 4004
Desired Output
Date ID RANK Effective Start Effective End
31/05/2010 57 1 31/05/2010 29/06/2010
31/05/2010 57 1 31/05/2010 29/06/2010
30/06/2010 57 3 30/06/2010 30/07/2010
31/07/2010 57 4 31/07/2010 30/08/2010
31/08/2010 57 5 31/08/2010 29/09/2010
30/09/2010 57 6 30/09/2010 30/09/2010
31/10/2010 57 7 31/10/2010 30/11/2010
31/12/2010 57 8 31/12/2010 30/01/2011
31/01/2011 57 9 31/01/2011 30/03/2011
31/03/2011 57 10 31/03/2011 29/04/2011
30/04/2011 57 11 30/04/2011 30/05/2011
31/05/2011 57 12 31/05/2011 31/12/9999
31/05/2010 4004 1 31/05/2010 30/07/2010
31/07/2010 4004 2 31/07/2010 29/11/2010
30/11/2010 4004 3 30/11/2010 29/04/2011
30/04/2011 4004 4 30/04/2011 31/12/9999
June 6, 2011 at 1:22 am
This problem is hard to solve unless we have another ID column which defines an unique row number per row; like an identity column. Without order of rows, the results may vary pretty much.
June 6, 2011 at 1:48 am
This MAY NOT be the best code or the best-performant one, but this cuts the deal.
Sample data ( for anyone who is interested in solving this problem)
SET NOCOUNT ON
--= This is to ensure that sample data is inserted into the table
--= you may/may not use this sentence
SET DATEFORMAT DMY
--= Temp table to hold sample data
DECLARE @TestTable TABLE
( DateValues DATETIME , IDs INT )
--= Sample data
INSERT INTO @TestTable
SELECT '31/05/2010' , 57
UNION ALL SELECT '31/05/2010' , 57
UNION ALL SELECT '30/06/2010' , 57
UNION ALL SELECT '31/07/2010' , 57
UNION ALL SELECT '31/08/2010' , 57
UNION ALL SELECT '30/09/2010' , 57
UNION ALL SELECT '31/10/2010' , 57
UNION ALL SELECT '31/12/2010' , 57
UNION ALL SELECT '31/01/2011' , 57
UNION ALL SELECT '31/03/2011' , 57
UNION ALL SELECT '30/04/2011' , 57
UNION ALL SELECT '31/05/2011' , 57
UNION ALL SELECT '31/05/2010' , 4004
UNION ALL SELECT '31/07/2010' , 4004
UNION ALL SELECT '30/11/2010' , 4004
UNION ALL SELECT '30/04/2011' , 4004
Now the code to produce the desired output:
; WITH DistinctDates AS
(
--= Assign a row number to the rows; this
--= will ensure the proper order of rows based on which we can form the rank
SELECT DateValues , IDs ,
RN = ROW_NUMBER() OVER ( ORDER BY (SELECT NULL))
FROM @TestTable
),
RowNumberDataSet AS
(
--= Get distinct Datevalues and Ids and rank them based on the order of the row
SELECT DateValues , IDs,
RNK = ROW_NUMBER() OVER (PARTITION BY IDs ORDER BY MAX(RN) )
FROM DistinctDates
GROUP BY DateValues, IDs
),
RevampedBaseTable AS
(
--= Assign the ranks to the dates
SELECT Base.DateValues , Base.IDs , Numbered.RNK
FROM @TestTable Base
INNER JOIN RowNumberDataSet Numbered
ON Base.DateValues = Numbered.DateValues AND
Base.IDs = Numbered.IDs
)
SELECT DateValues , IDs , [RANK] = RNK ,
[Effective Start] = DateValues ,
--= Calculate the next available rank for each row
--= get the date and subtract a day from that to arrive at the result
[Effective End] = ( SELECT DATEADD(DD , -1 , InlineQueryTable.DateValues)
FROM RevampedBaseTable InlineQueryTable
WHERE InlineQueryTable.RNK = OuterTable.RNK + 1 AND
InlineQueryTable.IDs = OuterTable.IDs )
FROM RevampedBaseTable OuterTable
Hope that helps!
June 6, 2011 at 2:00 am
If the date will always an ascending order, then this will also help:
SELECT OuterTable.DateValues , OuterTable.IDs ,
[Effective Start] = OuterTable.DateValues ,
--= Calculate the next available rank for each row
--= get the date and subtract a day from that to arrive at the result
[Effective End] = ISNULL (
( SELECT TOP 1 DATEADD(DD , -1 , InlineQueryTable.DateValues)
FROM @TestTable InlineQueryTable
WHERE InlineQueryTable.DateValues > OuterTable.DateValues AND
InlineQueryTable.IDs = OuterTable.IDs )
, OuterTable.DateValues)
FROM @TestTable OuterTable
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply