I want to show a percentage in a new column that reflects a gain\loss % between col1 and col2(The decimal fields in tables) returned in my query. The query produces a query result for each one of the decimal fields. We are doing some new calculations in the earns table and trying to see how those look compared to the earnsprod table seeing how close using a percentage. The tables listed are identical so I just listed one schema.
DECLARE @ColName varchar(100)
DECLARE @Table1 varchar(100) = 'Earns'
DECLARE @Table2 varchar(100) = 'Earns_Prod'
IF (OBJECT_ID('tempdb..#col') IS NOT NULL) DROP TABLE #col
SELECT IDENTITY(INT, 1, 1) RowNum , c.name
INTO #col
FROM SYS.Objects o
JOIN SYS.columns c on o.object_id = c.object_id
WHERE o.name = @Table1 AND NOT c.Name IN ('PlantCd','PartNbr','CostKey','RequestedBy','FreezeTime','FreezeYN','ApprovedBy','opn','level_code','machine')
DECLARE @Counter INT = (SELECT MAX(RowNum) FROM #col)
WHILE @Counter > 0
BEGIN
SET @ColName = (SELECT name FROM #Col WHERE RowNum= @Counter)
EXEC ('SELECT t1.partnbr
,t1.'+@ColName+' AS '+@Table1+@ColName+'
,t2.'+@ColName+' AS '+@Table2+@ColName+'
FROM '+@Table1+' t1
LEFT JOIN '+@Table2+' t2 ON t1.partnbr = t2.partnbr and t1.opn = t2.opn
WHERE t1.'+@ColName+' <> t2.'+@ColName)
SET @Counter = @Counter - 1
END
CREATE TABLE [dbo].[Earns](
[CostKey] [varchar](9) NOT NULL,
[PlantCd] [varchar](9) NOT NULL,
[PartNbr] [varchar](20) NOT NULL,
[Opn] [varchar](9) NOT NULL,
[Level_code] [int] NOT NULL,
[Machine] [varchar](10) NULL,
[Matl] [decimal](16, 8) NOT NULL,
[Labor] [decimal](16, 8) NOT NULL,
[VarOH] [decimal](16, 8) NOT NULL,
[FixOH] [decimal](16, 8) NOT NULL,
[Total] [decimal](16, 8) NOT NULL,
[CuRecovery] [decimal](16, 8) NOT NULL,
[CuLbs] [decimal](16, 8) NOT NULL,
[RequestedBy] [varchar](9) NULL,
[FreezeTime] [varchar](22) NULL,
[FreezeYN] [varchar](1) NULL,
[ApprovedBy] [varchar](9) NULL,
CONSTRAINT [PK_Earns] PRIMARY KEY NONCLUSTERED
(
[CostKey] ASC,
[PlantCd] ASC,
[PartNbr] ASC,
[Opn] ASC,
[Level_code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
February 20, 2022 at 9:28 am
This is not a question. What is the problem?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 20, 2022 at 11:52 am
How do I create the 3rd field which would be a percentage(difference) from col1 and col2?
thx
February 20, 2022 at 12:01 pm
DROP TABLE IF EXISTS #T1;
CREATE TABLE #T1
(
Col1 DECIMAL(19, 6) NOT NULL
,Col2 DECIMAL(19, 6) NOT NULL
,PCDiff AS CAST((Col1 - Col2) * 100 / Col1 AS DECIMAL(7, 4))
);
INSERT #T1
(
Col1
,Col2
)
VALUES
(5, 7);
SELECT *
FROM #T1 t;
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 20, 2022 at 6:10 pm
You'd have to check for zero in the divisor first. There's no DIVIDE() function in T-SQL.
IF(divisorColumn = 0, NULL, NumeratorColumn/DivisorColumn)
February 20, 2022 at 6:38 pm
You'd have to check for zero in the divisor first. There's no DIVIDE() function in T-SQL. IF(divisorColumn = 0, NULL, NumeratorColumn/DivisorColumn)
There's a more concise way of achieving that:
NULLIF(Divisor,0)
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 20, 2022 at 9:12 pm
Is there any easy way just to skip those records rather than return Null?
THx.
February 21, 2022 at 1:03 am
Please consider the possibility of SQL Injection, especially on @Table2.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 21, 2022 at 1:20 am
Can you elaborate?
Thx.
February 21, 2022 at 7:58 am
This was removed by the editor as SPAM
February 21, 2022 at 11:05 am
Is there any easy way just to skip those records rather than return Null?
THx.
The column is defined for every row of data in the table, therefore the idea of 'skipping' makes no sense.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 21, 2022 at 2:30 pm
I ended up with something like this. Do you see any issues I would run into?
Thx for comments...
IF (OBJECT_ID('tempdb..#col') IS NOT NULL) DROP TABLE #col
SELECT IDENTITY(INT, 1, 1) RowNum , c.name
INTO #col
FROM SYS.Objects o
JOIN SYS.columns c on o.object_id = c.object_id
WHERE o.name = @Table1 AND NOT c.Name IN ('PlantCd','PartNbr','CostKey','RequestedBy','FreezeTime','FreezeYN','ApprovedBy','opn','level_code','machine')
DECLARE @Counter INT = (SELECT MAX(RowNum) FROM #col)
WHILE @Counter > 0
BEGIN
SET @ColName = (SELECT name FROM #Col WHERE RowNum= @Counter)
EXEC ('SELECT t1.partnbr
,t1.'+@ColName+' AS '+@Table1+@ColName+'
,t2.'+@ColName+' AS '+@Table2+@ColName+'
,case when (t1.'+@ColName+' = 0 or t2.'+@ColName+' = 0) Then
Null
Else
CAST((t1.'+@ColName+' - t2.'+@ColName+') * 100 / t1.'+@ColName+' AS DECIMAL(16, 8))
end
FROM '+@Table1+' t1
LEFT JOIN '+@Table2+' t2 ON t1.partnbr = t2.partnbr and t1.opn = t2.opn
WHERE t1.'+@ColName+' <> t2.'+@ColName)
SET @Counter = @Counter - 1
END
I ended up with something like this. Do you see any issues I would run into?
As I have completely failed to grasp what it is you are going to be using this for, not really. But it does seem like a weird solution.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 21, 2022 at 8:07 pm
Can you elaborate?
Thx.
Sure. Do a search for SQL Injection and try some of the methods for the way you're using @Table2 and @ColName. @Table1 may not have the same problem because you first use the contents of @Table1 to qualify things as an actual table name.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply