Solved

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

Posted on 2014-09-22
8
172 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
  • 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 24

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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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 24

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

856 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