Problem with creating quri for breking a line in diferent lines again

Transact-SQL
1

Hellow again i manage to solve one proble but now same problem pop ap again i hope this time ith my new expirence i can explain it better so i can get help

so i have a quri that take one line in table and if it math the condition in my situation is if
Date and Tracking_Category_1 in table OLAP.VW_CUBE_Finance
eqval Date and Tracking_Category_1 in OLAP.test2
then it take line that have both condition and brake it in 3 more and divide amount that was in original line bitwin new ones

test7
test7873×124 4.15 KB

amountd is total amount spread bitvit new lines
my quri for that look like this

A1.Tracking_Category_1,
case
when A1.Month = CAST(b2.date AS DATE)
and a1.Tracking_Category_1 = AllocateFromTc
then AllocateToTc
else a1.Tracking_Category_1
end as Tracking_Category_d,
A1.TEXT_VAL,
case
when A1.Tracking_Category_1 = AllocateFromTc
and A1.Month = CAST(b2.date AS DATE)
then A1.Amount*[Percent]/100
else A1.Amount
end as Amountd
,A1.Amount

from OLAP.VW_CUBE_Finance_NAMES A1 , OLAP.Test B2
data in olap test look like this

test8
test8888×140 3.87 KB

so my problem is that in my final resolt i dont need to see the line that
is braking apart i will color it red i need to se only yelow
but i still se red one

test9
test9872×125 18.3 KB

i tryed to to used where to filter a resolt but there i ahve a problem to i have another data that dont need to be modifire outside of A1.Month = CAST(b2.date AS DATE)

and i cant efferct Tracking_Category_d becouse its just created in side of this quri

so how can i modifire my quri so i am is goint to get only yelow resolt and evrything out of it but not red

so far my final curi look like this

A1.Tracking_Category_1,
case
when A1.Month = CAST(b2.date AS DATE)
and a1.Tracking_Category_1 = AllocateFromTc
then AllocateToTc
else Tracking_Category_1
end as Tracking_Category_d,
A1.TEXT_VAL,
case
when A1.Tracking_Category_1 = AllocateFromTc
and A1.Month = CAST(b2.date AS DATE)
then A1.Amount*[Percent]/100
else A1.Amount
end as Amountd
,A1.Amount

from OLAP.VW_CUBE_Finance_NAMES A1 , OLAP.test2 b2
where A1.Month = CAST(b2.date AS DATE)
union
A1.Tracking_Category_1,
case
when A1.Month = CAST(b2.date AS DATE)
and a1.Tracking_Category_1 = AllocateFromTc
then AllocateToTc
else a1.Tracking_Category_1
end as Tracking_Category_d,
A1.TEXT_VAL,
case
when A1.Tracking_Category_1 = AllocateFromTc
and A1.Month = CAST(b2.date AS DATE)
then A1.Amount*[Percent]/100
else A1.Amount
end as Amountd
,A1.Amount

from OLAP.VW_CUBE_Finance_NAMES A1 , OLAP.Test B2
where A1.Month <> CAST(b2.date AS DATE)

2

DDL and sample data would be helpful, but a wild guess is can't you wrap it all in a subselect like this

select * from (
YOURQUERY) v
where tracking_Cagtegory_1 <> tracking_Cagtegory_d

1 Like
3

@Deimos_farm because we do not have access to your sql server data and in order to answer your question you need to give us sample data not as pictures but as follows

declare @VW_CUBE_Finance_NAMES(Name varchar(150), Source varchar(50),
AllocateToTc varchar(50), Percent int, FinanceDate date, AllocateFromTc)

insert into @VW_CUBE_Finance_NAMES
select 1, 'User', 'Houses: -@ Home', 25, '2020-01-11', 
'Default Category: Default Option' union all
select 2  -etc etc etc with all of the data in your image

You are asking for help so you need to provide this sample data which makes us have sample of your data in our sql server.

4

a ups i am slow i didnt relize that you need to try run it on you sql here sample they should let have you a copy of my data baze

INSERT INTO [OLAP].[VW_CUBE_Finance_NAMES]
([Month]
,[Scenario]
,[Account]
,[Organisation]
,[Tracking_Category_1]
,[Tracking_Category_2]
,[TEXT_VAL]
,[Amount])
VALUES
(<Month, nvarchar(600),>
,<Scenario, nvarchar(600),>
,<Account, nvarchar(600),>
,<Organisation, nvarchar(600),>
,<Tracking_Category_1, nvarchar(600),>
,<Tracking_Category_2, nvarchar(600),>
,<TEXT_VAL, nvarchar(max),>
,<Amount, numeric(38,20),>)
INSERT INTO [OLAP].[VW_CUBE_Finance_NAMES]
([Month]
,[Scenario]
,[Account]
,[Organisation]
,[Tracking_Category_1]
,[Tracking_Category_2]
,[TEXT_VAL]
,[Amount])
VALUES
('2020-11-1', 'Budget', 'Blackwood', 'dream', 'Default Category: Default Option' ,'Default Category: Default Option' ,'NULL',-5000),
('2020-11-1', 'Budget', 'Blackwood', 'dream', 'Default Category: Default Option' ,'Default Category: Default Option' ,'NULL',-500),
('2020-11-1', 'Budget', 'Blackwood', 'dream', 'Default Category: Default Option' ,'Default Category: Default Option' ,'NULL',-20),
('2020-11-1', 'Budget', 'Blackwood', 'dream', 'Default Category: Default Option' ,'Default Category: Default Option' ,'NULL', 952245.89),
('2020-12-1', 'Actual', 'Blackwood', 'dream', 'Default Category: Default Option' ,'Default Category: Default Option' ,'NULL',-5685.99),
('2020-12-1', 'Actual', 'Blackwood', 'dream', 'Default Category: Default Option' ,'Default Category: Default Option' ,'NULL',-0.02),
('2020-12-1', 'Actual', 'Blackwood', 'dream', 'Default Category: Default Option' ,'Default Category: Default Option' ,'NULL',-129486),
('2020-12-1', 'Actual', 'Blackwood', 'dream', 'Default Category: Default Option' ,'Default Category: Default Option' ,'NULL',391.46),
('2016-06-1', 'Actual', 'dlimk', 'Gor', 'Default Category: Default Option' ,'Default Category: Default Option','NULL',-186),
('2016-06-1', 'Actual', 'dlimk', 'Gor', 'Default Category: Default Option' ,'Default Category: Default Option','NULL',-12256.07),
('2016-06-1', 'Actual', 'dlimk', 'Gor', 'Default Category: Default Option' ,'Default Category: Default Option','NULL',942.18),
('2016-06-1', 'Actual', 'dlimk', 'Gor', 'Default Category: Default Option' ,'Default Category: Default Option','NULL',-4172.18)

INSERT INTO [OLAP].[test2]
([Name]
,[Source]
,[AllocateToTc]
,[Percent]
,[date]
,[AllocateFromTc])
VALUES
(<Name, nvarchar(600),>
,<Source, nvarchar(600),>
,<AllocateToTc, nvarchar(600),>
,<Percent, nvarchar(600),>
,<date, nvarchar(600),>
,<AllocateFromTc, nvarchar(600),>)

INSERT INTO [OLAP].[test2]
([Name]
,[Source]
,[AllocateToTc]
,[Percent]
,[date]
,[AllocateFromTc])
VALUES
('1','User','Houses: NSW001-@Home','25','2020-11-1','Default Category: Default Option'),
('2','User','Houses: N-01-KL','50','2020-11-1','Default Category: Default Option'),
('3','User','Houses: N-02-GR','25','2020-11-1','Default Category: Default Option'),
('4','User','Houses: N-@Home','20','2020-12-1','Default Category: Default Option'),
('5','User','Houses: N-01-KL','10','2020-12-1','Default Category: Default Option'),
('6','User','Houses: N-02-GR','50','2020-12-1','Default Category: Default Option'),
('7','User','Houses: N-03-BR','20','2020-12-1','Default Category: Default Option')

5

very good Dimitri , now please also provide

create table #test2
create table #VW_CUBE_Finance_NAMES

again remember we do not have access to your database so we do not know the data types for the columns. Also we do not have the OLAP schema, so you can make those temp tables as show with #

Also please test out the DDL and DML you provide in a local database. only post something you personally have tested and are sure works.

6

CREATE TABLE [OLAP].[test2](
[Name] nvarchar NULL,
[Source] nvarchar NULL,
[AllocateToTc] nvarchar NULL,
[Percent] nvarchar NULL,
[date] nvarchar NULL,
[AllocateFromTc] nvarchar NULL
) ON [PRIMARY]
GO

7

Still waiting for VW_CUBE_Finance_NAMES and did you test your sample script you provided locally before posting?

8

yes i am trying to make it right becouse VW_CUBE_Finance_NAMES
use joins from another taybel where is just numbers so i am trying to make right infor

9

CREATE TABLE [OLAP].[CUBE_Finance_WB](
[Month] [int] NOT NULL,
[Scenario] [int] NOT NULL,
[Account] [int] NOT NULL,
[Organisation] [int] NOT NULL,
[Tracking_Category_1] [int] NOT NULL,
[Tracking_Category_2] [int] NOT NULL,
[TEXT_VAL] nvarchar NULL,
[Amount] [numeric](38, 20) NULL,
CONSTRAINT [PK__Finance_WB] PRIMARY KEY NONCLUSTERED
(
[Month] ASC,
[Scenario] ASC,
[Account] ASC,
[Organisation] ASC,
[Tracking_Category_1] ASC,
[Tracking_Category_2] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [OLAP].[CUBE_Finance_WB] WITH CHECK ADD CONSTRAINT [FK__CUBE_Finance_WB__Account__Account__ID] FOREIGN KEY([Account])
REFERENCES [OLAP].[Account] ([ID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [OLAP].[CUBE_Finance_WB] CHECK CONSTRAINT [FK__CUBE_Finance_WB__Account__Account__ID]
GO

ALTER TABLE [OLAP].[CUBE_Finance_WB] WITH CHECK ADD CONSTRAINT [FK__CUBE_Finance_WB__Month__Month__ID] FOREIGN KEY([Month])
REFERENCES [OLAP].[Month] ([ID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [OLAP].[CUBE_Finance_WB] CHECK CONSTRAINT [FK__CUBE_Finance_WB__Month__Month__ID]
GO

ALTER TABLE [OLAP].[CUBE_Finance_WB] WITH CHECK ADD CONSTRAINT [FK__CUBE_Finance_WB__Organisation__Organisation__ID] FOREIGN KEY([Organisation])
REFERENCES [OLAP].[Organisation] ([ID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [OLAP].[CUBE_Finance_WB] CHECK CONSTRAINT [FK__CUBE_Finance_WB__Organisation__Organisation__ID]
GO

ALTER TABLE [OLAP].[CUBE_Finance_WB] WITH CHECK ADD CONSTRAINT [FK__CUBE_Finance_WB__Scenario__Scenario__ID] FOREIGN KEY([Scenario])
REFERENCES [OLAP].[Scenario] ([ID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [OLAP].[CUBE_Finance_WB] CHECK CONSTRAINT [FK__CUBE_Finance_WB__Scenario__Scenario__ID]
GO

ALTER TABLE [OLAP].[CUBE_Finance_WB] WITH CHECK ADD CONSTRAINT [FK__CUBE_Finance_WB__Tracking_Category_1__Tracking_Category_1__ID] FOREIGN KEY([Tracking_Category_1])
REFERENCES [OLAP].[Tracking_Category_1] ([ID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [OLAP].[CUBE_Finance_WB] CHECK CONSTRAINT [FK__CUBE_Finance_WB__Tracking_Category_1__Tracking_Category_1__ID]
GO

ALTER TABLE [OLAP].[CUBE_Finance_WB] WITH CHECK ADD CONSTRAINT [FK__CUBE_Finance_WB__Tracking_Category_2__Tracking_Category_2__ID] FOREIGN KEY([Tracking_Category_2])
REFERENCES [OLAP].[Tracking_Category_2] ([ID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [OLAP].[CUBE_Finance_WB] CHECK CONSTRAINT [FK__CUBE_Finance_WB__Tracking_Category_2__Tracking_Category_2__ID]
GO

NSERT INTO [OLAP].[CUBE_Finance_WB]
([Month]
,[Scenario]
,[Account]
,[Organisation]
,[Tracking_Category_1]
,[Tracking_Category_2]
,[TEXT_VAL]
,[Amount])
VALUES
(<Month, int,>
,<Scenario, int,>
,<Account, int,>
,<Organisation, int,>
,<Tracking_Category_1, int,>
,<Tracking_Category_2, int,>
,<TEXT_VAL, nvarchar(max),>
,<Amount, numeric(38,20),>)
GO

INSERT INTO [OLAP].[CUBE_Finance_WB]
([Month]
,[Scenario]
,[Account]
,[Organisation]
,[Tracking_Category_1]
,[Tracking_Category_2]
,[TEXT_VAL]
,[Amount])
VALUES
(20201101, 1, 1, 1, 1, 1, NULL, -5000),
(20201101, 2, 2, 1, 1, 1, NULL, -500),
(20201101, 3, 2, 1, 1, 1, NULL, -20),
(20201101, 3, 2, 1, 1, 1, NULL, 952245.89),
(20201201, 3, 2, 1, 1, 1, NULL, -5685.99),
(20201201, 3, 2, 1, 1, 1, NULL, -0.02),
(20201201, 3, 2, 1, 1, 1, NULL, -129486),
(20201201, 3, 2, 1, 1, 1, NULL, 391.46),
(20160601, 3, 2, 2, 1, 1, NULL, -186),
(20160601, 3, 2, 2, 1, 1, NULL, -12256.07),
(20160601, 3, 2, 2, 1, 1, NULL, 942.18),
(20160601, 3, 2, 2, 1, 1, NULL, -4172.18)

CREATE VIEW [OLAP].[VW_CUBE_Finance_NAMES]
AS
SELECT
A1.NAME as [Month],
A2.NAME as [Scenario],
A3.NAME as [Account],
A4.NAME as [Organisation],
A5.NAME as [Tracking_Category_1],
A6.NAME as [Tracking_Category_2],
C2.TEXT_VAL,
C2.Amount
FROM [OLAP].[CUBE_Finance_WB] c2
Inner JOIN [OLAP].[Month]A1
ON A1.ID = C2.[Month]
Inner JOIN [OLAP].[Scenario]A2
ON A2.ID = C2.[Scenario]
Inner JOIN [OLAP].[Account]A3
ON A3.ID = C2.[Account]
Inner JOIN [OLAP].[Organisation]A4
ON A4.ID = C2.[Organisation]
Inner JOIN [OLAP].[Tracking_Category_1]A5
ON A5.ID = C2.[Tracking_Category_1]
Inner JOIN [OLAP].[Tracking_Category_2]A6
ON A6.ID = C2.[Tracking_Category_2]

GO

10

Please check your DDL and data. It doesn't work (yes I'm using temp tables)

Violation of PRIMARY KEY constraint 'PK__Finance_WB'. Cannot insert duplicate key in object 'dbo.#CUBE_Finance_WB'. The duplicate key value is (20201101, 3, 2, 1, 1, 1).

Also, using Cascade Update and Cascade Delete on the FKs, puts a lot of strain on the db. A simple delete has to go through all the tables that have FKs on them to delete them even if they are already deleted. A better way would be for you to control what is deleted and delete from each column before deleting the parent record. That way you know EXACTLY what is being deleted

11

yikes! Maybe someone else can try. I don't think I might be explaining this the proper way.

12

This is what I was able to cobble together for DDL and data, but I get the error above

Summary

CREATE TABLE #CUBE_Finance_WB(
[Month] [int] NOT NULL,
[Scenario] [int] NOT NULL,
[Account] [int] NOT NULL,
[Organisation] [int] NOT NULL,
[Tracking_Category_1] [int] NOT NULL,
[Tracking_Category_2] [int] NOT NULL,
[TEXT_VAL] nvarchar NULL,
[Amount] [numeric](38, 20) NULL,
CONSTRAINT [PK__Finance_WB] PRIMARY KEY NONCLUSTERED
(
[Month] ASC,
[Scenario] ASC,
[Account] ASC,
[Organisation] ASC,
[Tracking_Category_1] ASC,
[Tracking_Category_2] ASC
)
)

INSERT INTO #CUBE_Finance_WB
([Month]
,[Scenario]
,[Account]
,[Organisation]
,[Tracking_Category_1]
,[Tracking_Category_2]
,[TEXT_VAL]
,[Amount])
VALUES
(20201101, 1, 1, 1, 1, 1, NULL, -5000),
(20201101, 2, 2, 1, 1, 1, NULL, -500),
(2020101, 3, 2, 1, 1, 1, NULL, -20),
(20201101, 3, 2, 1, 1, 1, NULL, 952245.89),
(20201201, 3, 2, 1, 1, 1, NULL, -5685.99),
(20201201, 3, 2, 1, 1, 1, NULL, -0.02),
(20201201, 3, 2, 1, 1, 1, NULL, -129486),
(20201201, 3, 2, 1, 1, 1, NULL, 391.46),
(20160601, 3, 2, 2, 1, 1, NULL, -186),
(20160601, 3, 2, 2, 1, 1, NULL, -12256.07),
(20160601, 3, 2, 2, 1, 1, NULL, 942.18),
(20160601, 3, 2, 2, 1, 1, NULL, -4172.18)

Create table #VW_CUBE_Finance_NAMES
(
[Month] nvarchar(600),
Scenario nvarchar(600),
Account nvarchar(600),
Organisation nvarchar(600),
Tracking_Category_1 nvarchar(600),
Tracking_Category_2 nvarchar(600),
TEXT_VAL nvarchar(max),
Amount numeric(38,20),)

INSERT INTO #VW_CUBE_Finance_NAMES
([Month]
,[Scenario]
,[Account]
,[Organisation]
,[Tracking_Category_1]
,[Tracking_Category_2]
,[TEXT_VAL]
,[Amount])
VALUES
('2020-11-1', 'Budget', 'Blackwood', 'dream', 'Default Category: Default Option' ,'Default Category: Default Option' ,'NULL',-5000),
('2020-11-1', 'Budget', 'Blackwood', 'dream', 'Default Category: Default Option' ,'Default Category: Default Option' ,'NULL',-500),
('2020-11-1', 'Budget', 'Blackwood', 'dream', 'Default Category: Default Option' ,'Default Category: Default Option' ,'NULL',-20),
('2020-11-1', 'Budget', 'Blackwood', 'dream', 'Default Category: Default Option' ,'Default Category: Default Option' ,'NULL', 952245.89),
('2020-12-1', 'Actual', 'Blackwood', 'dream', 'Default Category: Default Option' ,'Default Category: Default Option' ,'NULL',-5685.99),
('2020-12-1', 'Actual', 'Blackwood', 'dream', 'Default Category: Default Option' ,'Default Category: Default Option' ,'NULL',-0.02),
('2020-12-1', 'Actual', 'Blackwood', 'dream', 'Default Category: Default Option' ,'Default Category: Default Option' ,'NULL',-129486),
('2020-12-1', 'Actual', 'Blackwood', 'dream', 'Default Category: Default Option' ,'Default Category: Default Option' ,'NULL',391.46),
('2016-06-1', 'Actual', 'dlimk', 'Gor', 'Default Category: Default Option' ,'Default Category: Default Option','NULL',-186),
('2016-06-1', 'Actual', 'dlimk', 'Gor', 'Default Category: Default Option' ,'Default Category: Default Option','NULL',-12256.07),
('2016-06-1', 'Actual', 'dlimk', 'Gor', 'Default Category: Default Option' ,'Default Category: Default Option','NULL',942.18),
('2016-06-1', 'Actual', 'dlimk', 'Gor', 'Default Category: Default Option' ,'Default Category: Default Option','NULL',-4172.18)

Create table #test2
(Name nvarchar(600),
Source nvarchar(600),
AllocateToTc nvarchar(600),
Perc nvarchar(600),
dat nvarchar(600),
AllocateFromTc nvarchar(600))

INSERT INTO #test2
([Name]
,[Source]
,[AllocateToTc]
,[Perc]
,[dat]
,[AllocateFromTc])
VALUES
('1','User','Houses: NSW001-@Home','25','2020-11-1','Default Category: Default Option'),
('2','User','Houses: N-01-KL','50','2020-11-1','Default Category: Default Option'),
('3','User','Houses: N-02-GR','25','2020-11-1','Default Category: Default Option'),
('4','User','Houses: N-@Home','20','2020-12-1','Default Category: Default Option'),
('5','User','Houses: N-01-KL','10','2020-12-1','Default Category: Default Option'),
('6','User','Houses: N-02-GR','50','2020-12-1','Default Category: Default Option'),
('7','User','Houses: N-03-BR','20','2020-12-1','Default Category: Default Option')

13

ok i belive this time i got evrything corect
Step 1

CREATE TABLE [OLAP].[test3]
([Source] nvarchar NULL,
[AllocateToTc] nvarchar NULL,
[Percent] nvarchar NULL,
[date] nvarchar NULL,
[AllocateFromTc] nvarchar NULL
) ON [PRIMARY]
GO
INSERT INTO [OLAP].[test3]
([Source]
,[AllocateToTc]
,[Percent]
,[date]
,[AllocateFromTc])
VALUES
('User','Houses: N-@Home','25','2020-11-01','Default Category: Default Option'),
('User','Houses: N-01','50','2020-11-01','Default Category: Default Option'),
('User','Houses: N-02','25','2020-11-01','Default Category: Default Option'),
('User','Houses: N-@Home','20','2020-12-01','Default Category: Default Option'),
('User','Houses: N-01','10','2020-12-01','Default Category: Default Option'),
('User','Houses: N-02','50','2020-12-01','Default Category: Default Option'),
('User','Houses: N-03','20','2020-12-01','Default Category: Default Option')

Step 2

CREATE TABLE [OLAP].[CUBE_Finance_WB2](
[Month] nvarchar(600) NOT NULL,
[Scenario] nvarchar(600) NOT NULL,
[Account] nvarchar(600) NOT NULL,
[Organisation] nvarchar(600) NOT NULL,
[Tracking_Category_1] nvarchar(600) NOT NULL,
[TEXT_VAL] nvarchar NULL,
[Amount] [numeric](38, 20) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

INSERT INTO [OLAP].[CUBE_Finance_WB2]
([Month]
,[Scenario]
,[Account]
,[Organisation]
,[Tracking_Category_1]
,[Amount])
VALUES
('2016-06-01','Actual','GH','GH','Default Category: Default Option',733.80000000000000000000),
('2016-06-01','Actual','GH','GH','Default Category: Default Option' ,-10100.52000000000000000000),
('2016-06-01','Actual','GHc','GHc','Default Category: Default Option' ,-452.03000000000000000000),
('2016-06-01','Actual','GH','GH','Default Category: Default Option' ,-6642300.33000000000000000000),
('2020-11-01','Actual','GH','GHA','Default Category: Default Option' ,-185574.00000000000000000000),
('2020-11-01','Actual','GH','GH','Default Category: Default Option' ,10.00000000000000000000),
('2020-11-01','Actual','GH','GH','Default Category: Default Option' ,172.31000000000000000000),
('2020-11-01','Actual','GH','GH','Default Category: Default Option' ,29536.14000000000000000000),
('2020-11-01','Actual','GH','GH','Default Category: Default Option' ,426.22000000000000000000),
('2020-11-01','Budget','GH','GH','Default Category: Default Option' ,-500.00000000000000000000),
('2020-11-01','Budget','GH','GHP','Default Category: Default Option' ,1556.73000000000000000000),
('2020-11-01','Actual','GH','GH','Default Category: Default Option' ,490.18000000000000000000),
('2020-11-01','Budget','GH','GH','Default Category: Default Option' ,-20.00000000000000000000),
('2020-11-01','Actual','GH','GH','Default Category: Default Option' ,-7749.53000000000000000000),
('2020-12-01','Actual','GH','GH','Default Category: Default Option' ,777.17000000000000000000),
('2020-12-01','Actual','GH','GH','Default Category: Default Option' ,421.41000000000000000000),
('2020-12-01','Actual','GH','GH','Default Category: Default Option' ,0.06000000000000000000),
('2020-12-01','Actual','GH','GH','Default Category: Default Option' ,488.98000000000000000000),
('2021-05-01','Budget','GH','GH','Default Category: Default Option' ,57096.75000000000000000000),
('2021-05-01','Budget','GH','GH','Default Category: Default Option' ,-20.00000000000000000000),
('2021-06-01','Budget','GH','GH','Default Category: Default Option' ,-20.00000000000000000000),
('2021-06-01','Budget','GH','GH','Default Category: Default Option' ,-5000.00000000000000000000),
('2021-06-01','Budget','GH','GH','Default Category: Default Option' ,57096.75000000000000000000),
('2021-06-01','Budget','GH','GH','Default Category: Default Option' ,1556.73000000000000000000)

GO
Step 3

CREATE VIEW [OLAP].[VW_Deimos_view]
AS
select distinct
A1.Month as Date,
A1.Scenario,
A1.Account,
A1.Organisation,
A1.Tracking_Category_1,
case
when A1.Month = B2.Date
and a1.Tracking_Category_1 = AllocateFromTc
then AllocateToTc
else a1.Tracking_Category_1
end as Tracking_Category_d,
A1.TEXT_VAL,
case
when A1.Tracking_Category_1 = AllocateFromTc
and A1.Month = B2.Date
then A1.Amount*[Percent]/100
else A1.Amount
end as Amountd
,A1.Amount

from OLAP.CUBE_Finance_WB2 A1 , OLAP.test3 B2

14

i posted update i belive this time corect

15

Did you try to run your DDL??

Msg 8152, Level 16, State 4, Line 9
String or binary data would be truncated.
The statement has been terminated.

Also, how do you join these tables? This is a cartesian join, so all records joined to all records

from OLAP.CUBE_Finance_WB2 A1 , OLAP.test3 B2

16

I think the issue is when people post on this forum DDL stuff without the three code ticks ` it changes varchar(200) to just varchar.

@Deimos_farm please re-post or edit your code with the three ` in front and after your code