locked
select last row of column depending on another column RRS feed

  • Question

  • Hi, can anybody  help

    the code below, select  only last price for group gr1 and gr2

      

    create table gr
    (
     gr1 nvarchar(20),
     gr2 nvarchar(20),
     supl int,
     rt int,
     suplrt int, --suplrt-rt
     sales int,
     rt1 int,
     salesrt1 int,--suplrt-rt1
     );
     insert into gr values
     ('x','y',2,8,-6,4,2,(4-2));
    GO

    create table price
     (
     gr1 nvarchar(20),
     gr2 nvarchar(20),
     date_begin datetime,
     date_end datetime,
     price NUMERIC(18,2)
     );

     SET DATEFORMAT DMY

     insert into price values
     ('x','y','15.07.2014','31.01.2015',160),
     ('x','y','01.02.2015','31.03.2015',176),
     ('x','y','01.04.2015','28.02.2017',202.4),
     ('x','y','01.03.2017','31.01.2019',212.52),
     ('x','y','01.02.2019', NULL,225.27);
     GO

    --Query
    SELECT 
    T.gr1,T.gr2,T.supl,T.rt,T.suplrt,T.sales,T.rt1,T.salesrt1,
    T1.date_begin,
    T1.date_end,
    T1.price
    FROM gr T
    CROSS APPLY (SELECT TOP 1 date_begin,date_end,price FROM price WHERE gr1=T.gr1 AND gr2=T.gr2 ORDER BY date_begin DESC) T1




    but let's return to to price table, it can have column price_prior

    create table price
     (
     gr1 nvarchar(20),
     gr2 nvarchar(20),
     date_begin datetime,
     date_end datetime,
     price NUMERIC(18,2),


    price_prior(int)


     );

     SET DATEFORMAT DMY

     insert into price values
     ('x','y','15.07.2014','31.01.2015',160,1),
     ('x','y','01.02.2015','31.03.2015',176,1),
     ('x','y','01.04.2015','28.02.2017',202.4,3),
     ('x','y','01.03.2017','31.01.2019',212.52,4),
     ('x','y','01.02.2019', NULL,225.27,1);

     ('x','y','01.02.2019', NULL,225.27,4);

     GO

    If null  after data then it is last price. but it can be that i have 2 nulls like i provided

    How to do that if we have two or more nulls , select only price which has lowest value by price_prior. in this case

    ('x','y','01.02.2019', NULL,225.27,1);

    if we have such case
    ('x','y','01.03.2017','31.01.2019',212.52,3), ('x','y','01.02.2019', NULL,225.27,2);

    we take price with lowest value by price_piror, (here=2)

    ('x','y','01.02.2019', NULL,225.27,2)

    How to realize it?

    Tuesday, April 2, 2019 11:40 AM

Answers

  • If I understood you correctly, below query should work. I have modified the query in cross apply to provide the output. Pls check!

    SELECT T.gr1,
    	T.gr2,
    	T.supl,
    	T.rt,
    	T.suplrt,
    	T.sales,
    	T.rt1,
    	T.salesrt1,
    	T1.date_begin,
    	T1.date_end,
    	T1.price
    FROM gr T
    CROSS APPLY (
    	SELECT TOP 1 date_begin,
    		date_end,
    		price
    	FROM price
    	WHERE gr1 = T.gr1
    		AND gr2 = T.gr2
    		AND date_end IS NULL
    	ORDER BY price_prior
    	) T1

    • Marked as answer by Jury Wox Tuesday, April 2, 2019 2:23 PM
    Tuesday, April 2, 2019 1:07 PM

All replies