# Case structure

• ### Question

• Hi,

I have a scenario in which I allocate a ratio to employees depending on availability of a product.

Employees, get a '100 %' allocation always, but based on a ratio.

The Default ratio is 40 : ORA, 40 : PEA, 20 : GUA, to make a 'total' of 100% always.

So, if GUA, is 0, then I need to adjust ORA to 50, and PEA to 50, to get 100%

if PEA is 0, then I need to adjust ORA to 60 and GUA to 40, to get 100%

if ORA and PEA are both 0, then I need to adjust GUA to 100%

Many thanks,

Monday, September 16, 2019 5:26 AM

• If Excel picture represents the expected results, then check this preliminary query:

```;
with Q as
(
select EmpNo, ISNULL([GUA], 0) as GUA, ISNULL([PEA], 0) AS PEA, ISNULL([ORA], 0) AS ORA
from
(
select EmpNo, Product, [Value]
from tttt
) as t
pivot
(
MAX([Value])
for Product in ([GUA], [PEA], [ORA] )
) as p
),
GUAonly as
(
select EmpNo, 1.0 as GUA, 0.0 as PEA, 0.0 as ORA
from Q
where GUA <> 0 and PEA = 0 and ORA = 0
),
PEAonly as
(
select EmpNo, 0 as GUA, 1 as PEA, 0 as ORA
from Q
where GUA = 0 and PEA <> 0 and ORA = 0
),
ORAonly as
(
select EmpNo, 0 as GUA, 0 as PEA, 1 as ORA
from Q
where GUA = 0 and PEA = 0 and ORA <> 0
),
noGUA as
(
select EmpNo, 0 as GUA, 0.5 as PEA, 0.5 as ORA
from Q
where GUA = 0 and PEA <> 0 and ORA <> 0
),
noPEA as
(
select EmpNo, 0.4 as GUA, 0 as PEA, 0.6 as ORA
from Q
where GUA <> 0 and PEA = 0 and ORA <> 0
),
noORA as
(
select EmpNo, 0.4 as GUA, 0.6 as PEA, 0 as ORA
from Q
where GUA <> 0 and PEA <> 0 and ORA = 0
),
[All] as
(
select EmpNo, 0.2 as GUA, 0.4 as PEA, 0.4 as ORA
from Q
where GUA <> 0 and PEA <> 0 and ORA <> 0
),
U as
(
select * from GUAonly
union all
select * from PEAonly
union all
select * from ORAonly
union all
select * from noGUA
union all
select * from noPEA
union all
select * from noORA
union all
select * from [All]
),
T as
(
select U.EmpNo, U.GUA * Q.GUA + U.PEA * Q.PEA + U.ORA * Q.ORA as Total
from U
inner join Q on Q.EmpNo = U.EmpNo
)
update tttt
set Total = T.Total
from T
where T.EmpNo = tttt.EmpNo```

But if your INSERT statements represent the expected results, then it is not clear how 0.56731455334575664 and 0.3337084073996468 were calculated.

• Edited by Tuesday, September 17, 2019 4:24 AM Spelling.
• Marked as answer by Tuesday, September 17, 2019 7:40 AM
Monday, September 16, 2019 10:41 AM
• Hi Ron,

Is this what you want?

```CREATE TABLE [dbo].[tttt](
[EmpNo] [float] NULL,
[Month] [float] NULL,
[Value] [float] NULL,
[Product] [nvarchar](255) NULL,
[Total] [float] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[tttt] ([EmpNo], [Month], [Value], [Product], [Total]) VALUES
(566, 201908, 0.604535398230088,  N'GUA', 0.58811937153823712),
(566, 201908, 0.520541324311261,  N'ORA', 0.56731455334575664),
(566, 201908, 0.647489405419288,  N'PEA', 0.3337084073996468),
(-34, 201908, 0.510526315789473, N'GUA', NULL),
(-34, 201908, 0,  N'ORA', NULL),
(-34, 201908, 0.589720789614085,  N'PEA', NULL),
(-33, 201908, 0.42627960275019, N'GUA', NULL),
(-33, 201908, 0,  N'ORA', NULL),
(-33, 201908, 0.604535398230088,  N'PEA', NULL),
(-32, 201908, 0.480732177263969, N'GUA', NULL),
(-32, 201908, 0.489274106175514, N'ORA', NULL),
(-32, 201908, 0.579157516470353,  N'PEA', NULL),
(-31, 201908, 0.538853885388538, N'ORA', NULL),
(-31, 201908, 0,N'GUA', NULL),
(-31, 201908, 0.649993294890706,  N'PEA', NULL),
(-30, 201908, 0.63296488946684, N'ORA', NULL),
(-30, 201908, 0.654368932038834, N'GUA', NULL),
(-30, 201908, 0.732786631595234,  N'PEA', NULL),
(-29, 201908, 0.551873198847262, N'GUA', NULL),
(-29, 201908, 0.60219957081545, N'ORA', NULL),
(-29, 201908, 0.659031682896721,  N'PEA', NULL),
(-28, 201908, 0,  N'GUA', NULL),
(-28, 201908, 0.56548418024928, N'ORA', NULL),
(-28, 201908, 0.665600730927364, N'PEA', NULL),
(-26, 201908, 0.530063729564976, N'ORA', NULL),
(-26, 201908, 0.571220930232558,N'GUA', NULL)

;with cte1 as (
select * from (
select EmpNo,Month,[Value],Product from
tttt)s
pivot
(sum(value)for Product in ([GUA],[PEA],[ORA]))PVT
),
cte2 as (
select empno,month,GUA,CASE
when GUA<>0 AND PEA<>0 AND ORA <>0 THEN 0.2
when GUA=0 AND PEA<>0 AND ORA <>0 THEN 0
when GUA<>0 AND PEA=0 AND ORA <>0 THEN 0.4
when GUA<>0 AND PEA<>0 AND ORA =0 THEN 0.4
when GUA=0 AND PEA=0 AND ORA <>0 THEN 0
when GUA=0 AND PEA<>0 AND ORA =0 THEN 0
when GUA<>0 AND PEA=0 AND ORA =0 THEN 1
else null
end as ratiogua,
PEA,CASE
when GUA<>0 AND PEA<>0 AND ORA <>0 THEN 0.4
when GUA=0 AND PEA<>0 AND ORA <>0 THEN 0.5
when GUA<>0 AND PEA=0 AND ORA <>0 THEN 0
when GUA<>0 AND PEA<>0 AND ORA =0 THEN 0.6
when GUA=0 AND PEA=0 AND ORA <>0 THEN 0
when GUA=0 AND PEA<>0 AND ORA =0 THEN 1
when GUA<>0 AND PEA=0 AND ORA =0 THEN 0
else null
end as ratiopea,
ORA,CASE
when GUA<>0 AND PEA<>0 AND ORA <>0 THEN 0.4
when GUA=0 AND PEA<>0 AND ORA <>0 THEN 0.5
when GUA<>0 AND PEA=0 AND ORA <>0 THEN 0.6
when GUA<>0 AND PEA<>0 AND ORA =0 THEN 0
when GUA=0 AND PEA=0 AND ORA <>0 THEN 1
when GUA=0 AND PEA<>0 AND ORA =0 THEN 0
when GUA<>0 AND PEA=0 AND ORA =0 THEN 0
else null
end as ratioora
from cte1)

select *, GUA*ratiogua+ pea* ratiopea+ ORA*ratioora as total
from cte2

/*
empno  month    GUA                    ratiogua  PEA                    ratiopea   ORA                    ratioora   total
------ -------- ---------------------- --------- ---------------------- ---------- ---------------------- ---------- ----------------------
-34    201908   0.510526315789473      0.4       0.589720789614085      0.6        0                      0.0        0.55804300008424
-33    201908   0.42627960275019       0.4       0.604535398230088      0.6        0                      0.0        0.533233080038129
-32    201908   0.480732177263969      0.2       0.579157516470353      0.4        0.489274106175514      0.4        0.523519084511141
-31    201908   0                      0.0       0.649993294890706      0.5        0.538853885388538      0.5        0.594423590139622
-30    201908   0.654368932038834      0.2       0.732786631595234      0.4        0.63296488946684       0.4        0.677174394832596
-29    201908   0.551873198847262      0.2       0.659031682896721      0.4        0.60219957081545       0.4        0.614867141254321
-28    201908   0                      0.0       0.665600730927364      0.5        0.56548418024928       0.5        0.615542455588322
-26    201908   0.571220930232558      NULL      NULL                   NULL       0.530063729564976      NULL       NULL
566    201908   0.604535398230088      0.2       0.647489405419288      0.4        0.520541324311261      0.4        0.588119371538237
*/```

Sabrina

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

Tuesday, September 17, 2019 2:07 AM

### All replies

• Hi RonTech1,

Please provide a DDL, sample data population, logic, and desired output.

Monday, September 16, 2019 5:32 AM
• Hi Ron,

Could you post a script with CREATE TABLE + INSERT statements with sample data and the expected result of that sample data?  So that we’ll get a right direction and make some tests.

Sabrina

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

Monday, September 16, 2019 5:36 AM
• CREATE TABLE #t (col1 CHAR(3), ratio INT)

INSERT INTO #t VALUES ('ORA', 40)
INSERT INTO #t VALUES ('PEA', 20)
INSERT INTO #t VALUES ('GUA', 40)

DECLARE @col1 CHAR(3)='ORA', @col2 CHAR(3)='PEA'
DECLARE @ratio INT=0

SELECT col1,
CASE WHEN @col1='GUA' AND @ratio=0 AND @col2='' THEN
CASE WHEN col1='ORA' OR  col1='PEA' THEN 50 ELSE 0 END
ELSE CASE WHEN @col1='PEA' AND @ratio=0 AND @col2='' THEN
CASE WHEN col1='ORA' THEN 60
WHEN col1='GUA' THEN 40  ELSE  0 END

ELSE CASE WHEN @col1='ORA' AND @ratio=0 AND @col2='PEA ' THEN
CASE WHEN col1='GUA' THEN 100 ELSE 0 END   END

END END ratio
FROM #t

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Monday, September 16, 2019 5:54 AM
• Hi

CREATE TABLE [dbo].[table1](
[Mth] [float] NULL,
[EmpNo] [float] NULL,
[Product] [nvarchar](255) NULL,
[Ratio] [float] NULL,
[F5] [nvarchar](255) NULL,
[Result] [nvarchar](255) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[table1] ([Mth], [EmpNo], [Product], [Ratio],[Result]) VALUES (201908, 10111, N'GUA', 20, N'value * ratio')
INSERT [dbo].[table1] ([Mth], [EmpNo], [Product], [Ratio],[Result]) VALUES (201908, 10111, N'PEA', 40, NULL)
INSERT [dbo].[table1] ([Mth], [EmpNo], [Product], [Ratio],[Result]) VALUES (201908, 10111, N'ORA', 40, NULL)
INSERT [dbo].[table1] ([Mth], [EmpNo], [Product], [Ratio],[Result]) VALUES (201908, 69781, N'GUA', 40, NULL, NULL)
INSERT [dbo].[table1] ([Mth], [EmpNo], [Product], [Ratio],[Result]) VALUES (201908, 69781, N'PEA', 0, NULL, NULL)
INSERT [dbo].[table1] ([Mth], [EmpNo], [Product], [Ratio],[Result]) VALUES (201908, 69781, N'ORA', 60, NULL, NULL)
INSERT [dbo].[table1] ([Mth], [EmpNo], [Product], [Ratio],[Result]) VALUES (201908, 23845, N'GUA', 0, NULL, NULL)
INSERT [dbo].[table1] ([Mth], [EmpNo], [Product], [Ratio],[Result]) VALUES (201908, 23845, N'PEA', 50, NULL, NULL)
INSERT [dbo].[table1] ([Mth], [EmpNo], [Product], [Ratio],[Result]) VALUES (201908, 23845, N'ORA', 50, NULL, NULL)
INSERT [dbo].[table1] ([Mth], [EmpNo], [Product], [Ratio],[Result]) VALUES (201908, 698411, N'GUA', 40, NULL, NULL)
INSERT [dbo].[table1] ([Mth], [EmpNo], [Product], [Ratio],[Result]) VALUES (201908, 698411, N'PEA', 60, NULL, NULL)
INSERT [dbo].[table1] ([Mth], [EmpNo], [Product], [Ratio],[Result]) VALUES (201908, 698411, N'ORA', 0, NULL, NULL)
INSERT [dbo].[table1] ([Mth], [EmpNo], [Product], [Ratio],[Result]) VALUES (201908, 554210, N'GUA', 0, NULL, NULL)
INSERT [dbo].[table1] ([Mth], [EmpNo], [Product], [Ratio],[Result]) VALUES (201908, 554210, N'PEA', 0, NULL, NULL)
INSERT [dbo].[table1] ([Mth], [EmpNo], [Product], [Ratio],[Result]) VALUES (201908, 554210, N'ORA', 100, NULL, NULL)
INSERT [dbo].[table1] ([Mth], [EmpNo], [Product], [Ratio],[Result]) VALUES (201908, 574913, N'GUA', 0, NULL, NULL)
INSERT [dbo].[table1] ([Mth], [EmpNo], [Product], [Ratio],[Result]) VALUES (201908, 574913, N'PEA', 100, NULL, NULL)
INSERT [dbo].[table1] ([Mth], [EmpNo], [Product], [Ratio],[Result]) VALUES (201908, 574913, N'ORA', 0, NULL, NULL)
INSERT [dbo].[table1] ([Mth], [EmpNo], [Product], [Ratio],[Result]) VALUES (201908, 657812, N'GUA', 100, NULL, NULL)
INSERT [dbo].[table1] ([Mth], [EmpNo], [Product], [Ratio],[Result]) VALUES (201908, 657812, N'PEA', 0, NULL, NULL)
INSERT [dbo].[table1] ([Mth], [EmpNo], [Product], [Ratio],[Result]) VALUES (201908, 657812, N'ORA', 0, NULL, NULL)

Monday, September 16, 2019 6:04 AM
• Hi

There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

See below my solution helps you

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Monday, September 16, 2019 6:25 AM

• So, if GUA, is 0, then I need to adjust ORA to 50, and PEA to 50, to get 100%

if PEA is 0, then I need to adjust ORA to 60 and GUA to 40, to get 100%

if ORA and PEA are both 0, then I need to adjust GUA to 100%

Hi Ron,

Is this your original table? If yes, what is your desired output as I see all the rows matches with your requirement.

Sabrina

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

Monday, September 16, 2019 6:26 AM
• Hi,

The table given is what I desire

Monday, September 16, 2019 6:35 AM

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

Monday, September 16, 2019 6:38 AM
• Hi,

I have tried to explain differently

Script for table is

CREATE TABLE [dbo].[tttt](
[EmpNo] [float] NULL,
[Month] [float] NULL,
[Value] [float] NULL,
[Ratio] [float] NULL,
[Product] [nvarchar](255) NULL,
[Total] [float] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[tttt] ([EmpNo], [Month], [Value], [Ratio], [Product], [Total]) VALUES (566, 201908, 0.604535398230088, 2029, N'GUA', 0.58811937153823712)
INSERT [dbo].[tttt] ([EmpNo], [Month], [Value], [Ratio], [Product], [Total]) VALUES (566, 201908, 0.520541324311261, 6207, N'ORA', 0.56731455334575664)
INSERT [dbo].[tttt] ([EmpNo], [Month], [Value], [Ratio], [Product], [Total]) VALUES (566, 201908, 0.647489405419288, 7787, N'PEA', 0.3337084073996468)
INSERT [dbo].[tttt] ([EmpNo], [Month], [Value], [Ratio], [Product], [Total]) VALUES (-34, 201908, 0.510526315789473, 380, N'GUA', NULL)
INSERT [dbo].[tttt] ([EmpNo], [Month], [Value], [Ratio], [Product], [Total]) VALUES (-34, 201908, 0, 2515, N'ORA', NULL)
INSERT [dbo].[tttt] ([EmpNo], [Month], [Value], [Ratio], [Product], [Total]) VALUES (-34, 201908, 0.589720789614085, 5623, N'PEA', NULL)
INSERT [dbo].[tttt] ([EmpNo], [Month], [Value], [Ratio], [Product], [Total]) VALUES (-33, 201908, 0.42627960275019, 1309, N'GUA', NULL)
INSERT [dbo].[tttt] ([EmpNo], [Month], [Value], [Ratio], [Product], [Total]) VALUES (-33, 201908, 0, 6603, N'ORA', NULL)
INSERT [dbo].[tttt] ([EmpNo], [Month], [Value], [Ratio], [Product], [Total]) VALUES (-33, 201908, 0.604535398230088, 12656, N'PEA', NULL)
INSERT [dbo].[tttt] ([EmpNo], [Month], [Value], [Ratio], [Product], [Total]) VALUES (-32, 201908, 0.480732177263969, 1038, N'GUA', NULL)
INSERT [dbo].[tttt] ([EmpNo], [Month], [Value], [Ratio], [Product], [Total]) VALUES (-32, 201908, 0.489274106175514, 4615, N'ORA', NULL)
INSERT [dbo].[tttt] ([EmpNo], [Month], [Value], [Ratio], [Product], [Total]) VALUES (-32, 201908, 0.579157516470353, 10018, N'PEA', NULL)
INSERT [dbo].[tttt] ([EmpNo], [Month], [Value], [Ratio], [Product], [Total]) VALUES (-31, 201908, 0.538853885388538, 3333, N'ORA', NULL)
INSERT [dbo].[tttt] ([EmpNo], [Month], [Value], [Ratio], [Product], [Total]) VALUES (-31, 201908, 0, 585, N'GUA', NULL)
INSERT [dbo].[tttt] ([EmpNo], [Month], [Value], [Ratio], [Product], [Total]) VALUES (-31, 201908, 0.649993294890706, 7457, N'PEA', NULL)
INSERT [dbo].[tttt] ([EmpNo], [Month], [Value], [Ratio], [Product], [Total]) VALUES (-30, 201908, 0.63296488946684, 3076, N'ORA', NULL)
INSERT [dbo].[tttt] ([EmpNo], [Month], [Value], [Ratio], [Product], [Total]) VALUES (-30, 201908, 0.654368932038834, 515, N'GUA', NULL)
INSERT [dbo].[tttt] ([EmpNo], [Month], [Value], [Ratio], [Product], [Total]) VALUES (-30, 201908, 0.732786631595234, 6463, N'PEA', NULL)
INSERT [dbo].[tttt] ([EmpNo], [Month], [Value], [Ratio], [Product], [Total]) VALUES (-29, 201908, 0.551873198847262, 694, N'GUA', NULL)
INSERT [dbo].[tttt] ([EmpNo], [Month], [Value], [Ratio], [Product], [Total]) VALUES (-29, 201908, 0.60219957081545, 3728, N'ORA', NULL)
INSERT [dbo].[tttt] ([EmpNo], [Month], [Value], [Ratio], [Product], [Total]) VALUES (-29, 201908, 0.659031682896721, 7291, N'PEA', NULL)
INSERT [dbo].[tttt] ([EmpNo], [Month], [Value], [Ratio], [Product], [Total]) VALUES (-28, 201908, 0, 1274, N'GUA', NULL)
INSERT [dbo].[tttt] ([EmpNo], [Month], [Value], [Ratio], [Product], [Total]) VALUES (-28, 201908, 0.56548418024928, 5215, N'ORA', NULL)
INSERT [dbo].[tttt] ([EmpNo], [Month], [Value], [Ratio], [Product], [Total]) VALUES (-28, 201908, 0.665600730927364, 6567, N'PEA', NULL)
INSERT [dbo].[tttt] ([EmpNo], [Month], [Value], [Ratio], [Product], [Total]) VALUES (-26, 201908, 0.530063729564976, 3609, N'ORA', NULL)
INSERT [dbo].[tttt] ([EmpNo], [Month], [Value], [Ratio], [Product], [Total]) VALUES (-26, 201908, 0.571220930232558, 688, N'GUA', NULL)

So if one of the 3 products for each employee is a null, then the remaining 2 are marked up, if ORA is a 0, then PEA will be 60 and GUA will be 40, and so on (to always make a 100)

Possible ratios are given below

 Product Ratio1 Ratio2 Ratio3 Ratio4 Ratio5 Ratio6 Ratio7 GUA 0.2 0 0.4 0.4 0 0 1 PEA 0.4 0.5 0 0.6 0 1 0 ORA 0.4 0.5 0.6 0 1 0 0

Many thanks

• Edited by Monday, September 16, 2019 8:10 AM
Monday, September 16, 2019 8:06 AM
• Can you explain the logic  you got  0.2,0.4,0.4 for RATIO1?

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Monday, September 16, 2019 8:46 AM
• Hi,

The 0.2,0.4,0.4 is the default ratio.

Thus if there is a value for each product, then use the default ratio, but if 1 or 2 products are zero, then the remaining products need to be multiplied by a 'ratio'

Many thanks,

Monday, September 16, 2019 8:59 AM
• Which column should be updated in tttt table? Ratio, Total?

Show an example of calculation (the expression) for some rows. If 0.588119371538237 is a sample result, how was it calculated based on other numbers?

• Edited by Monday, September 16, 2019 9:31 AM
Monday, September 16, 2019 9:31 AM
• Hi,

It was calculated as per the below:

Because GUA, PEA and ORA all have values in the 'Value' column, then the default ratio is used (2:4:4)

Hence the result, for that employee is simply

(0.604535 * 0.2 + 0.520541 * 0.4  + 0.647489 * 0.4) to give a total of 0.588119371538237

Many thnaks

Monday, September 16, 2019 9:42 AM
• UPDATE [tttt] SET total=CASE WHEN  (Product='ORA' OR  Product='PEA'  OR  Product='GUA') AND
Total IS NOT NULL THEN CASE
WHEN Product='GUA' THEN 0.2
WHEN Product='ORA' THEN 0.4
WHEN Product='PEA' THEN 0.4 ELSE 0 END END

UPDATE [tttt] SET total=CASE WHEN (Product='PEA' AND Total IS NULL) OR
(Product='ORA' OR  Product='GUA')    THEN
CASE WHEN Product='ORA' THEN 0.6
WHEN Product='GUA' THEN 0.4
ELSE 0 END END
WHERE Total IS NULL AND (Product='ORA' OR Product='GUA')

----But  here the logic doers  not work , because there are already data in Product GUA ... Ca you elaborate on it?
UPDATE [tttt] SET total=   CASE WHEN Product='GUA' THEN 1.0 ELSE 0  END
WHERE ((Product='ORA' OR Product='PEA')  AND Total IS NULL)  ---or Product='GUA'

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Monday, September 16, 2019 9:50 AM
• If Excel picture represents the expected results, then check this preliminary query:

```;
with Q as
(
select EmpNo, ISNULL([GUA], 0) as GUA, ISNULL([PEA], 0) AS PEA, ISNULL([ORA], 0) AS ORA
from
(
select EmpNo, Product, [Value]
from tttt
) as t
pivot
(
MAX([Value])
for Product in ([GUA], [PEA], [ORA] )
) as p
),
GUAonly as
(
select EmpNo, 1.0 as GUA, 0.0 as PEA, 0.0 as ORA
from Q
where GUA <> 0 and PEA = 0 and ORA = 0
),
PEAonly as
(
select EmpNo, 0 as GUA, 1 as PEA, 0 as ORA
from Q
where GUA = 0 and PEA <> 0 and ORA = 0
),
ORAonly as
(
select EmpNo, 0 as GUA, 0 as PEA, 1 as ORA
from Q
where GUA = 0 and PEA = 0 and ORA <> 0
),
noGUA as
(
select EmpNo, 0 as GUA, 0.5 as PEA, 0.5 as ORA
from Q
where GUA = 0 and PEA <> 0 and ORA <> 0
),
noPEA as
(
select EmpNo, 0.4 as GUA, 0 as PEA, 0.6 as ORA
from Q
where GUA <> 0 and PEA = 0 and ORA <> 0
),
noORA as
(
select EmpNo, 0.4 as GUA, 0.6 as PEA, 0 as ORA
from Q
where GUA <> 0 and PEA <> 0 and ORA = 0
),
[All] as
(
select EmpNo, 0.2 as GUA, 0.4 as PEA, 0.4 as ORA
from Q
where GUA <> 0 and PEA <> 0 and ORA <> 0
),
U as
(
select * from GUAonly
union all
select * from PEAonly
union all
select * from ORAonly
union all
select * from noGUA
union all
select * from noPEA
union all
select * from noORA
union all
select * from [All]
),
T as
(
select U.EmpNo, U.GUA * Q.GUA + U.PEA * Q.PEA + U.ORA * Q.ORA as Total
from U
inner join Q on Q.EmpNo = U.EmpNo
)
update tttt
set Total = T.Total
from T
where T.EmpNo = tttt.EmpNo```

But if your INSERT statements represent the expected results, then it is not clear how 0.56731455334575664 and 0.3337084073996468 were calculated.

• Edited by Tuesday, September 17, 2019 4:24 AM Spelling.
• Marked as answer by Tuesday, September 17, 2019 7:40 AM
Monday, September 16, 2019 10:41 AM
• Hi Apologies,

Please see below  screenshot, ignore or remove the Ratio column

Monday, September 16, 2019 10:51 AM
• Hi Ron,

Is this what you want?

```CREATE TABLE [dbo].[tttt](
[EmpNo] [float] NULL,
[Month] [float] NULL,
[Value] [float] NULL,
[Product] [nvarchar](255) NULL,
[Total] [float] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[tttt] ([EmpNo], [Month], [Value], [Product], [Total]) VALUES
(566, 201908, 0.604535398230088,  N'GUA', 0.58811937153823712),
(566, 201908, 0.520541324311261,  N'ORA', 0.56731455334575664),
(566, 201908, 0.647489405419288,  N'PEA', 0.3337084073996468),
(-34, 201908, 0.510526315789473, N'GUA', NULL),
(-34, 201908, 0,  N'ORA', NULL),
(-34, 201908, 0.589720789614085,  N'PEA', NULL),
(-33, 201908, 0.42627960275019, N'GUA', NULL),
(-33, 201908, 0,  N'ORA', NULL),
(-33, 201908, 0.604535398230088,  N'PEA', NULL),
(-32, 201908, 0.480732177263969, N'GUA', NULL),
(-32, 201908, 0.489274106175514, N'ORA', NULL),
(-32, 201908, 0.579157516470353,  N'PEA', NULL),
(-31, 201908, 0.538853885388538, N'ORA', NULL),
(-31, 201908, 0,N'GUA', NULL),
(-31, 201908, 0.649993294890706,  N'PEA', NULL),
(-30, 201908, 0.63296488946684, N'ORA', NULL),
(-30, 201908, 0.654368932038834, N'GUA', NULL),
(-30, 201908, 0.732786631595234,  N'PEA', NULL),
(-29, 201908, 0.551873198847262, N'GUA', NULL),
(-29, 201908, 0.60219957081545, N'ORA', NULL),
(-29, 201908, 0.659031682896721,  N'PEA', NULL),
(-28, 201908, 0,  N'GUA', NULL),
(-28, 201908, 0.56548418024928, N'ORA', NULL),
(-28, 201908, 0.665600730927364, N'PEA', NULL),
(-26, 201908, 0.530063729564976, N'ORA', NULL),
(-26, 201908, 0.571220930232558,N'GUA', NULL)

;with cte1 as (
select * from (
select EmpNo,Month,[Value],Product from
tttt)s
pivot
(sum(value)for Product in ([GUA],[PEA],[ORA]))PVT
),
cte2 as (
select empno,month,GUA,CASE
when GUA<>0 AND PEA<>0 AND ORA <>0 THEN 0.2
when GUA=0 AND PEA<>0 AND ORA <>0 THEN 0
when GUA<>0 AND PEA=0 AND ORA <>0 THEN 0.4
when GUA<>0 AND PEA<>0 AND ORA =0 THEN 0.4
when GUA=0 AND PEA=0 AND ORA <>0 THEN 0
when GUA=0 AND PEA<>0 AND ORA =0 THEN 0
when GUA<>0 AND PEA=0 AND ORA =0 THEN 1
else null
end as ratiogua,
PEA,CASE
when GUA<>0 AND PEA<>0 AND ORA <>0 THEN 0.4
when GUA=0 AND PEA<>0 AND ORA <>0 THEN 0.5
when GUA<>0 AND PEA=0 AND ORA <>0 THEN 0
when GUA<>0 AND PEA<>0 AND ORA =0 THEN 0.6
when GUA=0 AND PEA=0 AND ORA <>0 THEN 0
when GUA=0 AND PEA<>0 AND ORA =0 THEN 1
when GUA<>0 AND PEA=0 AND ORA =0 THEN 0
else null
end as ratiopea,
ORA,CASE
when GUA<>0 AND PEA<>0 AND ORA <>0 THEN 0.4
when GUA=0 AND PEA<>0 AND ORA <>0 THEN 0.5
when GUA<>0 AND PEA=0 AND ORA <>0 THEN 0.6
when GUA<>0 AND PEA<>0 AND ORA =0 THEN 0
when GUA=0 AND PEA=0 AND ORA <>0 THEN 1
when GUA=0 AND PEA<>0 AND ORA =0 THEN 0
when GUA<>0 AND PEA=0 AND ORA =0 THEN 0
else null
end as ratioora
from cte1)

select *, GUA*ratiogua+ pea* ratiopea+ ORA*ratioora as total
from cte2

/*
empno  month    GUA                    ratiogua  PEA                    ratiopea   ORA                    ratioora   total
------ -------- ---------------------- --------- ---------------------- ---------- ---------------------- ---------- ----------------------
-34    201908   0.510526315789473      0.4       0.589720789614085      0.6        0                      0.0        0.55804300008424
-33    201908   0.42627960275019       0.4       0.604535398230088      0.6        0                      0.0        0.533233080038129
-32    201908   0.480732177263969      0.2       0.579157516470353      0.4        0.489274106175514      0.4        0.523519084511141
-31    201908   0                      0.0       0.649993294890706      0.5        0.538853885388538      0.5        0.594423590139622
-30    201908   0.654368932038834      0.2       0.732786631595234      0.4        0.63296488946684       0.4        0.677174394832596
-29    201908   0.551873198847262      0.2       0.659031682896721      0.4        0.60219957081545       0.4        0.614867141254321
-28    201908   0                      0.0       0.665600730927364      0.5        0.56548418024928       0.5        0.615542455588322
-26    201908   0.571220930232558      NULL      NULL                   NULL       0.530063729564976      NULL       NULL
566    201908   0.604535398230088      0.2       0.647489405419288      0.4        0.520541324311261      0.4        0.588119371538237
*/```

Sabrina

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

Tuesday, September 17, 2019 2:07 AM
• Hi Sabrina,

This solution works perfectly for my scenario.

Thanks so much!!

Regards

Tuesday, September 17, 2019 7:39 AM
• Hi Viorel_,

This solution works perfectly for my scenario.

Thanks so much!!

Regards

Tuesday, September 17, 2019 7:40 AM