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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy