Solved

Writing a SQL Procedure to loop through a recordset to create a table

Posted on 2014-09-22
8
178 Views
Last Modified: 2014-09-22
I would like some guidance on writing a stored procedure in MS SQL Server 2012 to loop a view I have created.  In the view are the employees that have multiple status changes.  What I am looking for are employees who have changed their TYPE from PRN to FT or PT or visa versa from one record to the other.

I want to  create a table that will store the EmplolyeeID, the older status date as the OLDDate, the older Type as OLDType, the newer status date as NEWDate and the newer Type as NEWType as one record.

The procedure would ignore TYPE that are NULL and move to the next record.

The attached file shows the results of the view.  The yellow highlighted records are the one that would be included in the new table.

Thanks

Glen
StatusChanges.xlsx
0
Comment
Question by:GPSPOW
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 25

Expert Comment

by:jogos
ID: 40337842
I am pretty sure you don't kneed a loop (cursor).  Sql is at it's best as you don't use it row by row but setbased.

For you row_number() and maybe used in a CTE  will be the better way to go.
row_number() : http://www.codeproject.com/Articles/308281/How-to-Use-ROW-NUMBER-to-Enumerate-and-Partition-R
cte : http://msdn.microsoft.com/en-us/library/ms175972.aspx
0
 
LVL 25

Expert Comment

by:chaau
ID: 40338066
You do not need a stored procedure. You can use LAG() function to get your result in a single query:

CREATE TABLE #Table1
	([EmployeeID] int, [Cnt] int, [Status] varchar(6), [StatusDate] varchar(10), [Reason] varchar(10), [StatusPayDate] date, [Type] varchar(4), [AuditDate] date)
;
	
INSERT INTO #Table1
	([EmployeeID], [Cnt], [Status], [StatusDate], [Reason], [StatusPayDate], [Type], [AuditDate])
VALUES
	(1001, 2, 'LEAVE', '2011-10-03', 'SN-NEW', NULL, NULL, '2013-02-06'),
	(1001, 2, 'ACTIVE', '2011-10-03', 'SN-NEW', NULL, NULL, '2013-03-04'),
	(1134, 2, NULL, NULL, NULL, NULL, 'FT', '2013-05-09'),
	(1134, 2, 'TERM', '2013-08-22', 'ST-SCHOOL', NULL, NULL, '2013-09-06'),
	(1134, 2, 'ACTIVE', '2013-08-22', 'ST-SCHOOL', NULL, NULL, '2013-09-16'),
	(1134, 2, 'TERM', '2013-08-22', 'ST-SCHOOL', NULL, NULL, '2013-09-20'),
	(1181, 2, 'ACTIVE', '2013-01-21', 'SN-NEW', NULL, 'PRN', '2013-01-22'),
	(1181, 2, NULL, NULL, NULL, NULL, 'FT', '2013-05-09'),
	(1182, 2, 'ACTIVE', '2013-01-23', 'SN-NEW', NULL, 'FT', '2013-01-22'),
	(1182, 2, 'ACTIVE', '2013-02-04', 'SN-NEW', NULL, 'FT', '2013-01-22'),
	(1182, 2, 'TERM', '2013-03-16', 'AA-BETTER', '2013-03-16', NULL, '2013-03-27'),
	(1184, 2, 'ACTIVE', '2013-01-21', 'SN-NEW', NULL, 'PRN', '2013-01-22'),
	(1184, 2, NULL, NULL, NULL, NULL, 'FT', '2013-05-09'),
	(1185, 2, 'ACTIVE', '2013-01-21', 'SN-NEW', NULL, 'FT', '2013-01-22'),
	(1185, 2, 'ACTIVE', '2013-01-21', 'BR-STATUS', NULL, 'PRN', '2013-10-21'),
	(1187, 2, 'ACTIVE', '2013-01-21', 'SN-NEW', NULL, 'FT', '2013-01-25'),
	(1187, 2, NULL, NULL, NULL, NULL, 'PRN', '2013-09-20'),
	(1189, 2, 'ACTIVE', '2013-01-21', 'SN-NEW', NULL, 'PRN', '2013-01-28'),
	(1189, 2, NULL, NULL, NULL, NULL, 'FT', '2013-03-27'),
	(1193, 2, 'ACTIVE', '2013-02-18', 'SN-NEW', NULL, 'PRN', '2013-02-15'),
	(1193, 2, 'ACTIVE', '2013-02-19', 'SN-NEW', NULL, 'PRN', '2013-02-15'),
	(1193, 2, 'TERM', '2013-10-28', 'BR-OTHER', NULL, NULL, '2013-10-31'),
	(1194, 2, 'ACTIVE', '2013-02-18', 'SN-NEW', NULL, 'FT', '2013-02-15'),
	(1194, 2, 'ACTIVE', '2013-03-04', 'SN-NEW', NULL, 'FT', '2013-02-19'),
	(1194, 2, 'TERM', '2013-05-23', 'SN-NEW', NULL, NULL, '2013-08-23'),
	(1201, 2, 'NEW', '2013-03-18', 'SA-NEW', NULL, 'FT', '2013-03-18'),
	(1201, 2, 'ACTIVE', '2013-03-18', 'SA-NEW', NULL, NULL, '2013-04-01'),
	(1206, 2, 'NEW', '2013-03-25', 'AA-OFF ACC', NULL, 'FT', '2013-03-25'),
	(1206, 2, 'ACTIVE', '2013-03-25', 'AA-OFF ACC', NULL, NULL, '2013-04-01'),
	(1206, 2, 'TERM', '2013-09-17', 'ST-NEWJOB', NULL, NULL, '2013-10-03'),
	(1212, 2, 'ACTIVE', '2013-04-15', 'SA-NEW', NULL, 'FT', '2013-04-09'),
	(1212, 2, 'TERM', '2013-05-01', 'ST-NOCALL', '2013-05-01', NULL, '2013-06-05'),
	(1212, 2, 'ACTIVE', '2013-05-01', 'ST-NOCALL', NULL, NULL, '2013-06-24'),
	(1212, 2, 'TERM', '2013-05-01', 'ST-NOCALL', NULL, NULL, '2013-08-07'),
	(1215, 2, 'ACTIVE', '2013-04-16', 'SN-NEW', NULL, 'PRN', '2013-04-17'),
	(1215, 2, NULL, NULL, NULL, NULL, 'FT', '2013-09-06'),
	(1228, 2, 'ACTIVE', '2013-05-13', 'SA-NEW', NULL, 'FT', '2013-05-10'),
	(1228, 2, 'ACTIVE', '2013-05-13', 'SA-CHG JOB', NULL, 'PRN', '2013-09-19'),
	(1229, 3, 'NEW', '2013-05-13', 'SA-NEW', NULL, 'PT', '2013-05-10'),
	(1229, 3, 'NEW', '2013-05-28', 'SA-NEW', NULL, NULL, '2013-05-15'),
	(1229, 3, 'ACTIVE', '2013-05-28', 'SA-NEW', NULL, NULL, '2013-06-11'),
	(1230, 2, 'NEW', '2013-05-28', 'SA-NEW', NULL, 'FT', '2013-05-16'),
	(1230, 2, 'ACTIVE', '2013-05-28', 'SA-NEW', NULL, NULL, '2013-06-11'),
	(1234, 3, 'NEW', '2013-05-28', 'SA-NEW', NULL, 'FT', '2013-05-24'),
	(1234, 3, 'ACTIVE', '2013-05-28', 'SA-NEW', NULL, NULL, '2013-06-11'),
	(1234, 3, 'TERM', '2013-12-13', 'ST-RESIGN', '2013-12-13', NULL, '2013-12-23'),
	(1234, 3, 'ACTIVE', '2013-05-28', 'SN-NEW', NULL, NULL, '2013-12-23'),
	(1234, 3, 'TERM', '2013-12-13', 'ST-RESIGN', '2013-12-13', NULL, '2013-12-24'),
	(1244, 3, 'ACTIVE', '2013-06-10', 'SA-NEW', NULL, 'FT', '2013-06-12'),
	(1244, 3, NULL, NULL, NULL, NULL, 'PRN', '2013-08-19'),
	(1244, 3, NULL, NULL, NULL, NULL, 'PRN', '2013-08-20'),
	(1244, 3, 'TERM', '2013-11-25', 'BR-OTHER', '2013-11-24', NULL, '2013-11-26'),
	(1245, 2, 'ACTIVE', '2013-06-24', 'SA-NEW', NULL, 'PT', '2013-06-14'),
	(1245, 2, 'ACTIVE', '2013-06-17', 'SA-NEW', NULL, 'PT', '2013-06-24'),
	(1245, 2, 'TERM', '2013-06-20', 'ST-PERS', NULL, NULL, '2013-08-23'),
	(1253, 3, 'ACTIVE', '2013-06-20', 'SA-NEW', NULL, 'PRN', '2013-06-24'),
	(1253, 3, NULL, NULL, NULL, NULL, 'FT', '2013-09-12'),
	(1253, 3, NULL, NULL, NULL, NULL, 'FT', '2013-09-12'),
	(1286, 2, 'ACTIVE', '2013-10-14', 'SN-NEW', NULL, 'FT', '2013-10-09'),
	(1286, 2, NULL, NULL, NULL, NULL, 'PRN', '2013-12-27'),
	(1296, 2, 'ACTIVE', '2013-10-28', 'SN-NEW', NULL, 'FT', '2013-10-21'),
	(1296, 2, NULL, NULL, NULL, NULL, 'PRN', '2013-11-06'),
	(1306, 2, 'ACTIVE', '2013-11-25', 'SN-NEW', NULL, 'FT', '2013-11-15'),
	(1306, 2, NULL, NULL, NULL, NULL, 'PRN', '2013-12-18'),
	(1309, 2, 'ACTIVE', '2013-11-25', 'SN-NEW', NULL, 'FT', '2013-11-20'),
	(1309, 2, 'ACTIVE', '2013-12-09', 'SN-NEW', NULL, 'FT', '2013-12-19'),
	(1310, 2, 'ACTIVE', '2013-11-25', 'SN-NEW', NULL, 'FT', '2013-11-20'),
	(1310, 2, 'ACTIVE', '2013-12-02', 'SN-NEW', NULL, 'FT', '2013-12-02'),
	(1310, 2, 'TERM', '2013-12-02', 'ST-DISCH', '2013-12-02', NULL, '2013-12-02'),
	(1311, 2, 'ACTIVE', '2013-12-09', 'SN-NEW', NULL, 'FT', '2013-12-03'),
	(1311, 2, NULL, NULL, NULL, NULL, 'PRN', '2013-12-18'),
	(1313, 3, 'ACTIVE', '2013-12-09', 'SN-NEW', NULL, 'FT', '2013-12-03'),
	(1313, 3, 'TERM', '2013-12-09', 'ST-NEVER', NULL, NULL, '2013-12-19'),
	(1313, 3, 'ACTIVE', '2013-01-06', 'SN-NEW', NULL, 'FT', '2013-12-19'),
	(1313, 3, NULL, NULL, NULL, NULL, 'PRN', '2013-12-19'),
	(160, 2, 'LEAVE', '2007-06-11', 'SN-NEW', NULL, NULL, '2013-02-28'),
	(160, 2, 'ACTIVE', '2007-06-11', 'SN-NEW', NULL, NULL, '2013-06-05'),
	(167, 3, 'ACTIVE', '2013-09-15', 'AS-SEMP', NULL, 'FT', '2013-10-18'),
	(167, 3, 'ACTIVE', '2013-09-15', 'BR-STATUS', NULL, NULL, '2013-10-18'),
	(167, 3, 'ACTIVE', '2007-07-02', 'BR-STATUS', NULL, NULL, '2013-10-21'),
	(351, 2, 'ACTIVE', '2012-06-08', 'ST-NEWJOB', NULL, NULL, '2013-02-19'),
	(351, 2, NULL, NULL, NULL, NULL, 'FT', '2013-02-19'),
	(441, 2, 'LEAVE', '2008-10-20', 'SN-NEW', NULL, NULL, '2013-01-16'),
	(441, 2, 'ACTIVE', '2008-10-20', 'SN-NEW', NULL, NULL, '2013-02-15'),
	(442, 2, 'LEAVE', '2008-10-20', 'SN-NEW', NULL, NULL, '2013-02-18'),
	(442, 2, 'ACTIVE', '2008-10-20', 'SN-NEW', NULL, NULL, '2013-02-28'),
	(45, 2, NULL, NULL, NULL, NULL, 'PRN', '2013-01-28'),
	(45, 2, 'ACTIVE', '2013-12-23', 'BR-STATUS', NULL, 'FT', '2014-01-02'),
	(464, 2, 'ACTIVE', '2013-05-04', 'ST - VIOL', NULL, 'FT', '2013-04-30'),
	(464, 2, 'ACTIVE', '2008-12-15', 'ST - VIOL', NULL, 'FT', '2013-06-13'),
	(591, 2, NULL, NULL, NULL, NULL, 'PRN', '2013-08-29'),
	(591, 2, NULL, NULL, NULL, NULL, 'FT', '2013-10-03'),
	(591, 2, 'TERM', '2013-11-03', 'ST-RESIGN', '2013-11-02', NULL, '2013-11-12'),
	(788, 2, NULL, NULL, NULL, NULL, 'PRN', '2013-04-25'),
	(788, 2, 'TERM', '2013-04-14', 'AA-AAP', '2013-04-13', NULL, '2013-05-09'),
	(788, 2, 'ACTIVE', '2010-08-23', 'SA-NEW', NULL, NULL, '2013-05-09'),
	(828, 2, 'ACTIVE', '2013-09-17', 'SA-REHIRE', NULL, 'FT', '2013-09-18'),
	(828, 2, 'TERM', '2013-11-25', 'ST-RESIGN', '2013-11-24', NULL, '2013-12-02'),
	(828, 2, 'ACTIVE', '2013-11-25', 'ST-RESIGN', NULL, NULL, '2013-12-10'),
	(828, 2, 'TERM', '2013-11-25', 'ST-RESIGN', NULL, NULL, '2013-12-10'),
	(832, 2, 'LEAVE', '2010-11-01', 'SN-NEW', NULL, NULL, '2013-02-07'),
	(832, 2, 'ACTIVE', '2010-11-01', 'SN-NEW', NULL, NULL, '2013-02-18'),
	(832, 2, 'TERM', '2013-04-13', 'ST-RESIGN', '2013-04-12', NULL, '2013-04-16'),
	(847, 2, 'LEAVE', '2010-11-15', 'SN-NEW', NULL, NULL, '2013-02-18'),
	(847, 2, 'ACTIVE', '2010-11-15', 'SN-NEW', NULL, NULL, '2013-06-05'),
	(856, 3, NULL, NULL, NULL, NULL, 'PRN', '2013-08-19'),
	(856, 3, NULL, NULL, NULL, NULL, 'PRN', '2013-08-19'),
	(856, 3, NULL, NULL, NULL, NULL, 'PRN', '2013-08-20'),
	(879, 2, 'ACTIVE', '2011-01-10', 'SN-NEW', NULL, NULL, '2013-06-06'),
	(879, 2, NULL, NULL, NULL, NULL, 'FT', '2013-08-23'),
	(881, 3, 'ACTIVE', '2011-01-10', 'SN-NEW', NULL, NULL, '2013-01-28'),
	(881, 3, 'LEAVE', '2011-01-10', 'SN-NEW', NULL, NULL, '2013-01-28'),
	(881, 3, 'ACTIVE', '2011-01-10', 'SN-NEW', NULL, NULL, '2013-02-04')
;

	with c as (
	select [EmployeeID], [Cnt], [Status], [StatusDate], [Reason], [StatusPayDate], [Type], [AuditDate],
	LAG([Type]) OVER (PARTITION BY [EmployeeID] ORDER BY [AuditDate]) AS PreType, 
	LAG([AuditDate]) OVER (PARTITION BY [EmployeeID] ORDER BY [AuditDate]) AS PreDate 
	FROM #Table1
	WHERE [Type] IN ('PRN','FT','PT'))
	SELECT * 
	FROM c 
	WHERE [Type] = 'PRN' AND PreType IN ('FT','PT') 
	   OR [Type] IN ('FT','PT') AND PreType = 'PRN'

	DROP TABLE #Table1

Open in new window

This query will produce the following result:
EmployeeID  Cnt         Status StatusDate Reason     StatusPayDate Type AuditDate  PreType PreDate
----------- ----------- ------ ---------- ---------- ------------- ---- ---------- ------- ----------
45          2           ACTIVE 2013-12-23 BR-STATUS  NULL          FT   2014-01-02 PRN     2013-01-28
591         2           NULL   NULL       NULL       NULL          FT   2013-10-03 PRN     2013-08-29
1181        2           NULL   NULL       NULL       NULL          FT   2013-05-09 PRN     2013-01-22
1184        2           NULL   NULL       NULL       NULL          FT   2013-05-09 PRN     2013-01-22
1185        2           ACTIVE 2013-01-21 BR-STATUS  NULL          PRN  2013-10-21 FT      2013-01-22
1187        2           NULL   NULL       NULL       NULL          PRN  2013-09-20 FT      2013-01-25
1189        2           NULL   NULL       NULL       NULL          FT   2013-03-27 PRN     2013-01-28
1215        2           NULL   NULL       NULL       NULL          FT   2013-09-06 PRN     2013-04-17
1228        2           ACTIVE 2013-05-13 SA-CHG JOB NULL          PRN  2013-09-19 FT      2013-05-10
1244        3           NULL   NULL       NULL       NULL          PRN  2013-08-19 FT      2013-06-12
1253        3           NULL   NULL       NULL       NULL          FT   2013-09-12 PRN     2013-06-24
1286        2           NULL   NULL       NULL       NULL          PRN  2013-12-27 FT      2013-10-09
1296        2           NULL   NULL       NULL       NULL          PRN  2013-11-06 FT      2013-10-21
1306        2           NULL   NULL       NULL       NULL          PRN  2013-12-18 FT      2013-11-15
1311        2           NULL   NULL       NULL       NULL          PRN  2013-12-18 FT      2013-12-03
1313        3           NULL   NULL       NULL       NULL          PRN  2013-12-19 FT      2013-12-19

(16 row(s) affected)

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40338076
With SQL 2012 you have access to the lead & lag functions which are very useful for this type of need where you need to access more than one row of information at the same time.

Please try this query:
select
      *
from (
      select
              e.employeeid
            , e.type                                                                 as older_type
            , e.auditdate                                                            as older_date
            , lead(e.type) over(partition by e.employeeid order by e.auditdate)      as newer_type
            , lead(e.auditdate) over(partition by e.employeeid order by e.auditdate) as newer_date
      from employees e
      inner join (
                  select
                         employeeid
                  from employees
                  where type is not null
                  group by employeeid
                  having min(type) = 'FT' and max(type) = 'PRN'
                  ) inc on e.employeeid = inc.employeeid
        ) d
where newer_type is not null

Open in new window

Nb: I could not see any reason why employeeid 1187 was not yellow - is there a reason?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:GPSPOW
ID: 40338198
Here is the code I put into the query:

create table #Table1(
EmployeeID int, EmpStatus varchar (10) ,StatReason varchar (74),EmpType varchar (10),StatusDate date 
);

insert into #Table1
SELECT     dbo.HrEmployeeAuditPositionFields.EmployeeID, CASE WHEN dbo.HrEmployeeAuditPositionFields.Status IS NULL 
                      THEN '' ELSE dbo.HrEmployeeAuditPositionFields.Status END AS EmpStatus, CASE WHEN dbo.HrEmployeeAuditPositionFields.StatusReason IS NULL 
                      THEN '' ELSE dbo.HrEmployeeAuditPositionFields.StatusReason END AS StatReason, CASE WHEN dbo.HrEmployeeAuditPositionFields.Type IS NULL 
                      THEN '' ELSE dbo.HrEmployeeAuditPositionFields.Type END AS EmpType, CASE WHEN dbo.HrEmployeeAuditPositionFields.StatusDateTime IS NULL 
                      THEN dbo.HrEmployeeAuditPositionFields.AuditDateTime ELSE dbo.HrEmployeeAuditPositionFields.StatusDateTime END AS StatusDate
FROM         dbo.HrEmployeeAuditPositionFields RIGHT OUTER JOIN
                      dbo.vw_MultiPositionAudit_EmployeeIDs ON dbo.HrEmployeeAuditPositionFields.EmployeeID = dbo.vw_MultiPositionAudit_EmployeeIDs.EmployeeID;
                      
with c as (
	select EmployeeID, EmpStatus, StatReason, EmpType, StatusDate,
	lag(EmpType) over (PARTITION by EmployeeID order by StatusDate) as PreType,
	lag(StatusDate) over (PARTITION by EmployeeID order by StatusDate) as PreDate
		from #Table1
		Where EmpType in ('PRN','FT','PT'))
		Select *
		From c
		Where EmpType = 'PRN' and PreType in ('FT','PT')
			or EmpType in ('PT','FT') and PreType = 'PRN'
			
Drop table #Table1

Open in new window


I was mistaken, I am running Sql 2008 R2 so is there an alternative to LAG?

thanks

Glen
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40338201
yes there are alternatives to lag but none quite as good :)
I'm short on time at the moment so I'll try later if no-one else has answered.
0
 
LVL 25

Accepted Solution

by:
chaau earned 500 total points
ID: 40338203
There is an alternative. You need to use ROW_NUMBER(). BTW, there is no reason to use #Table1. I used it to create a test table in my database. Use your SELECT statement directly in the query.
with c as (
	select [EmployeeID], [Cnt], [Status], [StatusDate], [Reason], [StatusPayDate], [Type], [AuditDate],
	ROW_NUMBER() OVER (PARTITION BY [EmployeeID] ORDER BY [AuditDate]) AS rn 
	FROM #Table1
	WHERE [Type] IN ('PRN','FT','PT'))
	SELECT c1.*, c2.[Type] AS PreType, c2.AuditDate AS PreDate 
	FROM c c1 INNER JOIN c c2
	ON c1.EmployeeID = c2.EmployeeID AND c1.rn = c2.rn + 1
	WHERE c1.[Type] = 'PRN' AND c2.[Type] IN ('FT','PT') 
	   OR c1.[Type] IN ('FT','PT') AND c2.[Type] = 'PRN'

Open in new window

0
 

Author Closing Comment

by:GPSPOW
ID: 40338223
Thank you.

Perfect solution

Glen
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40338298
although too late, here's my slant on it: also using row_number :)

**MS SQL Server 2008 Schema Setup**:

    CREATE TABLE Employees
    ([EmployeeID] int,[Cnt] int,[Status] varchar(6),[StatusDate] varchar(10),[Reason] varchar(10),[StatusPayDate] date,[Type] varchar(4),[AuditDate] date)
    ;
    
    INSERT INTO Employees
    ([EmployeeID],[Cnt],[Status],[StatusDate],[Reason],[StatusPayDate],[Type],[AuditDate])
    VALUES
    (1001,2,'LEAVE','2011-10-03','SN-NEW',NULL,NULL,'2013-02-06'),
    (1001,2,'ACTIVE','2011-10-03','SN-NEW',NULL,NULL,'2013-03-04'),
    (1134,2,NULL,NULL,NULL,NULL,'FT','2013-05-09'),
    (1134,2,'TERM','2013-08-22','ST-SCHOOL',NULL,NULL,'2013-09-06'),
    (1134,2,'ACTIVE','2013-08-22','ST-SCHOOL',NULL,NULL,'2013-09-16'),
    (1134,2,'TERM','2013-08-22','ST-SCHOOL',NULL,NULL,'2013-09-20'),
    (1181,2,'ACTIVE','2013-01-21','SN-NEW',NULL,'PRN','2013-01-22'),
    (1181,2,NULL,NULL,NULL,NULL,'FT','2013-05-09'),
    (1182,2,'ACTIVE','2013-01-23','SN-NEW',NULL,'FT','2013-01-22'),
    (1182,2,'ACTIVE','2013-02-04','SN-NEW',NULL,'FT','2013-01-22'),
    (1182,2,'TERM','2013-03-16','AA-BETTER','2013-03-16',NULL,'2013-03-27'),
    (1184,2,'ACTIVE','2013-01-21','SN-NEW',NULL,'PRN','2013-01-22'),
    (1184,2,NULL,NULL,NULL,NULL,'FT','2013-05-09'),
    (1185,2,'ACTIVE','2013-01-21','SN-NEW',NULL,'FT','2013-01-22'),
    (1185,2,'ACTIVE','2013-01-21','BR-STATUS',NULL,'PRN','2013-10-21'),
    (1187,2,'ACTIVE','2013-01-21','SN-NEW',NULL,'FT','2013-01-25'),
    (1187,2,NULL,NULL,NULL,NULL,'PRN','2013-09-20'),
    (1189,2,'ACTIVE','2013-01-21','SN-NEW',NULL,'PRN','2013-01-28'),
    (1189,2,NULL,NULL,NULL,NULL,'FT','2013-03-27'),
    (1193,2,'ACTIVE','2013-02-18','SN-NEW',NULL,'PRN','2013-02-15'),
    (1193,2,'ACTIVE','2013-02-19','SN-NEW',NULL,'PRN','2013-02-15'),
    (1193,2,'TERM','2013-10-28','BR-OTHER',NULL,NULL,'2013-10-31'),
    (1194,2,'ACTIVE','2013-02-18','SN-NEW',NULL,'FT','2013-02-15'),
    (1194,2,'ACTIVE','2013-03-04','SN-NEW',NULL,'FT','2013-02-19'),
    (1194,2,'TERM','2013-05-23','SN-NEW',NULL,NULL,'2013-08-23'),
    (1201,2,'NEW','2013-03-18','SA-NEW',NULL,'FT','2013-03-18'),
    (1201,2,'ACTIVE','2013-03-18','SA-NEW',NULL,NULL,'2013-04-01'),
    (1206,2,'NEW','2013-03-25','AA-OFF ACC',NULL,'FT','2013-03-25'),
    (1206,2,'ACTIVE','2013-03-25','AA-OFF ACC',NULL,NULL,'2013-04-01'),
    (1206,2,'TERM','2013-09-17','ST-NEWJOB',NULL,NULL,'2013-10-03'),
    (1212,2,'ACTIVE','2013-04-15','SA-NEW',NULL,'FT','2013-04-09'),
    (1212,2,'TERM','2013-05-01','ST-NOCALL','2013-05-01',NULL,'2013-06-05'),
    (1212,2,'ACTIVE','2013-05-01','ST-NOCALL',NULL,NULL,'2013-06-24'),
    (1212,2,'TERM','2013-05-01','ST-NOCALL',NULL,NULL,'2013-08-07'),
    (1215,2,'ACTIVE','2013-04-16','SN-NEW',NULL,'PRN','2013-04-17'),
    (1215,2,NULL,NULL,NULL,NULL,'FT','2013-09-06'),
    (1228,2,'ACTIVE','2013-05-13','SA-NEW',NULL,'FT','2013-05-10'),
    (1228,2,'ACTIVE','2013-05-13','SA-CHG JOB',NULL,'PRN','2013-09-19'),
    (1229,3,'NEW','2013-05-13','SA-NEW',NULL,'PT','2013-05-10'),
    (1229,3,'NEW','2013-05-28','SA-NEW',NULL,NULL,'2013-05-15'),
    (1229,3,'ACTIVE','2013-05-28','SA-NEW',NULL,NULL,'2013-06-11'),
    (1230,2,'NEW','2013-05-28','SA-NEW',NULL,'FT','2013-05-16'),
    (1230,2,'ACTIVE','2013-05-28','SA-NEW',NULL,NULL,'2013-06-11'),
    (1234,3,'NEW','2013-05-28','SA-NEW',NULL,'FT','2013-05-24'),
    (1234,3,'ACTIVE','2013-05-28','SA-NEW',NULL,NULL,'2013-06-11'),
    (1234,3,'TERM','2013-12-13','ST-RESIGN','2013-12-13',NULL,'2013-12-23'),
    (1234,3,'ACTIVE','2013-05-28','SN-NEW',NULL,NULL,'2013-12-23'),
    (1234,3,'TERM','2013-12-13','ST-RESIGN','2013-12-13',NULL,'2013-12-24'),
    (1244,3,'ACTIVE','2013-06-10','SA-NEW',NULL,'FT','2013-06-12'),
    (1244,3,NULL,NULL,NULL,NULL,'PRN','2013-08-19'),
    (1244,3,NULL,NULL,NULL,NULL,'PRN','2013-08-20'),
    (1244,3,'TERM','2013-11-25','BR-OTHER','2013-11-24',NULL,'2013-11-26'),
    (1245,2,'ACTIVE','2013-06-24','SA-NEW',NULL,'PT','2013-06-14'),
    (1245,2,'ACTIVE','2013-06-17','SA-NEW',NULL,'PT','2013-06-24'),
    (1245,2,'TERM','2013-06-20','ST-PERS',NULL,NULL,'2013-08-23'),
    (1253,3,'ACTIVE','2013-06-20','SA-NEW',NULL,'PRN','2013-06-24'),
    (1253,3,NULL,NULL,NULL,NULL,'FT','2013-09-12'),
    (1253,3,NULL,NULL,NULL,NULL,'FT','2013-09-12'),
    (1286,2,'ACTIVE','2013-10-14','SN-NEW',NULL,'FT','2013-10-09'),
    (1286,2,NULL,NULL,NULL,NULL,'PRN','2013-12-27'),
    (1296,2,'ACTIVE','2013-10-28','SN-NEW',NULL,'FT','2013-10-21'),
    (1296,2,NULL,NULL,NULL,NULL,'PRN','2013-11-06'),
    (1306,2,'ACTIVE','2013-11-25','SN-NEW',NULL,'FT','2013-11-15'),
    (1306,2,NULL,NULL,NULL,NULL,'PRN','2013-12-18'),
    (1309,2,'ACTIVE','2013-11-25','SN-NEW',NULL,'FT','2013-11-20'),
    (1309,2,'ACTIVE','2013-12-09','SN-NEW',NULL,'FT','2013-12-19'),
    (1310,2,'ACTIVE','2013-11-25','SN-NEW',NULL,'FT','2013-11-20'),
    (1310,2,'ACTIVE','2013-12-02','SN-NEW',NULL,'FT','2013-12-02'),
    (1310,2,'TERM','2013-12-02','ST-DISCH','2013-12-02',NULL,'2013-12-02'),
    (1311,2,'ACTIVE','2013-12-09','SN-NEW',NULL,'FT','2013-12-03'),
    (1311,2,NULL,NULL,NULL,NULL,'PRN','2013-12-18'),
    (1313,3,'ACTIVE','2013-12-09','SN-NEW',NULL,'FT','2013-12-03'),
    (1313,3,'TERM','2013-12-09','ST-NEVER',NULL,NULL,'2013-12-19'),
    (1313,3,'ACTIVE','2013-01-06','SN-NEW',NULL,'FT','2013-12-19'),
    (1313,3,NULL,NULL,NULL,NULL,'PRN','2013-12-19'),
    (160,2,'LEAVE','2007-06-11','SN-NEW',NULL,NULL,'2013-02-28'),
    (160,2,'ACTIVE','2007-06-11','SN-NEW',NULL,NULL,'2013-06-05'),
    (167,3,'ACTIVE','2013-09-15','AS-SEMP',NULL,'FT','2013-10-18'),
    (167,3,'ACTIVE','2013-09-15','BR-STATUS',NULL,NULL,'2013-10-18'),
    (167,3,'ACTIVE','2007-07-02','BR-STATUS',NULL,NULL,'2013-10-21'),
    (351,2,'ACTIVE','2012-06-08','ST-NEWJOB',NULL,NULL,'2013-02-19'),
    (351,2,NULL,NULL,NULL,NULL,'FT','2013-02-19'),
    (441,2,'LEAVE','2008-10-20','SN-NEW',NULL,NULL,'2013-01-16'),
    (441,2,'ACTIVE','2008-10-20','SN-NEW',NULL,NULL,'2013-02-15'),
    (442,2,'LEAVE','2008-10-20','SN-NEW',NULL,NULL,'2013-02-18'),
    (442,2,'ACTIVE','2008-10-20','SN-NEW',NULL,NULL,'2013-02-28'),
    (45,2,NULL,NULL,NULL,NULL,'PRN','2013-01-28'),
    (45,2,'ACTIVE','2013-12-23','BR-STATUS',NULL,'FT','2014-01-02'),
    (464,2,'ACTIVE','2013-05-04','ST - VIOL',NULL,'FT','2013-04-30'),
    (464,2,'ACTIVE','2008-12-15','ST - VIOL',NULL,'FT','2013-06-13'),
    (591,2,NULL,NULL,NULL,NULL,'PRN','2013-08-29'),
    (591,2,NULL,NULL,NULL,NULL,'FT','2013-10-03'),
    (591,2,'TERM','2013-11-03','ST-RESIGN','2013-11-02',NULL,'2013-11-12'),
    (788,2,NULL,NULL,NULL,NULL,'PRN','2013-04-25'),
    (788,2,'TERM','2013-04-14','AA-AAP','2013-04-13',NULL,'2013-05-09'),
    (788,2,'ACTIVE','2010-08-23','SA-NEW',NULL,NULL,'2013-05-09'),
    (828,2,'ACTIVE','2013-09-17','SA-REHIRE',NULL,'FT','2013-09-18'),
    (828,2,'TERM','2013-11-25','ST-RESIGN','2013-11-24',NULL,'2013-12-02'),
    (828,2,'ACTIVE','2013-11-25','ST-RESIGN',NULL,NULL,'2013-12-10'),
    (828,2,'TERM','2013-11-25','ST-RESIGN',NULL,NULL,'2013-12-10'),
    (832,2,'LEAVE','2010-11-01','SN-NEW',NULL,NULL,'2013-02-07'),
    (832,2,'ACTIVE','2010-11-01','SN-NEW',NULL,NULL,'2013-02-18'),
    (832,2,'TERM','2013-04-13','ST-RESIGN','2013-04-12',NULL,'2013-04-16'),
    (847,2,'LEAVE','2010-11-15','SN-NEW',NULL,NULL,'2013-02-18'),
    (847,2,'ACTIVE','2010-11-15','SN-NEW',NULL,NULL,'2013-06-05'),
    (856,3,NULL,NULL,NULL,NULL,'PRN','2013-08-19'),
    (856,3,NULL,NULL,NULL,NULL,'PRN','2013-08-19'),
    (856,3,NULL,NULL,NULL,NULL,'PRN','2013-08-20'),
    (879,2,'ACTIVE','2011-01-10','SN-NEW',NULL,NULL,'2013-06-06'),
    (879,2,NULL,NULL,NULL,NULL,'FT','2013-08-23'),
    (881,3,'ACTIVE','2011-01-10','SN-NEW',NULL,NULL,'2013-01-28'),
    (881,3,'LEAVE','2011-01-10','SN-NEW',NULL,NULL,'2013-01-28'),
    (881,3,'ACTIVE','2011-01-10','SN-NEW',NULL,NULL,'2013-02-04')
    ;
    

**Query 1**:

    with CTE as (
                  select
                        e.employeeid
                      , e.type 
                      , e.auditdate 
                      , row_number() over(partition by e.employeeid order by e.auditdate) as rn
                  from employees e
                  inner join (
                            select
                                   employeeid
                            from employees
                            where type is not null
                            group by employeeid
                            having min(type) = 'FT' and max(type) = 'PRN'
                            ) inc on e.employeeid = inc.employeeid
                  where e.type is not null
                 )
    select
      o.employeeid
    , o.type      old_type
    , o.auditdate old_date
    , n.type      new_type
    , n.auditdate new_date
    from CTE o
    inner join CTE n on o.employeeid = n.employeeid
                    and o.rn = 1
                    and n.rn = 2
    

**[Results][2]**:
    
    | EMPLOYEEID | OLD_TYPE |   OLD_DATE | NEW_TYPE |   NEW_DATE |
    |------------|----------|------------|----------|------------|
    |         45 |      PRN | 2013-01-28 |       FT | 2014-01-02 |
    |        591 |      PRN | 2013-08-29 |       FT | 2013-10-03 |
    |       1181 |      PRN | 2013-01-22 |       FT | 2013-05-09 |
    |       1184 |      PRN | 2013-01-22 |       FT | 2013-05-09 |
    |       1185 |       FT | 2013-01-22 |      PRN | 2013-10-21 |
    |       1187 |       FT | 2013-01-25 |      PRN | 2013-09-20 |
    |       1189 |      PRN | 2013-01-28 |       FT | 2013-03-27 |
    |       1215 |      PRN | 2013-04-17 |       FT | 2013-09-06 |
    |       1228 |       FT | 2013-05-10 |      PRN | 2013-09-19 |
    |       1244 |       FT | 2013-06-12 |      PRN | 2013-08-19 |
    |       1253 |      PRN | 2013-06-24 |       FT | 2013-09-12 |
    |       1286 |       FT | 2013-10-09 |      PRN | 2013-12-27 |
    |       1296 |       FT | 2013-10-21 |      PRN | 2013-11-06 |
    |       1306 |       FT | 2013-11-15 |      PRN | 2013-12-18 |
    |       1311 |       FT | 2013-12-03 |      PRN | 2013-12-18 |
    |       1313 |       FT | 2013-12-03 |       FT | 2013-12-19 |



  [1]: http://sqlfiddle.com/#!3/55c9b/6

Open in new window

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question