Solved

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

Posted on 2014-09-22
8
157 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:GPSPOW
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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
Comment Utility
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
Comment Utility
Thank you.

Perfect solution

Glen
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

763 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now