Calculate Price Based on Order Date 1

[briangriffin]

Order history table looks like this:

PurchaseOrderID	            LineID	DateTime	Price
24111	                       1	2014-12-22 	23.89
24111	                       1	2014-12-23 	19.17
24111	                       1	2015-04-21 	25.42

This code:

select 
tb1.PurchaseOrderID,
tb1.LineID,
tb1.DateTime as startdate,
dateadd(d,-1,tb2.DateTime) as enddate,
tb1.NewPrice as Price

from PO_Prices tb1 join PO_Prices tb2 
	on tb2.DateTime > tb1.DateTime
	and tb1.PurchaseOrderID = tb2. PurchaseOrderID
	and tb1.LineID = tb2.LineID
	
left join PO_Prices tb3 
	on tb1.DateTime < tb3.DateTime 
		and tb3.DateTime < tb2.DateTime
		and tb1.PurchaseOrderID = tb3. PurchaseOrderID
	    and tb1.LineID = tb3.LineID
		
where tb3.DateTime is null

Gives me this:

PurchaseOrderID	              LineID	startdate	enddate	         Price
24111	                         1	2014-12-22 	2014-12-22 	23.89
24111	                         1	2014-12-23 	2015-04-20 	19.17

So it's close, but missing the last line which needs to be

24111               1   2014-04-21      (current date)  25.42

What am I missing, or is there a better way? TIA.

 

[DaveInIowa]

SELECT *, DATEADD(DAY, -1, LEAD([DateTime], 1, GETDATE())
       OVER (PARTITION BY PurchaseOrderID, LineID ORDER BY [DateTime] ASC)) AS EndDate
  FROM   PO_Prices

SELECT pop.PurchaseOrderID, pop.LineID, pop.DateTime StartDate,
       COALESCE(popEndDate.EndDate, GETDATE()) EndDate
  FROM PO_Prices pop
 OUTER
 APPLY (SELECT TOP 1 DATEADD(DAY, -1, DateTime) EndDate
		  FROM PO_Prices
		 WHERE PurchaseOrderID = pop.PurchaseOrderID
		   AND LineID = pop.LineID
		   AND DateTime > pop.DateTime
		 ORDER BY DateTime) popEndDate

 

[briangriffin]

Perfect! Now stored in code scrapbook, thanks!