Need help with corecly applyingwhere statment

1

Good day i trying to filter filter out information in my tabel i have a quri
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.Test4 B2
that give me
test11

so now i need to filter out information i dont need to have Default Category: Default Option
in Tracking_Category_d on 2020-12-01 and 2020-11-01 but need to keep one on 2020-10-01

here test data

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

2

I don't understand why the one category is only showing the first entry. The business rules are not supplied and the cartesian join will create huge data sets, but you can try this cte

;with cte as (
select  Date, tc1, amount_new, Tracking_Category_d, Amountd,
		Row_number() over (partition by tc1, Tracking_Category_d order by date) as RowNum
  from (
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
) v
) 


select Date, tc1, amount_new, Tracking_Category_d, Amountd
  from cte 
  where Tracking_Category_d = 'Default Category: Default Option'
    and rownum = 1
union all
select Date, tc1, amount_new, Tracking_Category_d, Amountd
  from cte 
  where Tracking_Category_d != 'Default Category: Default Option'
3

what do you mean by business rules?

4

A business rule would define why/when we need to keep only one of these types, but not one of Houses: N-GR

5

has already been asked to define business rule