Help Plz
-
Hi,
Below is my sample data from table TranscationIn
PositionNbr FromDate ToDate IncentivePlanTypeCode
----------- ------------------------------------------------------ ------------------------------------------------------ ---------------------
90176256 2003-05-03 00:00:00.000 2003-06-30 00:00:00.000 C1
90176256 2003-07-01 00:00:00.000 2003-11-20 00:00:00.000 C1
90176256 2003-11-21 00:00:00.000 2003-11-24 00:00:00.000 C1
90176256 2003-11-25 00:00:00.000 2003-12-03 00:00:00.000 S2
90176256 2003-12-04 00:00:00.000 9999-12-31 00:00:00.000 C1
in this FromDate, ToDate columns are of datatype DateTime
i need to write a sql query( not a stored proc or a user defined function), so tht i can get my result as
PositionNbr FromDate ToDate IncentivePlanTypeCode
----------- ------------------------------------------------------ ------------------------------------------------------ ---------------------
90176256 2003-05-03 00:00:00.000 2003-11-24 00:00:00.000 C1
basically it shuld look for contingious records where FromDate of record1 should be exactly next day of the previous record and all shuld have similar IncentivePlanTypeCode
TIA,
sudheer
Regards,
Sudheer
-
Hi cutespn,
try this self joinining query, I hope it'll help:
SELECT
TI1.PositionNbr, TI1.IncentivePlanTypeCode,
MIN(TI1.FromDate) AS Min_FromDate,
MAX(TI2.ToDate) AS Max_To_Date
FROM
TranscationIn AS TI1
INNER JOIN TranscationIn AS TI2
ON (TI1.PositionNbr = TI2.PositionNbr) AND
(TI1.IncentivePlanTypeCode = TI2.IncentivePlanTypeCode) AND
(TI2.FromDate = DATEADD(Day, 1, TI1.ToDate))
GROUP BY
TI1.PositionNbr, TI1.IncentivePlanTypeCode
If the table is large, some proper indexes on the join columns will speed up the execution.
Regards,
Goce Smilevski.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply