Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

determine first instance as one type and all other instances as another type Expand / Collapse
Author
Message
Posted Thursday, March 07, 2013 11:06 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 04, 2014 4:49 PM
Points: 29, Visits: 266
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_ID	CERT_ID	Field_ID	certDate
31183 31996 DR 1987-12-13 00:00:00.000
31183 31997 SV 1995-02-28 00:00:00.000
31183 31998 SV 2004-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.
Post #1428151
Posted Thursday, March 07, 2013 1:58 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:31 PM
Points: 11,949, Visits: 10,982
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1428256
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse