September 18, 2014 at 1:36 pm
--Here is the data I am working with:
CREATE TABLE COORDBEN( ID INT IDENTITY(1,1),
MEMNBR VARCHAR(10),
RECORDTYPE VARCHAR(3),
EFFDATE INT,
POLICYNUM VARCHAR(10),
FILENAME VARCHAR(100))
-- The following is the loading of the COORDBEN table with data from text files, and added the
--record's source file name to the record where the
--D and T segments indicate the date and time the data was created in the file.
insert into COORDBEN(MEMNBR, RECORDTYPE, EFFDATE, POLICYNUM, FILENAME) VALUES('2221110001', 'PRM', 20130101, 'A776600', 'A.D20130101.T0516923')
insert into COORDBEN(MEMNBR, RECORDTYPE, EFFDATE, POLICYNUM, FILENAME) VALUES('2221110004', 'PRM', 20090701, 'B253521', 'A.D20130203.T0672411')
insert into COORDBEN(MEMNBR, RECORDTYPE, EFFDATE, POLICYNUM, FILENAME) VALUES('2221110001', 'SUP', 20140101, 'A776600', 'A.D20130204.T0213933')
insert into COORDBEN(MEMNBR, RECORDTYPE, EFFDATE, POLICYNUM, FILENAME) VALUES('2221110005', 'PRM', 20150101, 'C255551', 'A.D20140501.T0516211')
insert into COORDBEN(MEMNBR, RECORDTYPE, EFFDATE, POLICYNUM, FILENAME) VALUES('2221110005', 'PRM', 20140201, 'A666270', 'A.D20140102.T2301826')
insert into COORDBEN(MEMNBR, RECORDTYPE, EFFDATE, POLICYNUM, FILENAME) VALUES('2221110006', 'SUP', 20150101, 'X272811', 'A.D20140103.T0612981')
insert into COORDBEN(MEMNBR, RECORDTYPE, EFFDATE, POLICYNUM, FILENAME) VALUES('2221110006', 'PRM', 20140101, 'A776601', 'A.D20130103.T0618555')
insert into COORDBEN(MEMNBR, RECORDTYPE, EFFDATE, POLICYNUM, FILENAME) VALUES('2221110006', 'SUP', 20150101, 'B768801', 'A.D20130104.T0743805')
--The assumption would be that the files are loaded in a strict chronological order by date and time. --However, this may not necessarily be the case I I need to
--account for any mishandling.
--Now that the data is loaded, I need to query the data to find, for each memnbr, the most recent
--effective date for each policynum and RecordType where the
--data creation date is the highest.
September 18, 2014 at 1:43 pm
Awesome job posting sample data and ddl. The challenge I see is that you didn't post enough data. Following your business rules:
--Now that the data is loaded, I need to query the data to find, for each memnbr, the most recent
--effective date for each policynum and RecordType where the
--data creation date is the highest.
There is only 1 row for each group of rows here in your sample data.
That being said I am pretty sure you need something like this.
with SortedResults as
(
select * , ROW_NUMBER() over (partition by MEMNBR, POLICYNUM, RECORDTYPE order by EFFDATE desc) as RowNum
from COORDBEN
)
select *
from SortedResults
where RowNum = 1
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply