agupta
December 14, 2019, 1:03am
#1
For the table below, I am trying to write a query where I can find the cpb on a specific note_id for a specific date. For example if I want to get the cpb for note_id 32753521 as of the effective date of 08/12/2015. I need to write this query on 6000 records. Can someone help please? Thank you so much.
Note cpb effective date
78905463 1001 01/03/1999
32753521 5619 08/12/2015
32753521 5602 08/13/2015
33225522 15000 02/01/1998
33225522 5618 07/27/1999
33225522 5601 07/16/1999
33335436 1027 01/06/1999
67622551 5637 08/09/2015
67622551 5620 08/10/2015
67622551 5601 08/11/2015
78985522 5600 07/15/1999
33225522 5619 07/28/1999
33334422 1000 01/01/1999
Hi is this what you are looking for
please click arrow to the left for Drop Create Data Script
drop table #data
go
create table #data
(
Note_Id int,
cpb int,
effective_date date
)
go
insert into #data select 78905463, 1001 ,'01/03/1999'
insert into #data select 32753521, 5619 ,'08/12/2015'
insert into #data select 32753521, 5602 ,'08/13/2015'
insert into #data select 33225522, 15000 ,'02/01/1998'
insert into #data select 33225522, 5618 ,'07/27/1999'
insert into #data select 33225522, 5601 ,'07/16/1999'
insert into #data select 33335436, 1027 ,'01/06/1999'
insert into #data select 67622551, 5637 ,'08/09/2015'
insert into #data select 67622551, 5620 ,'08/10/2015'
insert into #data select 67622551, 5601 ,'08/11/2015'
insert into #data select 78985522, 5600 ,'07/15/1999'
insert into #data select 33225522, 5619 ,'07/28/1999'
insert into #data select 33334422, 1000 ,'01/01/1999'
go
select 'data',* from #data
go
select cpb from #data where note_id = 32753521 and effective_date = '08/12/2015'