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

Deimos_farm · February 1, 2021, 9:08am

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

harishgg1 · February 1, 2021, 10:46am

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'

Deimos_farm · February 1, 2021, 11:40am

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

harishgg1 · February 1, 2021, 12:24pm

then remove it

using where clause

where Tracking_Category_d <> 'x'

Deimos_farm · February 1, 2021, 10:13pm

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

Deimos_farm · February 2, 2021, 12:38am

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

harishgg1 · February 2, 2021, 6:27am

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

Deimos_farm · February 3, 2021, 1:35am

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

Deimos_farm · February 3, 2021, 1:39am

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

yosiasz · February 3, 2021, 2:10am

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

Deimos_farm · February 3, 2021, 6:10am

    a1 int        b2 nvarchar(600)

yosiasz · February 3, 2021, 6:34am

So Month is int Date is nvarchar(600)?

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

harishgg1 · February 3, 2021, 6:37am

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

harishgg1 · February 3, 2021, 6:57am

Deimos_farm · February 4, 2021, 6:50am

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

yosiasz · February 7, 2021, 1:59pm

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

Deimos_farm · February 10, 2021, 6:10am

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