Problem with creating query for allocating amount
-
good day i came a cross of a problem
I have a query that takes 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
equal Date and Tracking_Category_1 in OLAP.test2
then it take line that have both condition and braek it in 3 more and divide amount that was in original line between new ones
its successfully done what it mus tmut now i need to put in view that will replace original data with new one and leave rest in peace
so that what i get in example of my test data base i dont need red but need to keep yellow and white
here is data for base
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?- This topic was modified 3 years, 10 months ago by Steve Jones - SSC Editor. Reason: minor spelling and code formatting
-
I think the following might give you the data rows you need?:
declare @sampledata1 table
(
[Date] date ,TC1 varchar(100),Amount_New decimal(10,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)
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 b2.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
--Tracking_category_d is displaying a1.tc1 and a1.tc1 != 'Default Category: Default Option'
(not (A1.Date = B2.Date and a1.TC1= b2.AllocateFromTc)
and a1.TC1!='Default Category: Default Option')
or
--Tracking_category_d is displaying b2.AllocateToTc so include it in the results
(A1.Date = B2.Date and a1.TC1= b2.AllocateFromTc)If it is correct then I will try to improve the query to give the correct order.
I put some comments in the where clause so you can see, and check, my logic.
-
it does the right thing but it also remove Default Category: Default Option on 2020-10-01 that i need to keep
-
Sorry for my mistake. I am having another look at your question and I noticed something:
You said that you need to keep the items marked yellow in your attachment. On line 6 column tracking_category_d is marked in yellow and has the value 'houses:nsw001-@Home'.
However 'houses:nsw001-@Home' is not in your test data?
Did I misunderstand something or is there a difference between your test data and the attachment?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply