Get mismatched location

kasim mohamed 581 Reputation points
2021-07-02T10:50:56.08+00:00

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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,693 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Viorel 112.1K Reputation points
    2021-07-02T11:23:03.553+00:00

    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
    
    0 comments No comments

  2. EchoLiu-MSFT 14,571 Reputation points
    2021-07-05T06:12:24.063+00:00

    Hi @kasim mohamed

    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.

    0 comments No comments