select only rows with max value on a column

Question

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

Answer

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'?

Answer

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:

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.

Answer

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:

I've assumed that when there is a new C row after an A, it all starts over.
Other status values can be ignored.
The customers are indepedent of each other.
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

Share via

select only rows with max value on a column

3 answers

Your answer