October 19, 2016 at 6:20 am
Hi ,
I have below table
CREATE TABLE #tmp(Date DATE, Amount Int)
INSERT #tmp
( Date, Amount )
SELECT '2016-10-01',2700
UNION
SELECT '2016-10-02',2700
UNION
SELECT '2016-10-03',2700
UNION
SELECT '2016-10-04',3000
UNION
SELECT '2016-10-05',3000
UNION
SELECT '2016-10-06',2700
UNION
SELECT '2016-10-07',4700
UNION
SELECT '2016-10-08',4700
UNION
SELECT '2016-10-09',4700
UNION
SELECT '2016-10-10',5700
UNION
SELECT '2016-10-11',5700
UNION
SELECT '2016-10-12',2700
UNION
SELECT '2016-10-13',2700
UNION
SELECT '2016-10-14',3000
And I want output like :
/*
Expected OUTPUT :
StartDAte End Date Amount
2016-10-01 2016-10-03 2700
2016-10-04 2016-10-05 3000
2016-10-06 2016-10-06 2700
2016-10-07 2016-10-09 4700
2016-10-10 2016-10-11 5700
2016-10-12 2016-10-13 2700
2016-10-14 2016-10-14 3000
*/
Please help to solve it.
Thanks
October 19, 2016 at 6:52 am
possibly ??
WITH cte AS (
SELECT Date,
Amount,
ROW_NUMBER() OVER(ORDER BY date) - DENSE_RANK() OVER(PARTITION BY amount ORDER BY date) AS grp
FROM #tmp
)
SELECT MIN(Date) AS startdate,
MAX(Date) AS enddate,
MAX(Amount) AS amount
FROM cte
GROUP BY grp
ORDER BY MIN(Date)
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
October 19, 2016 at 8:29 am
Thanks livingstone :-), this one is perfect .. meanwhile I have done like this
SELECT *,LEAD(Amount,1,0)OVER (ORDER BY date) nextv,lag(Amount,1,0)OVER (ORDER BY date) PRevv
FROM #tmp
),CTE1 as (
SELECT CASE WHEN PRevv<>Amount THEN Date END startdate,CASE WHEN nextv<>amount THEN Date END enddate, Amount
FROM CTE
)
SELECT startdate,(SELECT MIN(enddate) FROM CTE1 tt WHERE amount=t.amount AND tt.enddate>=t.startdate) ,Amount
FROM CTE1 t
WHERE startdate IS NOT NULL
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply