Problem with creating quri for alocating amount

Transact-SQL
1

Good day some people alredy saw this qvestion from me but here i am again i made a good saple data this time

Step one 1

create table olap.sampledata1
(
[Date] date ,TC1 varchar(100),Amount_New decimal(10,2)
)
Step 2

INSERT INTO [OLAP].[sampledata1]
([Date]
,[TC1]
,[Amount_New])
VALUES ('2020-11-01','Houses:N-Home',70),
('2020-11-01','Houses:N-KL',80),
('2020-11-01','Default Category: Default Option',90),
('2020-11-01','Houses:NS-Home',22.5),
('2020-11-01','Houses:N-KL',45),
('2020-11-01','Houses:N-GR',22.5),
('2020-12-01','Houses:N-Home',100),
('2020-12-01','Default Category: Default Option',250),
('2020-12-01','Houses:N-Kl',110),
('2020-12-01','Houses:N-Home',24),
('2020-12-01','Houses:N-KL',12),
('2020-12-01','Houses:N-GR',60),
('2020-10-01','Houses:N-BR',24),
('2020-10-01','Houses:N-KL',12),
('2020-10-01','Houses:N-GR',60),
('2020-10-01','Houses:N-BR',24),
('2020-10-01','Default Category: Default Option',70)

Step 3

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

Step 4

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

Step 5

CREATE VIEW [OLAP].[VW_Deimos_view]
AS
select distinct
A1.Date,
A1.TC1,
A1.Amount_New,
case
when A1.Date = B2.Date
and a1.TC1 = b2.AllocateFromTc
then AllocateToTc
else a1.TC1
end as Tracking_Category_d,
case
when A1.TC1 = b2.AllocateFromTc
and A1.Date = B2.Date
then A1.Amount_New*[Percent]/100
else A1.Amount_New
end as Amountd

from olap.sampledata1 A1 , OLAP.Test B2

so mt problem is that when
i say where A1.Date <> B2.Date
its not aplying it
and that i need to breake line where
where A1.Date = B2.Date
and a1.TC1 = b2.AllocateFromTc
and after that i dont need a line that was broke

test10
test101325×515 46.5 KB

so i need to keep yelow and getd rid of red

2

Your sample data is wrong and your DDL doesn't work. On top of that you are trying to make 'Houses:NS-Home' = 'Houses: NS-Home' which it never will because of the space after the colon. I would guess you are looing for something like this a1.TC1 = b2.AllocateToTc

3 
use sqlteam
go


declare @sampledata1 table 
(
[Date] date ,TC1 varchar(100),Amount_New decimal(10,2)
)
--Step 2

INSERT INTO @sampledata1
([Date]
,[TC1]
,[Amount_New])
VALUES ('2020-11-01','Houses:N-Home',70),
('2020-11-01','Houses:N-KL',80),
('2020-11-01','Default Category: Default Option',90),
('2020-11-01','Houses:NS-Home',22.5),
('2020-11-01','Houses:N-KL',45),
('2020-11-01','Houses:N-GR',22.5),
('2020-12-01','Houses:N-Home',100),
('2020-12-01','Default Category: Default Option',250),
('2020-12-01','Houses:N-Kl',110),
('2020-12-01','Houses:N-Home',24),
('2020-12-01','Houses:N-KL',12),
('2020-12-01','Houses:N-GR',60),
('2020-10-01','Houses:N-BR',24),
('2020-10-01','Houses:N-KL',12),
('2020-10-01','Houses:N-GR',60),
('2020-10-01','Houses:N-BR',24),
('2020-10-01','Default Category: Default Option',70)


declare @test2 TABLE (
[Name] nvarchar(150) NULL,
[Source] nvarchar(150) NULL,
[AllocateToTc] nvarchar(150) NULL,
[Percent] nvarchar(150) NULL,
[date] nvarchar(150) NULL,
[AllocateFromTc] nvarchar(150) NULL
)

--Step 4

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


--CREATE VIEW [VW_Deimos_view]
--AS
select distinct
A1.Date,
A1.TC1,
A1.Amount_New,
case
when A1.Date = B2.Date
and a1.TC1 = b2.AllocateFromTc
then AllocateToTc
else a1.TC1
end as Tracking_Category_d,
case
when A1.TC1 = b2.AllocateFromTc
and A1.Date = B2.Date
then A1.Amount_New*[Percent]/100
else A1.Amount_New
end as Amountd

from @sampledata1 A1 , @test2 B2
where A1.Date <> B2.Date
4

So you want only the items highlighted in yellow? 8 rows?

5

i need to keep all exsept red

6

sorry ignore where A1.Date = B2.Date
and a1.TC1 = b2.AllocateFromTc i was doing some testing

7

how did you post this in this good format ?

8

i was trying to it by using union but when i was telling in second part dont show me where
A1.Date <> B2.Date it just ignore it and still show resolts

9

I know some magic tricks, you have to pay me to tell you

10

now please tell us why you want to keep all except the red. what is the rule/filter, business requirement(s) that dictates those in red to be excluded. in plain English, without using any SQL Query.

11

So ok the reason I don’t need the one that red is because that it messing my total amount
The hole idea of this query to create view that will automatically take information about one company and divide it and amount between others
As you cans see on screenshot under red line for example on date 2020-12-01
Amount is equal 250 and under it 4 yellow line that this amount is spread across
50+ 25 +125+ 50 = 250
So my query does it job and divide but it left red the one that was divided and it mess total because its already present in Coolum divided between yellow.
I added total amount new to check and see with one is divided and you can see that in front of yellow total is 250 show that it’s the result of divide by my query
So ya its just messing my total by being there and I don’t know how to get rid of it
i hope it make sence

12

"that it messing my total amount" & "as you cans see on screenshot under red line for example on date 2020-12-01 Amount is equal 250 and under it 4 yellow line"

are not a business requirement, we need logic to apply on the data to filter them out.

13

i just dont need to see defoult at Tracking_Category_d on dates that = dates in OLAP.Test B2

14

but your first row in the image is

Default Category: Default Option
15

yes its date is not in list in tabel olap test 2

in test i have 2020-11-01 and 2020-12-01
first one have 2020-10-01

so i dont need to see Default Category: Default Option
in coloum Tracking_Category_d
when dates in olap.sampledata1 is eqval dates from OLAP.Test B2
but i need defoult that not eqval datest in OLAP.Test B2

1 Like