select last row of column depending on another column

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?

Answer 1

Hi you write table and data. And return data which you need.

Answer 2

Hi you write table and data. And return data which you need.

I provide needed output :)

Answer 3

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

Answer 4

Look at this question. https://social.msdn.microsoft.com/Forums/sqlserver/en-US/dd0b4185-f728-42ee-9959-1e47f5ae7406/display-top-accumulated-x-quantities-in-each-group?forum=transactsql#29175297-794f-459c-9959-4887cb78cf49

I need data and query as this example