Post reply

Returning a record when a missing value in a table exists.

  • November 6, 2018 at 8:54 am

    #376353

    Hello all, 

     I have a scenario where I need to show a currency exchange in a result set. The problem I've found is that when all of the joins are in place 1 record is missing from my anchor table. See the below code which you could run in tempdb. 
    The result is 
    Gi Pdate Vend VendInvNum USDebit USCredit LcyAmt Rate
    12345 2018-01-01 8880 4343 500.00 0.00 400.00 0.80
    12346 2018-01-02 8881 5343 1010.00 0.00 818.10 0.81
    12347 2018-01-03 8882 6343 70.00 0.00 57.40 0.82
    12349 2018-01-05 8880 4345 100.50 0.00 83.42 0.83
    72351 2018-01-05 8880 0 0.00 25.00 0.00 0.83

    The record I am missing is from the #gl table. This is due to the #exrate table not having a value on '01/04/2018'. 
     72350,01/04/2018  8883 0.00 40.00 3 1127
    How can I get t-sql to the next record in the #exrate table?
    Since '01/04/2018' does not exist I would want to use '01/05/2018'

    use tempdb
    drop table #gl;
    drop table #inv;
    drop table #exrate
    --create temp tables
    create table #gl
    (
     Gi   int   not null
     ,Pdate  date  not null
     ,Vend  int   not null
     ,USDebit decimal(10,2)  not null
     ,USCredit decimal(10,2)  not null
     ,DocType int   not null
     ,AcctNum int   not null
    )
    create table #inv
    (
     PpI   int   not null
     ,Pdate  date  not null
     ,Vend  int   not null
     ,LcyAmt  decimal(10,2)  not null
     ,VendInvNum int   not null
    )
    create table #exrate
    (
     SDate  date  not null
     ,Rate  decimal(10,2)  not null
     ,CurrId  varchar(50) not null
    )

    --insert test data
    insert into #gl
    (
     Gi,Pdate,Vend,USDebit,USCredit,Doctype,AcctNum
    )
    Values
     ('12345','01/01/2018','8880','500.00','0.00','2','1127')
     ,('12346','01/02/2018','8881','1010.00','0.00','2','1127')
     ,('12347','01/03/2018','8882','70.00','0.00','2','1127')
     ,('72350','01/04/2018','8883','0.00','40.00','3','1127')
     ,('12348','01/05/2018','8880','90.00','0.00','2','1128')
     ,('12349','01/05/2018','8880','100.50','0.00','2','1127')
     ,('72351','01/05/2018','8880','0.00','25.00','3','1127')
    insert into #inv
    (
     PpI, Pdate,Vend,LcyAmt,VendInvNum
    )
    Values
     ('12345','01/01/2018','8880','400.00','4343')
     ,('12346','01/02/2018','8881','818.10','5343')
     ,('12347','01/03/2018','8882','57.40','6343')
     ,('12348','01/05/2018','8880','74.70','4344')
     ,('12349','01/05/2018','8880','83.42','4345')
    insert into #exrate
    (
     Sdate,Rate,CurrId
    )
    Values
     ('01/01/2018','0.8','GBP')
     ,('01/01/2018','1.3','CAD')
     ,('01/02/2018','0.81','GBP')
     ,('01/02/2018','1.31','CAD')
     ,('01/03/2018','0.82','GBP')
     ,('01/03/2018','1.32','CAD')
     ,('01/05/2018','0.83','GBP')
     ,('01/05/2018','1.33','CAD')

    --check inserts
    select * from #gl;
    select * from #inv;
    select * from #exrate;

    --create anchor table
    With genl as
    (
    select Gi
    ,Pdate
    ,Vend
    ,USDebit
    ,USCredit
    from #gl
    where AcctNum = 1127 and DocType in ('2','3')
    )
    select
    g.Gi
    ,g.Pdate
    ,g.Vend
    ,isnull(i.VendInvNum,0) as VendInvNum
    ,g.USDebit
    ,g.USCredit
    ,isnull(i.LcyAmt,0) as LcyAmt
    ,e.Rate
    from genl as g
    left join #inv as i
    on g.Gi = i.PpI
    left join #exrate as e
    on g.Pdate = e.SDate
    where e.CurrId = 'GBP'



     I look forward to your response and thanks in advance.

  • November 6, 2018 at 9:38 am

    #2012561

    Hi
    Since you have added a where clause on the left join - it will behave like a inner join - if you replace this with 'and' you will find the missing record
    But I guess you want to pull the exchange rate from the next date if the date is missing in from the exchange rate table - in that case below query should work

    --create anchor table

    ;With genl as(
    select Gi,Pdate,Vend,USDebit,USCreditfrom #gl
    where AcctNum = 1127 and DocType in ('2','3')
    ), gen_subset as(

    select g

    .Gi ,g.Pdate ,e.SDate ,g.Vend ,
    isnull(i.VendInvNum,0) as VendInvNum ,g.USDebit ,g.USCredit ,isnull(i.LcyAmt,0) as LcyAmt
    ,ROW_NUMBER() over (partition by gi , g.pdate order by sdate) as RN
    left join #inv as I on g.Gi = i.PpI
    left join #exrate as e on g.Pdate <= e.SDate  and e.CurrId = 'GBP'

    )



  • November 7, 2018 at 7:08 am

    #2012684

    Nice!
    Here is the final code

    use tempdb
    drop table #gl;
    drop table #inv;
    drop table #exrate
    --create temp tables
    create table #gl
    (
     Gi   int   not null
     ,Pdate  date  not null
     ,Vend  int   not null
     ,USDebit decimal(10,2)  not null
     ,USCredit decimal(10,2)  not null
     ,DocType int   not null
     ,AcctNum int   not null
    )
    create table #inv
    (
     PpI   int   not null
     ,Pdate  date  not null
     ,Vend  int   not null
     ,LcyAmt  decimal(10,2)  not null
     ,VendInvNum int   not null
    )
    create table #exrate
    (
     SDate  date  not null
     ,Rate  decimal(10,2)  not null
     ,CurrId  varchar(50) not null
    )

    --insert test data
    insert into #gl
    (
     Gi,Pdate,Vend,USDebit,USCredit,Doctype,AcctNum
    )
    Values
     ('12345','01/01/2018','8880','500.00','0.00','2','1127')
     ,('12346','01/02/2018','8881','1010.00','0.00','2','1127')
     ,('12347','01/03/2018','8882','70.00','0.00','2','1127')
     ,('72350','01/04/2018','8883','0.00','40.00','3','1127')
     ,('12348','01/05/2018','8880','90.00','0.00','2','1128')
     ,('12349','01/05/2018','8880','100.50','0.00','2','1127')
     ,('72351','01/05/2018','8880','0.00','25.00','3','1127')
    insert into #inv
    (
     PpI, Pdate,Vend,LcyAmt,VendInvNum
    )
    Values
     ('12345','01/01/2018','8880','400.00','4343')
     ,('12346','01/02/2018','8881','818.10','5343')
     ,('12347','01/03/2018','8882','57.40','6343')
     ,('12348','01/05/2018','8880','74.70','4344')
     ,('12349','01/05/2018','8880','83.42','4345')
    insert into #exrate
    (
     Sdate,Rate,CurrId
    )
    Values
     ('01/01/2018','0.8','GBP')
     ,('01/01/2018','1.3','CAD')
     ,('01/02/2018','0.81','GBP')
     ,('01/02/2018','1.31','CAD')
     ,('01/03/2018','0.82','GBP')
     ,('01/03/2018','1.32','CAD')
     ,('01/05/2018','0.83','GBP')
     ,('01/05/2018','1.33','CAD')

    --check inserts
    select * from #gl;
    select * from #inv;
    select * from #exrate;

    --create anchor table
    With genl as
    (
    select Gi
    ,Pdate
    ,Vend
    ,USDebit
    ,USCredit
    from #gl
    where AcctNum = 1127 and DocType in ('2','3')
    ),
    gensubset as
    (
    select
    g.Gi
    ,g.Pdate
    ,g.Vend
    ,isnull(i.VendInvNum,0) as VendInvNum
    ,g.USDebit
    ,g.USCredit
    ,isnull(i.LcyAmt,0) as LcyAmt
    ,e.Sdate
    ,e.Rate
    ,ROW_NUMBER() over (partition by  gi, g.Pdate order by e.Sdate) as RN
    from #gl as g
    left join #inv as i on g.Gi = i.PpI
    left join #exrate as e on g.Pdate <= e.SDate and e.CurrId = 'GBP'
    )
    select distinct
    g.Gi
    ,g.Pdate
    ,g.Vend
    ,isnull(i.VendInvNum,0) as VendInvNum
    ,g.USDebit
    ,g.USCredit
    ,isnull(i.LcyAmt,0) as LcyAmt
    ,e.Rate
    from genl as g
    left join #inv as i
    on g.Gi = i.PpI
    left join gensubset as e
    on g.Pdate = e.Pdate
    where e.RN = 1

Viewing 3 posts - 1 through 2 (of 2 total)

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