Share via

Convert query in adf

Vineet S 1,390 Reputation points
2024-08-15T19:38:32.3366667+00:00

Hi, how to convert the query of temp table and variable in adf sql query.. Am unable to convert.. Tried cte


--/*
DECLARE 
	@Period VARCHAR(20)='202406',
	@Facility NVARCHAR(20)='',
	@ShowFacility BIT=0,
	@GroupBy NVARCHAR(20)='Lineslip',
	@FrontingCarrier NVARCHAR(256)='',
	@PlacingBroker NVARCHAR(256)='',
	@Carrier NVARCHAR(256)='Certus',
	@LineslipReference NVARCHAR(30) = '',
	@BinderUMR NVARCHAR(30)='',
	@IndemnityClient NVARCHAR(65) ='',
	@EventName NVARCHAR(50)='',
	@CatCode NVARCHAR(50)='',
	@UMR NVARCHAR(20)='',
	@ClaimReference NVARCHAR(20)='',
	@Threshold VARCHAR(10)='',
	@Class NVARCHAR(256)='',
	@ExClass NVARCHAR(256)='Aviation',
	@ProductType NVARCHAR(256)='',
	@ExProductType NVARCHAR(256)='',
	@PolicyInceptionDateFrom VARCHAR(8)='',
	@PolicyInceptionDateTo VARCHAR(8)='',
	@ZeroClosed NCHAR(1)='',
	@LegacyBackload NCHAR(1)=''
--*/

SELECT
	CarrierKey
INTO #CarrierList
FROM tblDimCarrier c
INNER JOIN (
	SELECT CarrierName=Field2
	FROM IC.tblLookup
	WHERE LookupFilter='CarrierList'
	AND Field1=@Carrier
) l ON l.CarrierName=c.CarrierName


CREATE TABLE #FrontingOverride (
	PolicyKey INT,
	Line DECIMAL(16,8)
)

IF @FrontingCarrier='xMitsui' BEGIN
	INSERT INTO #FrontingOverride
	SELECT
		PolicyKey,
		Line=CONVERT(DECIMAL(16,8),Field7)/100
	FROM IC.tblLookup l
	INNER JOIN vwDimPolicy p 
		ON p.PolicyReference=Field1
		AND p.SectionCode=Field2
	WHERE LookupFilter LIKE 'MitsuiPolicySection%'
	AND ISNULL(Field7,'')<>''

	CREATE INDEX ix1 ON #FrontingOverride (PolicyKey)
END

SELECT
	pm.PolicyMarketKey
INTO #TPM
FROM tblDimPolicyMarket pm
INNER JOIN vwDimCarrier c ON c.CarrierKey=pm.CarrierKey
LEFT OUTER JOIN #CarrierList cl ON cl.CarrierKey=pm.CarrierKey
LEFT OUTER JOIN vwDimFrontingCarrier fc1 ON fc1.FrontingCarrierkey=pm.L1Carrierkey
LEFT OUTER JOIN vwDimFrontingCarrier fc2 ON fc2.FrontingCarrierkey=pm.L2Carrierkey
LEFT OUTER JOIN vwDimFrontingCarrier fc3 ON fc3.FrontingCarrierkey=pm.L3Carrierkey
LEFT OUTER JOIN vwDimPlacingBroker pb ON pb.PlacingBrokerKey=pm.PlacingBrokerKey
WHERE 1=1
AND (@FrontingCarrier='' OR @FrontingCarrier IN(fc1.FrontingCarrierShortName,fc2.FrontingCarrierShortName,fc3.FrontingCarrierShortName,fc1.FrontingCarrierName,fc2.FrontingCarrierName,fc3.FrontingCarrierName))
AND (@Carrier='' OR @Carrier IN(c.RSCarrierName,c.CarrierShortName) OR cl.CarrierKey IS NOT NULL)
AND (@LineslipReference='' OR pm.LineslipReference LIKE '%' +@LineslipReference +'%')
AND (@PlacingBroker='' OR @PlacingBroker IN(pb.PlacingBrokerName,pb.PlacingBrokerShortName))

SELECT 
	c.ClaimKey,
	IncurredCnvUSD=ISNULL(t.IncurredCnvUSD,0)*-1
INTO #CT
FROM tblDimClaim c
LEFT OUTER JOIN (
	SELECT
		ClaimKey,
		IncurredCnvUSD=SUM(ISNULL(t.ValueCnvUSD1,0))*-1
	FROM vwFactTransaction t 
	INNER JOIN tblDimAccount a ON a.AccountKey=t.AccountKey
	INNER JOIN tblDimAccountGroup ag ON ag.AccountGroupKey=a.AccountGroupKey
	INNER JOIN #TPM tpm ON tpm.PolicyMarketKey=t.PolicyMarketKey
	WHERE AccountGroupCode IN('ClmOs','ClmPay')
	AND ISNULL(t.OutwardsRITypeKey,0)=0
	GROUP BY ClaimKey
) t ON t.ClaimKey=c.ClaimKey
INNER JOIN tblDimPolicy p ON p.PolicyKey=c.PolicyKey
INNER JOIN tblDimPolicyHeader ph ON ph.PolicyHeaderKey=p.PolicyHeaderKey
LEFT OUTER JOIN vwDimBinder b ON b.BinderKey=p.BinderKey
LEFT OUTER JOIN tblDimEvent e ON e.EventKey=c.EventKey
LEFT OUTER JOIN tblDimCatastrophe ecat ON ecat.CatastropheKey=e.CatastropheKey
LEFT OUTER JOIN tblDimClient cc ON cc.ClientKey=c.IndemnityClientKey
LEFT OUTER JOIN tblDimClass cl ON cl.ClassKey=p.ClassKey
LEFT OUTER JOIN tblDimProduct pr ON pr.ProductKey=p.ProductKey
WHERE (@UMR='' OR p.UniqueMarketReference=@UMR)
AND (@ClaimReference='' OR c.ClaimReference=@ClaimReference)
AND (@EventName='' OR e.EventName LIKE '%' + @EventName + '%')
AND (@CatCode='' OR ecat.CatastropheCode IN(SELECT TRIM(Value) FROM string_split(@CatCode,'')))
AND (@Threshold='' OR IncurredCnvUSD>=@Threshold)
AND (@IndemnityClient='' OR cc.ClientName LIKE '%' + @IndemnityClient + '%')
AND (@BinderUMR='' OR b.BinderUMR =@BinderUMR)
AND (ISNULL(@Class,'')='' OR cl.ClassName IN(SELECT TRIM(value) FROM string_split(@Class,',')))
AND (ISNULL(@ExClass,'')='' OR ISNULL(cl.ClassName,'') NOT IN(SELECT TRIM(value) FROM string_split(@ExClass,',')))
AND (ISNULL(@ProductType,'')='' OR pr.ProductName IN(SELECT TRIM(value) FROM string_split(@ProductType,',')))
AND (ISNULL(@ExProductType,'')='' OR ISNULL(pr.ProductName,'') NOT IN(SELECT TRIM(value) FROM string_split(@ExProductType,',')))
AND (@PolicyInceptionDateFrom='' OR ph.PolicyInceptionDate>=@PolicyInceptionDateFrom)
AND (@PolicyInceptionDateTo='' OR ph.PolicyInceptionDate<=@PolicyInceptionDateTo)
AND ISNULL(c.OutwardsRITypeKey,0)=0
--Remove 0 incurred claims from list
AND (ISNULL(@ZeroClosed,'')=''
OR (ISNULL(@ZeroClosed,'')='N' AND NOT (ISNULL(t.IncurredCnvUSD,0)=0 AND c.ClaimStatus='Closed'))
OR (ISNULL(@ZeroClosed,'')='Y' AND (ISNULL(t.IncurredCnvUSD,0)=0 AND c.ClaimStatus='Closed')))
--Only include claims that have a backlaod movement
AND (@LegacyBackload='' OR @LegacyBackload='N' 
OR (@LegacyBackload='Y' AND EXISTS (
	SELECT 1
	FROM tblFactTransactionHeader th
	INNER JOIN tblDimTransactionGroup tg ON tg.TransactionGroupKey=th.TransactionGroupKey
	INNER JOIN tblFactTransactionLine tl ON tl.TransactionKey=th.TransactionKey
	WHERE tg.TransactionGroupCode IN('ClmMoveLegacy','ClmMoveBackload')
	AND tl.ClaimKey=c.ClaimKey)
))

CREATE INDEX ix1 ON #CT (ClaimKey)

SELECT
	ClaimKey,
	CarrierKey,
	PolicyMarketKey,
	OrigCurrencyKey,
	SettCurrencyKey,
	ClmOsIndmOrig,
	ClmOsFeeOrig,
	ClmPayIndmOrig,
	ClmPayFeeOrig,
	ClmOsIndmSett,
	ClmOsFeeSett,
	ClmPayIndmSett,
	ClmPayFeeSett,
	ClmOsIndmCnvUSD,
	ClmOsFeeCnvUSD,
	ClmPayIndmCnvUSD,
	ClmPayFeeCnvUSD,
	ClmReceivableCnvUSD,
	ClmReceivedCnvUSD,
	ClmReceivableBackloadCnvUSD,
	ClmReceivedBackloadCnvUSD
INTO #FacilityTotals
FROM (
SELECT
	ClaimKey,
	CarrierKey,
	PolicyMarketKey,
	OrigCurrencyKey,
	SettCurrencyKey,

	ClmOsIndmOrig,
	ClmOsFeeOrig,
	ClmPayIndmOrig,
	ClmPayFeeOrig,

	ClmOsIndmSett,
	ClmOsFeeSett,
	ClmPayIndmSett,
	ClmPayFeeSett,

	ClmOsIndmCnvUSD,
	ClmOsFeeCnvUSD,
	ClmPayIndmCnvUSD,
	ClmPayFeeCnvUSD,
	ClmReceivableCnvUSD,
	ClmReceivedCnvUSD,
	ClmReceivableBackloadCnvUSD,
	ClmReceivedBackloadCnvUSD
	
FROM (
SELECT
	ClaimKey,
	CarrierKey,
	PolicyMarketKey,
	OrigCurrencyKey,
	SettCurrencyKey,
	ClmOsIndmOrig=SUM(ClmOsIndmOrig),
	ClmOsFeeOrig=SUM(ClmOsFeeOrig),
	ClmPayIndmOrig=SUM(ClmPayIndmOrig),
	ClmPayFeeOrig=SUM(ClmPayFeeOrig),
	ClmOsIndmSett=SUM(ClmOsIndmSett),
	ClmOsFeeSett=SUM(ClmOsFeeSett),
	ClmPayIndmSett=SUM(ClmPayIndmSett),
	ClmPayFeeSett=SUM(ClmPayFeeSett),
	ClmOsIndmCnvUSD=SUM(ClmOsIndmCnvUSD),
	ClmOsFeeCnvUSD=SUM(ClmOsFeeCnvUSD),
	ClmPayIndmCnvUSD=SUM(ClmPayIndmCnvUSD),
	ClmPayFeeCnvUSD=SUM(ClmPayFeeCnvUSD),
	ClmReceivableCnvUSD=SUM(ClmReceivableCnvUSD),
	ClmReceivedCnvUSD=SUM(ClmReceivedCnvUSD),
	ClmReceivableBackloadCnvUSD=SUM(ClmReceivableBackloadCnvUSD),
	ClmReceivedBackloadCnvUSD=SUM(ClmReceivedBackloadCnvUSD)
FROM (
SELECT
	tl.ClaimKey,
	CarrierKey=IIF(@ShowFacility='True' OR @GroupBy IN('Carrier','Lineslip','Contract Year'),tl.CarrierKey,null),
	PolicyMarketKey=IIF(@ShowFacility='True' OR @GroupBy IN('Carrier','Lineslip','Contract Year'),tl.PolicyMarketKey,null),
	OrigCurrencyKey,
	SettCurrencyKey,
	ClmOsIndmOrig=IIF(AccountCode LIKE 'ClmOsIndm%',ValueOrig,0)*-1,
	ClmOsFeeOrig=IIF(AccountCode LIKE 'ClmOsFee%',ValueOrig,0)*-1,
	ClmPayIndmOrig=IIF(AccountCode LIKE 'ClmPayIndm%' OR AccountCode='ClmPayLossFund',ValueOrig,0)*-1,
	ClmPayFeeOrig=IIF(AccountCode LIKE 'ClmPayFee%',ValueOrig,0)*-1,
	ClmOsIndmSett=IIF(AccountCode LIKE 'ClmOsIndm%',ValueSett,0)*-1,
	ClmOsFeeSett=IIF(AccountCode LIKE 'ClmOsFee%',ValueSett,0)*-1,
	ClmPayIndmSett=IIF(AccountCode LIKE 'ClmPayIndm%' OR AccountCode='ClmPayLossFund',ValueSett,0)*-1,
	ClmPayFeeSett=IIF(AccountCode LIKE 'ClmPayFee%',ValueSett,0)*-1,
	ClmOsIndmCnvUSD=IIF(AccountCode LIKE 'ClmOsIndm%',ValueCnvUSD1,0)*-1,
	ClmOsFeeCnvUSD=IIF(AccountCode LIKE 'ClmOsFee%',ValueCnvUSD1,0)*-1,
	ClmPayIndmCnvUSD=IIF(AccountCode LIKE 'ClmPayIndm%' OR AccountCode='ClmPayLossFund',ValueCnvUSD1,0)*-1,
	ClmPayFeeCnvUSD=IIF(AccountCode LIKE 'ClmPayFee%',ValueCnvUSD1,0)*-1,
	ClmReceivableCnvUSD=IIF(AccountCode='ClmReceivableUW',ValueCnvUSD1,0),
	ClmReceivedCnvUSD=IIF(AccountCode='ClmReceivedUW',ValueCnvUSD1,0)*-1,
	ClmReceivableBackloadCnvUSD=IIF(AccountCode='ClmReceivableUWBackload',ValueCnvUSD1,0),
	ClmReceivedBackloadCnvUSD=IIF(AccountCode='ClmReceivedUWBackload',ValueCnvUSD1,0)*-1

FROM vwFactTransaction tl
INNER JOIN tblDimTransactionGroup tg ON tg.TransactionGroupKey=tl.TransactionGroupKey
INNER JOIN #CT ct ON ct.Claimkey=tl.ClaimKey
INNER JOIN #TPM tpm ON tpm.PolicyMarketKey=tl.PolicyMarketKey
INNER JOIN tblDimClaim c ON c.ClaimKey=tl.ClaimKey
INNER JOIN tblDimPolicy p ON p.PolicyKey=c.PolicyKey
INNER JOIN tblDimAccount a ON a.AccountKey=tl.AccountKey
INNER JOIN tblDimAccountGroup ag ON ag.AccountGroupKey=a.AccountGroupKey 
--INNER JOIN tblDimCarrier c0 ON c0.CarrierKey=tl.CarrierKey
WHERE AccountGroupCode IN('ClmOs','ClmPay','ClmReceivableUW','ClmReceivableUWBackload') 
AND (@Period='' OR tl.AccountingPeriod<=@Period)
AND ISNULL(tl.OutwardsRITypeKey,0)=0
) qry
GROUP BY 
	CarrierKey,
	PolicyMarketKey,
	OrigCurrencyKey,
	SettCurrencyKey,
	ClaimKey
) qry2
) qry3

CREATE INDEX ix1 ON #FacilityTotals (ClaimKey)

SELECT
	ClaimKey,
	OrigCurrencyKey,
	SettCurrencyKey,
	MktClmOsIndmOrig,
	MktClmOsFeeOrig,
	MktClmPayIndmOrig,
	MktClmPayFeeOrig,

	MktClmOsIndmSett,
	MktClmOsFeeSett,
	MktClmPayIndmSett,
	MktClmPayFeeSett,

	MktClmOsIndmCnvUSD,
	MktClmOsFeeCnvUSD,
	MktClmPayIndmCnvUSD,
	MktClmPayFeeCnvUSD

INTO #MarketTotals
FROM (
SELECT
	ClaimKey,
	OrigCurrencyKey,
	SettCurrencyKey,
	MktClmOsIndmOrig,
	MktClmOsFeeOrig,
	MktClmPayIndmOrig,
	MktClmPayFeeOrig,

	MktClmOsIndmSett,
	MktClmOsFeeSett,
	MktClmPayIndmSett,
	MktClmPayFeeSett,

	MktClmOsIndmCnvUSD,
	MktClmOsFeeCnvUSD,
	MktClmPayIndmCnvUSD,
	MktClmPayFeeCnvUSD
	
FROM (
SELECT
	ClaimKey,
	OrigCurrencyKey,
	SettCurrencyKey,
	--TransactionDate,
	MktClmOsIndmOrig=SUM(MktClmOsIndmOrig),
	MktClmOsFeeOrig=SUM(MktClmOsFeeOrig),
	MktClmPayIndmOrig=SUM(MktClmPayIndmOrig),
	MktClmPayFeeOrig=SUM(MktClmPayFeeOrig),

	MktClmOsIndmSett=SUM(MktClmOsIndmSett),
	MktClmOsFeeSett=SUM(MktClmOsFeeSett),
	MktClmPayIndmSett=SUM(MktClmPayIndmSett),
	MktClmPayFeeSett=SUM(MktClmPayFeeSett),

	MktClmOsIndmCnvUSD=SUM(MktClmOsIndmCnvUSD),
	MktClmOsFeeCnvUSD=SUM(MktClmOsFeeCnvUSD),
	MktClmPayIndmCnvUSD=SUM(MktClmPayIndmCnvUSD),
	MktClmPayFeeCnvUSD=SUM(MktClmPayFeeCnvUSD)
FROM (
SELECT
	tl.ClaimKey,
	OrigCurrencyKey,
	SettCurrencyKey,
	MktClmOsIndmOrig=IIF(AccountCode LIKE 'MktClmOsIndm%',ValueOrig,0)*-1,
	MktClmOsFeeOrig=IIF(AccountCode LIKE 'MktClmOsFee%',ValueOrig,0)*-1,
	MktClmPayIndmOrig=IIF(AccountCode LIKE 'MktClmPayIndm%' OR AccountCode='MktClmPayLossFund',ValueOrig,0)*-1,
	MktClmPayFeeOrig=IIF(AccountCode LIKE 'MktClmPayFee%',ValueOrig,0)*-1,

	MktClmOsIndmSett=IIF(AccountCode LIKE 'MktClmOsIndm%',ValueSett,0)*-1,
	MktClmOsFeeSett=IIF(AccountCode LIKE 'MktClmOsFee%',ValueSett,0)*-1,
	MktClmPayIndmSett=IIF(AccountCode LIKE 'MktClmPayIndm%' OR AccountCode='MktClmPayLossFund',ValueSett,0)*-1,
	MktClmPayFeeSett=IIF(AccountCode LIKE 'MktClmPayFee%',ValueSett,0)*-1,

	MktClmOsIndmCnvUSD=IIF(AccountCode LIKE 'MktClmOsIndm%',ValueCnvUSD1,0)*-1,
	MktClmOsFeeCnvUSD=IIF(AccountCode LIKE 'MktClmOsFee%',ValueCnvUSD1,0)*-1,
	MktClmPayIndmCnvUSD=IIF(AccountCode LIKE 'MktClmPayIndm%' OR AccountCode='MktClmPayLossFund',ValueCnvUSD1,0)*-1,
	MktClmPayFeeCnvUSD=IIF(AccountCode LIKE 'MktClmPayFee%',ValueCnvUSD1,0)*-1
FROM vwFactTransaction tl
INNER JOIN tblDimTransactionGroup tg ON tg.TransactionGroupKey=tl.TransactionGroupKey
INNER JOIN #CT ct ON ct.Claimkey=tl.ClaimKey
INNER JOIN tblDimClaim c ON c.ClaimKey=tl.ClaimKey
INNER JOIN tblDimPolicy p ON p.PolicyKey=c.PolicyKey
INNER JOIN tblDimAccount a ON a.AccountKey=tl.AccountKey
INNER JOIN tblDimAccountGroup ag ON ag.AccountGroupKey=a.AccountGroupKey 
WHERE AccountGroupCode IN(
	'MktClmOs',
	'MktClmPay'
)
AND ISNULL(tl.OutwardsRITypeKey,0)=0
AND (@Period='' OR tl.AccountingPeriod<=@Period)
AND (@UMR='' OR p.UniqueMarketReference=@UMR)
AND (@ClaimReference='' OR @ClaimReference=c.ClaimReference)
) qry
GROUP BY 
	ClaimKey,
	OrigCurrencyKey,
	SettCurrencyKey

) qry2
) qry3

CREATE INDEX #ix1 ON #MarketTotals (ClaimKey)

SELECT 
	Carrier=ISNULL(c0.CarrierName,''),
	LineslipReference=ISNULL(pm.LineslipReference,''),
	GroupBy=CASE @GroupBy
		WHEN 'Lineslip' THEN IIF(ISNULL(pm.LineslipReference,'')='',c0.CarrierShortName,pm.LineslipReference)
		WHEN 'Event' THEN ISNULL(e.EventName,' No Event')
		WHEN 'Product' THEN pr.ProductName
		WHEN 'Carrier' THEN c0.CarrierShortName
		WHEN 'Contract Year' THEN 
			CASE WHEN ISNULL(cnt.ContractInceptionDate,ncnt.ContractInceptionDate) IS NULL THEN ISNULL(c0.CarrierShortName,'Unknown')
			ELSE CAST(YEAR(ISNULL(cnt.ContractInceptionDate,ncnt.ContractInceptionDate)) AS NVARCHAR)
		END
		ELSE ''
	END,
	ContractInceptionDate=ISNULL(cnt.ContractInceptionDate,ncnt.ContractInceptionDate),
	ReinsuredName=p.ReinsuredName,
	UMR=p.UniqueMarketReference,
	AccountName=c.ClaimInsuredName,
	ROKPolicyRef=ph.PolicyReference,
	p.SectionCode,
	UW=u.UnderwriterName,
	c.UniqueClaimReference,
	ClaimReference=c.ClaimReference,
	BrokerUCR=BrokerReference,
	Client=cc.ClientName,
	IndustryCode=pr.ProductName,
	Class=cl.ClassName,
	InceptionDate=CONVERT(DATE,ph.PolicyInceptionDate),
	ExpiryDate=CONVERT(DATE,ph.PolicyExpiryDate),
	Location=c.LossLocation,
	Country='',
	OpenedDate=CONVERT(DATE,c.ClaimStatusOpened),
	DateOfLoss=CONVERT(DATE,c.LossDateFrom),
	DateReportedToRUw=CONVERT(DATE,c.ClaimNotificationDate),
	ClaimStatusToROK=ISNULL(c.ClaimStatus,''),
	LossName=c.LossName,
	LossCircumstances=c.ClaimDescription,
	EventCat=ISNULL(ISNULL(ecat.CatastropheCode,e.EventName),''),
	OriginalCurrency=oc.CurrencyCode,
	SettlementCurrency=sc.CurrencyCode,
	LastOSMovementDate=c.LastMovementDateOS,
	LastPaidMovementDate=c.LastMovementDatePaid,

---- Market Movements
---- Paid
	MktPdIndmSCC=mt.MktClmPayIndmSett,
	MktPdFeesSCC=mt.MktClmPayFeeSett,
	MktPdTotlSCC=mt.MktClmPayFeeSett+mt.MktClmPayIndmSett,
---- OS
	MktOSIndmSCC=mt.MktClmOSIndmSett,
	MktOSFeesSCC=mt.MktClmOSFeeSett,
	MktOSTotlSCC=mt.MktClmOSFeeSett+mt.MktClmOSIndmSett,
---- Incurred
	MktIncSCC=mt.MktClmOsFeeSett+mt.MktClmOsIndmSett+mt.MktClmPayFeeSett+mt.MktClmPayIndmSett,

	ROKSignedLine=CASE
		WHEN fo.PolicyKey IS NULL THEN CONVERT(DECIMAL(16,8),p.PolicyLine*ISNULL(pm.Line,1))
		ELSE fo.Line
	END,
---- Paid
	PdIndmSCC=CASE
		WHEN fo.PolicyKey IS NULL THEN ct.ClmPayIndmSett
		ELSE mt.MktClmPayIndmSett*fo.Line
	END,
	PdFeesSCC=CASE
		WHEN fo.PolicyKey IS NULL THEN ct.ClmPayFeeSett
		ELSE mt.MktClmPayFeeSett*fo.Line
	END,
	PdTotlSCC=CASE
		WHEN fo.PolicyKey IS NULL THEN ct.ClmPayFeeSett+ct.ClmPayIndmSett
		ELSE (mt.MktClmPayFeeSett+mt.MktClmPayIndmSett)*fo.Line
	END,
---- OS
	OSIndmSCC=CASE
		WHEN fo.PolicyKey IS NULL THEN ct.ClmOSIndmSett
		ELSE mt.MktClmOSIndmSett*fo.Line
	END,
	OSFeesSCC=CASE
		WHEN fo.PolicyKey IS NULL THEN ct.ClmOSFeeSett
		ELSE mt.MktClmOSFeeSett*fo.Line
	END,
	OSTotlSCC=CASE
		WHEN fo.PolicyKey IS NULL THEN ct.ClmOSFeeSett+ct.ClmOSIndmSett
		ELSE (mt.MktClmOSFeeSett+mt.MktClmOSIndmSett)*fo.Line
	END,
---- Incurred
	IncSCC=CASE
		WHEN fo.PolicyKey IS NULL THEN ct.ClmOsFeeSett+ct.ClmOsIndmSett+ct.ClmPayFeeSett+ct.ClmPayIndmSett
		ELSE (mt.MktClmOsFeeSett+mt.MktClmOsIndmSett+mt.MktClmPayFeeSett+mt.MktClmPayIndmSett)*fo.Line
	END,

	OSTotlCnvUSD=CASE
		WHEN fo.PolicyKey IS NULL THEN ct.ClmOsFeeCnvUSD+ct.ClmOsIndmCnvUSD
		ELSE (mt.MktClmOSFeeCnvUSD+mt.MktClmOSIndmCnvUSD)*fo.Line
	END,
	PdTotlCnvUSD=CASE
		WHEN fo.PolicyKey IS NULL THEN ct.ClmPayFeeCnvUSD+ct.ClmPayIndmCnvUSD
		ELSE (mt.MktClmPayFeeCnvUSD+mt.MktClmPayIndmCnvUSD)*fo.Line
	END,

	IncTotlCnvUSD=CASE
		WHEN fo.PolicyKey IS NULL THEN ct.ClmOsFeeCnvUSD+ct.ClmOsIndmCnvUSD+ct.ClmPayFeeCnvUSD+ct.ClmPayIndmCnvUSD
		ELSE (mt.MktClmOsFeeCnvUSD+mt.MktClmOsIndmCnvUSD+mt.MktClmPayFeeCnvUSD+mt.MktClmPayIndmCnvUSD)*fo.Line
	END,

	ReceivableCnvUSD=ct.ClmReceivableCnvUSD,
	ReceivedCnvUSD=ct.ClmReceivedCnvUSD,
	DueCnvUSD=ct.ClmReceivableCnvUSD-ct.ClmReceivedCnvUSD,

	ReceivableBackloadCnvUSD=ct.ClmReceivableBackloadCnvUSD,
	ReceivedBackloadCnvUSD=ct.ClmReceivedBackloadCnvUSD,
	DueBackloadCnvUSD=ct.ClmReceivableBackloadCnvUSD-ct.ClmReceivedBackloadCnvUSD,
   	  
	DayBookReference,
	n.Narrative,
	c.SharePointURL,
	WatchList=CASE WHEN c.WatchList=1 THEN 'Yes' END,
	c.ComplexTriageNames,
	ss.SourceSystemName
FROM #FacilityTotals ct
INNER JOIN #MarketTotals mt
	ON mt.ClaimKey=ct.ClaimKey
	AND mt.OrigCurrencyKey=ct.OrigCurrencyKey
	AND mt.SettCurrencyKey=ct.SettCurrencyKey
INNER JOIN tblDimCurrency oc ON oc.CurrencyKey=ct.OrigCurrencyKey
INNER JOIN tblDimCurrency sc ON sc.CurrencyKey=ct.SettCurrencyKey
INNER JOIN tblDimClaim c ON c.ClaimKey=ct.ClaimKey
LEFT OUTER JOIN #FrontingOverride fo ON fo.PolicyKey=c.PolicyKey
LEFT OUTER JOIN tblDimClient cc ON cc.ClientKey=c.IndemnityClientKey
LEFT OUTER JOIN tblDimPolicyMarket pm ON pm.PolicyMarketKey=ct.PolicyMarketKey
LEFT OUTER JOIN vwDimCarrier c0 ON c0.CarrierKey=ct.CarrierKey
LEFT OUTER JOIN tblDimEvent e ON e.EventKey=c.EventKey
LEFT OUTER JOIN tblDimCatastrophe ecat ON ecat.CatastropheKey=e.CatastropheKey
INNER JOIN tblDimPolicy p ON p.PolicyKey=c.PolicyKey
INNER JOIN tblDimSourceSystem ss ON ss.SourceSystemKey=p.SourceSystemKey
INNER JOIN tblDimPolicyHeader ph ON ph.PolicyHeaderKey=p.PolicyHeaderKey
LEFT OUTER JOIN tblDimProduct pr ON pr.ProductKey=p.ProductKey
LEFT OUTER JOIN tblDimClass cl ON cl.ClassKey=p.ClassKey
LEFT OUTER JOIN tblDimUnderwriter u ON u.UnderwriterKey=p.UnderwriterKey
LEFT OUTER JOIN vwDimContract cnt ON cnt.ContractKey=pm.ContractKey
LEFT OUTER JOIN vwDimContract ncnt ON ncnt.ContractKey=pm.NewContractKey
LEFT OUTER JOIN (
	SELECT
		ClaimKey,
		Narrative=STRING_AGG(Narrative,',') WITHIN GROUP (ORDER BY EntryDate DESC)
	FROM (
		SELECT
			ClaimKey,
			EntryDate,
			Narrative=CONVERT(NVARCHAR(MAX),FORMAT(EntryDate,'dd-MMM-yyyy HH:mm') + ' ' + EntryUser + ': ' + Narrative)
		FROM tblDimClaimNarrative
	) cn
	GROUP BY ClaimKey
) n ON n.ClaimKey=c.ClaimKey

WHERE 1=1

ORDER BY GroupBy,UMR,ClaimReference,OriginalCurrency,SettlementCurrency,Carrier

DROP TABLE IF EXISTS #TPM
DROP TABLE IF EXISTS #CT
DROP TABLE IF EXISTS #FacilityTotals
DROP TABLE IF EXISTS #MarketTotals
DROP TABLE IF EXISTS #FrontingOverride
DROP TABLE IF EXISTS #CarrierList
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
11,623 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Amira Bedhiafi 33,071 Reputation points Volunteer Moderator
    2024-08-15T20:40:54.74+00:00

    To convert this SQL query with temporary tables and variables into something that can be used in Azure Data Factory (ADF), you will need to make several changes. ADF doesn't support temporary tables (#tempTable), table variables, or procedural logic like DECLARE, IF, and BEGIN...END in its native SQL processing. Instead, you'll need to rewrite the query to either use Common Table Expressions (CTEs), permanent tables, or inline subqueries.

    Here’s a general approach you can take:

    1. Replace Variables: ADF SQL doesn't support variables in the same way as T-SQL. Instead, you would need to parameterize the query in ADF, using pipeline parameters or variables, and pass them into the SQL query.
    2. Replace Temporary Tables: Use CTEs (Common Table Expressions) or derived tables (subqueries) instead of temporary tables. If the temporary tables are being reused multiple times within the query, consider breaking the query into smaller steps, each writing to a permanent staging table in the database.

    Original:

    
DECLARE 

    @Carrier NVARCHAR(256)='Certus'

SELECT

    CarrierKey

INTO #CarrierList

FROM tblDimCarrier c

INNER JOIN (

    SELECT CarrierName=Field2

    FROM IC.tblLookup

    WHERE LookupFilter='CarrierList'

    AND Field1=@Carrier

) l ON l.CarrierName=c.CarrierName

    Refactored for ADF:

    
WITH CarrierList AS (

    SELECT c.CarrierKey

    FROM tblDimCarrier c

    INNER JOIN (

        SELECT CarrierName=Field2

        FROM IC.tblLookup

        WHERE LookupFilter='CarrierList'

        AND Field1='Certus'  -- Replace this with a parameter in ADF

    ) l ON l.CarrierName=c.CarrierName

)

    Replacing Another Temporary Table:

    Original:

    
SELECT

    pm.PolicyMarketKey

INTO #TPM

FROM tblDimPolicyMarket pm

INNER JOIN vwDimCarrier c ON c.CarrierKey=pm.CarrierKey

LEFT OUTER JOIN #CarrierList cl ON cl.CarrierKey=pm.CarrierKey

    Refactored:

    
WITH TPM AS (

    SELECT

        pm.PolicyMarketKey

    FROM tblDimPolicyMarket pm

    INNER JOIN vwDimCarrier c ON c.CarrierKey=pm.CarrierKey

    LEFT OUTER JOIN CarrierList cl ON cl.CarrierKey=pm.CarrierKey

)

    Refactor the Entire Query:

    • Each #tempTable should be converted into a CTE.
    • Complex logic like IF...BEGIN...END might need to be handled by breaking the logic into separate queries or handled by ADF pipeline conditions and branching.
    • Final SELECT statements should reference the CTEs.

    Given the complexity of your query, here is a very high-level refactor:

    
WITH CarrierList AS (

    -- CarrierList CTE

    SELECT c.CarrierKey

    FROM tblDimCarrier c

    INNER JOIN (

        SELECT CarrierName=Field2

        FROM IC.tblLookup

        WHERE LookupFilter='CarrierList'

        AND Field1='Certus' -- Pass as parameter in ADF

    ) l ON l.CarrierName=c.CarrierName

),

TPM AS (

    -- TPM CTE

    SELECT pm.PolicyMarketKey

    FROM tblDimPolicyMarket pm

    INNER JOIN vwDimCarrier c ON c.CarrierKey=pm.CarrierKey

    LEFT OUTER JOIN CarrierList cl ON cl.CarrierKey=pm.CarrierKey

),

CT AS (

    -- CT CTE (similar logic applied as above)

)

-- Final select referencing CTEs

SELECT 

    -- Your final select logic

FROM TPM

INNER JOIN CT ON CT.ClaimKey = TPM.PolicyMarketKey

-- Add other joins and conditions as necessary

    You'll need to adapt this structure to your entire query, carefully replacing each temporary table with a CTE and ensuring that any variables are handled through parameters passed into the query via ADF.

    https://learn.microsoft.com/en-us/answers/questions/1279999/can-we-create-temporary-table-in-adf

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.