# question

## Sales Report with Up-Down Indicator

![Sales Table][1] ![alt text][2] [1]: /storage/temp/1216-salestable.png [2]: /storage/temp/1217-finaloutput.jpg As you can see, i have two tables. Sales table and output which i want. Output table contain three columns FromID, ToID and SalesIndicator. Lets take the example of first row. In the sales table from ID 1 to ID 4, sales is increasing hence indicator is Up. Lets take the example of second row. In the sales table from ID 4 to ID 7, sales is decreasing hence indicator is Down. Please help me to write a code which will populate the output table using sales table. Thank You, Akash
salestable.png (4.0 KiB)
finaloutput.jpg (12.9 KiB)

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Like 1 ·
Don't forget to vote on the answers you've received and mark one as a solution.
0 Likes 0 ·

Here's one way but I'm sure there's a simpler method! This solution uses 3 CTEs. The first 'walks' the data and uses the LAG() function to determine if the indicator should be UP or DOWN. Then the second CTE uses a self join to group the continuous identical indicators and give us a unique id for each group. The third CTE then uses windowed aggregate functions over this grouping to get the start and end ids for each group, but we have to tweak the edge case of the starting group as the first group is deemed to 'start' with it's first entry, whereas the others 'start' with the previous entry. declare @Sales table (id int, Sales int) insert into @sales select 1,100 insert into @sales select 2,110 insert into @sales select 3,120 insert into @sales select 4,130 insert into @sales select 5,100 insert into @sales select 6,90 insert into @sales select 7,80 insert into @sales select 8,90 insert into @sales select 9,100 insert into @sales select 10,110 insert into @sales select 11,70 insert into @sales select 12,80 insert into @sales select 13,90 insert into @sales select 14,100 insert into @sales select 15,110 insert into @sales select 16,80 ;with cte1 as ( select id , Sales, case when Sales >= lag(Sales,1,0)over(order by id) then 'UP' else 'DOWN' end indicator from @Sales ) ,cte2 as ( select id , Sales , indicator, isnull((select min(id) from cte1 b where b.indicator <> a.indicator and b.id > a.id),0) as UniqueGroupingID from cte1 a ) ,cte3 as ( select distinct min(id)over(partition by UniqueGroupingID order by id rows between unbounded preceding and unbounded following) -1 as FromID, max(id)over(partition by UniqueGroupingID order by id rows between unbounded preceding and unbounded following) as ToID, indicator from cte2 ) select case when FromID = 0 then 1 else FromID end as FromID, ToID, indicator from cte3

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

;WITH cte (FromID,ToID,SalesIndicator,GroupNO) AS ( SELECT t1.ID, t2.ID,CASE WHEN t1.Sales < t2.Sales THEN 'UP' ELSE 'Down' END ,t1.ID-ROW_NUMBER() OVER (PARTITION BY CASE WHEN t1.Sales < t2.Sales THEN 'UP' ELSE 'Down' END ORDER BY t1.ID) FROM @sales t1 JOIN @sales t2 ON t2.ID = t1.ID+1) SELECT MIN(FromID) AS [FromID],MAX(ToID) AS [ToID],SalesIndicator FROM cte GROUP BY GroupNo,SalesIndicator ORDER BY FromID ASC

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 Ah that's better - I got bogged down after starting with LAG(), and then couldn't get out of that mindset. Sometimes just doing the self join makes life easier.
1 Like 1 ·
But you can still use the LAG/LEAD functions if you want - you just have to use another cte: ;with cte1 as ( select id as FromId, coalesce(lead(id,1,null)over(order by id),id) as ToId, Sales, case when Sales ]]
1 Like 1 ·
yes, thats true...self join makes life a lot easier. :-)
0 Likes 0 ·