none
How to use a Over/Partion clause to select a dynamic minimum row?

    Question

  • What I am attempting to do is when fshipbook+fshipbuy+fshipmake <> sorels.fjoqty + sorels.fbook + sorels.fbqty, I want a new column created showing the minimum date(sorels.fduedate) from that criteria labeled as EarliestDate.

    This the expected result that I'm looking for

    CREATE TABLE ExpectedResult (
      sorelsfsono INT
      , sorelsfjoqty int
      ,sorelsfbook int
      , sorelsfbqty int
      , fshipbook int    
      , fshipbuy int
      , fshipmake int  
      , sorelsfduedate date 
      , EarliestDate date
    );
    
    
    Insert into ExpectedResult
    Values ('039069',0,1,0,1,0,0,'2019-03-19','2019-04-03'),
           ('039069',1,0,0,1,0,0,'2019-03-19','2019-04-03'),
           ('039069',0,1,0,1,0,0,'2019-03-19','2019-04-03'),
           ('039069',0,1,0,1,0,0,'2019-03-25','2019-04-03'),
           ('039069',0,0,0,1,0,0,'2019-04-03','2019-04-03'),
           ('039069',0,1,0,0,0,0,'2019-04-10','2019-04-03'),
    	   ('039069',0,1,0,0,0,0,'2019-04-17','2019-04-03')
    This is my actual query
    SELECT
    sorels.fsono,
    sorels.fjoqty ,
    sorels.fbook ,
    sorels.fbqty
    fshipbook,
    fshipbuy,
    fshipmake,
    fshipbook+fshipbuy+fshipmake as totalshipped,
    sorels.fjoqty + sorels.fbook + sorels.fbqty as totalordered
    
    FROM m2mdata35.dbo.sorels   
    
    INNER JOIN soitem on sorels.fsono+sorels.finumber=soitem.fsono+soitem.finumber  
    INNER JOIN somast on sorels.fsono=somast.fsono  
    LEFT JOIN cspopup ON 'SHIPVIA             '+somast.fshipvia=cspopup.fcpopkey+cspopup.fcpopval
    LEFT JOIN inmast on soitem.fpartno+soitem.fpartrev=inmast.fpartno+inmast.frev 
    LEFT JOIN incros on inmast.fpartno+inmast.frev=incros.fpartno+incros.fcpartrev
    LEFT JOIN jomast on sorels.fstatus=jomast.fjobno 
    LEFT JOIN joitem on jomast.fjobno=joitem.fjobno
    LEFT JOIN jodrtg on jomast.fjobno=jodrtg.fjobno 
    LEFT JOIN inwork on jodrtg.fpro_id=inwork.fcpro_id
    LEFT JOIN slcdpmx on somast.fcustno=slcdpmx.fcustno  
    LEFT JOIN syphon ON somast.fcontact=syphon.fcontact AND somast.fcustno=syphon.fcsourceid 
    LEFT JOIN syaddr ON 'SLCDPM    '+somast.fcustno+'S'+sorels.fshptoaddr=syaddr.fcalias+syaddr.fcaliaskey+syaddr.fcaddrtype+syaddr.fcaddrkey 
     
    WHERE LEFT(somast.fstatus,1)<>'C' 
    and fsocoord<>'IFP' and fsocoord<>'711'   
    and somast.fsono='039069' 
    Order By sorels.fsono, sorels.fduedate

    Tuesday, April 23, 2019 5:02 PM

Answers

  • Hi David9501,

     

    Please try following script.

    ;with cte1 as (
    SELECT
    sorels.fsono,
    sorels.fjoqty ,
    sorels.fbook ,
    sorels.fbqty,
    fshipbook,
    fshipbuy,
    fshipmake,
    fshipbook+fshipbuy+fshipmake as totalshipped,
    sorels.fjoqty + sorels.fbook + sorels.fbqty as totalordered,
    min(sorels.fduedate ) Over(Partition by sorels.fsono, Case when fshipbook+fshipbuy+fshipmake <> sorels.fjoqty + sorels.fbook + sorels.fbqty then 1 else 0 end ) as EarliestDate,
    Case when fshipbook+fshipbuy+fshipmake <> sorels.fjoqty + sorels.fbook + sorels.fbqty then 1 else 0 end as [value] 
    FROM m2mdata35.dbo.sorels   
    
    INNER JOIN soitem on sorels.fsono+sorels.finumber=soitem.fsono+soitem.finumber  
    INNER JOIN somast on sorels.fsono=somast.fsono  
    LEFT JOIN cspopup ON 'SHIPVIA             '+somast.fshipvia=cspopup.fcpopkey+cspopup.fcpopval
    LEFT JOIN inmast on soitem.fpartno+soitem.fpartrev=inmast.fpartno+inmast.frev 
    LEFT JOIN incros on inmast.fpartno+inmast.frev=incros.fpartno+incros.fcpartrev
    LEFT JOIN jomast on sorels.fstatus=jomast.fjobno 
    LEFT JOIN joitem on jomast.fjobno=joitem.fjobno
    LEFT JOIN jodrtg on jomast.fjobno=jodrtg.fjobno 
    LEFT JOIN inwork on jodrtg.fpro_id=inwork.fcpro_id
    LEFT JOIN slcdpmx on somast.fcustno=slcdpmx.fcustno  
    LEFT JOIN syphon ON somast.fcontact=syphon.fcontact AND somast.fcustno=syphon.fcsourceid 
    LEFT JOIN syaddr ON 'SLCDPM    '+somast.fcustno+'S'+sorels.fshptoaddr=syaddr.fcalias+syaddr.fcaliaskey+syaddr.fcaddrtype+syaddr.fcaddrkey 
     
    WHERE LEFT(somast.fstatus,1)<>'C' 
    and fsocoord<>'IFP' and fsocoord<>'711'   
    and somast.fsono='039069' 
    ),cte2 as (
    select distinct fsono,EarliestDate from cte1 where [value]=1 
    )
    select a.fsono sorelsfsono,a.fjoqty sorelsfjoqty,a.fbook sorelsfbook,a.fbqty sorelsfbqty,
    a.fshipbook,a.fshipbuy,a.fshipmake,a.totalshipped,a.totalordered,b.EarliestDate
    from cte1 a join cte2 b on a.fsono=b.fsono


    Hope it can help you.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by David9501 Thursday, April 25, 2019 4:13 PM
    Wednesday, April 24, 2019 3:26 AM

All replies

  • This?

    SELECT
    sorels.fsono,
    sorels.fjoqty ,
    sorels.fbook ,
    sorels.fbqty
    fshipbook,
    fshipbuy,
    fshipmake,
    fshipbook+fshipbuy+fshipmake as totalshipped,
    sorels.fjoqty + sorels.fbook + sorels.fbqty as totalordered
    ,
     Case when fshipbook+fshipbuy+fshipmake <> sorels.fjoqty + sorels.fbook + sorels.fbqty then  
     min(sorels.fduedate ) Over(Partition by sorels.fsono, Case when fshipbook+fshipbuy+fshipmake <> sorels.fjoqty + sorels.fbook + sorels.fbqty then 1 else 0 end Order by sorels.fduedate ) 
     else sorels.fduedate
     end as EarliestDate 
    
    FROM m2mdata35.dbo.sorels 
    --where

    Tuesday, April 23, 2019 5:43 PM
    Moderator
  • This?

    SELECT
    sorels.fsono,
    sorels.fjoqty ,
    sorels.fbook ,
    sorels.fbqty
    fshipbook,
    fshipbuy,
    fshipmake,
    fshipbook+fshipbuy+fshipmake as totalshipped,
    sorels.fjoqty + sorels.fbook + sorels.fbqty as totalordered
    ,
     Case when fshipbook+fshipbuy+fshipmake <> sorels.fjoqty + sorels.fbook + sorels.fbqty then  
     min(sorels.fduedate ) Over(Partition by sorels.fsono, Case when fshipbook+fshipbuy+fshipmake <> sorels.fjoqty + sorels.fbook + sorels.fbqty then 1 else 0 end Order by sorels.fduedate ) 
     else sorels.fduedate
     end as EarliestDate 
    
    FROM m2mdata35.dbo.sorels 
    --where


    Almost, I want all of the rows in the EarliestDate column to have the same date 2019-04-03
    Tuesday, April 23, 2019 6:56 PM
  • Remove sorels.fsono column in partition by.
    Tuesday, April 23, 2019 7:27 PM
    Moderator
  • That did not work. I received the same results.
    Tuesday, April 23, 2019 8:22 PM
  • Hi David9501,

     

    Please try following script.

    ;with cte1 as (
    SELECT
    sorels.fsono,
    sorels.fjoqty ,
    sorels.fbook ,
    sorels.fbqty,
    fshipbook,
    fshipbuy,
    fshipmake,
    fshipbook+fshipbuy+fshipmake as totalshipped,
    sorels.fjoqty + sorels.fbook + sorels.fbqty as totalordered,
    min(sorels.fduedate ) Over(Partition by sorels.fsono, Case when fshipbook+fshipbuy+fshipmake <> sorels.fjoqty + sorels.fbook + sorels.fbqty then 1 else 0 end ) as EarliestDate,
    Case when fshipbook+fshipbuy+fshipmake <> sorels.fjoqty + sorels.fbook + sorels.fbqty then 1 else 0 end as [value] 
    FROM m2mdata35.dbo.sorels   
    
    INNER JOIN soitem on sorels.fsono+sorels.finumber=soitem.fsono+soitem.finumber  
    INNER JOIN somast on sorels.fsono=somast.fsono  
    LEFT JOIN cspopup ON 'SHIPVIA             '+somast.fshipvia=cspopup.fcpopkey+cspopup.fcpopval
    LEFT JOIN inmast on soitem.fpartno+soitem.fpartrev=inmast.fpartno+inmast.frev 
    LEFT JOIN incros on inmast.fpartno+inmast.frev=incros.fpartno+incros.fcpartrev
    LEFT JOIN jomast on sorels.fstatus=jomast.fjobno 
    LEFT JOIN joitem on jomast.fjobno=joitem.fjobno
    LEFT JOIN jodrtg on jomast.fjobno=jodrtg.fjobno 
    LEFT JOIN inwork on jodrtg.fpro_id=inwork.fcpro_id
    LEFT JOIN slcdpmx on somast.fcustno=slcdpmx.fcustno  
    LEFT JOIN syphon ON somast.fcontact=syphon.fcontact AND somast.fcustno=syphon.fcsourceid 
    LEFT JOIN syaddr ON 'SLCDPM    '+somast.fcustno+'S'+sorels.fshptoaddr=syaddr.fcalias+syaddr.fcaliaskey+syaddr.fcaddrtype+syaddr.fcaddrkey 
     
    WHERE LEFT(somast.fstatus,1)<>'C' 
    and fsocoord<>'IFP' and fsocoord<>'711'   
    and somast.fsono='039069' 
    ),cte2 as (
    select distinct fsono,EarliestDate from cte1 where [value]=1 
    )
    select a.fsono sorelsfsono,a.fjoqty sorelsfjoqty,a.fbook sorelsfbook,a.fbqty sorelsfbqty,
    a.fshipbook,a.fshipbuy,a.fshipmake,a.totalshipped,a.totalordered,b.EarliestDate
    from cte1 a join cte2 b on a.fsono=b.fsono


    Hope it can help you.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by David9501 Thursday, April 25, 2019 4:13 PM
    Wednesday, April 24, 2019 3:26 AM
  • SELECT
    sorels.fsono,
    sorels.fjoqty ,
    sorels.fbook ,
    sorels.fbqty
    fshipbook,
    fshipbuy,
    fshipmake,
    fshipbook+fshipbuy+fshipmake as totalshipped,
    sorels.fjoqty + sorels.fbook + sorels.fbqty as totalordered
    ,
      
     min(sorels.fduedate ) Over(Partition by sorels.fsono, Case when fshipbook+fshipbuy+fshipmake <> sorels.fjoqty + sorels.fbook + sorels.fbqty then 1 else 0 end Order by sorels.fduedate )  as EarliestDate 
    
    FROM m2mdata35.dbo.sorels 
    --where
    Wednesday, April 24, 2019 3:48 AM
    Moderator
  • This works well for my original question but I'm losing twos of the total rows in the final output.
    Wednesday, April 24, 2019 3:50 PM
  • It is not possible because the query didn't change your filter condition at all.
    Wednesday, April 24, 2019 3:57 PM
    Moderator
  • Hi David9501,

    Thank you for your reply.

     

    I tried to do an example using above script and it will work well. Here is the example .

    If Object_ID('test','U') Is Not Null Drop Table test
    go
    CREATE TABLE test (
    fsono INT
    ,fjoqty int
    ,fbook int
    ,fbqty int
    ,fshipbook int    
    ,fshipbuy int
    ,fshipmake int  
    ,fduedate date  
    );
    
    Insert into test
    Values ('039069',0,1,0,1,0,0,'2019-03-19'),
           ('039069',1,0,0,1,0,0,'2019-03-19'),
           ('039069',0,1,0,1,0,0,'2019-03-19'),
           ('039069',0,1,0,1,0,0,'2019-03-25'),
           ('039069',0,0,0,1,0,0,'2019-04-03'),
           ('039069',0,1,0,0,0,0,'2019-04-10'),
    	   ('039069',0,1,0,0,0,0,'2019-04-17')
    
    
    
    ;with cte1 as (
    SELECT
    fsono,
    fjoqty ,
    fbook ,
    fbqty,
    fshipbook,
    fshipbuy,
    fshipmake,
    fshipbook+fshipbuy+fshipmake as totalshipped,
    fjoqty + fbook + fbqty as totalordered,
    min(fduedate ) Over(Partition by fsono, Case when fshipbook+fshipbuy+fshipmake <> fjoqty + fbook + fbqty then 1 else 0 end ) as EarliestDate,
    Case when fshipbook+fshipbuy+fshipmake <> fjoqty + fbook + fbqty then 1 else 0 end as [value] 
    FROM test
    ),cte2 as (
    select distinct fsono,EarliestDate from cte1 where [value]=1 
    )
    select a.fsono sorelsfsono,a.fjoqty sorelsfjoqty,a.fbook sorelsfbook,a.fbqty sorelsfbqty,
    a.fshipbook,a.fshipbuy,a.fshipmake,a.totalshipped,a.totalordered,b.EarliestDate
    from cte1 a join cte2 b on a.fsono=b.fsono
    /*
    sorelsfsono sorelsfjoqty sorelsfbook sorelsfbqty fshipbook   fshipbuy    fshipmake   totalshipped totalordered EarliestDate
    ----------- ------------ ----------- ----------- ----------- ----------- ----------- ------------ ------------ ------------
    39069       0            1           0           1           0           0           1            1            2019-04-03
    39069       1            0           0           1           0           0           1            1            2019-04-03
    39069       0            1           0           1           0           0           1            1            2019-04-03
    39069       0            1           0           1           0           0           1            1            2019-04-03
    39069       0            0           0           1           0           0           1            0            2019-04-03
    39069       0            1           0           0           0           0           0            1            2019-04-03
    39069       0            1           0           0           0           0           0            1            2019-04-03
    */
     
    
    
    

    If above sample doesn’t satisfy your requirement, please share us your table structure and some sample data along with your expected result. So that we’ll get a right direction and make some test.


    Best Regards,

    Rachel

     


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, April 25, 2019 3:18 AM