March 7, 2013 at 11:06 am
Need to identify the first / earliest certDate matching cert_id as 'Initial" and each additional cert_ID as 'Re-certification' for each Field_id for each ABR_ID.
ABR_IDCERT_IDField_IDcertDate
3118331996DR1987-12-13 00:00:00.000
3118331997SV1995-02-28 00:00:00.000
3118331998SV2004-07-16 00:00:00.000
Given the example above cert_ids 31996 and 31997 would be considered as Initial certification and cert_ID 31998 would be considered a Re-certification.
Build the table
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[jwmTestCert](
[abr_ID] [nvarchar](10) NOT NULL,
[cert_id] [int] NOT NULL,
[field_id] [nvarchar](3) NOT NULL,
[certDate] [datetime] NOT NULL,
[ExpireYear] [nvarchar](4) NULL,
[status] [nvarchar](25) NOT NULL,
[statusDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
Populate
insert into jwmTestCert (abr_ID,Cert_ID, Field_ID, certDate) values (31183,31996,'dr', '1987-12-13')
insert into jwmTestCert (abr_ID,Cert_ID, Field_ID, certDate) values (31183,31997,'sv', '1995-02-28')
insert into jwmTestCert (abr_ID,Cert_ID, Field_ID, certDate) values (31183,31996,'sv', '2004-07-16')
Thank you for your time.
March 7, 2013 at 1:58 pm
I took the liberty of making your sample data and table work correctly.
if object_id('jwmTestCert') is not null
drop table jwmTestCert
CREATE TABLE [dbo].[jwmTestCert](
[abr_ID] [nvarchar](10) NOT NULL,
[cert_id] [int] NOT NULL,
[field_id] [nvarchar](3) NOT NULL,
[certDate] [datetime] NOT NULL,
[ExpireYear] [nvarchar](4) NULL,
[status] [nvarchar](25) NULL,
[statusDate] [datetime] NULL
) ON [PRIMARY]
GO
insert into jwmTestCert (abr_ID,Cert_ID, Field_ID, certDate) values (31183,31996,'dr', '1987-12-13')
insert into jwmTestCert (abr_ID,Cert_ID, Field_ID, certDate) values (31183,31997,'sv', '1995-02-28')
insert into jwmTestCert (abr_ID,Cert_ID, Field_ID, certDate) values (31183,31998,'sv', '2004-07-16')
Assuming this is correct you can easily use ROW_NUMBER for this.
select *, case when RowNum = 1 then 'Original' else 'Recertification' end
from
(
select *, ROW_NUMBER() OVER (partition by field_id Order by certDate) as RowNum from jwmTestCert
) x
_______________________________________________________________
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