Início / dba / Perguntas / 331784
Accepted
John Marston
Asked: 2023-10-03 10:14:28 +0800 CST

Obtenha pontos de outra tabela com base na categoria e faixa de valores

  • 772

Estou tentando obter uma coluna que especifique os pontos comparando o valor da tabela original com os valores mínimo e máximo de outra tabela. Tenho uma tabela principal, que consiste em algumas colunas de valores e a respectiva coluna de pontos a serem inseridos. Aqui está o exemplo:

Main table
--------------------------------------------------------------------------------
| User   | Department |  BU  |  Revenue | Revenuepoint | Profit |  Profitpoint |
--------------------------------------------------------------------------------
| A      | 1000000    | 101  |  400     |              | 200    |              |
| B      | 1000001    | 101  |  300     |              | 100    |              |
| C      | 1000000    | 102  |  350     |              | 150    |              |
--------------------------------------------------------------------------------


Point table
----------------------------------------------------------------
| Category| Department |  BU  |  Fromvalue  | Tovalue | Point  |
----------------------------------------------------------------
| Revenue | 1000000    | 101  |  0          | 200     | 1      |
| Revenue | 1000000    | 101  |  201        | 400     | 2      |
| Revenue | 1000000    | 102  |  0          | 300     | 1      |
| Revenue | 1000000    | 102  |  301        | 400     | 2      |
| Revenue | 1000001    | 101  |  0          | 200     | 1      |
| Revenue | 1000001    | 101  |  201        | 400     | 2      |
| Profit  | 1000000    | 101  |  0          | 100     | 1      |
| Profit  | 1000000    | 101  |  101        | 300     | 2      |
| Profit  | 1000000    | 102  |  0          | 50      | 1      |
| Profit  | 1000000    | 102  |  51         | 200     | 2      |
| Profit  | 1000001    | 101  |  0          | 50      | 1      |
| Profit  | 1000001    | 101  |  51         | 200     | 2      |
----------------------------------------------------------------


Expected table
--------------------------------------------------------------------------------
| User   | Department |  BU  |  Revenue | Revenuepoint | Profit |  Profitpoint |
--------------------------------------------------------------------------------
| A      | 1000000    | 101  |  400     |       2      | 200    |       2      |
| B      | 1000001    | 101  |  300     |       2      | 40     |       1      |
| C      | 1000000    | 102  |  350     |       2      | 150    |       2      |
--------------------------------------------------------------------------------

Departamento e BU precisam corresponder e o RevenuePoint precisa pegar os registros onde Categoria = Receita, igual ao ProfitPoint.

Tentei algumas maneiras que encontrei aqui, mas a maioria não é tão complexa quanto a minha, então não tenho ideia de como sair daqui.

Este é o código que eu tentei

SELECT 
a.User, 
a.Department, 
a.BU, 
a.Revenue, 
(SELECT MAX(b.Point) from Point b WHERE a.Revenue BETWEEN b.fromvalue and b.tovalue) as Revenuepoint,
b.Point as Revenuepoint,
a.Profit, 
(SELECT MAX(b.Point) from Point b WHERE a.Profit BETWEEN b.fromvalue and b.tovalue) as Profitpoint
FROM Sales a
sql-server

1 respostas

  1. Best Answer

    CROSS APPLY uma tabela PIVOT -

    Documentação do MSDN aqui - https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver16

    Preenchi seus dados usando este código -

    CREATE TABLE #sales
(
    [User] CHAR(1)
  , Department INT
  , BU INT
  , Revenue INT
  , RevenuePoint INT
  , Profit INT
  , ProfitPoint INT
);

INSERT INTO #sales
VALUES
('A', 1000000, 101, 400, NULL, 200, NULL)
, ('B', 1000001, 101, 300, NULL, 100, NULL)
, ('C', 1000000, 102, 350, NULL, 150, NULL);

CREATE TABLE #point
(
    Category VARCHAR(10)
  , Department INT
  , BU INT
  , FromValue INT
  , ToValue INT
  , Point INT
);

INSERT INTO #point
VALUES
('Revenue', 1000000, 101, 0, 200, 1)
, ('Revenue', 1000000, 101, 201, 400, 2)
, ('Revenue', 1000000, 102, 0, 300, 1)
, ('Revenue', 1000000, 102, 301, 400, 2)
, ('Revenue', 1000001, 101, 0, 200, 1)
, ('Revenue', 1000001, 101, 201, 400, 2)
, ('Profit', 1000000, 101, 0, 100, 1)
, ('Profit', 1000000, 101, 101, 300, 2)
, ('Profit', 1000000, 102, 0, 50, 1)
, ('Profit', 1000000, 102, 51, 200, 2)
, ('Profit', 1000001, 101, 0, 50, 1)
, ('Profit', 1000001, 101, 51, 200, 1);

    Então, ao executar isso, você obtém o conjunto de dados desejado -

    SELECT s.[User]
     , s.Department
     , s.BU
     , s.Revenue
     , display.Revenue
     , s.Profit
     , display.Profit
FROM #sales AS s
    CROSS APPLY
(
    SELECT p_pvt.Profit
         , p_pvt.Revenue
    FROM
    (
        SELECT p.Category
             , p.Point
        FROM #point AS p
        WHERE s.Department = p.Department
              AND s.BU = p.BU
              AND
              (
                  (
                      s.Revenue
              BETWEEN p.FromValue AND p.ToValue
                      AND p.Category = 'Revenue'
                  )
                  OR
                  (
                      s.Profit
              BETWEEN p.FromValue AND p.ToValue
                      AND p.Category = 'Profit'
                  )
              )
    ) AS p_root
    PIVOT
    (
        MAX(Point)
        FOR Category IN (Revenue, Profit)
    ) AS p_pvt
) AS display;

    A instrução UPDATE ficaria assim -

    UPDATE s
SET s.RevenuePoint = display.Revenue
  , s.ProfitPoint = display.Profit
FROM #sales AS s
    CROSS APPLY
(
    SELECT p_pvt.Profit
         , p_pvt.Revenue
    FROM
    (
        SELECT p.Category
             , p.Point
        FROM #point AS p
        WHERE s.Department = p.Department
              AND s.BU = p.BU
              AND
              (
                  (
                      s.Revenue
              BETWEEN p.FromValue AND p.ToValue
                      AND p.Category = 'Revenue'
                  )
                  OR
                  (
                      s.Profit
              BETWEEN p.FromValue AND p.ToValue
                      AND p.Category = 'Profit'
                  )
              )
    ) AS p_root
    PIVOT
    (
        MAX(Point)
        FOR Category IN (Revenue, Profit)
    ) AS p_pvt
) AS display;

    A tabela de resultados é a seguinte -

    User Department  BU          Revenue     RevenuePoint     Profit      ProfitPoint
---- ----------- ----------- ----------- ----------- ----------- -----------
A    1000000     101         400         2           200         2
B    1000001     101         300         2           100         1
C    1000000     102         350         2           150         2
    • 0

relate perguntas