SQLTeam.com | Weblogs | Forums

Problem with creating a corect quri for breking a line in diferent lines

i created a quri that broke line in few saparate and when its come to resolt it still showing the one that i origanaly broke i dont need that how can i make it not show in final resolt
select distinct
A1.Month,
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 'x'
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 A1 , OLAP.Test B2

exsample of resolt the line that broke is x

Month Tracking_Category_d Amountd Amount
2020-11-01 Houses: NHome -168953.465 -675813.86
2020-11-01 Houses: N-KL -337906.93 -675813.86
2020-11-01 Houses: NGR -168953.465 -675813.86
2020-11-01 x -675813.86 -675813.86

hi

i dont think it broke ..

this case statement is putting the 'x'

case
when A1.Month = B2.Date
and a1.Tracking_Category_1 = AllocateFromTc
then AllocateToTc
else 'x'

yes but i dont need x where it separeted on other lines it mess up amount total

then remove it

using where clause

where Tracking_Category_d <> 'x'

1 Like

i tryed but it also remove x that i need
ihave other x with not broken ib other lines with i need to keep so my total would be eqval total of broken lines . its probably my bad i forgot to mantion that i also have a date colom with one of filters
so if case
when A1.Month = B2.Date
and a1.Tracking_Category_1 = AllocateFromTc
then AllocateToTc
else 'x'

and if A1.Month = B2.Date he doint it corect he show x
but when he show new lines and old one that was broke

so oridginaly its look like this

Month Tracking_Category_d Amountd Amount
2020-11-01 x -675813.86 -675813.86

then i got this
Month Tracking_Category_d Amountd Amount
2020-11-01 Houses: NHome -168953.465 -675813.86
2020-11-01 Houses: N-KL -337906.93 -675813.86
2020-11-01 Houses: NGR -168953.465 -675813.86
2020-11-01 x -675813.86 -675813.86

but i need
Month Tracking_Category_d Amountd Amount
2020-11-01 Houses: NHome -168953.465 -675813.86
2020-11-01 Houses: N-KL -337906.93 -675813.86
2020-11-01 Houses: NGR -168953.465 -675813.86

and i just relize that i cant reference Tracking_Category_d becouse its was just creasted in this view
so i cant aply where to it

is it possible to show data in excel and explain
..when 'x' is needed and 'x' not needed
image

yes here test4
so i have 1 defoult that is on 1/11/2020 this one is breake in 4 new one and i need it gone
and another defoult that is on 1/12/2020 this one is need to stay

so i come up with solution like this but the problem now is that i need it to be avtomatical in first part where i say
where A1.Month <> '2020-11-01'
and A1.Month <> '2020-12-01'

but if i say
A1.Month <> B2.Date
filter is not aplaying for some reson

but filter
A1.Month = B2.Date
is working
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.VW_CUBE_Finance_NAMES 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.VW_CUBE_Finance_NAMES A1 , OLAP.Test B2
where A1.Month = B2.Date

What are the data types for Month and date? Are they date or varchar?

    a1 int        b2 nvarchar(600)

So Month is int Date is nvarchar(600)?

Now show us some sample data in those columns real data just for those 2 columns

hi

i have created the sample data ..

drop table #sampledata 

create table #sampledata
(
[Date] date ,TC1 varchar(100),Amount_New  decimal(10,2) ,Amount_old int null
)
go 
																									
insert into #sampledata([Date],TC1,Amount_New,Amount_old) select  '1/11/2020','Houses:N-Home'					,70	   ,null
insert into #sampledata([Date],TC1,Amount_New,Amount_old) select  '1/11/2020','Houses:N-KL'					,80	   ,null
insert into #sampledata([Date],TC1,Amount_New,Amount_old) select  '1/11/2020','Default Category:Default Option',90	   ,90
insert into #sampledata([Date],TC1,Amount_New,Amount_old) select  '1/11/2020','Houses:NS-Home'					,22.5  ,90
insert into #sampledata([Date],TC1,Amount_New,Amount_old) select  '1/11/2020','Houses:N-KL'					,45	   ,90
insert into #sampledata([Date],TC1,Amount_New,Amount_old) select  '1/11/2020','Houses:N-GR'					,22.5  ,90
insert into #sampledata([Date],TC1,Amount_New,Amount_old) select  '1/12/2020','Houses:N-Home'					,100   ,null
insert into #sampledata([Date],TC1,Amount_New,Amount_old) select  '1/12/2020','Default Category:Default Option',250   ,null
insert into #sampledata([Date],TC1,Amount_New,Amount_old) select  '1/12/2020','Houses:N-Kl'					,110   ,null
insert into #sampledata([Date],TC1,Amount_New,Amount_old) select  '1/12/2020','Houses:N-Home'					,24	   ,null
insert into #sampledata([Date],TC1,Amount_New,Amount_old) select  '1/12/2020','Houses:N-KL'					,12	   ,null
insert into #sampledata([Date],TC1,Amount_New,Amount_old) select  '1/12/2020','Houses:N-GR'					,60	   ,null
insert into #sampledata([Date],TC1,Amount_New,Amount_old) select  '1/12/2020','Houses:N-BR'					,24	   ,null

select * from #sampledata

image

thank i am not a person who create tabels so didnt thout about it but i had a talk with person that did it and he say that there is no option when he create it is there way to make code transfer varchar to int

you have the dates in Month as 2021-01-01 format but your query does <> '01/01/2021'. Because both columns are varchar(600) its not going to work unless you do a cast on the where clause (very bad idea) or change the data types to date. Its not a date column, its varchar

thanks for tip manage to solve probleme just by adeting CAST(b2.date AS DATE)