August 4, 2016 at 8:12 am
Hello! I have simplified this example quite a bit from my actual predicament but hopefully its enough to give you guys what you need to help.
I would like to summarize this data to where it shows me ONLY the most recent "version" of my data records. Each record is defined by the "identification cd". For each identification cd, when it is inserted into the table for the first time, it is created with an identifier of "Initial". That initial record is the most recent "version" of the data (and gets a version number of 0) and then later on, the record could get changed. If it is changed, a new record is entered with an identifier of "change" and the version number gets incremented to 1. All the data elements are repopulated and anything that differs from the initial record is captured. An identification cd can have an infinite number of "change" records or it may not have any. So, whatever the max version number for each identification cd would be the "current" version of the record.
I would like to create a view in the database that will just store all the most recent versions of all the data. But for some reason I'm having a brain fart on how to do this in a simple query.
Sample table and data is below:
CREATE TABLE [DBO].[MY_TABLE](
[UNIQUE_ROW_ID] [INT] IDENTITY(10000,1) NOT NULL,
[IDENTIFICATION_CODE] VARCHAR(6) NOT NULL,
[STATUS_DESCR] [VARCHAR](100) NULL,
[STATUS_FLAG] [VARCHAR](1) NOT NULL,
[CREATE_DATE] [DATETIME] NOT NULL,
[SUBMIT_DATE] [DATETIME] NULL,
[SYS_DESC] [VARCHAR](255) NULL,
[SUBMISSION_TYPE] [VARCHAR](25) NULL,
[VERSION_NO] [INT] NULL,
[SUPPLY_CATEGORY] [CHAR](2) NULL,
[PRIME_USE] [NVARCHAR](MAX) NULL,
CONSTRAINT [PK_MY_TABLE] PRIMARY KEY CLUSTERED
(
[UNIQUE_ROW_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
insert into my_table values ('AB1234','Approved','C','01/01/2016','03/01/2016','Blah blah blah','Initial',0,'2F','Double blah blah blah');
insert into my_table values ('AB1234','Approved','C','04/01/2016','06/01/2016','Something different','Change',1,'2F','Double blah blah blah');
insert into my_table values ('BCD123','Approved','C','03/20/2016','04/01/2016','I am a great description','Initial',0,'3D','I shall be useless');
insert into my_table values ('BCD123','Approved','C','05/01/2016','06/01/2016','This is a better description','Change',1,'3D','Continue to be useless');
insert into my_table values ('BCD123','Approved','C','06/01/2016','07/01/2016','Super special description','Change',2,'3D','I have become useful');
insert into my_table values ('FG9321','Approved','C','07/01/2016','08/01/2016','A different description than the others','Initial',0,'H7','Say what?');
August 4, 2016 at 8:29 am
Do you need something like this?
WITH CTE AS(
SELECT * , ROW_NUMBER() OVER (PARTITION BY IDENTIFICATION_CODE ORDER BY VERSION_NO DESC) rn
FROM [MY_TABLE]
)
SELECT UNIQUE_ROW_ID
,IDENTIFICATION_CODE
,STATUS_DESCR
,STATUS_FLAG
,CREATE_DATE
,SUBMIT_DATE
,SYS_DESC
,SUBMISSION_TYPE
,VERSION_NO
,SUPPLY_CATEGORY
,PRIME_USE
FROM CTE
WHERE rn = 1;
August 4, 2016 at 8:42 am
Yes! That works perfectly. I haven't used any CTE's before.
Thank you muchly.
August 4, 2016 at 8:54 am
Good, but do you understand how it works?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply