Last_Value equivalent - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Last_Value equivalent

Author

Topic

koushikchandra
Starting Member

24 Posts

Posted - 2011-04-07 : 11:12:47

Hi,

I have two tables where the data looks like below :

Table 1:
SalesOrder SalesProduct SalesDate
S1 P1 20110328
S2 P1 20110329
S3 P1 20110330

Table 2 :
IP_Product IP_Date IP_Qty
P1 20110328 10
P1 20110330 13

Now in Table 2 data is not there for 20110329 i.e. 29-Mar-2011. But I want to see the Quantity for 29-Mar as 10. means the quantity which was valid just before 29-Mar.

SalesOrder SalesProduct SalesDate IP_Qty
S1 P1 20110328 10
S2 P1 20110329 10
S3 P1 20110330 13

Can some one please help me with the sql here.

Regards,
Koushik

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-07 : 11:33:27

DECLARE @Table1 Table (SalesOrder char(2), SalesProduct char(2), SalesDate DateTime)
INSERT INTO @Table1
SELECT 'S1','P1','20110328' UNION ALL
SELECT 'S2','P1','20110329' UNION ALL
SELECT 'S3','P1','20110330'

DECLARE @Table2 Table(IP_Product char(2), IP_Date datetime, IP_Qty int)
INSERT INTO @Table2
SELECT 'P1','20110328', 10 UNION ALL
SELECT 'P1','20110330', 13

SELECT t1.SalesOrder,t1.SalesProduct,t1.SalesDate,qty.IP_QTY
FROM @table1 t1

CROSS APPLY(Select top 1IP_Qty
from @table2 t2
where t1.SalesProduct = t2.IP_Product
and t1.SalesDate >= t2.IP_Date
order by t2.IP_date desc
) qty

Jim

Everyday I learn something that somebody else already knew

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2011-04-08 : 14:44:17

Also you can use LEFT JOIN and Correlated Subquery like this:

DECLARE @Table1 Table (SalesOrder char(2), SalesProduct char(2), SalesDate DateTime)
INSERT INTO @Table1
SELECT 'S1','P1','20110328' UNION ALL
SELECT 'S2','P1','20110329' UNION ALL
SELECT 'S3','P1','20110330'

DECLARE @Table2 Table(IP_Product char(2), IP_Date datetime, IP_Qty int)
INSERT INTO @Table2
SELECT 'P1','20110328', 10 UNION ALL
SELECT 'P1','20110330', 13 

SELECT T1.*, COALESCE(T2.IP_Qty, (SELECT TOP 1 IP_Qty
                                 FROM @Table2
                                WHERE IP_Date < SalesDate
                                ORDER BY Ip_date DESC)) AS IP_Qty  
  FROM @Table1 AS T1
  LEFT JOIN @Table2 AS T2
         ON T1.SalesProduct = T2.IP_Product
        AND  T1.SalesDate = T2.IP_Date;
        
/*
SalesOrder SalesProduct SalesDate               IP_Qty
---------- ------------ ----------------------- -----------
S1         P1           2011-03-28 00:00:00.000 10
S2         P1           2011-03-29 00:00:00.000 10
S3         P1           2011-03-30 00:00:00.000 13
*/

______________________

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources