Add LAG or ROW_NUMBER into an Unusually Sorted Table-CodePudding

I had previously asked a question about how to sort by table in an unusual way (SQL Server: Two Level Sort (Order/Group By??)). I have added the solution to my code:

SELECT [Date]
      ,[ID]
      ,[Account]
      ,[First_Last]
FROM [Table]
ORDER BY [ID], MIN([Date]) OVER (PARTITION BY [Account], [ID]), [Account], [Date]

Date        ID      Account  First_Last
9/29/2017   1111    753      1
9/28/2018   1111    753      2
10/31/2018  1111    45       1
1/29/2021   1111    45       4
2/28/2017   2222    621      1
2/28/2018   2222    621      2
6/30/2017   2222    1007     1
6/29/2018   2222    1007     2
9/29/2017   2222    481      1
1/31/2018   2222    481      2
10/31/2017  2222    488      1
1/31/2018   2222    488      2
11/30/2017  2222    582      1
1/31/2019   2222    582      3

Now, I want to add change flags that signal when Account changes, but whenever I try LAG or ROW_NUMBER the ORDER BY statement is giving me trouble. If I use:

[Account] <> LAG([Account]) OVER (ORDER BY [ID], MIN([Date]) OVER (PARTITION BY [Account], [ID]), [Account], [Date])

It shows me this error:

Windowed functions cannot be used in the context of another windowed function or aggregate.

But if I don't use a different ORDER BY without a window function, Account is being compared to an incorrect number instead of the previous one such as:

SELECT [Date]
      ,[ID]
      ,[Account]
      ,[First_Last]
      ,CASE WHEN[Account] <> LAG([Account]) OVER (ORDER BY [Account]) THEN '1' END AS [Acct_Chg]
FROM [Table]
ORDER BY [ID], MIN([Date]) OVER (PARTITION BY [Account], [ID]), [Account], [Date]

Which resulted in this:

Date        ID      Account  First_Last  Acct_Chg
9/29/2017   1111    753      1           
9/28/2018   1111    753      2           
10/31/2018  1111    45       1           
1/29/2021   1111    45       4           1
2/28/2017   2222    621      1           1
2/28/2018   2222    621      2
6/30/2017   2222    1007     1           
6/29/2018   2222    1007     2           1
9/29/2017   2222    481      1           
1/31/2018   2222    481      2
10/31/2017  2222    488      1           
1/31/2018   2222    488      2           1
11/30/2017  2222    582      1           1
1/31/2019   2222    582      3

Which is incorrect.

Ultimately, what I am looking for is this:

Date        ID      Account  First_Last  Acct_Chg
9/29/2017   1111    753      1           
9/28/2018   1111    753      2           
10/31/2018  1111    45       1           1
1/29/2021   1111    45       4
2/28/2017   2222    621      1           1
2/28/2018   2222    621      2
6/30/2017   2222    1007     1           1
6/29/2018   2222    1007     2
9/29/2017   2222    481      1           1
1/31/2018   2222    481      2
10/31/2017  2222    488      1           1
1/31/2018   2222    488      2
11/30/2017  2222    582      1           1
1/31/2019   2222    582      3

I don't know of I can just do this with LAG or whether I need ROW_NUMBER, but I could not get either to work the way I want.

Any help would be greatly appreciated.

Thank you.

UPDATE:

I tried:

SELECT [Date]
      ,[ID]
      ,[Account]
      ,[First_Last]
      ,CASE WHEN [Account] <> LAG([Account]) OVER (ORDER BY [Account], [Date] DESC) THEN '1' END AS [Acct_Chg]
FROM [Table]
ORDER BY [ID], MIN([Date]) OVER (PARTITION BY [Account], [ID]), [Account], [Date]

And got this:

Date        ID      Account  First_Last  Acct_Chg
9/29/2017   1111    753      1           
9/28/2018   1111    753      2           
10/31/2018  1111    45       1
1/29/2021   1111    45       4           1
2/28/2017   2222    621      1           
2/28/2018   2222    621      2
6/30/2017   2222    1007     1           
6/29/2018   2222    1007     2
9/29/2017   2222    481      1           
1/31/2018   2222    481      2
10/31/2017  2222    488      1           
1/31/2018   2222    488      2           1
11/30/2017  2222    582      1           
1/31/2019   2222    582      3           1

CodePudding user response：

As you have found out that you cannot window function inside another window function.

The workaround is the use CTE and find the MINDATE MIN([Date]) OVER (PARTITION BY [Account], [ID]) inside the CTE. Then on the outer query you can use the LAG() as per normal.

WITH CTE AS
(
    SELECT [Date]
          ,[ID]
          ,[Account]
          ,[First_Last]
          ,MIN([Date]) OVER (PARTITION BY [Account], [ID]) AS [MINDATE]
    FROM  [Table]
)
SELECT [Date]
      ,[ID]
      ,[Account]
      ,[First_Last]
      ,CASE WHEN [Account] <> LAG([Account]) OVER (ORDER BY [ID], [MINDATE], [Account], [Date]) 
            THEN '1' 
            END AS [Acct_Chg]
FROM   CTE
ORDER BY [ID], [MINDATE], [Account], [Date]