November 6, 2018 at 8:54 am
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'
November 6, 2018 at 9:38 am
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
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 = 1Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply