3

I have 2 tables as following:

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 repartition ID_TYPE for reservation? Table 2 priority indicate the order for the reservation, priority 1 is to reserve first and etc. Is it possible to create a table/View/CTE in T-SQL like following?

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, the reservation is calculated based on the total product in table 2 and the number of product reserved in table 1. for exemple, on 2014, we have reservation 5 for ID_M = 001 in table 1, we search the ID_M = 001 in table 2, we reserve first for the product in priority 1, so it's ID_type 3333. so we get first line in the desired table: 001, 3333, 2014, 5.

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

Shania
  • 45
  • 7
  • Formatting 101... – AntDC Aug 02 '17 at 11:54
  • I'm afraid you will not solve this with an SQL statement, you will need to write a T-SQL procedure with logic. If you need directions for this, please tell. – Stefano Losi Aug 02 '17 at 12:04
  • Hi Stefano Losi, thank you for the reply, i would be appreciated that you could share T-SQL procedure for this function. – Shania Aug 02 '17 at 12:08
  • What version of SQL Server? – Alex Aug 02 '17 at 12:10
  • SQL Server 2012 – Shania Aug 02 '17 at 12:11
  • 2
    What have you tried so far? Please show some effort in order to encourage us to help. Please keep in mind, SO is not a code generation service. Please read [ask] in [help] for more info. Try to share something you have tried, try to explain what is the problem with it, why you can't make it work, etc. – Pred Aug 02 '17 at 12:46
  • I have tried to add one calculated column for the culmulated number by priority from table 2, and the culmulated number for reservation by date in table 1. I joined with Two tables. The problem is how to calculate the number of product for each ID_Type. – Shania Aug 02 '17 at 12:58
  • Why ID_Type 3333 is only two times? There it no uses in 2016? – Maurício Pontalti Neri Aug 02 '17 at 14:40
  • @StefanoLosi How do you figure it is not possible? I'm inclined to agree but can't think how to prove it. – Martin Brown Aug 02 '17 at 15:32
  • @MaurícioPontaltiNeri because from table 2, we can see total product for ID_Type 3333 is 8, on 2014, we have reservation 5 and on 2015, we have reservation for 10, 3 from ID_Type 3333 and the rest will take from ID_Type 1111 based on the Priority of the product. So ID_Type 3333 is all reserved by the year of 2015, so there is no ID_Type 3333 for 2016 – Shania Aug 02 '17 at 16:17
  • Ok. I understand the logic. Today lately I will post an solution. – Maurício Pontalti Neri Aug 02 '17 at 16:31
  • @StefanoLosi could you pls share the solution on the Tsql procedure ? – Shania Aug 02 '17 at 21:02
  • @MaurícioPontaltiNeri thank you for the quick reply, I will test the code. – Shania Aug 04 '17 at 08:22

2 Answers2

3

To resolve this code I created:

  1. a unique id (rownumber)for each row in the both table
  2. Join both tables using the rownumber twice, one to get the value from t1, and another to get the value from t2

  3. In the end, I use a SUM function to add from t1 and subtract from t2

    CREATE PROC #sp_test -- Remove # in your environment

    AS

    -- This lines dosn't existis on 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

    -- This lines dosn't existis on 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 ,a.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 ,a.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 reservation ELSE reservation *- 1 END) OVER( partition BY id_m ORDER BY rownumber, level rows UNBOUNDED PRECEDING ) * ( CASE WHEN level = 0 THEN 1 ELSE -1 END ) as reservation FROM cte_union)

    SELECT id_m ,id_type ,priority ,date ,reservation

    FROM cte_final ORDER BY id_m ,priority

    go

    EXEC #sp_test

    go

    DROP PROC #sp_test

Maurício Pontalti Neri
  • 1,060
  • 1
  • 10
  • 17
  • does not work in certain cases e.g. reservation: replace 10 with 15 in `INSERT @t1 (ID_M, Date, Reservation) select '001', 2015, 10`. That is assuming I understand the logic. Still, well done on getting this far! – Alex Aug 03 '17 at 12:59
  • Sorry, Today I am away, and I cannot check it now. But if the reservation is set 15 the product stock will be less than 0 I do not know if it is possible in this context. At night I can think more about it. – Maurício Pontalti Neri Aug 03 '17 at 13:30
  • This version works fine, but I don't why the Indent is wrong. – Maurício Pontalti Neri Aug 04 '17 at 10:00
1

This version is using tally tables to "explode" records in each table to allow range overlap detection:

I have checked additional cases e.g. reservation: replace 10 with 15 in INSERT @t1 (ID_M, Date, Reservation) select '001', 2015, 10 - seems to return 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

P.S. some credit goes to @MaurícioPontaltiNeri answer, specifically for the idea of using "ordered matching" 

(SELECT Row_number() OVER ( ORDER BY id_m, priority) rownumber
...
FROM cte_t1 a 
   INNER JOIN cte_t2 b 
           ON a.rownumber = b.rownumber
Alex
  • 4,885
  • 3
  • 19
  • 39