December 3, 2015 at 12:09 pm
PROBLEM:
Table has multiple dated records for each category, I need to extract the 2 most recent records by category.
SAMPLE DATA:
CREATE TABLE CATEGORYHISTORY(
CAT_ID VARCHAR(5),
STARTDATE DATE,
ENDDATE DATE,
REASON CHAR(1))
INSERT INTO CATEGORYHISTORY (CAT_ID, STARTDATE, ENDDATE, REASON) VALUES('ABCDE', '2015-01-01', '2015-05-31', 'X')
INSERT INTO CATEGORYHISTORY (CAT_ID, STARTDATE, ENDDATE, REASON) VALUES('ABCDE', '2015-06-01', '2015-12-31', 'C')
INSERT INTO CATEGORYHISTORY (CAT_ID, STARTDATE, ENDDATE, REASON) VALUES('ABCDE', '2016-01-01', NULL, 'B')
INSERT INTO CATEGORYHISTORY (CAT_ID, STARTDATE, ENDDATE, REASON) VALUES('FGHIJ', '2015-01-01', '2015-04-30', 'X')
INSERT INTO CATEGORYHISTORY (CAT_ID, STARTDATE, ENDDATE, REASON) VALUES('FGHIJ', '2015-05-01', '2015-12-31', 'C')
INSERT INTO CATEGORYHISTORY (CAT_ID, STARTDATE, ENDDATE, REASON) VALUES('FGHIJ', '2016-01-01', NULL, 'B')
RESULTS TO BE FOUND:
CAT_ID STARTDATE ENDDATE REASON
ABCDE 2015-06-01 2015012-31 C
ABCDE 2016-01-01 NULL B
FGHIJ 2015-05-01 2015-12-31 C
FGHIJ 2016-01-01 NULL B
Is there a simple TSQL answer to this?
December 3, 2015 at 12:45 pm
Nilssond (12/3/2015)
PROBLEM:Table has multiple dated records for each category, I need to extract the 2 most recent records by category.
SAMPLE DATA:
CREATE TABLE CATEGORYHISTORY(
CAT_ID VARCHAR(5),
STARTDATE DATE,
ENDDATE DATE,
REASON CHAR(1))
INSERT INTO CATEGORYHISTORY (CAT_ID, STARTDATE, ENDDATE, REASON) VALUES('ABCDE', '2015-01-01', '2015-05-31', 'X')
INSERT INTO CATEGORYHISTORY (CAT_ID, STARTDATE, ENDDATE, REASON) VALUES('ABCDE', '2015-06-01', '2015-12-31', 'C')
INSERT INTO CATEGORYHISTORY (CAT_ID, STARTDATE, ENDDATE, REASON) VALUES('ABCDE', '2016-01-01', NULL, 'B')
INSERT INTO CATEGORYHISTORY (CAT_ID, STARTDATE, ENDDATE, REASON) VALUES('FGHIJ', '2015-01-01', '2015-04-30', 'X')
INSERT INTO CATEGORYHISTORY (CAT_ID, STARTDATE, ENDDATE, REASON) VALUES('FGHIJ', '2015-05-01', '2015-12-31', 'C')
INSERT INTO CATEGORYHISTORY (CAT_ID, STARTDATE, ENDDATE, REASON) VALUES('FGHIJ', '2016-01-01', NULL, 'B')
RESULTS TO BE FOUND:
CAT_ID STARTDATE ENDDATE REASON
ABCDE 2015-06-01 2015012-31 C
ABCDE 2016-01-01 NULL B
FGHIJ 2015-05-01 2015-12-31 C
FGHIJ 2016-01-01 NULL B
Is there a simple TSQL answer to this?
Excellent job posting ddl and sample data. Makes this a lot easier. You can do this quite easily with ROW_NUMBER.
with MyCte as
(
select *
, ROW_NUMBER() over(partition by CAT_ID order by StartDate desc) as RowNum
from CATEGORYHISTORY
)
select *
from MyCte
where RowNum <= 2
order by CAT_ID
, StartDate
_______________________________________________________________
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