Avatar of JimiJ13
JimiJ13
Flag 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.
Microsoft SQL Server

Avatar of undefined
Last Comment
JimiJ13

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Brian Crowe

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ste5an

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
JimiJ13

ASKER
Excellent!
Steve Wales

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

(Since you used the output I gave you in that question here :) )
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
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.
Steve Wales

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.
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
PortletPaul

where's the error?
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.
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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
PortletPaul

('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),
PortletPaul

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
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
PortletPaul

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?
PortletPaul

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

PortletPaul

goodness, this question has been answered!

sorry, but this should be a new question

apologies to all concerned.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ste5an

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

JimiJ13

ASKER
Hi PortletPaul,

I have opened new question related to this at https://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.