Tracking Inventory with multiple items

JimiJ13
JimiJ13 used Ask the Experts™
on
Tracking Inventory with multiple items


MS SQL, Stock movement, tracking
This question is related to but closed already: http://www.experts-exchange.com/Database/MS-SQL-Server/Q_28423550.html

However, this time I need to get the following:

1) Inventory movement per stock_code at given date parameter value or range.
2) Inventory Balance per stock_code at any given month as parameter value.

Sample data are as follows:

CREATE TABLE #Transaction
(
      [control]      VARCHAR(10),
      stock_code      VARCHAR(10),
      action_date      DATE,
      qty                  INT,
      price            MONEY
)

INSERT INTO #Transaction
(
      control,
      stock_code,
      action_date,
      qty,
      price
) VALUES  

    ('IA 001', '1001', '3/31/2014', -1, 10),
      ('IA 001', '1004', '3/31/2014', -3, 10),
     ('IA 001', '1005', '3/31/2014', 1, 10),
      ('IA 002', '1001', '4/30/2014', -4, 10),
      ('IA 003', '1004', '5/30/2014', 5, 10),
      ('IA 004', '1001', '6/30/2014', -1, 10),
      ('RR 001', '1001', '3/1/2014', 5, 10),
     ('RR 001', '1006', '3/1/2014', 5, 103),
     ('RR 001', '1008', '3/1/2014', 5, 210),
      ('RR 002', '1001', '4/3/2014', 5, 9),
    ('RR 002', '1006', '4/3/2014', 11, 99),
    ('RR 002', '1008', '4/3/2014', 50, 199),
     ('RR 002', '1005', '4/3/2014', 55, 99),
      ('RR 003', '1002', '5/1/2014', 5, 10),
      ('RR 004', '1001', '5/3/2014', 5, 9),
      ('RR 004', '1006', '5/3/2014',25 , 102),
      ('RR 004', '1008', '5/3/2014', 50, 219),
      ('RR 004', '1005', '5/3/2014', 58, 89),
      ('WIR 001', '1001', '3/5/2014', -2, 10),
      ('WIR 002', '1001', '4/5/2014', -2, 10),
     ('WIR 002', '1006', '4/5/2014', -25, 102),
     ('WIR 002', '1008', '4/5/2014', -20, 219),

      ('WIR 003', '1002', '5/5/2014', -4, 10),

      ('IA 005', '1001', '6/30/2014', -5, 11),
      ('RR 005', '1006', '6/30/2014', 20, 100),
      ('WIR 005', '1008', '6/30/2014', -20, 201),
      ('WIR 004', '1001', '6/5/2014', -5, 10)

Any help would be highly appreciated.

Thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Developer
Commented:
That should be easy.

-- 1)
DECLARE @FromDate DATE = '2014-03-01';
DECLARE @ToDate DATE = '2014-04-30';

SELECT  stock_code ,
        DATEDIFF(DAY, MIN(action_date), MAX(action_date)) AS DaysCovered ,
        SUM(qty) AS qty_moved
FROM    #Transaction
WHERE   action_date BETWEEN @FromDate AND @ToDate
GROUP BY stock_code;

-- 2)
DECLARE @Month AS DATE = '2014-03-01';

SELECT  stock_code ,
        DATEDIFF(DAY, MIN(action_date), MAX(action_date)) AS DaysCovered ,
        SUM(qty) AS qty_balance
FROM    #Transaction
WHERE   action_date <= EOMONTH(@Month)
GROUP BY stock_code;

Open in new window


Here's a good book about T-SQL: Microsoft SQL Server 2012 T-SQL Fundamentals.

It's available also for 2008.

btw, see also EOMONTH() Equivalent in SQL Server 2008 R2 and below.
JimiJ13I T Consultant

Author

Commented:
-With modification as follows works great!

-- 2)

Select @Month = Cast(DATEADD(month, DATEDIFF(month,0,getdate()) +1,0)-1 As Date);

SELECT  stock_code ,
        DATEDIFF(DAY, MIN(action_date), MAX(action_date)) AS DaysCovered ,
        SUM(qty) AS qty_balance
FROM    #Transaction
WHERE   action_date <= @Month
GROUP BY stock_code;

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial