0

I need to divide items in consecutive groups:


SUP_id  Item    from        to      restult
1       A_3 2019-01-11  2019-12-06      1
1       A_3 2019-01-11  2019-02-17      1
1       A_3 2019-01-08  2019-01-10      1
1       A_2 2018-10-06  2019-01-07      2
1       A_2 2018-12-04  2019-01-07      2
1       A_2 2018-10-06  2018-12-03      2
1       A_3 2018-04-21  2018-10-05      3
1       A_3 2018-04-16  2018-10-05      3
1       A_3 2018-08-26  2018-10-05      3
1       A_3 2018-04-16  2018-08-25      3
1       A_3 2018-04-16  2018-06-09      3
1       A_2 2018-02-25  2018-04-15      4
1       A_2 2018-01-02  2018-04-15      4
2       A_2 2018-01-02  2018-02-24      4
2       A_2 2018-01-02  2018-02-01      4

Already tried row_number(), rank(). Unfortunately I can't use neither lag() nor lead(), although I use SQL Server 2012. Recently I came up with the idea to use something like that:

WITH ab AS
(
   SELECT SUP_id, Item, date_from, date_to,
          rownum = ROW_NUMBER() OVER (PARTITION BY SUP_id ORDER BY data_do DESC)
     FROM #as_ a
)
SELECT ab.*, nex.Item next_Item, 
       RANK() OVER (PARTITION BY ab.SUP_id ORDER BY ab.Item DESC) r2,
       CASE WHEN ab.Item = nex.Item THEN 1 ELSE 0 END AS x
  FROM ab
  LEFT JOIN ab nex 
    ON ab.rownum+1 =nex.rownum
 ORDER BY date_to DESC

The idea behind it was to find last row within the group and based on that do some calculation. Then I got stuck without if statement.

MarcinJ
  • 3,471
  • 2
  • 14
  • 18
Faber
  • 1
  • 1
  • 2
    Why can't you use lead or lag? I am trying to understand what you are trying to do here but just can't wrap my head around it. Can you explain this? – Sean Lange Apr 10 '19 at 15:09
  • I just finished up being jammed up with a possibly similar issue. You might might this helpful: https://stackoverflow.com/questions/27680999/resetting-row-number-according-to-record-data-change – Jeffrey Eldredge Apr 10 '19 at 15:10
  • If I try to use LEAD( ) or LAG( ) I receive an error:The Parallel Data Warehouse (PDW) features are not enabled. – Faber Apr 15 '19 at 14:48
  • Jeffrey Eldredge. Thanks a lot. Solution in your topic is just great:) Worked perfectly. – Faber Apr 15 '19 at 14:50

0 Answers0