Get Data/StockQty for all dates

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

General SQL Server Forums

New to SQL Server Programming

Get Data/StockQty for all dates

Author

Topic

s_rajmohan
Starting Member

2 Posts

Posted - 2011-10-28 : 17:20:27

Hi,

I have a table with data for few dates. It is an inventory history table. If there is no data for a particular date, then the stock Qty is Stock Qty from previous reported date. I would like to get Stock Qty for all dates by Joining to date table (contains all Dates from Year 2001 to 2020)

For e.g. TABLE-A has following data

Product| Date | Stock Qty
P1,10/01/2011, 200
P1,10/05/2011, 100
P1,10/20/2011,500
P1,10/23/2011,300

TABLE-B Date table
(contains all Dates from Year 2001 to 2020)

I would like to get result as:

Product,Date,Stock Qty

P1,10/01/2011,200
P1,10/02/2011,200
P1,10/03/2011,200
P1,10/04/2011,200
P1,10/05/2011,100
P1,10/06/2011,100
P1,10/07/2011,100
.................
.................
P1,10/20/2011,500
P1,10/21/2011,500
P1,10/22/2011,500
P1,10/22/2011,300

How do I achieve this?

Thanks in advance..

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-10-28 : 18:06:31

Left join dates table with the inventory history table and return the result set to the client. On the client populate stock quantity with the previous quantity if returned one is null. That way you get the best performance.

If you insist doing everything in sql, insert results from left join in table variable and use cursor to update values. Or use other technique used for running totals like problems.

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2011-10-28 : 18:20:42

Try This:


WITH CTE AS
(
   SELECT A.Product, A.Date, A.[Stock Qty],
          ROW_NUMBER() OVER(PARTITION BY A.Product ORDER BY A.Date) AS row_num
     FROM [TABLE-A] AS A
)
SELECT A1.Product, ISNULL(B.Date, A1.Date) AS Date, A1.[Stock Qty]
  FROM CTE AS A1
       LEFT OUTER JOIN
       CTE AS A2
       ON A2.Product = A1.Product
          AND A2.row_num = A1.row_num + 1
       OUTER APPLY
       (SELECT B.Date
          FROM [TABLE-B] AS B
         WHERE B.Date >= A1.Date
           AND B.Date < A2.Date) AS B

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-29 : 00:56:58

no need of CTE in fact. you can just use like


SELECT m.Product,m.Date,COALESCE(n.[Stock Qty],t.[Stock Qty]) AS [Stock Qty]
FROM
(
SELECT a.Product,b.Date 
FROM (SELECT DISTINCT Product FROM tableA) a
CROSS JOIN tableB b
)m
LEFT JOIN tableA n
ON n.Date =m.Date 
AND n.Product = m.Product
OUTER APPLY (SELECT TOP 1 [Stock Qty]
             FROM tableA
             WHERE Product = m.Product
             AND Date < m.Date
             ORDER BY Date DESC)t

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

s_rajmohan
Starting Member

2 Posts

Posted - 2011-10-31 : 14:38:44

Thank you, malpashaa, Visakh16 and mmarovic. I will try these out and let you know.

Thanks again...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-01 : 04:21:53

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-11-01 : 08:16:53

quote:
Originally posted by s_rajmohan

Thank you, malpashaa, Visakh16 and mmarovic. I will try these out and let you know.

Thanks again...

You are welcome. Feedback is always appreciated.

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources