Link to home
Start Free TrialLog in
Avatar of JimiJ13
JimiJ13Flag for Philippines

asked on

Transform transactions

Dear Expert,

I have this transaction table

control         stock_code          action_date           qty      price
IA 001            1001           2014-03-31           -1      10
RR 001                  1001           2014-03-01            5      10
RR 002                  1001           2014-04-03            5      9
WIR 001          1001           2014-03-05           -2      10


How can Transform its presentation into this?:

control    stock_code action_Date qty         value       run_qty     run_val     price
---------- ---------- ----------- ----------- ----------- ----------- ----------- -----------
RR 001     1001       2014-03-01    5           50          5           50          10
WIR 001    1001       2014-03-05    -2          -20         3           30          10
IA 001     1001       2014-03-31    -1          -10         2           20          10
RR 002     1001       2014-04-03    5           45          7           65          9

Any offered solution would be highly appreciated.
ASKER CERTIFIED SOLUTION
Avatar of Brian Crowe
Brian Crowe
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Or when using SQL Server 2012+ you can use the new OVER() clausee:

E.g.
SELECT	[control], 
	stock_code, 
	action_date, 
	qty,
	SUM(qty) OVER ( ORDER BY action_date ROWS UNBOUNDED PRECEDING )  AS run_qty,
	SUM(qty * price) OVER ( ORDER BY action_date ROWS UNBOUNDED PRECEDING ) AS run_val,
	price
FROM	#Transaction AS T
ORDER BY action_date;

Open in new window


This will result in a more optimzed execution plan:
User generated image
Avatar of JimiJ13

ASKER

Excellent!
Isn't this a duplicate of this question? https://www.experts-exchange.com/questions/28422940/Inventory-Stock-movement.html

(Since you used the output I gave you in that question here :) )
Avatar of JimiJ13

ASKER

Hi Steve,

I need an Inventory Tracking solution, and I am very grateful that your partial solution leads me to completion by looking for somebody to provide the remaining part (the transform in MS SQL). I can't wait, but I need quick solution to meet my delivery schedule. I don't want to get stuck so I need to take an immediately action, and I got my solution on time.  
 

Thanks.
If you check the original response, a full solution was given for SQL Server at the beginning of the post (it just had extra bits at the bottom).

The output I showed you was output from an actual query written on SQL Server.

Regardless, glad you got your solution.
Avatar of JimiJ13

ASKER

Hi Steve,

I'm very sorry for my wrong presumption. Your last post mixed me up and made me to believe that the it was supposed to be my solution not the latter, and I did not even test because it gave me an error when I pasted on the view window. Please see image.
InventoryTracking.jpg
where's the error?
Avatar of JimiJ13

ASKER

Hi Brian,

Do you have any idea why this is having a problem?

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 002', '1001', '4/31/2014', -4, 10),
      ('IA 003', '1001', '5/31/2014', 5, 10),
      ('IA 004', '1001', '6/31/2014', -1, 10),
      ('RR 001', '1001', '3/1/2014', 5, 10),
      ('RR 002', '1001', '4/3/2014', 5, 9),
      ('RR 003', '1001', '5/1/2014', 5, 10),
      ('RR 004', '1001', '5/3/2014', 5, 9),
      ('WIR 001', '1001', '3/5/2014', -2, 10),
      ('WIR 002', '1001', '4/5/2014', -2, 10),
      ('WIR 003', '1001', '5/5/2014', -4, 10),
      ('WIR 004', '1001', '6/5/2014', -5, 10)

SELECT [control], stock_code, action_date, qty, qty*price AS value,
      (SELECT SUM(qty) FROM #Transaction WHERE action_date <= T.action_date) AS run_qty,
      (SELECT SUM(qty * price) FROM #Transaction WHERE action_date <= T.action_date) AS run_val,
      price
FROM #Transaction AS T
ORDER BY action_date

This is the error:

Msg 241, Level 16, State 1, Line 10
Conversion failed when converting date and/or time from character string.

Thanks.
Avatar of JimiJ13

ASKER

Brian,

Never mind, there are no 4/31 and 6/31.

Anyway, I think my next question is how to get my Inventory level per item at the end of every month - without showing details of transactions.

Your help would be gladly appreciated.


Thanks.
('IA 002', '1001', '4/31/2014', -4, 10),
  ('IA 004', '1001', '6/31/2014', -1, 10),

31st of April and June?
(it can't convert those to dates)

 ('IA 002', '1001', '4/30/2014', -4, 10),
  ('IA 004', '1001', '6/30/2014', -1, 10),
apologies you had already caught the dates

If you are using sql server 2012 there is a more efficient way. What version of SQL Server are you using?

--- before sql 2012
SELECT
     [control]
    , stock_code
    , action_date
    , qty
    , qty * price AS value
    , run_qty
    , run_val
    , price
FROM (
      SELECT
            [control]
          , stock_code
          , action_date
          , qty
          , qty * price AS value
          , (
                  SELECT
                        SUM(qty)
                  FROM Transactions
                  WHERE action_date <= T.action_date
            )           
            AS run_qty
          , (
                  SELECT
                        SUM(qty * price)
                  FROM Transactions
                  WHERE action_date <= T.action_date
            )           
            AS run_val
          , price
          , datepart(day,action_date)
            AS act_day 
          , max(datepart(day,action_date)) over (partition by year(action_date), month(action_date))
            AS act_max_day
      FROM Transactions AS T
      ) AS T2
WHERE act_day = act_max_day
ORDER BY
      action_date
;

-- sql 2012 +
SELECT
     [control]
    , stock_code
    , action_date
    , qty
    , qty * price AS value
    , run_qty
    , run_val
    , price
FROM (
      SELECT
            [control]
          , stock_code
          , action_date
          , qty
          , qty * price                                  AS value
          , SUM(qty) OVER (ORDER BY action_date)         AS run_qty
          , SUM(qty * price) OVER (ORDER BY action_date) AS run_val
          , price
          , datepart(day,action_date)                    AS act_day 
          , max(datepart(day,action_date)) over (partition by year(action_date), month(action_date)) AS act_max_day
      FROM Transactions
      ) AS T
WHERE act_day = act_max_day
ORDER BY
      action_date
;

Open in new window

both of those produce this result:
| CONTROL | STOCK_CODE | ACTION_DATE | QTY | VALUE | RUN_QTY | RUN_VAL | PRICE |
|---------|------------|-------------|-----|-------|---------|---------|-------|
|  IA 001 |       1001 |  2014-03-31 |  -1 |   -10 |       2 |      20 |    10 |
|  IA 002 |       1001 |  2014-04-30 |  -4 |   -40 |       1 |       5 |    10 |
|  IA 003 |       1001 |  2014-05-31 |   5 |    50 |      12 |     110 |    10 |
|  IA 004 |       1001 |  2014-06-30 |  -1 |   -10 |       6 |      50 |    10 |
		

Open in new window

available at: http://sqlfiddle.com/#!6/92273/16
Avatar of JimiJ13

ASKER

Hi PortletPaul,

I tried your code but it doesn't gave the correct results. Please see below with 2 item codes:
CREATE TABLE #Transaction1
(
      [control]      VARCHAR(10),
      stock_code      VARCHAR(10),
      action_date      DATE,
      qty                  INT,
      price            MONEY
)

INSERT INTO #Transaction1
(
      control,
      stock_code,
      action_date,
      qty,
      price
)
VALUES
      ('IA 001', '1001', '3/31/2014', -1, 10),
      ('IA 002', '1001', '4/30/2014', -4, 10),
      ('IA 003', '1002', '5/30/2014', 5, 10),
      ('IA 004', '1001', '6/30/2014', -1, 10),
      ('RR 001', '1001', '3/1/2014', 5, 10),
      ('RR 002', '1001', '4/3/2014', 5, 9),
      ('RR 003', '1002', '5/1/2014', 5, 10),
      ('RR 004', '1001', '5/3/2014', 5, 9),
      ('WIR 001', '1001', '3/5/2014', -2, 10),
      ('WIR 002', '1001', '4/5/2014', -2, 10),
      ('WIR 003', '1002', '5/5/2014', -4, 10),
      ('IA 005', '1001', '6/30/2014', 0, 0),
      ('RR 005', '1001', '6/30/2014', 0, 0),
      ('WIR 005', '1001', '6/30/2014', 0, 0),
      ('WIR 004', '1001', '6/5/2014', -5, 10)

SELECT
     [control]
    , stock_code
    , action_date
    , qty
    , qty * price AS value
    , run_qty
    , run_val
    , price
FROM (
      SELECT
            [control]
          , stock_code
          , action_date
          , qty
          , qty * price AS value
          , (
                  SELECT
                        SUM(qty)
                  FROM #Transaction1
                  WHERE action_date <= T.action_date
            )           
            AS run_qty
          , (
                  SELECT
                        SUM(qty * price)
                  FROM #Transaction1
                  WHERE action_date <= T.action_date
            )           
            AS run_val
          , price
          , datepart(day,action_date)
            AS act_day 
          , max(datepart(day,action_date)) over (partition by year(action_date), month(action_date))
            AS act_max_day
      FROM #Transaction1 AS T
      ) AS T2
      
WHERE act_day = act_max_day
ORDER BY
      action_date
;

Drop Table #Transaction1;

Open in new window

Thanks.
as you are the person who decides what is correct I believe you, but what is the correct result?
please supply the expected result

also, in the real data does action_date also hold time information?  

i.e. if we have to decide which record on the last day of a month, how do we do that?
here are different results because I have changed the "partition by" conditions. I don't know if any are right as I don't have that guidance from you. Refer to lines 35 and 103 for the differences
   -- stock_code, control
    SELECT
         [control]
        , stock_code
        , action_date
        , qty
        , qty * price AS value
        , run_qty
        , run_val
        , price
    FROM (
          SELECT
                [control]
              , stock_code
              , action_date
              , qty
              , qty * price AS value
              , (
                      SELECT
                            SUM(qty)
                      FROM Transactions
                      WHERE action_date <= T.action_date
                )           
                AS run_qty
              , (
                      SELECT
                            SUM(qty * price)
                      FROM Transactions
                      WHERE action_date <= T.action_date
                )           
                AS run_val
              , price
              , datepart(day,action_date)
                AS act_day 
              , max(datepart(day,action_date)) over (partition by stock_code, control, year(action_date), month(action_date))
                AS act_max_day
          FROM Transactions AS T
          ) AS T2
    WHERE act_day = act_max_day
    ORDER BY
          action_date
        , stock_code
        , control
    

**[Results][2]**:
    
    | CONTROL | STOCK_CODE | ACTION_DATE | QTY | VALUE | RUN_QTY | RUN_VAL | PRICE |
    |---------|------------|-------------|-----|-------|---------|---------|-------|
    |  RR 001 |       1001 |  2014-03-01 |   5 |    50 |       5 |      50 |    10 |
    | WIR 001 |       1001 |  2014-03-05 |  -2 |   -20 |       3 |      30 |    10 |
    |  IA 001 |       1001 |  2014-03-31 |  -1 |   -10 |       2 |      20 |    10 |
    |  RR 002 |       1001 |  2014-04-03 |   5 |    45 |       7 |      65 |     9 |
    | WIR 002 |       1001 |  2014-04-05 |  -2 |   -20 |       5 |      45 |    10 |
    |  IA 002 |       1001 |  2014-04-30 |  -4 |   -40 |       1 |       5 |    10 |
    |  RR 003 |       1002 |  2014-05-01 |   5 |    50 |       6 |      55 |    10 |
    |  RR 004 |       1001 |  2014-05-03 |   5 |    45 |      11 |     100 |     9 |
    | WIR 003 |       1002 |  2014-05-05 |  -4 |   -40 |       7 |      60 |    10 |
    |  IA 003 |       1002 |  2014-05-30 |   5 |    50 |      12 |     110 |    10 |
    | WIR 004 |       1001 |  2014-06-05 |  -5 |   -50 |       7 |      60 |    10 |
    |  IA 004 |       1001 |  2014-06-30 |  -1 |   -10 |       6 |      50 |    10 |
    |  IA 005 |       1001 |  2014-06-30 |   0 |     0 |       6 |      50 |     0 |
    |  RR 005 |       1001 |  2014-06-30 |   0 |     0 |       6 |      50 |     0 |
    | WIR 005 |       1001 |  2014-06-30 |   0 |     0 |       6 |      50 |     0 |


**Query 2**:

    -- stock code
    SELECT
         [control]
        , stock_code
        , action_date
        , qty
        , qty * price AS value
        , run_qty
        , run_val
        , price
    FROM (
          SELECT
                [control]
              , stock_code
              , action_date
              , qty
              , qty * price AS value
              , (
                      SELECT
                            SUM(qty)
                      FROM Transactions
                      WHERE action_date <= T.action_date
                )           
                AS run_qty
              , (
                      SELECT
                            SUM(qty * price)
                      FROM Transactions
                      WHERE action_date <= T.action_date
                )           
                AS run_val
              , price
              , datepart(day,action_date)
                AS act_day 
              , max(datepart(day,action_date)) over (partition by stock_code, year(action_date), month(action_date))
                AS act_max_day
          FROM Transactions AS T
          ) AS T2
    WHERE act_day = act_max_day
    ORDER BY
          action_date
        , stock_code
        , control
    

**[Results][3]**:
    
    | CONTROL | STOCK_CODE | ACTION_DATE | QTY | VALUE | RUN_QTY | RUN_VAL | PRICE |
    |---------|------------|-------------|-----|-------|---------|---------|-------|
    |  IA 001 |       1001 |  2014-03-31 |  -1 |   -10 |       2 |      20 |    10 |
    |  IA 002 |       1001 |  2014-04-30 |  -4 |   -40 |       1 |       5 |    10 |
    |  RR 004 |       1001 |  2014-05-03 |   5 |    45 |      11 |     100 |     9 |
    |  IA 003 |       1002 |  2014-05-30 |   5 |    50 |      12 |     110 |    10 |
    |  IA 004 |       1001 |  2014-06-30 |  -1 |   -10 |       6 |      50 |    10 |
    |  IA 005 |       1001 |  2014-06-30 |   0 |     0 |       6 |      50 |     0 |
    |  RR 005 |       1001 |  2014-06-30 |   0 |     0 |       6 |      50 |     0 |
    | WIR 005 |       1001 |  2014-06-30 |   0 |     0 |       6 |      50 |     0 |



  [1]: http://sqlfiddle.com/#!6/9046e/3

Open in new window

goodness, this question has been answered!

sorry, but this should be a new question

apologies to all concerned.
I'm not sure how "item level" connects to control and stock code in your example, but you need to aggregate on a monthly grain:

SELECT	/* [control], */
	stock_code, 
	EOMONTH(action_date), 
	SUM(qty) AS monthly_qty,
	MIN(price) AS price
FROM	#Transaction AS T
GROUP BY /* [control], */
	stock_code, 
	EOMONTH(action_date);

Open in new window

Avatar of JimiJ13

ASKER

Hi PortletPaul,

I have opened new question related to this at https://www.experts-exchange.com/questions/28438497/Tracking-Inventory-with-multiple-items.html

For the main time, there's an error in your proposed solution. Please refer to the image attached.User generated image

Thanks.