Problem with eqval and not eqval on sql

Transact-SQL
1

Good day today come across of a intrsting problem in end of my curi when i say
A1.Month = b2.date its working but when i am saying
A1.Month = b2.date or A1.Month != b2.date or
where not A1.Month = b2.date its not working what can be a problem ?

2

my idea

these sort of things are very common in SQL

everyone runs into these issues ALL THE TIME

debugging .. is very common

there are ways to find out what's the problem
( that's another PHD subject by itself )

First
create Sample Data

Next
do the testing

3 
drop table #sampledata
create table #sampledata
(
month date, 
Date date
)

insert into #sampledata values
('2020-10-09','2019-11-14'),
('2021-10-09','2021-10-09')

select 'sample data', * from #sampledata 

select 'a.month= b.Date',* from #sampledata a join #sampledata b on a.month= b.Date

select 'A.Month = b.date or A.Month != b.date',* from #sampledata a join #sampledata b on A.Month = b.date or A.Month != b.date

image
image725×288 7.29 KB

4

Is there more filters in the where clause you are not showing us. When you introduce OR in the where clause you have to remember to use () parenthesis in appropriate places

5

What do you mean by "not working"?

Also, be aware that a NULL value will fail all = or != tests.

6

the value is not nul its nvarcahr on both sides
and by not working i mean tat when i apply = parameter is does the calcuklation
and when i am tryin anything ot of <> =! or not it just ignore and show evrything

7

We need to see the full query. And some sample data rows via CREATE TABLE and INSERT statements. Sounds like there is something wrong in your join.

8

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