select only rows with max value on a column

Yassir 201 Reputation points
2021-09-16T20:46:10.873+00:00

Hello,

I have the table with data as bellow :

  create table tbl_Fact(
          IdCmd int,
          DateCMD datetime,
          DateLIV datetime,
          Stat varchar(15),
          CA int,
          CUST int
          )
        insert into tbl_Fact
        select 1,'2016-05-15',null,'C',100,1
        union
        select 2,'2016-05-20',null,'C',110,1
        union
        select 3,'2016-05-22',null,'C',120,1
        union
        select 4,'2016-05-22',null,'C',80,2
        union
        select 5,'2016-05-24',null,'C',100,2
        union
        select 6,'2016-05-26',null,'C',120,1
        union
        select 7,'2016-05-27','2016-05-29','A',60,1
        union
        select 8,'2016-05-28','2016-05-29','A',60,1

I want to retrieve the value like as bellow

IdCmd DateCMD              DateLIV                                Stat CA    CUST MaxVal
1 2016-05-15 00:00:00.000 NULL                                 C 100    1         NULL
2 2016-05-20 00:00:00.000 NULL                                 C 110    1         NULL
3 2016-05-22 00:00:00.000 NULL                                 C 120    1           120
4 2016-05-22 00:00:00.000 NULL                                 C 80    2          NULL
5 2016-05-24 00:00:00.000 NULL                                 C 100    2           100
6 2016-05-26 00:00:00.000 NULL                                 C 120    1          NULL
7 2016-05-27 00:00:00.000 2016-05-29 00:00:00.000 A 60    1          NULL
8 2016-05-28 00:00:00.000 2016-05-29 00:00:00.000 A 60    1           0

When i have Dateliv null and stat ='C' i want to get the max of CA (row 3 and 5) and if i have DateLiv null and stat='C' and just after i have DateLiv not null and Stat='A'
I want to do subtraction of the first line with stat C and the cumulative of stat A example (row 6-7-8)

How can i do that ?
Thanks

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,582 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Erland Sommarskog 103.9K Reputation points MVP
    2021-09-16T21:49:42.47+00:00

    Here is a query that gives you the MaxValue:

    ; WITH CTE AS (
        SELECT IdCmd, DateCMD, DateLIV, Stat, CA, CUST,
               MaxVal = MAX(CA) OVER(PARTITION BY CUST, Stat)
       FROM  tbl_Fact
    )
    SELECT IdCmd, DateCMD, DateLIV, Stat, CA, CUST,
           CASE WHEN MaxVal = CA AND Stat = 'C' THEN MaxVal END AS MaxVal
    FROM   CTE
    ORDER BY IdCmd
    

    But for the sum, I think we need to know more. Is DateLIV always NULL when Stat is 'C'? Always non-NULL when Stat is 'A'? Can there be other Stat values? Can there be more rows with Stat = 'C' after the ones with Stat = 'A'?


  2. EchoLiu-MSFT 14,576 Reputation points
    2021-09-17T02:21:27.223+00:00

    Please check:

    ;WITH cte AS (  
        SELECT *,MAX(CA) OVER(PARTITION BY CUST, Stat) MM,  
    		   SUM(CA) OVER(PARTITION BY CUST, Stat) SumVal,  
    	       LAG(Stat) OVER(ORDER BY IdCmd) LagStat,  
    	       LAG(CA) OVER(ORDER BY IdCmd) LagCA  
    	FROM tbl_Fact)  
      
    SELECT IdCmd, DateCMD, DateLIV, Stat, CA, CUST,  
           CASE WHEN MM = CA AND Stat = 'C' THEN MM   
           WHEN LagStat ='C' AND Stat='A' THEN LagCA-SumVal END MaxVal   
    FROM cte  
    ORDER BY IdCmd  
    

    Output:
    132923-image.png

    If you have any question, please feel free to let me know.

    Regards
    Echo


    If the answer is helpful, please click "Accept Answer" and upvote it.


  3. Erland Sommarskog 103.9K Reputation points MVP
    2021-09-18T20:18:43.4+00:00

    It seems that you (or someone else?) deleted the other two questions, so I guess is the one to work with.

    Here is a solution. Before I present it, I need to say few things. This is nothing you can take as spoonfood, but you need to read it closely and make sure that you understand it. I recommend that you look at the output from each CTE.

    The reason for this is that I had make some assumptions of what result you want when there is more data. My assumptions may be wrong. But you may be able to correct my query. A few things:

    1. I've assumed that when there is a new C row after an A, it all starts over.
    2. Other status values can be ignored.
    3. The customers are indepedent of each other.
    4. The grpno that appears group each sequence of C and A rows. A C row that comes just before an A row, counts as an A row.

    Here it goes. I added some more sample data:

    DROP TABLE IF EXISTS tbl_Fact
    go
           create table tbl_Fact(
                   IdCmd int,
                   DateCMD datetime,
                   DateLIV datetime,
                   Stat varchar(15),
                   CA int,
                   CUST int
                   )
                 insert into tbl_Fact
                 select 1,'2016-05-15',null,'C',100,1
                 union all
                 select 2,'2016-05-20',null,'C',110,1
                 union all
                 select 3,'2016-05-22',null,'C',120,1
                 union all
                 select 4,'2016-05-22',null,'C',80,2
                 union all
                 select 5,'2016-05-24',null,'C',100,2
                 union all
                 select 6,'2016-05-26',null,'C',120,1
                 union all
                 select 7,'2016-05-27','2016-05-29','A',60,1
                 union all
                 select 8,'2016-05-28','2016-05-29','A',60,1
                 union all
                 select 9,'2017-01-15',null,'C',11000,1
                 union all
                 select 10,'2017-01-16',null,'D',1100,1
                 union all
                 select 11,'2017-05-15',null,'C',1100,1
                 union all
                 select 12,'2017-05-20',null,'C',1110,1
                 union all
                 select 13,'2017-05-22',null,'C',1120,1
                 union all
                 select 14,'2017-05-22',null,'C',180,2
                 union all
                 select 15,'2017-05-24',null,'C',1100,2
                 union all
                 select 16,'2017-05-26',null,'C',1120,1
                 union all
                 select 17,'2017-05-27','2016-05-29','A',160,1
                 union all
                 select 18,'2017-05-28','2016-05-29','A',160,1
    go
    ; WITH CTE1 AS (
       SELECT IdCmd, DateCMD, DateLIV, CA, CUST, Stat,
               ModStat = IIF (Stat = 'C' AND LEAD(Stat) OVER (PARTITION BY CUST ORDER BY IdCmd) = 'A', 'A', Stat),
               CASign = IIF(Stat = 'A', -1, 1)
       FROM  tbl_Fact
       WHERE  Stat IN ('C', 'A')
    ), CTE2 AS (
       SELECT IdCmd, DateCMD, DateLIV, CA, CASign, CUST, Stat,ModStat,
              cstrowno = row_number() OVER(PARTITION BY CUST ORDER BY DateCMD, IdCmd),
              cststatrowno = row_number() OVER(PARTITION BY CUST, ModStat ORDER BY DateCMD, IdCmd)
       FROM   CTE1
    ), CTE3 AS (
       SELECT IdCmd, DateCMD, DateLIV, CA, CASign, CUST, Stat, ModStat,
              grpno = cststatrowno - cstrowno
       FROM  CTE2
    )
    SELECT IdCmd, DateCMD, DateLIV, Stat, CA, CUST,
           MaxVal = CASE WHEN ModStat = 'C' THEN
                              CASE WHEN CA = MAX(CA) OVER (PARTITION BY CUST, grpno) THEN CA END
                         WHEN ModStat = 'A' THEN
                              SUM(CA*CASign) OVER(PARTITION BY CUST, grpno
                                                  ORDER BY DateCMD, IdCmd ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
                     END
    FROM   CTE3
    ORDER BY CUST, IdCmd
    
    0 comments No comments