question

kasimmohamed-6993 avatar image
0 Votes"
kasimmohamed-6993 asked EchoLiu-msft commented

Get mismatched location

Hi

i have a voucher table where it has a voucher with current location like below
create table #Voucher (code varchar(20), loc varchar(6))
insert into #Voucher values ('1001', '8')
insert into #Voucher values ('1002', '1')
insert into #Voucher values ('1003', '3')
insert into #Voucher values ('1004', '2')
select * from #Voucher;

and Location table have voucher table location history
create table #Location (code varchar(20), loc varchar(6), EnDate date)
insert into #Location values ('1001', '1', '2021-01-01')
insert into #Location values ('1001', '3', '2021-01-02')
insert into #Location values ('1001', '8', '2021-01-03')

insert into #Location values ('1002', '1', '2021-01-01')

insert into #Location values ('1003', '1', '2021-01-01')
insert into #Location values ('1003', '3', '2021-01-02')

insert into #Location values ('1004', '5', '2021-01-01')
insert into #Location values ('1004', '2', '2021-01-02')
select * from #Location;

i need to select the record where the voucher location start from '1'
from location table and current location from Voucher table not match with
initial location of Location table.

i need the result table like below
create table #Result (code varchar(20), Initialloc varchar(6), Currentloc varchar(6))
insert into #Result values ('1001', '1', '8')
insert into #Result values ('1003', '1', '3')

select * from #Result;

drop table #Voucher
drop table #Location
drop table #Result

Thanks

sql-server-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Could you please validate all the answers so far and provide any update?
If all of them are not working or helpful, please provide more sample data or details about your issue.

0 Votes 0 ·
Viorel-1 avatar image
0 Votes"
Viorel-1 answered Viorel-1 edited

Check theses interpretations:

 ;
 with CTE as
 (
     select code, loc, first_value(loc) over (partition by code order by EnDate) as fl
     from #Location
     where loc like '1%'
 )
 select CTE.code, CTE.loc as Initialloc, v.loc as Currentloc 
 from CTE 
 inner join #Voucher v on v.code = CTE.code and v.loc <> fl

or:

 select code, '1' as Initialloc, loc as Currentloc 
 from #Voucher v
 where exists (select * from #Location where code = v.code and loc = 1)
 intersect
 select code, '1', loc
 from #Location
 where loc <> 1

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @kasimmohamed-6993

Please also check:

 select l.code,l.loc Initialloc,v.loc Currentloc
 from #Location l
 join #Voucher v
 on l.code=v.code
 where l.loc=1 and l.loc<>v.loc

Or:

 select l.code,l.loc Initialloc,v.loc Currentloc
 from #Location l
 join #Voucher v
 on l.code=v.code
 where l.loc like '1%' and l.loc<>v.loc

Output:
111781-image.png


If you have any question, please feel free to let me know.


Regards
Echo


If the answer is helpful, please click "Accept Answer" and upvote it.


image.png (2.3 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.