Transform transactions

JimiJ13
JimiJ13 used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Administrator
Top Expert 2005
Commented:
CREATE TABLE #Transaction
(
      [control]      VARCHAR(10),
      stock_code      VARCHAR(10),
      action_date      DATETIME,
      qty                  INT,
      price            MONEY
)

INSERT INTO #Transaction
(
      control,
      stock_code,
      action_date,
      qty,
      price
)
VALUES
      ('IA 001', '1001', '3/31/2014', -1, 10),
      ('RR 001', '1001', '3/1/2014', 5, 10),
      ('RR 002', '1001', '4/3/2014', 5, 9),
      ('WIR 001', '1001', '3/5/2014', -2, 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
ste5anSenior Developer

Commented:
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:
Actual execution plan
JimiJ13I T Consultant

Author

Commented:
Excellent!
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Steve WalesSenior Database Administrator

Commented:
Isn't this a duplicate of this question? http://www.experts-exchange.com/Database/MS-SQL-Server/Q_28422940.html

(Since you used the output I gave you in that question here :) )
JimiJ13I T Consultant

Author

Commented:
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.
Steve WalesSenior Database Administrator

Commented:
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.
JimiJ13I T Consultant

Author

Commented:
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
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
where's the error?
JimiJ13I T Consultant

Author

Commented:
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.
JimiJ13I T Consultant

Author

Commented:
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.
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
('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),
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
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
JimiJ13I T Consultant

Author

Commented:
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.
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
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?
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
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

PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
goodness, this question has been answered!

sorry, but this should be a new question

apologies to all concerned.
ste5anSenior Developer

Commented:
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

JimiJ13I T Consultant

Author

Commented:
Hi PortletPaul,

I have opened new question related to this at http://www.experts-exchange.com/Database/MS-SQL-Server/SQL_Server_2008/Q_28438497.html

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

Thanks.

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