Insert entry on a new temp table after make cell calculations-CodePudding

I have a SQL Server table (source table) with an analogy column of type string. What I'm trying to achieve is the calculation of pieces and the insertion of new entry when analogy is "1/1".

More specific: When analogy is 1/1

take productName as it is,
put color as "white", -
keep analogy text the same "1/1"
divide by 2 the number of psc on both current and new entry.

What I've tried so far is to create a #temp table by using SELECT INTO and then try to create a recursion to check analogy before inserting a new entry on #temp table. However query didn't work and stuck.

I've used this example of Denis Lukichev but I'm not sure if this approach is suitable for my solution. Moreover this approach of Felix Pamittan is closer to what I want but i don't know how to integrate it on my example.

Any help or reference on how to achieve the solution will be appreciated..

Source table:

productName	color	analogy	psc
Alpha	Gray	1/1	1000
Beta	Gray	1/1	1000
Gama	Gray	2/1	1500

How to achieve the following result on a new temp table?:

productName	color	analogy	psc
Alpha	Gray	1/1	500
Alpha	white	1/1	500
Beta	Gray	1/1	500
Beta	white	1/1	500
Gama	Gray	2/1	1000
Gama	white	2/1	500

Moreover is there any chance of using other analogy and recalculate psc. For example: if the analogy is 2/1 means 2 slots are for Gray and one slot for white, then according analogy will be 500 500 =1000 psc for Gray and 500 psc for white.

UPDATE

After using the helpful suggestion of Dordi, it considered a close to solution until use another color.

More specific, i've added 'White' and 'Black' colors and the result was not as intended.

SQL Fiddle

MS SQL Server 2017 Schema Setup:

CREATE TABLE sourceTable (
    productName varchar(50),
    color varchar(50),
    analogy varchar(50),
    psc int
);

INSERT INTO sourceTable (productName, color, analogy, psc) VALUES ('Alpha', 'Gray', '1/1',1000);
INSERT INTO sourceTable (productName, color, analogy, psc) VALUES ('Gama', 'Black', '1/2',1500);
INSERT INTO sourceTable (productName, color, analogy, psc) VALUES ('Gama', 'White', '3/0',1500);

Query 1:

SELECT t.productName,
x.color,
t.analogy,
CASE x.color 
    WHEN 'Gray' THEN psc * CAST(LEFT(analogy,CHARINDEX('/',analogy) - 1) as int) / (CAST(LEFT(analogy,CHARINDEX('/',analogy) - 1) as int)    CAST(RIGHT(analogy,CHARINDEX('/',analogy) - 1) as int) )
    WHEN 'Black' THEN psc * CAST(LEFT(analogy,CHARINDEX('/',analogy) - 1) as int) / (CAST(LEFT(analogy,CHARINDEX('/',analogy) - 1) as int)    CAST(RIGHT(analogy,CHARINDEX('/',analogy) - 1) as int) )
    WHEN 'White' THEN psc * CAST(RIGHT(analogy,CHARINDEX('/',analogy) - 1) as int)  / (CAST(LEFT(analogy,CHARINDEX('/',analogy) - 1) as int)    CAST(RIGHT(analogy,CHARINDEX('/',analogy) - 1) as int) )
END AS psc
FROM sourceTable t 
CROSS JOIN (VALUES ('Gray'),('White'),('Black')) AS x(color)

Results:

| productName | color | analogy |  psc |
|-------------|-------|---------|------|
|       Alpha |  Gray |     1/1 |  500 |
|       Alpha | White |     1/1 |  500 |
|       Alpha | Black |     1/1 |  500 |
|        Gama |  Gray |     1/2 |  500 |
|        Gama | White |     1/2 | 1000 |
|        Gama | Black |     1/2 |  500 |
|        Gama |  Gray |     3/0 | 1500 |
|        Gama | White |     3/0 |    0 |
|        Gama | Black |     3/0 | 1500 |

But the preferred results are:

| productName | color | analogy |  psc |
|-------------|-------|---------|------|
|       Alpha |  Gray |     1/1 |  500 |
|       Alpha | White |     1/1 |  500 |
|        Gama | Black |     1/2 |  500 |
|        Gama | White |     1/2 | 1000 |
|        Gama | White |     3/0 | 1500 |
|        Gama | White |     3/0 |    0 |

I was thinking CROSS JOIN (VALUES ('Gray'),('White'),('Black')) AS x(color) is the issue here, maybe it should take dynamic (select distinct) the colors, or another case scenario dealing with color name.

Any thoughts?

CodePudding user response：

A combination of APPLY operator and the appropriate calculations is another option:

SELECT t.productName, a. color, t.analogy, a.psc
FROM (  
   SELECT 
      productName, 
      color, 
      analogy, 
      psc,
      CONVERT(int, LEFT(analogy, CHARINDEX('/', analogy) - 1)) AS analogy1,
      CONVERT(int, STUFF(analogy, 1, CHARINDEX('/', analogy), '')) AS analogy2
   FROM sourceTable
) t 
CROSS APPLY (VALUES 
   (t.color, ROUND(t.analogy1 * 1.0 / (t.analogy1   t.analogy2) * t.psc, 0)), 
   ('White', ROUND(t.analogy2 * 1.0 / (t.analogy1   t.analogy2) * t.psc, 0))
) a (color, psc)

CodePudding user response：

Based on your explanation, "analogy" is the repartition of psc over colors.

Here's another approach to calculate:

SELECT t.productName,
x.color,
t.analogy,
CASE x.color 
    WHEN 'Gray' THEN psc * CAST(LEFT(analogy,CHARINDEX('/',analogy) - 1) as int) / (CAST(LEFT(analogy,CHARINDEX('/',analogy) - 1) as int)    CAST(RIGHT(analogy,CHARINDEX('/',analogy) - 1) as int) )
    WHEN 'White' THEN psc * CAST(RIGHT(analogy,CHARINDEX('/',analogy) - 1) as int)  / (CAST(LEFT(analogy,CHARINDEX('/',analogy) - 1) as int)    CAST(RIGHT(analogy,CHARINDEX('/',analogy) - 1) as int) )
END AS psc
FROM #TEMP t 
CROSS JOIN (VALUES ('Gray'),('White')) AS x(color)

EDIT

Yes you can add a distint if you have multiple colors, your query becomes:

SELECT t.productName,
x.color,
t.analogy,
CASE  
    WHEN x.color = 'White' AND x.IsSource = 0 THEN psc * CAST(RIGHT(analogy,CHARINDEX('/',analogy) - 1) as int)  / (CAST(LEFT(analogy,CHARINDEX('/',analogy) - 1) as int)    CAST(RIGHT(analogy,CHARINDEX('/',analogy) - 1) as int) )
  ELSE psc * CAST(LEFT(analogy,CHARINDEX('/',analogy) - 1) as int) / (CAST(LEFT(analogy,CHARINDEX('/',analogy) - 1) as int)    CAST(RIGHT(analogy,CHARINDEX('/',analogy) - 1) as int) )
END AS psc
FROM sourceTable t 
INNER JOIN (SELECT DISTINCT color AS Id,color AS color,1 IsSource FROM sourceTable
  UNION ALL
  SELECT DISTINCT color AS Id,'White' AS color,0 IsSource FROM sourceTable
  ) AS x ON t.color = x.Id

Here's a dbfiddle