question

Akash avatar image
Akash asked

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
tsqlsqlteam
salestable.png (4.0 KiB)
finaloutput.jpg (12.9 KiB)
2 comments
10 |1200 characters needed characters left characters exceeded

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

The site works on votes. Please show any helpful answers by clicking on the thumbs up next to those answers. If one answer lead you to a solution indicate that by clicking on the the check mark next to that answer.
1 Like 1 ·
Don't forget to vote on the answers you've received and mark one as a solution.
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
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
10 |1200 characters needed characters left characters exceeded

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

Akash avatar image
Akash answered
;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
3 comments
10 |1200 characters needed characters left characters exceeded

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 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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