CODEWITHSUNDEEP
sql-serversql-server-2014
RTR
RTR

Reputation: 129

SQL, using Group by until specific trend (increment, decrement, same)

I would like to know how can i modify my code for considering all the same values of suppose 10 as UP till the time it is incrementing and then down for decrement and SAME if there is no change till the time there is no variation in the value (increment, decrement, same).

Here is my code :

;with etape1 as
    (
select ROW_NUMBER() OVER(ORDER BY mnth) AS id,* from [InsideTSQL2008].[alioune].[Sales]
    )
    ,
 etape2 as
 (
 select 
    a.id, b.mnth AS START , a.mnth AS FINISH , 
   a.qty - b.qty AS TREND
FROM
   etape1 a
   LEFT  JOIN etape1 b
   on a.id = b.id+1
   )
   select * from etape2;

My Result is :

id  START       FINISH      TREND
1   NULL        2007-12-01  NULL
2   2007-12-01  2008-01-01  10
3   2008-01-01  2008-02-01  10
4   2008-02-01  2008-03-01  10
5   2008-03-01  2008-04-01  10
6   2008-04-01  2008-05-01  0
7   2008-05-01  2008-06-01  -10
8   2008-06-01  2008-07-01  -10
9   2008-07-01  2008-08-01  -10
10  2008-08-01  2008-09-01  -10
11  2008-09-01  2008-10-01  10
12  2008-10-01  2008-11-01  -10
13  2008-11-01  2008-12-01  20
14  2008-12-01  2009-01-01  10
15  2009-01-01  2009-02-01  10
16  2009-02-01  2009-03-01  -40

My final result as required should be like :

Start        End      Trend

200712      200712     unknown
200801      200804     UP
200805      200805     SAME
200806      200809     DOWN
200810      200810     UP
200811      200811     DOWN
200812      200812     UP
200903      200903     DOWN
200904      200905     SAME
200906      200907     UP

Any help would be really helpful; Thanks

Upvotes: 0

Views: 175

Answers (1)

lysp
lysp

Reputation: 36

Took me a few goes (and a few hours), but I think I have what you want:

DECLARE @Sales AS TABLE (mnth datetime, qty int)

INSERT INTO @Sales
SELECT '2016-01-01', 10 UNION ALL 
SELECT '2016-02-01', 20 UNION ALL 
SELECT '2016-03-01', 30 UNION ALL 
SELECT '2016-04-01', 40 UNION ALL 
SELECT '2016-05-01', 40 UNION ALL 
SELECT '2016-06-01', 30 UNION ALL 
SELECT '2016-07-01', 20 UNION ALL 
SELECT '2016-08-01', 30 UNION ALL 
SELECT '2016-09-01', 40 UNION ALL 
SELECT '2016-10-01', 45 UNION ALL 
SELECT '2016-11-01', 50

;WITH etape1 AS (
    SELECT ROW_NUMBER() OVER(ORDER BY mnth) AS id, * FROM @Sales
)
, etape2 AS (
    SELECT id, lag(mnth) OVER (ORDER BY id) AS START, mnth AS FINISH, CASE WHEN qty - LAG(qty) OVER (ORDER BY id) < 0 THEN -1 WHEN qty - LAG(qty) OVER (ORDER BY id) > 0 THEN 1 ELSE 0 END AS TREND
    FROM etape1
)
, etape3 AS (
    SELECT id, START, FINISH, TREND, lag(TREND) OVER (ORDER BY id) AS PrevTrend
    FROM etape2
) 
, etape4 AS (
    SELECT id, START, FINISH, TREND, SUM(CASE WHEN TREND = PREVTREND THEN 0 ELSE 1 END) OVER (ORDER BY id ROWS UNBOUNDED PRECEDING) AS Change
    FROM etape3
)
SELECT MIN(START) AS START, MAX(FINISH) AS FINISH, CASE WHEN MIN(TREND) IS NULL THEN 'Unknown' WHEN MIN(TREND) < 0 THEN 'Down' WHEN MIN(TREND) > 0 THEN 'Up' WHEN MIN(Start) is NULL THEN 'Unknown' ELSE 'Same' END AS TREND
FROM etape4
GROUP BY Change
ORDER BY START

Results are:

START       FINISH      TREND
NULL        2016-01-01  Unknown
2016-01-01  2016-04-01  Up
2016-04-01  2016-05-01  Same
2016-05-01  2016-07-01  Down
2016-07-01  2016-11-01  Up

Upvotes: 1

Related Questions