Can I use a CTE to make these two tables become one?-CodePudding

Is there any way I can make these two queries become one query? I need all of this to be in one table. Currently I'm not using any common table expression and I believe if I use them I can make these two codes into one single table.

I would also like a column called 'G' which is a sum of 'E' and 'F'

Is there any way I can combine these two queries into one?

This is my code for two seperate queries:

DECLARE @Results TABLE
(
    Code NVARCHAR(20),
    Name NVARCHAR(100),
    EFFECTIVEDATE DATE,
    Reference NVARCHAR(20),
    RATE Decimal(3,2)

)

INSERT INTO @Results
SELECT
TP.Reference AS [Code],
TP.NAME AS [Name],
FORMAT(D.StartDate,'yyyy-MM-dd') AS [EFFECTIVEDATE],
D.Reference AS [Reference],  
DR.Rate AS [RATE]                                                                                                                                                                           
FROM TradingP TP
RIGHT JOIN Schema S on S.TradingPId = TP.Id 
RIGHT JOIN Deals D ON D.SchemaId = S.Id
LEFT JOIN DealResults DR on D.Id = DR.DealId
LEFT JOIN DealTurnoverBands DTR ON D.Id = DTR.DealId

SELECT 
[Code] 'Supplier Code',
[Name] 'Supplier',
[EFFECTIVEDATE] 'Effective Date',
[A] 'A %',
[B] 'B %',
[C] 'C %',
[D] 'D %',
[E] 'E %'
FROM @Results
PIVOT (
MAX(RATE)
FOR
Reference IN ([A], [B], [C], [D], [E]) 
)
as pivottable1

DECLARE @Results TABLE
(
    Code NVARCHAR(20),
    Name NVARCHAR(100),
    EFFECTIVEDATE DATE,
    Reference NVARCHAR(20),
    RATE Decimal(3,2)

)

INSERT INTO @Results
SELECT
TP.Reference AS [Code],
TP.NAME AS [Name],
FORMAT(D.StartDate,'yyyy-MM-dd') AS [EFFECTIVEDATE],
D.Reference AS [Reference],  
DTB.PercentageRate AS [RATE]                                                                                                                                                                           
FROM TradingPartners TP
RIGHT JOIN Schemes S on S.TradingPartnerId = TP.Id 
RIGHT JOIN Deals D ON D.SchemeId = S.Id
LEFT JOIN DealTurnoverBands DTB ON D.Id = DTB.DealId
WHERE PercentageRate IS NOT NULL AND PercentageRate >0.1

SELECT 
[Code] 'Supplier Code',
[Name] 'Supplier',
[EFFECTIVEDATE] 'Effective Date',
[F] 'F %'
FROM @Results
PIVOT (
MAX(RATE)
FOR
Reference IN ([F]) 
)
as pivottable1 
```

CodePudding user response：

You don't need a CTE; just insert both sets of data into your table variable.

Also, you should avoid converting a datetime value to a string just to remove the time portion; just cast it to date.

And if you're testing for records where the PercentageRate is greater than 0.1, you don't also need to test whether PercentageRate is NOT NULL; a NULL value will not pass the greater-than test.

DECLARE @Results TABLE
(
    Code NVARCHAR(20),
    Name NVARCHAR(100),
    EFFECTIVEDATE DATE,
    Reference NVARCHAR(20),
    RATE Decimal(3,2)
);

INSERT INTO @Results
(
    Code,
    Name,
    EFFECTIVEDATE,
    Reference,
    RATE
)
SELECT
    TP.Reference AS [Code],
    TP.NAME AS [Name],
    CAST(D.StartDate As date) AS [EFFECTIVEDATE],
    D.Reference AS [Reference],  
    DR.Rate AS [RATE]                                                                                                                                                                           
FROM 
    TradingP TP
    RIGHT JOIN Schema S on S.TradingPId = TP.Id 
    RIGHT JOIN Deals D ON D.SchemaId = S.Id
    LEFT JOIN DealResults DR on D.Id = DR.DealId
    LEFT JOIN DealTurnoverBands DTR ON D.Id = DTR.DealId
WHERE
    TP.Reference IN ('A', 'B', 'C', 'D', 'E')
;

INSERT INTO @Results
(
    Code,
    Name,
    EFFECTIVEDATE,
    Reference,
    RATE
)
SELECT
    TP.Reference AS [Code],
    TP.NAME AS [Name],
    CAST(D.StartDate As date) AS [EFFECTIVEDATE],
    D.Reference AS [Reference],  
    DTB.PercentageRate AS [RATE]                                                                                                                                                                           
FROM 
    TradingPartners TP
    RIGHT JOIN Schemes S on S.TradingPartnerId = TP.Id 
    RIGHT JOIN Deals D ON D.SchemeId = S.Id
    LEFT JOIN DealTurnoverBands DTB ON D.Id = DTB.DealId
WHERE 
    TP.Reference = 'F'
And
    DTB.PercentageRate > 0.1
;

SELECT 
    [Code] 'Supplier Code',
    [Name] 'Supplier',
    [EFFECTIVEDATE] 'Effective Date',
    [A] 'A %',
    [B] 'B %',
    [C] 'C %',
    [D] 'D %',
    [E] 'E %',
    [F] 'F %',
    [E]   [F] 'G %'
FROM 
    @Results
    PIVOT 
    (
        MAX(RATE)
        FOR Reference IN ([A], [B], [C], [D], [E], [F]) 
    ) As pivottable1
;

CodePudding user response：

Just saying, but a "Common Table Expression" or CTE is just something like a re-usable template for a sub-query.

For example, this query:

WITH CTE_DATA AS (select * from tableA) 
SELECT * FROM CTE_DATA AS data;

Returns exactly the same as this query:

SELECT * FROM (select * from tableA) AS data;

But to combine both your queries, you can just union them in the source query of the pivot.

WITH CTE1 AS (
    SELECT
    TP.Reference AS [Code],
    TP.NAME AS [Name],
    FORMAT(D.StartDate,'yyyy-MM-dd') AS [EFFECTIVEDATE],
    D.Reference AS [Reference],  
    DR.Rate AS [RATE]                                                                                                                                                                           
    FROM TradingP TP
    RIGHT JOIN Schema S on S.TradingPId = TP.Id 
    RIGHT JOIN Deals D ON D.SchemaId = S.Id
    LEFT JOIN DealResults DR on D.Id = DR.DealId
    LEFT JOIN DealTurnoverBands DTR ON D.Id = DTR.DealId
) 
, CTE2 AS (
    SELECT
    TP.Reference AS [Code],
    TP.NAME AS [Name],
    FORMAT(D.StartDate,'yyyy-MM-dd') AS [EFFECTIVEDATE],
    D.Reference AS [Reference],  
    DTB.PercentageRate AS [RATE]                                                                                                                                                                           
    FROM TradingPartners TP
    RIGHT JOIN Schemes S on S.TradingPartnerId = TP.Id 
    RIGHT JOIN Deals D ON D.SchemeId = S.Id
    LEFT JOIN DealTurnoverBands DTB ON D.Id = DTB.DealId
    WHERE PercentageRate IS NOT NULL 
      AND PercentageRate > 0.1
) 
SELECT 
[Code] AS [Supplier Code],
[Name] AS [Supplier],
[EFFECTIVEDATE] AS [Effective Date],
[A] AS [A %],
[B] AS [B %],
[C] AS [C %],
[D] AS [D %],
[E] AS [E %],
[F] AS [F %]
FROM (
   SELECT Code, Name, EFFECTIVEDATE, Reference, RATE
   FROM CTE1

   UNION ALL

   SELECT Code, Name, EFFECTIVEDATE, Reference, RATE
   FROM CTE2
) Src
PIVOT (
  MAX(RATE)
  FOR Reference IN ([A], [B], [C], [D], [E], [F]) 
) Pvt;