How to get result (Selling Price) which is nearest to the closing date?

Question

Dear Experts 

Thanking you in advance for taking interest in this query. I am having a table wherein I am having a multiple pricing of an item on the basis of starting date and ending date. I need to have only the current price and last applicable price on the basis of start date and end date. 

Following is the sql query to generate the table

CREATE TABLE mytable(
   Item  VARCHAR(1) NOT NULL PRIMARY KEY
  ,Sdate VARCHAR(9) NOT NULL
  ,EDATE VARCHAR(9)
  ,SP    INTEGER  NOT NULL
);
INSERT INTO mytable(Item,Sdate,EDATE,SP) VALUES ('A','01-Apr-20','31-Mar-21',75);
INSERT INTO mytable(Item,Sdate,EDATE,SP) VALUES ('A','01-Apr-21','31-Mar-22',100);
INSERT INTO mytable(Item,Sdate,EDATE,SP) VALUES ('A','01-Apr-22','15-Mar-23',125);
INSERT INTO mytable(Item,Sdate,EDATE,SP) VALUES ('A','01-Apr-23',NULL,150);

The desired output is as follows


+------+---------+------------+
| Item | SP_Last | SP_Current |
+------+---------+------------+
| A    |     125 |        150 |
+------+---------+------------+

I have designed a query something like below

Select
*
From
mytable as SP_C
Left Join mytable as SP_O ON SP_C.Item = SP_O.Item
		AND SP_O.EDATE = SP_O.EDATE < SP_C.Sdate 
		AND SP_C.EDATE is NULL
Where	
		SP_C.EDATE is null

But it is giving an error. Can any body help me to achieve the above objective?

Answer 1

CREATE TABLE mytable(
   Item  VARCHAR(1) NOT NULL  
  ,Sdate VARCHAR(9) NOT NULL
  ,EDATE VARCHAR(9)
  ,SP    INTEGER  NOT NULL
);
INSERT INTO mytable(Item,Sdate,EDATE,SP) VALUES ('A','01-Apr-20','31-Mar-21',75);
INSERT INTO mytable(Item,Sdate,EDATE,SP) VALUES ('A','01-Apr-21','31-Mar-22',100);
INSERT INTO mytable(Item,Sdate,EDATE,SP) VALUES ('A','01-Apr-22','15-Mar-23',125);
INSERT INTO mytable(Item,Sdate,EDATE,SP) VALUES ('A','01-Apr-23',NULL,150);
;with mycte as (
select *, 
row_number() over(partition by Item  order by Try_convert(date,Isnull(EDATE,'31-Dec-49') ,103) desc ) rn 
,Try_convert(date,Isnull(EDATE,'31-Dec-2049') ,103) dt
from  mytable
)
Select Item  
, Max(Case when rn=2 then SP else null end)  SP_Last 
, Max(Case when rn=1 then SP else null end)  SP_Current

 from mycte
 group by Item

drop   TABLE mytable

Answer 2

Check if this works:

select Item, 
	SP_Current = (select SP from mytable where Item = t.Item and getdate() between Sdate and isnull(Edate, getdate())),
	SP_Last = (select top(1) SP from mytable where Item = t.Item order by Sdate desc)
from mytable t
group by Item

Answer 3

Hi @Manish Papreja

But it is giving an error.

It seems that the JOIN ON condition causes error. While for this issue, you could use the window function LAG() to access data from a previous row in the same result set without the use of a self-join.

Please check this query:

;WITH CTE AS
(
 SELECT *,LAG(SP)OVER(PARTITION BY Item ORDER BY Sdate ) AS SP_Last
         ,ROW_NUMBER()OVER(PARTITION BY Item ORDER BY Sdate DESC)AS RNum
 From mytable 
)
SELECT Item,SP AS SP_Current,SP_Last
FROM CTE
WHERE RNum = 1

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.