Subtraction in order of sale in TSQL

Question

Subtraction in order of sale in TSQL

I have 2 tables:

Table 1:

ID_M, Date, Reservation
001,   2014,   5 
001,   2015,    10
001,   2016,    18
002,   2015,    6
002,   2016,    22

Table 2:

ID_M, ID_type, Priority,Product_Total
001,   1111,       2,        10
001,   2222,       3,        15
001,   3333,       1,         8
002,   1111,       2,        12
002,   2222,       1,        16

Is it possible to calculate the redistribution of ID_TYPE for a reservation? The priority in Table 2 indicates the reservation order, priority 1 - reserve first, etc. Is it possible to create a table / View / CTE in T-SQL like below?

ID_M, ID_Type, DATE, Reservation
001, 3333,     2014, 5,
001, 3333,     2015, 3,
001, 1111,     2015, 7,
001, 1111,     2016, 3,
001, 2222,     2016, 15,
002, 2222,     2015, 6,
002, 2222,     2016, 10,
002, 1111,     2016, 12,

Here, reservations are calculated based on the total product in table 2 and the quantity of product reserved in table 1. For example, in 2014 we have reservation 5 for ID_M = 001 in table 1, we look for ID_M = 001 in table 2, we first reserve the product in priority 1, so it's ID_type 3333.therefore we get the first row in the desired table: 001, 3333, 2014, 5.

in 2015 we have 10 reservations in table 1, so we first use the product left over from 2014 for ID_Type 3333, and then we use ID_Type 1111, the left product for ID_type 3333 is 3 (8-5), so the left reservations are 7 (10-3) is for ID_Type 1111.so we got 2nd and 3rd rows from the desired table 3:
001, 3333, 2015, 3, 001, 1111, 2015, 7,

+3

sql sql-server tsql sql-server-2012

Shania 02 Aug 17 at 11:51

source to share

2 answers

Maurício Pontalti Neri · Answer 1 · 2017-08-03T03:30:59+0000

To solve this code, I created:

unique identifier (rownumber) for each row in both tables
Interact with two tables using double mileage, one to get the value from t1 and the other to get the value from t2
At the end, I use the SUM function to add from t1 and subtract from t2

CREATE PROC # sp_test - remove # in your environment

AS

- These lines do not exist in your environment - Start

DECLARE @ t1 AS TABLE (id_m CHAR (3), date INT, reservation INT)

DECLARE @ t2 AS TABLE (id_m CHAR (3), id_type INT, priority INT, product_total INT)

INSERT @ t1 (id_m, Date, Reservation) SELECT '001', 2014, 5

INSERT @ t1 (id_m, Date, Reservation) SELECT '001', 2015, 10

INSERT @ t1 (id_m, Date, Reservation) SELECT '001', 2016, 18

INSERT @ t1 (id_m, Date, Reservation) SELECT '002', 2015, 6

INSERT @ t1 (id_m, Date, Reservation) SELECT '002', 2016, 22

INSERT @ t2 (id_m, id_type priority, Product_total) SELECT '001', 1111, 2, 10

INSERT @ t2 (id_m, id_type priority, Product_total) SELECT '001', 2222, 3, 15

INSERT @ t2 (id_m, id_type priority, Product_total) SELECT '001', 3333, 1, 8

INSERT @ t2 (id_m, id_type priority, Product_total) SELECT '002', 1111, 2, 12

INSERT @ t2 (id_m, id_type priority, Product_total) SELECT '002', 2222, 1, 16

- These lines do not exist in your environment - Start

; WITH cte_t1

AS (SELECT Row_number () OVER (ORDER BY id_m, date) rownumber

*

FROM @ t1),

cte_t2

AS (SELECT Row_number ()

OVER (

ORDER BY id_m, priority) rownumber *

FROM @ t2), cte_trn1

AS (SELECT DISTINCT 0 level, a.rownumber, b.id_m, b.id_type, b.priority, date, A.reservation

FROM cte_t1 a INNER JOIN cte_t2 b ON a.rownumber = b.rownumber), cte_trn2

AS (SELECT DISTINCT 1 level, a.rownumber, b.id_m, b.id_type, b.priority, date, B.product_total

FROM cte_t1 a INNER JOIN cte_t2 b ON a.rownumber = b.rownumber),

cte_union AS (

SELECT * FROM cte_trn1 a UNION ALL SELECT * FROM cte_trn2 b), cte_final

AS (SELECT id_m, id_type priority, Date, Sum (CASE WHEN level = 0 THEN booking Reservation ELSE * - 1 END) ABOVE (section BY id_m ORDER BY rownumber, UNBOUNDED PRECEDING level rows) * (CASE WHEN level = 0 THEN 1 ELSE -1 END) as booking FROM cte_union)

SELECT id_m, id_type Priority, Date, Reservation

FROM cte_final ORDER BY id_m, Priority

go

EXE # sp_test

go

DROP PRO # sp_test

Alex · Answer 2 · 2017-08-03T14:10:16+0000

This version uses table tables to "blow up" the records in each table to allow overlap detection:

I've checked additional cases, eg. reservation: replace 10 with 15v INSERT @t1 (ID_M, Date, Reservation) select '001', 2015, 10

- seems to return the correct result (based on my understanding of the problem).

CREATE TABLE #t1 ( id_m CHAR(3) ,date INT ,reservation INT )
CREATE TABLE #t2 ( id_m CHAR(3) ,id_type INT ,priority INT ,product_total INT )
INSERT #t1 (ID_M, Date, Reservation) select '001', 2014, 5 
INSERT #t1 (ID_M, Date, Reservation) select '001', 2015, 10
INSERT #t1 (ID_M, Date, Reservation) select '001', 2016, 18
INSERT #t1 (ID_M, Date, Reservation) select '002', 2015, 6
INSERT #t1 (ID_M, Date, Reservation) select '002', 2016, 22
INSERT #t2 (ID_M, ID_type, Priority,Product_Total) select '001', 3333, 1, 8
INSERT #t2 (ID_M, ID_type, Priority,Product_Total) select '001', 1111, 2, 10
INSERT #t2 (ID_M, ID_type, Priority,Product_Total) select '001', 2222, 3, 15
INSERT #t2 (ID_M, ID_type, Priority,Product_Total) select '002', 2222, 1, 16
INSERT #t2 (ID_M, ID_type, Priority,Product_Total) select '002', 1111, 2, 12


;WITH
    -- Tally Table start
    lv0 AS (SELECT 0 AS g UNION ALL SELECT 0),
    lv1 AS (SELECT 0 AS g FROM lv0 AS a CROSS JOIN lv0 AS b), -- 4
    lv2 AS (SELECT 0 AS g FROM lv1 AS a CROSS JOIN lv1 AS b), -- 16
    lv3 AS (SELECT 0 AS g FROM lv2 AS a CROSS JOIN lv2 AS b), -- 256
    lv4 AS (SELECT 0 AS g FROM lv3 AS a CROSS JOIN lv3 AS b), -- 65,536
    --lv5 AS (SELECT 0 AS g FROM lv4 AS a CROSS JOIN lv4 AS b), -- 4,294,967,296
    Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM lv4),
    -- Tally Table end

    -- Explode reservations to have the same number of rows as reservation value
    reservations AS(
        SELECT *, ROW_NUMBER() OVER( PARTITION BY id_m ORDER BY date ) AS CalcOrder
        FROM #t1
            INNER JOIN Nums ON n <= reservation ),

    -- Explode products to have the same number of rows as Product_Total value
    products AS(
        SELECT *, ROW_NUMBER() OVER( PARTITION BY id_m ORDER BY Priority ) AS CalcOrder
        FROM #t2
            INNER JOIN Nums ON n <= Product_Total )

SELECT b.id_m, date, reservation AS RequiredReservation, id_type, priority, product_total, COUNT(*) AS Reservation
FROM reservations AS b
    INNER JOIN products AS d ON b.id_m = d.id_m AND b.CalcOrder = d.CalcOrder
GROUP BY b.id_m, id_type, date, reservation, priority, product_total
ORDER BY b.id_m, date, priority, RequiredReservation

PS some of them refer to @ MaurícioPontaltiNeri answer specifically for the idea of using "ordered match"

(SELECT Row_number() OVER ( ORDER BY id_m, priority) rownumber
...
FROM cte_t1 a 
   INNER JOIN cte_t2 b 
           ON a.rownumber = b.rownumber

Subtraction in order of sale in TSQL

More articles: