Optimizing a CTE - but getting extra records

Transact-SQL
1

Hope someone can help. I currently have been given this:

DECLARE @AggregatorMotorResultMeasures AS TABLE
(
TransactionID INT,
WebReference VARCHAR (30),
BusinessEventID INT,
AuditRequestDate DATE,
AuditRequestDateTime DATETIME
)

INSERT INTO @AggregatorMotorResultMeasures
VALUES 
(
'1463603686', '8843-764472-6954', '0', '2018-07-02', '2018-07-02 00:02:44.000'
)

DECLARE @AggregatorGeneralRisk AS TABLE
(
TransactionID INT,
DUQQuestionNumber INT,
DUQResponse VARCHAR (30),
AuditRequestDateID DATETIME,
BranchCode VARCHAR (30),
QuoteDate DATE
)

INSERT INTO @AggregatorGeneralRisk
VALUES 
(
'1463603686', '580', '0', '2018-07-02 00:02:44.000', 'MGA', '2018-07-02'
)

INSERT INTO @AggregatorGeneralRisk
VALUES 
(
'1463603686', '544', 'Yes', '2018-07-02 00:02:44.000', 'MGA', '2018-07-02'
)

INSERT INTO @AggregatorGeneralRisk
VALUES 
(
'1463603686', '578', 'No', '2018-07-02 00:02:44.000', 'MGA', '2018-07-02'
)

INSERT INTO @AggregatorGeneralRisk
VALUES 
(
'1463603686', '689', 'N/A', '2018-07-02 00:02:44.000', 'MGA', '2018-07-02'
)

INSERT INTO @AggregatorGeneralRisk
VALUES 
(
'1463603686', '687', 'Alloy Wheels', '2018-07-02 00:02:44.000', 'MGA', '2018-07-02'
)

INSERT INTO @AggregatorGeneralRisk
VALUES 
(
'1463603686', '688', 'N/A', '2018-07-02 00:02:44.000', 'MGA', '2018-07-02'
)

INSERT INTO @AggregatorGeneralRisk
VALUES 
(
'1463603686', '694', '0', '2018-07-02 00:02:44.000', 'MGA', '2018-07-02'
)

INSERT INTO @AggregatorGeneralRisk
VALUES 
(
'1463603686', '696', '0', '2018-07-02 00:02:44.000', 'MGA', '2018-07-02'
)

INSERT INTO @AggregatorGeneralRisk
VALUES 
(
'1463603686', '695', '0', '2018-07-02 00:02:44.000', 'MGA', '2018-07-02'
)

INSERT INTO @AggregatorGeneralRisk
VALUES 
(
'1463603686', '892', 'Car', '2018-07-02 00:02:44.000', 'MGA', '2018-07-02'
)

INSERT INTO @AggregatorGeneralRisk
VALUES 
(
'1463603686', '690', 'N/A', '2018-07-02 00:02:44.000', 'MGA', '2018-07-02'
)

INSERT INTO @AggregatorGeneralRisk
VALUES 
(
'1463603686', '693', '0', '2018-07-02 00:02:44.000', 'MGA', '2018-07-02'
)

INSERT INTO @AggregatorGeneralRisk
VALUES 
(
'1463603686', '691', 'N/A', '2018-07-02 00:02:44.000', 'MGA', '2018-07-02'
)

INSERT INTO @AggregatorGeneralRisk
VALUES 
(
'1463603686', '692', '0', '2018-07-02 00:02:44.000', 'MGA', '2018-07-02'
)

INSERT INTO @AggregatorGeneralRisk
VALUES 
(
'1463603686', '40', 'Office/factory car park', '2018-07-02 00:02:44.000', 'MGA', '2018-07-02'
)

INSERT INTO @AggregatorGeneralRisk
VALUES 
(
'1463603686', '235', 'Annually', '2018-07-02 00:02:44.000', 'MGA', '2018-07-02'
)

INSERT INTO @AggregatorGeneralRisk
VALUES 
(
'1463603686', '429', 'No', '2018-07-02 00:02:44.000', 'MGA', '2018-07-02'
)

INSERT INTO @AggregatorGeneralRisk
VALUES 
(
'1463603686', '430', '0', '2018-07-02 00:02:44.000', 'MGA', '2018-07-02'
)

;WITH CTE_MotorResultMeasures AS
(
SELECT TransactionID, WebReference, BusinessEventId, AuditRequestDate, AuditRequestDateTime
FROM @AggregatorMotorResultMeasures
WHERE AuditRequestDateTime BETWEEN '2018-07-02 00:00:00' AND '2018-07-02 00:05:00'
)
,

CTE_Mod1 AS
(
SELECT TransactionID, CASE WHEN DUQQuestionNumber = '687' THEN DUQResponse END AS Vehicle_Modification_1 
FROM @AggregatorGeneralRisk
WHERE DUQQuestionNumber = '687'
AND AuditRequestDateID BETWEEN '2018-07-02 00:00:00' AND '2018-07-02 00:05:00'
)
,

CTE_Mod2 AS
(
SELECT TransactionID, CASE WHEN DUQQuestionNumber = '688' THEN DUQResponse END AS Vehicle_Modification_2
FROM @AggregatorGeneralRisk
WHERE DUQQuestionNumber = '688'
AND AuditRequestDateID BETWEEN '2018-07-02 00:00:00' AND '2018-07-02 00:05:00'
)
,

CTE_Mod3 AS
(
SELECT TransactionID, CASE WHEN DUQQuestionNumber = '689' THEN DUQResponse END AS Vehicle_Modification_3 
FROM @AggregatorGeneralRisk
WHERE DUQQuestionNumber = '689'
AND AuditRequestDateID BETWEEN '2018-07-02 00:00:00' AND '2018-07-02 00:05:00'
)
,

CTE_GenRisk AS
(
SELECT TransactionID, BranchCode, QuoteDate FROM @AggregatorGeneralRisk
WHERE AuditRequestDateID BETWEEN '2018-07-02 00:00:00' AND '2018-07-02 00:05:00'
)

SELECT DISTINCT 
agr.BranchCode,
arm.transactionID AS measure_TransactionId,
arm.WebReference,
arm.BusinessEventId,
arm.AuditRequestDate,
arm.AuditRequestDateTime,
CONVERT (VARCHAR (10), arm.AuditRequestDateTime, 120) AS Quote_Date,
agr.QuoteDate,
duq1.Vehicle_Modification_1,
duq2.Vehicle_Modification_2,
duq3.Vehicle_Modification_3
 
FROM CTE_GenRisk agr 
JOIN CTE_MotorResultMeasures arm ON arm.TransactionId = agr.TransactionId
LEFT JOIN CTE_Mod1 duq1 ON duq1.TransactionId = arm.TransactionId
LEFT JOIN CTE_Mod2 duq2 ON duq2.TransactionId = arm.TransactionId
LEFT JOIN CTE_Mod3 duq3 ON duq3.TransactionId = arm.TransactionId 
 
ORDER BY AuditRequestDateTime DESC

This returns one record. I've been asked if the various CTE_Mods can be amalgamated into one (this currently only tests three criteria, there are likely to be dozens in the near future, hence the need to keep this script sensible). So I tried this:

DECLARE @AggregatorMotorResultMeasures AS TABLE
(
TransactionID INT,
WebReference VARCHAR (30),
BusinessEventID INT,
AuditRequestDate DATE,
AuditRequestDateTime DATETIME
)

INSERT INTO @AggregatorMotorResultMeasures
VALUES 
(
'1463603686', '8843-764472-6954', '0', '2018-07-02', '2018-07-02 00:02:44.000'
)

DECLARE @AggregatorGeneralRisk AS TABLE
(
TransactionID INT,
DUQQuestionNumber INT,
DUQResponse VARCHAR (30),
AuditRequestDateID DATETIME,
BranchCode VARCHAR (30),
QuoteDate DATE
)

INSERT INTO @AggregatorGeneralRisk
VALUES 
(
'1463603686', '580', '0', '2018-07-02 00:02:44.000', 'MGA', '2018-07-02'
)

INSERT INTO @AggregatorGeneralRisk
VALUES 
(
'1463603686', '544', 'Yes', '2018-07-02 00:02:44.000', 'MGA', '2018-07-02'
)

INSERT INTO @AggregatorGeneralRisk
VALUES 
(
'1463603686', '578', 'No', '2018-07-02 00:02:44.000', 'MGA', '2018-07-02'
)

INSERT INTO @AggregatorGeneralRisk
VALUES 
(
'1463603686', '689', 'N/A', '2018-07-02 00:02:44.000', 'MGA', '2018-07-02'
)

INSERT INTO @AggregatorGeneralRisk
VALUES 
(
'1463603686', '687', 'Alloy Wheels', '2018-07-02 00:02:44.000', 'MGA', '2018-07-02'
)

INSERT INTO @AggregatorGeneralRisk
VALUES 
(
'1463603686', '688', 'N/A', '2018-07-02 00:02:44.000', 'MGA', '2018-07-02'
)

INSERT INTO @AggregatorGeneralRisk
VALUES 
(
'1463603686', '694', '0', '2018-07-02 00:02:44.000', 'MGA', '2018-07-02'
)

INSERT INTO @AggregatorGeneralRisk
VALUES 
(
'1463603686', '696', '0', '2018-07-02 00:02:44.000', 'MGA', '2018-07-02'
)

INSERT INTO @AggregatorGeneralRisk
VALUES 
(
'1463603686', '695', '0', '2018-07-02 00:02:44.000', 'MGA', '2018-07-02'
)

INSERT INTO @AggregatorGeneralRisk
VALUES 
(
'1463603686', '892', 'Car', '2018-07-02 00:02:44.000', 'MGA', '2018-07-02'
)

INSERT INTO @AggregatorGeneralRisk
VALUES 
(
'1463603686', '690', 'N/A', '2018-07-02 00:02:44.000', 'MGA', '2018-07-02'
)

INSERT INTO @AggregatorGeneralRisk
VALUES 
(
'1463603686', '693', '0', '2018-07-02 00:02:44.000', 'MGA', '2018-07-02'
)

INSERT INTO @AggregatorGeneralRisk
VALUES 
(
'1463603686', '691', 'N/A', '2018-07-02 00:02:44.000', 'MGA', '2018-07-02'
)

INSERT INTO @AggregatorGeneralRisk
VALUES 
(
'1463603686', '692', '0', '2018-07-02 00:02:44.000', 'MGA', '2018-07-02'
)

INSERT INTO @AggregatorGeneralRisk
VALUES 
(
'1463603686', '40', 'Office/factory car park', '2018-07-02 00:02:44.000', 'MGA', '2018-07-02'
)

INSERT INTO @AggregatorGeneralRisk
VALUES 
(
'1463603686', '235', 'Annually', '2018-07-02 00:02:44.000', 'MGA', '2018-07-02'
)

INSERT INTO @AggregatorGeneralRisk
VALUES 
(
'1463603686', '429', 'No', '2018-07-02 00:02:44.000', 'MGA', '2018-07-02'
)

INSERT INTO @AggregatorGeneralRisk
VALUES 
(
'1463603686', '430', '0', '2018-07-02 00:02:44.000', 'MGA', '2018-07-02'
)

;WITH CTE_MotorResultMeasures AS
(
SELECT TransactionID, WebReference, BusinessEventId, AuditRequestDate, AuditRequestDateTime
FROM @AggregatorMotorResultMeasures
WHERE AuditRequestDateTime BETWEEN '2018-07-02 00:00:00' AND '2018-07-02 00:05:00'
)
,

CTE_Mod AS
(
SELECT TransactionID, CASE WHEN DUQQuestionNumber = '687' THEN DUQResponse END AS Vehicle_Modification_1,
CASE WHEN DUQQuestionNumber = '688' THEN DUQResponse END AS Vehicle_Modification_2, 
CASE WHEN DUQQuestionNumber = '689' THEN DUQResponse END AS Vehicle_Modification_3  
FROM @AggregatorGeneralRisk
WHERE DUQQuestionNumber IN ('687', '688', '689')
AND AuditRequestDateID BETWEEN '2018-07-02 00:00:00' AND '2018-07-02 00:05:00'
)
,

--CTE_Mod2 AS
--(
--SELECT TransactionID, CASE WHEN DUQQuestionNumber = '688' THEN DUQResponse END AS Vehicle_Modification_2
--FROM @AggregatorGeneralRisk
--WHERE DUQQuestionNumber = '688'
--AND AuditRequestDateID BETWEEN '2018-07-02 00:00:00' AND '2018-07-02 00:05:00'
--)
--,

--CTE_Mod3 AS
--(
--SELECT TransactionID, CASE WHEN DUQQuestionNumber = '689' THEN DUQResponse END AS Vehicle_Modification_3 
--FROM @AggregatorGeneralRisk
--WHERE DUQQuestionNumber = '689'
--AND AuditRequestDateID BETWEEN '2018-07-02 00:00:00' AND '2018-07-02 00:05:00'
--)
--,

CTE_GenRisk AS
(
SELECT TransactionID, BranchCode, QuoteDate FROM @AggregatorGeneralRisk
WHERE AuditRequestDateID BETWEEN '2018-07-02 00:00:00' AND '2018-07-02 00:05:00'
)

SELECT DISTINCT 
agr.BranchCode,
arm.transactionID AS measure_TransactionId,
arm.WebReference,
arm.BusinessEventId,
arm.AuditRequestDate,
arm.AuditRequestDateTime,
CONVERT (VARCHAR (10), arm.AuditRequestDateTime, 120) AS Quote_Date,
agr.QuoteDate,
duq1.Vehicle_Modification_1,
duq1.Vehicle_Modification_2,
duq1.Vehicle_Modification_3
 
FROM CTE_GenRisk agr 
JOIN CTE_MotorResultMeasures arm ON arm.TransactionId = agr.TransactionId
LEFT JOIN CTE_Mod duq1 ON duq1.TransactionId = arm.TransactionId
--LEFT JOIN CTE_Mod2 duq2 ON duq2.TransactionId = arm.TransactionId
--LEFT JOIN CTE_Mod3 duq3 ON duq3.TransactionId = arm.TransactionId 
 
ORDER BY AuditRequestDateTime DESC

This, however, returns three records which isn't what is wanted.

Does anyone know a way to have all of the question "tests" in one place and get the output into a single record as per the first script?

Thanks in advance.

2

Can you explain the requirements? What are you trying to accomplish? From a quick look, CTE_Mod returns 3 rows. You may want to put max around Vehicle_Modification_1, Vehicle_Modification_2 and Vehicle_Modification_3 and group by TransactionID. That would probably return 1 row

3

try this

if object_id('tempdb..#AggregatorMotorResultMeasures') is not null drop table #AggregatorMotorResultMeasures

create TABLE #AggregatorMotorResultMeasures(
TransactionID INT,
WebReference VARCHAR (30),
BusinessEventID INT,
AuditRequestDate DATE,
AuditRequestDateTime DATETIME
)

INSERT INTO #AggregatorMotorResultMeasures
VALUES ('1463603686', '8843-764472-6954', '0', '2018-07-02', '2018-07-02 00:02:44.000')

if object_id('tempdb..#AggregatorGeneralRisk') is not null drop table #AggregatorGeneralRisk

create TABLE #AggregatorGeneralRisk(
TransactionID INT,
DUQQuestionNumber INT,
DUQResponse VARCHAR (30),
AuditRequestDateID DATETIME,
BranchCode VARCHAR (30),
QuoteDate DATE
)

INSERT INTO #AggregatorGeneralRisk
VALUES ('1463603686', '580', '0', '2018-07-02 00:02:44.000', 'MGA', '2018-07-02')
,('1463603686', '544', 'Yes', '2018-07-02 00:02:44.000', 'MGA', '2018-07-02')
,('1463603686', '578', 'No', '2018-07-02 00:02:44.000', 'MGA', '2018-07-02')
,('1463603686', '689', 'N/A', '2018-07-02 00:02:44.000', 'MGA', '2018-07-02')
,('1463603686', '687', 'Alloy Wheels', '2018-07-02 00:02:44.000', 'MGA', '2018-07-02')
,('1463603686', '688', 'N/A', '2018-07-02 00:02:44.000', 'MGA', '2018-07-02')
,('1463603686', '694', '0', '2018-07-02 00:02:44.000', 'MGA', '2018-07-02')
,('1463603686', '696', '0', '2018-07-02 00:02:44.000', 'MGA', '2018-07-02')
,('1463603686', '695', '0', '2018-07-02 00:02:44.000', 'MGA', '2018-07-02')
,('1463603686', '892', 'Car', '2018-07-02 00:02:44.000', 'MGA', '2018-07-02')
,('1463603686', '690', 'N/A', '2018-07-02 00:02:44.000', 'MGA', '2018-07-02')
,('1463603686', '693', '0', '2018-07-02 00:02:44.000', 'MGA', '2018-07-02')
,('1463603686', '691', 'N/A', '2018-07-02 00:02:44.000', 'MGA', '2018-07-02')
,('1463603686', '692', '0', '2018-07-02 00:02:44.000', 'MGA', '2018-07-02')
,('1463603686', '40', 'Office/factory car park', '2018-07-02 00:02:44.000', 'MGA', '2018-07-02')
,('1463603686', '235', 'Annually', '2018-07-02 00:02:44.000', 'MGA', '2018-07-02')
,('1463603686', '429', 'No', '2018-07-02 00:02:44.000', 'MGA', '2018-07-02')
, ('1463603686', '430', '0', '2018-07-02 00:02:44.000', 'MGA', '2018-07-02')

/*
select * from #AggregatorMotorResultMeasures

select * from #AggregatorGeneralRisk

*/

select Distinct
a.BranchCode
,a.TransactionID
,WebReference
,BusinessEventID
,AuditRequestDate
,AuditRequestDateTime
,CONVERT (VARCHAR (10), b.AuditRequestDateTime, 120) AS Quote_Date
,a.QuoteDate
,Mod1.DUQResponse as Vehicle_Modification_1
,Mod2.DUQResponse as Vehicle_Modification_2
,Mod3.DUQResponse as Vehicle_Modification_3
,Mod4.DUQResponse as Vehicle_Modification_4
,Mod5.DUQResponse as Vehicle_Modification_5
,Mod6.DUQResponse as Vehicle_Modification_6
,Mod7.DUQResponse as Vehicle_Modification_7
from #AggregatorGeneralRisk a
left join #AggregatorMotorResultMeasures b on a.TransactionID = b.TransactionID
left join #AggregatorGeneralRisk Mod1 on a.TransactionID = Mod1.TransactionID and Mod1.DUQQuestionNumber = 687
left join #AggregatorGeneralRisk Mod2 on a.TransactionID = Mod2.TransactionID and Mod2.DUQQuestionNumber = 688
left join #AggregatorGeneralRisk Mod3 on a.TransactionID = Mod3.TransactionID and Mod3.DUQQuestionNumber = 892
left join #AggregatorGeneralRisk Mod4 on a.TransactionID = Mod4.TransactionID and Mod4.DUQQuestionNumber = 690
left join #AggregatorGeneralRisk Mod5 on a.TransactionID = Mod5.TransactionID and Mod5.DUQQuestionNumber = 429
left join #AggregatorGeneralRisk Mod6 on a.TransactionID = Mod6.TransactionID and Mod6.DUQQuestionNumber = 40
left join #AggregatorGeneralRisk Mod7 on a.TransactionID = Mod7.TransactionID and Mod7.DUQQuestionNumber = 235

1 Like
4

Don't hardcode it. You can use a dynamic pivot. If there is a lookup table to the DuqQuestionNumber then you can join to that and use that column in the @Cols variable to get descriptive column headings

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.DuqQuestionNumber) 
            FROM #AggregatorGeneralRisk c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT BranchCode
				,TransactionID
				,WebReference
				,BusinessEventID
				,AuditRequestDate
				,AuditRequestDateTime
				,CONVERT (VARCHAR (10), AuditRequestDateTime, 120) AS Quote_Date
				,QuoteDate, ' + @cols + ' from 
            (
                select BranchCode
					,a.TransactionID
                    ,WebReference
					,BusinessEventID
					,AuditRequestDate
					,AuditRequestDateTime
					,CONVERT (VARCHAR (10), b.AuditRequestDateTime, 120) AS Quote_Date
					,QuoteDate
					, DuqQuestionNumber
                    , DuqResponse
                from #AggregatorGeneralRisk a
					join #AggregatorMotorResultMeasures b
						 on a.TransactionID = b.TransactionID
           ) x
            pivot 
            (
                 max(DuqResponse)
                for DuqQuestionNumber in (' + @cols + ')
            ) p '


execute(@query)
1 Like
5 
;WITH CTE_MotorResultMeasures AS
(
SELECT TransactionID, WebReference, BusinessEventId, AuditRequestDate, AuditRequestDateTime
FROM @AggregatorMotorResultMeasures
WHERE AuditRequestDateTime BETWEEN '2018-07-02 00:00:00' AND '2018-07-02 00:05:00'
)
SELECT 
	agr.BranchCode,
	arm.transactionID AS measure_TransactionId,
	arm.WebReference,
	arm.BusinessEventId,
	arm.AuditRequestDate,
	arm.AuditRequestDateTime,
	CONVERT (VARCHAR (10), arm.AuditRequestDateTime, 120) AS Quote_Date,
	agr.QuoteDate,
	agr.Vehicle_Modification_1,
	agr.Vehicle_Modification_2,
	agr.Vehicle_Modification_3
FROM 
(
	Select 
		BranchCode,
		QuoteDate,
		TransactionID,
		Max(CASE WHEN DUQQuestionNumber = '687' THEN DUQResponse END) AS Vehicle_Modification_1,
		Max(CASE WHEN DUQQuestionNumber = '688' THEN DUQResponse END) AS Vehicle_Modification_2,
		Max(CASE WHEN DUQQuestionNumber = '689' THEN DUQResponse END) AS Vehicle_Modification_3 
	FROM @AggregatorGeneralRisk
	WHERE 
		AuditRequestDateID BETWEEN '2018-07-02 00:00:00' AND '2018-07-02 00:05:00'
	Group By 
		BranchCode,
		QuoteDate,
		TransactionID
) agr
JOIN CTE_MotorResultMeasures arm ON arm.TransactionId = agr.TransactionId

ORDER BY AuditRequestDateTime DESC
1 Like