Where statment is not working when reference to table

Question

Where statment is not working when reference to table

Deimos_farm

SSC Veteran

Points: 273
More actions
February 2, 2021 at 4:41 am

#3839959

Good day today i manage to come across a problem when i am using where statment in my quri and refer to table
like
when A1.Month <> B2.Date
and then cheking resolt they still contain resolt that equal A1.Month = B2.Date
but when i do it manualy
where A1.Month <> '2020-11-01'
and A1.Month <> '2020-12-01'
it do all corecly what can be a problem ?
below is workin exsample but insterd of
where A1.Month <> '2020-11-01'
and A1.Month <> '2020-12-01'
in firs i need to have
where A1.Month <> B2.Date
but id doesnt work (
select
A1.Month as Date,
A1.Scenario,
A1.Account,
A1.Organisation,
A1.Tracking_Category_2,
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 A1 , OLAP.Test B2
where A1.Month <> '2020-11-01'
and A1.Month <> '2020-12-01'
union
select
A1.Month as Date,
A1.Scenario,
A1.Account,
A1.Organisation,
A1.Tracking_Category_2,
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. A1 , OLAP.Test B2
where A1.Month = B2.Date

March 16, 2021 at 6:37 am

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ktflash Default port Points: 1400 More actions · Answer 1

For future posts plz take a look at this if you want help: https://www.sqlservercentral.com/blogs/how-to-post-a-t-sql-question-on-a-public-forum

To the problem: is this your issue, that these 2 where clauses queries dont produce the same result?

select * 
from OLAP. A1 , OLAP.Test B2 
where A1.Month <> '2020-11-01'
      and A1.Month <> '2020-12-01'

--VS

select *
from OLAP. A1 , OLAP.Test B2
where A1.Month <> B2.Date

There could be multiple issues, one way to help is show provide us some example data.

One of the most helpful things would be some results of the following, that would allow us to see what he actually compares here.

select A1.Month,B2.Date
from OLAP. A1 , OLAP.Test B2
where A1.Month <> B2.Date

My assumption is you are joining these 2 tables not with their primary keys.

If the only thing you filter is A1.Month <> B2.Date you basically create cartesian product --> match every row of table Test with every

row of table A1.

Example, im joining 2 calendars showing ur problem. Each table A and B contains every date of the year.

Here is ur issue displayed for an example day, 2012-01-01 is matched with every other day in the other table EXCEPT the one where its the same. So each row of table A is matched with every other row in the other table except the one where a.Date=b.Date

Unbenannt

I want to be the very best
Like no one ever was

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

Deimos_farm SSC Veteran Points: 273 More actions · Answer 2

yes you right i am combining 2 tabels with no primary keys how can iged rid of one that is not eqval here some sample data

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

DesNorton SSC-Insane Points: 24386 More actions · Answer 3

First, you want to be using the new join format

FROM        @sampledata1 AS A1
CROSS JOIN  @test2       AS B2

Second, your original post is looking for A1.Month <> B2.Date, but A1 does not have a Month column, and B1 date column is a string.

I would suggest converting the B1.Date to a date data type, and then getting the 1st day of the month for each table, and doing the WHRE based on the 1st day of the month.

SELECT      DISTINCT
            A1.Date
          , A1.TC1
          , A1.Amount_New
          , Tracking_Category_d = CASE
                                    WHEN A1.Date = CONVERT(date, B2.date)
                                     AND A1.TC1 = B2.AllocateFromTc THEN B2.AllocateToTc
                                    ELSE A1.TC1
                                  END
          , Amountd             = CASE
                                    WHEN A1.TC1 = B2.AllocateFromTc
                                     AND A1.Date = B2.date THEN A1.Amount_New * B2.[Percent] / 100
                                    ELSE A1.Amount_New
                                  END
FROM        @sampledata1 AS A1
CROSS JOIN  @test2       AS B2
WHERE       DATEADD(MONTH, DATEDIFF(MONTH, 0, A1.Date), 0) <> DATEADD(MONTH, DATEDIFF(MONTH, 0, CONVERT(date, B2.Date)), 0);

How to post data/code on a forum to get the best help.

Deimos_farm SSC Veteran Points: 273 More actions · Answer 4

wow i think thats workd can you explain how to use this @format i am self lener so dont know about it

Deimos_farm SSC Veteran Points: 273 More actions · Answer 5

sorry i tryed to aply this metod to my actual date bse and it still show the resolt how do i need to re right it

select DISTINCT
A1.Month as Date,
A1.Scenario,
A1.Account,
A1.Organisation,
A1.Tracking_Category_2,
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.VW_CUBE_Finance_NAMES as  A1  
CROSS JOIN  olap.Test4      AS B2
WHERE       DATEADD(MONTH, DATEDIFF(MONTH, 0, A1.Month), 0) <> DATEADD(MONTH, DATEDIFF(MONTH, 0, CONVERT(date, B2.Date)), 0)
order by date