How to automize easier than builing hundreds of tables with each case

Transact-SQL
1

Hello,

I found this site very usefull so i wonder if someone with much more experience would be able to help me. I am looking for an easier way to automatize it. I know how to make it but it would take me too long and many tables to build to take every situation that can occur. Iwould take every case each by each in different tables but i have more than 100 cases. In this example i gave only a few just to give me stat on how to make it easier. Any suggestion would be highly appreciated :slight_smile:

I have this table:
create table [dbo].[Table](
ID nvarchar(50) null,
Line nvarchar (10) null,
Category nvarchar(100) null,
Unit nvarchar(100) null,
Amount float null

insert into Table values:
('A','96','Any','Comp Serv','100'),
('A','UY','Any','Comp','200'),
('B','96','Any','Comp Serv','10'),
('B','MV','lades','Comp Serv','10'),
('B','S7','lades','Comp','20'),
('E','96','Any','Comp Serv','40'),
('E','MV','lades','Comp','50'),
('E','1V','SDC','Comp','60'),
('E', 'FE','System','Comp Serv','70'),
('E', 'PD','ollo','Comp','50'),
('F','1V','SDC','Comp','10'),
('F', '96','Any','Comp Serv','30'),
('F', 'PD','ollo','Serv','30'),
('F','VM','Software','Stor Serv','90'),
('G','96','Any','Com Serv','90'),
('H','96','Any','Comp Serv','90'),
('H','VM','Software','Stor Serv','30')

this is the table:
image

An ID can have many Business Lines (column 2). I have to focus on the these Lines: 96. But these lines are mixed with other Business lines per ID.
I need to find a way to rename the "Any" name in Category column with different names but I have to take into consideration different facts:

  • If an Id has only 96 or 96 and UY and no other type of Line, instead of Any I put what I have in Unit column. for example I have ID=A,G

  • If an Id has among 96 only Lines like: MV or S7 then I rename "Any" into "Blades". Ex: ID=B

  • If an Id has among 96 only Lines like: MV, UZ but also 1V,FS or FE then I need to multiply the "Any" row with the number of the other Lines. Example: Id=E
    I would need to have for 96 Line the following:
    96 lades 20*(10/(10+50+30+40))
    96 SDC 20*(50/(10+50+30+40)
    96 System 20*(30/(10+50+30+40)
    96 ollo 20*(40/(10+50+30+40)
    where 20 is the vale of 96 Line, 10 is the value of the first Line divided by the sum of all the lines except 96. The same goes for teh other rows. So at the end i multiplied 96 rows by 4 rows.

  • If an ID has among 96, UY, MV,S7,1V,FE,PD other Lines , then I ignore that other line and I implement the same formula as above but only for 96, UY, MV,S7,1V,FE,PD
    example ID=F
    I ignore VM because is not in my list and i remain with PD and 1V. At the end i would have for 96 Line the following:

96 SDC 10*(40/(40+20))
96 ollo 10*(20/(40+20))

  • If I have 96 with any Line except UY, MV,S7,1V,FE,PD then I put Other +Unit

thank you :slight_smile:

2

Please explain, in plain English, what this data does with no sample data and no code. What is it that you are attempting to capture?
What is the business requirement

3

Hello,

I gave an example of data on which im working right now. I need to rename where i have "Any" in the Category column with the names that have other Business Lines (column 2). I just gave some examples. i cant give all the examples. But if this example works,i will be able to make it for all the necessary combinations. In my example i took the "96" business line (in reality i have many more but if it works for this it will work for the rest) For this 96 business line i put all the combinations that may occur.
An ID has one ore more business lines. For some business lines i could map their Category, but for others (ex:96 business line) i wasnt able to map it because we have some rules. I put those rules in the combinations above: If the ID has on 96b business line, then in the Categpry column ill put what i have in another column.(column Unit). But if in an ID i have a mixture of 2 or more Business lines and among them i have also 96, then the rules change like i explained. at the end i shuld see this:
image

4

Hi

One way is to create a table and populate
All the conditions
AND join

Example
Create table #caseConditions
(
Condition varchar(10) ,
ReplaceWith varchar(10)
)
Go

Insert into #caseCondition select 'har', 'ok'
Insert into#caseCondition select 'pra', 'aa'
Go

Select replace ( a.condition , a.replacewith )
From #table a join #caseCondition b
On a.condition = b.condition

Hope this helps :slightly_smiling_face::slightly_smiling_face:

5

sorry but i dont understand
what is har or pra?

6

With no results and a poor explaination the following might get you started:

WITH TPivot
AS
(
	SELECT ID
		,COALESCE([96], 0) AS [96]
		,COALESCE([1V], 0) AS [1V]
		,COALESCE([FE], 0) AS [FE]
		,COALESCE([MV], 0) AS [MV]
		,COALESCE([PD], 0) AS [PD]
		,COALESCE([S7], 0) AS [S7]
		,COALESCE([UY], 0) AS [UY]
		,COALESCE([VM], 0) AS [VM]
		,COALESCE([UZ], 0) AS [UZ]
		,COALESCE([FS], 0) AS [FS]
	FROM
	(
		SELECT ID, Line, Amount
		FROM <YourTable>
	) S
	PIVOT
	(
		SUM(Amount)
		FOR Line IN ([96],[1V],[FE],[MV],[PD],[S7],[UY],[VM],[UZ],[FS])
	) P
)
SELECT T.ID, T.Line
	,CASE
		WHEN P.[96] <> 0
			AND P.[1V]+P.[FE]+P.[MV]+P.[PD]+P.[S7]+P.[VM] = 0
		THEN T.Unit
		WHEN P.[96] <> 0
			AND P.[MV]+P.[S7] <> 0
			AND P.[1V]+P.[FE]+P.[PD]+P.[UY]+P.[VM] = 0
		THEN 'Blades'
		WHEN P.[96] <> 0
			AND P.[UY]+P.[MV]+P.[S7]+P.[1V]+P.[FE]+P.[PD] = 0
			AND P.[VM]+P.[UZ]+P.[FS] <> 0
		THEN 'Other ' + T.Unit
		ELSE T.Category
	END AS Category
	,T.Unit
	,CASE
		WHEN T.Line = '96'
		THEN T.Amount
		WHEN P.[96] <> 0
			AND P.[UY]+P.[MV]+P.[S7]+P.[1V]+P.[FE]+P.[PD] <> 0
			AND T.Line IN ('UY','MV','S7','1V','FE','PD')
		THEN P.[96]*(T.Amount/(P.[UY]+P.[MV]+P.[S7]+P.[1V]+P.[FE]+P.[PD]))
		WHEN P.[96] <> 0
			AND P.[MV]+P.[UZ] <> 0
			AND P.[1V]+P.[FS]+P.[FE] <> 0
		THEN P.[96]*(T.Amount/(P.[1V]+P.[FE]+P.[MV]+P.[PD]+P.[S7]+P.[UY]+P.[VM]+P.[UZ]+P.[FS]))
		ELSE T.Amount
	END AS Amount
	--,*
FROM <YourTable> T
	JOIN TPivot P
		ON T.ID = P.ID;
1 Like