March 2, 2017 at 3:27 am
I have two tables Tbl1 and Tbl2 having the format shown in the attached Excel File.
Is it possible to evaluate the expression in Tbl2 with corresponding values in Tbl1 for specific dates?
For example: For the date 2/27/2017
SC-1a / SC-1b would evaluate to 883/1025 = 0.8614 and
((SC-2a + SC-2b + SC-2c) - (SC-2d + SC-2e + SC-2f + SC-2g + SC-2h + SC-2i)) / (SC-2a + SC-2b + SC-2c) would evaluate to ((734+291+0) - (19+0+58+1+0+0)) / (734+291+0) = 0.9239
Searching on Google provided links dealing with evaluating mathematical expressions that had direct numbers in them. In this case, text in the expression needs to be replaced by corresponding numbers and then evaluated. I'm very new to SQL and any assistance would be appreciated. Thanks for your assistance.
I have also posted the same question at http://stackoverflow.com/questions/42532094/evaluating-a-text-expression-based-on-values-from-another-table
March 2, 2017 at 3:33 am
Could you perhaps provide the data in a consumable SQL format? Many of us are unable/not allowed to download untrusted Excel files, as we use the forums while at work.
Have a look at the link in my signature for details on how to achieve this.
Thanks.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
March 2, 2017 at 5:53 am
Your first problem is that you have tag names that include mathematical operators. How do you know whether SC-1 is "SC" minus 1, or just the tag "SC-1".
Once that can be solved, a solution should be possible. No guarantees that it will be pretty. If you provide some more details on what you are trying to achieve, a better solution could be suggested.
March 2, 2017 at 7:13 am
Well... I have a solution, it's dirty, it's not pretty,it's probably got an hideous query plan, but for the 2 examples you gave, it works. If you ever have more than 26 metric's though, it'll fall over.
Based on the data from Stackoverflow, as I can read that. Those with more expertise than me, please feel free to rip this apart 😎 :CREATE TABLE #Metrics (MetricID VARCHAR(5),
MetricDate DATE,
MetricValue INT);
CREATE TABLE #Calculations (CalculationID VARCHAR(4), --These shouldn't have the same name in both tables as they are not the same thing
Calculation VARCHAR(500));
GO
INSERT INTO #Metrics
VALUES
('SC-1a','20170227',883),
('SC-1b','20170227',1025),
('SC-2a','20170227',734),
('SC-2b','20170227',291),
('SC-2c','20170227',0),
('SC-2d','20170227',19),
('SC-2e','20170227',0),
('SC-2f','20170227',58),
('SC-2g','20170227',1),
('SC-2h','20170227',0),
('SC-2i','20170227',0);
INSERT INTO #Calculations
VALUES
('SC-1','SC-1a / SC-1b'),
('SC-2', '((SC-2a + SC-2b + SC-2c) - (SC-2d + SC-2e + SC-2f + SC-2g + SC-2h + SC-2i)) / (SC-2a + SC-2b + SC-2c)');
GO
DECLARE @DATE DATE = '27-Feb-2017';
WITH Replacements AS (
SELECT CalculationID, Calculation, Calculation As CastCalculation, 1 AS RN
FROM #Calculations C
--WHERE C.CalculationID = 'SC-1'
UNION ALL
SELECT R.CalculationID,
CAST(REPLACE(R.Calculation, M.MetricID, CAST(M.MetricValue AS varchar(100))) AS varchar(500)),
CAST(REPLACE(R.Calculation, M.MetricID, 'CAST(' + CAST(M.MetricValue AS varchar(100)) + ' AS decimal(12,4))') AS varchar(500)),
RN + 1
FROM Replacements R
JOIN #Metrics M ON PATINDEX('%' + M.MetricID + '%', R.Calculation) > 0
AND M.MetricDate = @Date
AND CHAR(RN + 96) = RIGHT(M.MetricID,1)
)
SELECT *
INTO #DSQL
FROM Replacements R
WHERE RN = (SELECT MAX(sq.RN) FROM Replacements sq WHERE sq.CalculationID = R.CalculationID);
DECLARE @SQL VARCHAR(MAX);
SELECT @SQL = STUFF((SELECT 'UNION ALL' + CHAR(10) + 'SELECT ''' + CalculationID + ''' AS CalculationID, ''' + Calculation + ''' AS Calculation, ' + CastCalculation + ' AS CalculatedMetricValue' + CHAR(10)
FROM #DSQL
FOR XML PATH('')),1,10,'')
SELECT @SQL;
EXEC (@SQL);
DROP TABLE #DSQL;
GO
--Clean up
DROP TABLE #Metrics;
DROP TABLE #Calculations;
P.s. Yes, I know I didn't need the Temporary table #DSQL, I could just put the result from the CTE Replacements. The main reason was I split it out at that point so that I could separate the thought process, then i just didn't bother merging the two back together. If i can be bothered (have time), I will fix that later.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
March 2, 2017 at 7:34 am
CREATE TABLE #Metrics (MetricID VARCHAR(5),
MetricDate DATE,
MetricValue INT);CREATE TABLE #Calculations (CalculationID VARCHAR(4), --These shouldn't have the same name in both tables as they are not the same thing
Calculation VARCHAR(500));
GO
INSERT INTO #Metrics
VALUES
('SC-1a','20170227',883),
('SC-1b','20170227',1025),
('SC-2a','20170227',734),
('SC-2b','20170227',291),
('SC-2c','20170227',0),
('SC-2d','20170227',19),
('SC-2e','20170227',0),
('SC-2f','20170227',58),
('SC-2g','20170227',1),
('SC-2h','20170227',0),
('SC-2i','20170227',0);
INSERT INTO #Calculations
VALUES
('SC-1','SC-1a / SC-1b'),
('SC-2', '((SC-2a + SC-2b + SC-2c) - (SC-2d + SC-2e + SC-2f + SC-2g + SC-2h + SC-2i)) / (SC-2a + SC-2b + SC-2c)');
GO
DECLARE @DATE DATE = '27-Feb-2017';
DECLARE @SQL VARCHAR(MAX);
WITH Replacements AS (
SELECT CalculationID, Calculation, Calculation As CastCalculation, 1 AS RN
FROM #Calculations C
UNION ALL
SELECT R.CalculationID,
CAST(REPLACE(R.Calculation, M.MetricID, CAST(M.MetricValue AS varchar(100))) AS varchar(500)),
CAST(REPLACE(R.Calculation, M.MetricID, 'CAST(' + CAST(M.MetricValue AS varchar(100)) + ' AS decimal(12,4))') AS varchar(500)),
RN + 1
FROM Replacements R
JOIN #Metrics M ON PATINDEX('%' + M.MetricID + '%', R.Calculation) > 0
AND M.MetricDate = @Date
AND CHAR(RN + 96) = RIGHT(M.MetricID,1)
)
SELECT @SQL = STUFF((SELECT 'UNION ALL' + CHAR(10) + 'SELECT ''' + CalculationID + ''' AS CalculationID, ''' + Calculation + ''' AS Calculation, ' + CastCalculation + ' AS CalculatedMetricValue' + CHAR(10)
FROM Replacements R
WHERE RN = (SELECT MAX(sq.RN) FROM Replacements sq WHERE sq.CalculationID = R.CalculationID)
FOR XML PATH('')),1,10,'');
--SELECT @SQL;
EXEC (@SQL);
GO
DROP TABLE #Metrics;
DROP TABLE #Calculations;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
March 2, 2017 at 7:57 am
Thom and DesNorton, apologies for the delay in response.
DesNorton, 'SC-1' is just a tag and not SC minus 1.
Thanks a lot Thom for putting in the code to create the tables as well as the calculation. It'll take me a while to go thorough and understand it as I am fairly new with SQL. I'll post back once I get the hang of this.
We actually have a few hundred metrics and thought that if this was possible, it would be easier than writing a procedure with hundreds of individual calculations.
March 6, 2017 at 4:13 am
Just for fun, a slightly different approach for generating the calculation
😎
USE TEEST;
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'dbo.TBL_METRIC_VALUES') IS NOT NULL DROP TABLE dbo.TBL_METRIC_VALUES;
CREATE TABLE dbo.TBL_METRIC_VALUES
(
MetricVID CHAR(5) NOT NULL
,[Date] DATE NOT NULL
,[Value] VARCHAR(20) NOT NULL
,MetricCID AS (LEFT(MetricVID,4))
,MetricSq AS (RIGHT(MetricVID,1))
);
IF OBJECT_ID(N'dbo.TBL_METRIC_CALC') IS NOT NULL DROP TABLE dbo.TBL_METRIC_CALC;
CREATE TABLE dbo.TBL_METRIC_CALC
(
MetricCID VARCHAR(5) NOT NULL
,MetricCalc VARCHAR(2000) NOT NULL
);
INSERT INTO dbo.TBL_METRIC_VALUES(MetricVID,[Date],[Value])
VALUES
('SC-1a',CONVERT(DATE,'27/02/2017',103), '883' )
,('SC-1b',CONVERT(DATE,'27/02/2017',103),'1025' )
,('SC-2a',CONVERT(DATE,'27/02/2017',103), '734' )
,('SC-2b',CONVERT(DATE,'27/02/2017',103), '291' )
,('SC-2c',CONVERT(DATE,'27/02/2017',103), '0' )
,('SC-2d',CONVERT(DATE,'27/02/2017',103), '19' )
,('SC-2e',CONVERT(DATE,'27/02/2017',103), '0' )
,('SC-2f',CONVERT(DATE,'27/02/2017',103), '58' )
,('SC-2g',CONVERT(DATE,'27/02/2017',103), '1' )
,('SC-2h',CONVERT(DATE,'27/02/2017',103), '0' )
,('SC-2i',CONVERT(DATE,'27/02/2017',103), '0' );
INSERT INTO dbo.TBL_METRIC_CALC(MetricCID,MetricCalc)
VALUES
('SC-1','SC-1a / SC-1b')
,('SC-2','((SC-2a + SC-2b + SC-2c) - (SC-2d + SC-2e + SC-2f + SC-2g + SC-2h + SC-2i)) / (SC-2a + SC-2b + SC-2c)');
;WITH BASE_CALC_PARSING AS
(
SELECT
MC.MetricCID
,CONVERT(XML,'<IX V="'+REPLACE(MC.MetricCalc,MC.MetricCID,'"/><IX V="')+'" />',0) AS MC_XML
FROM dbo.TBL_METRIC_CALC MC
)
,INDIVIDUAL_ENTRIES AS
(
SELECT
BCP.MetricCID
,ROW_NUMBER() OVER
(
PARTITION BY BCP.MetricCID
ORDER BY @@VERSION
) AS RID
,BX.DATA.value('@V','VARCHAR(100)') AS ENTRY_VAL
FROM BASE_CALC_PARSING BCP
CROSS APPLY BCP.MC_XML.nodes('IX') BX(DATA)
)
,ELEMENT_GROUP AS
(
SELECT
IE.MetricCID
,IE.RID
,CASE
WHEN IE.RID = 1 THEN IE.ENTRY_VAL
ELSE REPLACE(IE.ENTRY_VAL,LEFT(IE.ENTRY_VAL,1),MV.Value)
END AS PART_VAL
FROM INDIVIDUAL_ENTRIES IE
LEFT OUTER JOIN dbo.TBL_METRIC_VALUES MV
ON IE.MetricCID = MV.MetricCID
AND LEFT(IE.ENTRY_VAL,1) = MV.MetricSq
)
SELECT
MC.MetricCID
,(
SELECT
'' + EG.PART_VAL
FROM ELEMENT_GROUP EG
WHERE MC.MetricCID = EG.MetricCID
ORDER BY EG.RID
FOR XML PATH(''),TYPE
).value('(./text())[1]','VARCHAR(MAX)') AS FORULA_TO_CALC
FROM dbo.TBL_METRIC_CALC MC;
OutputMetricCID FORULA_TO_CALC
--------- ----------------------------------------------------------------
SC-1 883 / 1025
SC-2 ((734 + 291 + 0) - (19 + 0 + 58 + 1 + 0 + 0)) / (734 + 291 + 0)
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply