Gowtham Ramamoorthy Gowtham Ramamoorthy - 4 months ago 8
SQL Question

Replacing values in a string using recursion in SQL

I'm trying to repalce values into a string based on the column 'Yearmonth' and 'Timedimensiondate' in the 'Mathlogictable' shown in below code....

I was able to do it successfully but now I need to replace the string with zero values which are not there in the Mathlogictable.

For eg: if you execute the below code we can find the first row having values for import-consumptionqty1(314) but not for import consumtionQty(1539)..... like

YEARMONTH OUTPUTFORMULA TIMEDIMENSIONDATE
CV(N1) 2002: 10 236846.000 + Import - Consumption qty1(1539) 2002-10-01


I need to replace the strings which doesn't have values to zero in the formula column...... such that my output should be like

Yearmonth Formula TimeDimensionDate
CV(N1) 2002: 10 236846.000 + 0 2002-10-01


How can I achieve this ?

Please note that i have created a tem table called #tablevaluedfunction and inserted the output in that table.... and made a join.....

Below is the code i'm trying

BEGIN
CREATE TABLE #MathLogicTable
(
IDNUM INTEGER IDENTITY(1,1),
FORMULA Varchar(160),
INPUTName varchar(160),
AttributeValue Decimal(15,3),
yearmonth varchar(160),
TimeDimensionDate date
)

INSERT INTO #MathLogicTable (FORMULA,INPUTName,AttributeValue,yearmonth,TimeDimensionDate)
VALUES ('Import - Consumption qty1(314) + Import - Consumption qty1(1539)','Import - Consumption qty1(1539)',855,'CV(N1) 2002: 1','2002-01-01')
,('Import - Consumption qty1(314) + Import - Consumption qty1(1539)','Import - Consumption qty1(1539)',4860,'CV(N1) 2002: 2','2002-02-01')
,('Import - Consumption qty1(314) + Import - Consumption qty1(1539)','Import - Consumption qty1(1539)',21683,'CV(N1) 2002: 3','2002-03-01')
,('Import - Consumption qty1(314) + Import - Consumption qty1(1539)','Import - Consumption qty1(1539)',12800,'CV(N1) 2002: 4','2002-04-01')
,('Import - Consumption qty1(314) + Import - Consumption qty1(1539)','Import - Consumption qty1(1539)',802,'CV(N1) 2002: 5','2002-05-01')
,('Import - Consumption qty1(314) + Import - Consumption qty1(1539)','Import - Consumption qty1(314)',250616,' CV(N1) 2002: 6 ','2002-06-01')
,('Import - Consumption qty1(314) + Import - Consumption qty1(1539)','Import - Consumption qty1(314)',333064,' CV(N1) 2002: 7 ','2002-07-01')
,('Import - Consumption qty1(314) + Import - Consumption qty1(1539)','Import - Consumption qty1(314)',131397,' CV(N1) 2002: 8 ','2002-08-01')
,('Import - Consumption qty1(314) + Import - Consumption qty1(1539)','Import - Consumption qty1(314)',294510,' CV(N1) 2002: 9 ','2002-09-01')
,('Import - Consumption qty1(314) + Import - Consumption qty1(1539)','Import - Consumption qty1(314)',236846,' CV(N1) 2002: 10 ','2002-10-01')
,('Import - Consumption qty1(314) + Import - Consumption qty1(1539)','Import - Consumption qty1(314)',397183,' CV(N1) 2002: 11 ','2002-11-01')
,('Import - Consumption qty1(314) + Import - Consumption qty1(1539)','Import - Consumption qty1(314)',128616,' CV(N1) 2002: 12 ','2002-12-01')


;with DataSource1 AS
(
SELECT ROW_NUMBER() OVER(PARTITION BY [YEARMONTH] ORDER BY [IDNUM]) AS [ReplacementOrderID]
,[YEARMONTH]
,[formula]
,[TimeDimensionDate]
,[INPUTNAME] AS [ReplacementString]
,AttributeValue AS [ReplacementValue]
FROM #MathLogicTable
)

,RecursiveDataSource AS
(
SELECT [ReplacementOrderID]
,[YEARMONTH]
,[TimeDimensionDate]
,CASE WHEN ReplacementValue IS NULL THEN REPLACE([formula], [ReplacementString], 0) ELSE REPLACE([formula], [ReplacementString], [ReplacementValue]) END AS [formula]
FROM DataSource1
WHERE [ReplacementOrderID] = 1
UNION ALL
SELECT DS.[ReplacementOrderID]
,DS.[YEARMONTH]
,DS.TimeDimensionDate
,CASE WHEN DS.[ReplacementValue] IS NULL THEN REPLACE(RDS.[formula], DS.[ReplacementString], 0) ELSE REPLACE(RDS.[formula], DS.[ReplacementString], DS.[ReplacementValue]) END AS [formula]

FROM RecursiveDataSource RDS
INNER JOIN DataSource1 DS
ON RDS.[ReplacementOrderID] + 1 = DS.[ReplacementOrderID]
AND RDS.[YEARMONTH] = DS.[YEARMONTH]
)



SELECT RDS.[YEARMONTH]
,RDS.[formula]
,RDS.[TimeDimensionDate]
FROM RecursiveDataSource RDS
INNER JOIN
(
SELECT [YEARMONTH]
,MAX([ReplacementOrderID]) AS [ReplacementOrderID]
FROM DataSource1
GROUP BY [YEARMONTH]
) DS
ON RDS.[YEARMONTH] = DS.[YEARMONTH]
AND RDS.[ReplacementOrderID] = DS.[ReplacementOrderID]
ORDER BY RDS.[YEARMONTH]
option (maxrecursion 0);


DROP TABLE #MathLogicTable

END


Thanks

Answer
;WITH cteSplitFormula AS (
    SELECT
       IDNUM
       ,TimeDimensionDate
       ,CAST(LTRIM(RTRIM(LEFT(REPLACE(Formula,'Import -','ImportReplaced'), PATINDEX('%[+-\*/^]%',REPLACE(Formula,'Import -','ImportReplaced')+'+')-1))) AS VARCHAR(5000)) AS FormulaItem
       ,STUFF(REPLACE(Formula,'Import -','ImportReplaced'), 1, PATINDEX('%[+-\*/^]%',REPLACE(Formula,'Import -','ImportReplaced')+'+'), '')  as RemainingFromula
    FROM
       #MathLogicTable

    UNION ALL
    SELECT
       IDNUM
       ,TimeDimensionDate
       ,CAST(LTRIM(RTRIM(LEFT(RemainingFromula, PATINDEX('%[+-\*/^]%',RemainingFromula+'+')-1))) AS VARCHAR(5000)) AS FormulaItem
       ,STUFF(RemainingFromula, 1, PATINDEX('%[+-\*/^]%',RemainingFromula+'+'), '')  as RemainingFromula
    FROM
       cteSplitFormula
    WHERE 
       LEN(RemainingFromula) > 0

)

, ctePartsValues AS (
    SELECT
       sf.IDNUM
       ,sf.TimeDimensionDate
       ,REPLACE(sf.FormulaItem,'ImportReplaced','Import -') AS FormulaItem
       ,ISNULL(m.AttributeValue,0) AS ReplacementValue
       ,ROW_NUMBER() OVER(PARTITION BY sf.IDNUM ORDER BY (SELECT 1)) AS [ReplacementOrderID]
    FROM
       cteSplitFormula sf
       FULL OUTER JOIN #MathLogicTable m
       ON REPLACE(sf.FormulaItem,'ImportReplaced','Import -') = LTRIM(RTRIM(m.INPUTName))
       AND sf.TimeDimensionDate = m.TimeDimensionDate
)

, cteRecursiveDataSource AS (
    SELECT
       m.IDNUM
       ,REPLACE(m.Formula,pv.FormulaItem,pv.ReplacementValue) as Formula
       ,pv.ReplacementOrderID
    FROM
       #MathLogicTable m
       INNER JOIN ctePartsValues pv
       ON m.IDNUM = pv.IDNUM
       AND pv.ReplacementOrderID = 1

    UNION ALL

    SELECT
       rds.IDNUM
       ,REPLACE(rds.Formula,pv.FormulaItem,pv.ReplacementValue) as Formula
       ,pv.ReplacementOrderID
    FROM
       cteRecursiveDataSource rds
       INNER JOIN ctePartsValues pv
       ON rds.IDNUM = pv.IDNUM
       AND rds.ReplacementOrderID + 1 = pv.ReplacementOrderID
)

, cteIdentifyRowWithLastReplacementStatement AS (
    SELECT
       *
       ,ROW_NUMBER() OVER (PARTITION BY IDNUM ORDER BY ReplacementOrderId DESC) as FinalRow
    FROM
       cteRecursiveDataSource
)

SELECT *
FROM
    cteIdentifyRowWithLastReplacementStatement
WHERE
    FinalRow = 1