Retrieve rows where specific columns are different between columns
-
I have the following SQL (that I got assistance on her with) to obtain both highest (max) and 2nd highest dated rows. There are some cases in the data where the only difference in the 'OLD ROW' and 'NEW ROW' is the effective date. What I would like to do is only show the old and new rows per EMPLID where either the BANK_CD and/or the ACCOUNT_NUM are different between the 'old' and 'new' rows.
WITH CTE AS ( SELECT A.EMPLID , C.VENDOR_ID , B.FIRST_NAME , B.LAST_NAME ,
A.BANK_CD , A.ACCOUNT_NUM , A.ACCOUNT_TYPE , A.PRIORITY , A.LAST_UPDATE_DATE , A.EFFDT ,
MAX(A.LAST_UPDATE_DATE) OVER(PARTITION BY A.EMPLID) AS MAX_UPDATE_DATE ,
ROW_NUMBER() OVER(PARTITION BY A.EMPLID ORDER BY A.EFFDT DESC , D.EFFDT DESC) AS RN
FROM PS_DIRECT_DEPOSIT D INNER JOIN PS_DIR_DEP_DISTRIB A ON A.EMPLID = D.EMPLID
AND A.EFFDT = D.EFFDT
INNER JOIN PS_EMPLOYEES B ON B.EMPLID = A.EMPLID
INNER JOIN PS_GHS_DIR_DEP_VND C ON C.EMPLID = A.EMPLID
INNER JOIN PS_DIR_DEP_DISTRIB E ON E.EMPLID = A.EMPLID AND E.EFFDT = A.EFFDT AND E.PRIORITY = A.PRIORITY
WHERE B.EMPL_STATUS NOT IN ('T','R','D')
AND ((A.DEPOSIT_TYPE = 'P' AND A.AMOUNT_PCT = 100)
OR A.PRIORITY = 999 OR A.DEPOSIT_TYPE = 'B')
AND D.EFF_STATUS = 'A' )SELECT CASE WHEN RN = 1 THEN 'NEW ROW' WHEN RN = 2 THEN 'OLD ROW' END AS 'ROW_TYPE' , *
FROM CTE WHERE RN IN (1, 2) AND MAX_UPDATE_DATE >= GETDATE() - 8Here is an example of output I am getting now:
ROW_TYPE EMPLID VENDOR_ID FIRST_NAME LAST_NAME BANK_CD ACCOUNT_NUM ACCOUNT_TYPE PRIORITY LAST_UPDATE_DATE EFFDT MAX_UPDATE_DATE RN
NEW ROW 12345 XYZ123 John Smith 111111122 45678 C 999 03/12/2019 03/12/2019 03/12/2019 1
OLD ROW 12345 XYZ123 John Smith 111111122 45678 C 999 10/25/2017 10/25/2017 10/25/2017 2
NEW ROW 47831 A86464 Samm Bulle 754566654 98865 C 999 03/12/2019 06/08/2018 03/12/2019 1
OLD ROW 47831 A86464 Samm Bulle 754566654 45678 C 999 10/25/2017 06/08/2018 10/25/2017 2
NEW ROW 32456 KG4561 Kilo Renne 875123311 32146 C 300 09/02/218 09/02/2018 09/02/2018 1
OLD ROW 32456 KG4561 Kilo Renne 971215477 78131 C 310 12/21/2017 12/21/2017 12/21/2017 2In the above example, I would NOT want to output the first two set of EMPLID rows (top 4 rows) as they have the same BANK_CD and ACCOUNT_NUM between the old and new row. The last set of rows I would want to keep in my output as you can see the BANK_CD and ACCOUNT_NUM are different (either could be though to be included).
I tried adding an self join for PS_DIR_DEP_DISTRIB and then adding in the WHERE clause where AND (A.BANK_CD <> E.BANK_CD OR A.ACCOUNT_NUM <> E.ACCOUNT_NUM) as follows:
WITH CTE AS ( SELECT A.EMPLID --, C.VENDOR_ID , B.FIRST_NAME , B.LAST_NAME ,
A.BANK_CD , A.ACCOUNT_NUM , A.ACCOUNT_TYPE , A.PRIORITY , A.LAST_UPDATE_DATE ,
A.EFFDT , MAX(A.LAST_UPDATE_DATE) OVER(PARTITION BY A.EMPLID) AS MAX_UPDATE_DATE ,
ROW_NUMBER() OVER(PARTITION BY A.EMPLID ORDER BY A.EFFDT DESC , D.EFFDT DESC) AS RN
FROM PS_DIRECT_DEPOSIT D
INNER JOIN PS_DIR_DEP_DISTRIB A ON A.EMPLID = D.EMPLID AND A.EFFDT = D.EFFDT
INNER JOIN PS_EMPLOYEES B ON B.EMPLID = A.EMPLID
INNER JOIN PS_GHS_DIR_DEP_VND C ON C.EMPLID = A.EMPLID
---ADDING SELF JOIN BELOW---
INNER JOIN PS_DIR_DEP_DISTRIB E ON E.EMPLID = A.EMPLID AND E.EFFDT = A.EFFDT AND E.PRIORITY = A.PRIORITY
WHERE B.EMPL_STATUS NOT IN ('T','R','D')
AND ((A.DEPOSIT_TYPE = 'P' AND A.AMOUNT_PCT = 100)
OR A.PRIORITY = 999 OR A.DEPOSIT_TYPE = 'B')
AND D.EFF_STATUS = 'A'
---ADDING NEW WHERE CONDITION BELOW---
AND (E.ACCOUNT_NUM <> A.ACCOUNT_NUM OR E.BANK_CD <> A.BANK_CD ))SELECT CASE WHEN RN = 1 THEN 'NEW ROW' WHEN RN = 2 THEN 'OLD ROW' END AS 'ROW_TYPE' , *
FROM CTE WHERE RN IN (1, 2) AND MAX_UPDATE_DATE >= GETDATE() - 8When I make the above changes though, I do not get any data returned at all. What am I doing wrong?
Just for additional clarity, here is a visual of what I am trying to accomplish:
-
This is a standard gaps and islands problem. If you post CONSUMABLE data (as in the first link in my signature) we can help you with this query.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA - drew.allen - Wednesday, March 20, 2019 12:56 PM
Sorry about that, here is sample data being returned being returned in the query that I'm hoping ca be consumed. If not, please let me know. Thanks!
ROW_TYPE EMPLID FIRST_NAME LAST_NAME BANK_CD ACCOUNT_NUM ACCOUNT_TYPE PRIORITY LAST_UPDATE_DATE EFFDT MAX_UPDATE_DATE RN
NEW ROW 385301 Test1First Test1Last 12345 74333 C 999 3/13/2019 3/13/2019 3/13/2019 1
OLD ROW 385301 Test1First Test1Last 12345 74333 C 999 10/12/2018 10/12/2018 3/13/2019 2
NEW ROW 978255 Test2First Test2Last 2313 169146 C 999 3/13/2019 3/6/2019 3/13/2019 1
OLD ROW 978255 Test2First Test2Last 41438 985126 C 999 7/25/2018 7/25/2018 3/13/2019 2
OLD ROW 391431 Test3First Test3Last 894303 632950 C 310 3/5/2014 6/5/2013 3/15/2019 2
NEW ROW 391431 Test3First Test3Last 894303 27687 S 999 3/15/2019 3/15/2019 3/15/2019 1 -
"Consumable data" means CREATE TABLE and INSERT scripts that people here can copy & paste into SSMS to recreate your problem. If you don't do that, it's very likely you won't get a tested answer.
- kdrymer - Wednesday, March 20, 2019 5:40 PM
You obviously didn't read the first link in my signature. It's very first point is that this is the WRONG way to post data.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
I apologize and hope I have what is needed now. I have created the CREATE TABLE and INSERT scripts in the attached Word document. I did have to change the Date datatype from 'PSDATE' to 'DATETIME' as 'PSDATE' I found is a custom defined datatype in our database, this should not change the input though. I grouped these together by table name for all the tables currently in my CTE, and have populated the insert scripts with example rows.
EMPLID 21345 is what I would expect to be in the final output, as you can see that the ACCOUNT_NUM changed. EMPLID 26110 ,097432 ,1045632, and 100765 should not be in the final output as both the ACCOUNT_NUM and BANK_CD are the same on both of that EMPLID's rows. As long as either the ACCOUNT_NUM and/or the BANK_CD is different between the EMPLID's 'NEW ROW' and 'OLD ROW' then I want to retrieve those.
- kdrymer - Thursday, March 21, 2019 11:32 AM
Most people are not willing to open a Word file posted on the Internet by some random stranger.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA - drew.allen - Thursday, March 21, 2019 11:47 AM
I did that because the scripts are quite long and I didn't want to clutter up the thread more than necessary. I'll put it all in the post though if that's helpful.
-
PS_DIRECT_DEPOSIT
CREATE TABLE [dbo].[PS_DIRECT_DEPOSIT]
(
[EMPLID] VARCHAR(11) COLLATE Latin1_General_BIN NOT NULL
, [EFFDT] DATETIME NOT NULL
, [EFF_STATUS] VARCHAR(1) COLLATE Latin1_General_BIN NOT NULL
, [SUPPR_DDP_ADVICE] VARCHAR(1) COLLATE Latin1_General_BIN NOT NULL
)INSERT [dbo].[PS_DIRECT_DEPOSIT] ([EMPLID], [EFFDT], [EFF_STATUS], [SUPPR_DDP_ADVICE]) VALUES ('21345', CAST('2017-10-17 00:00:00' AS DateTime),'A', 'N')
INSERT [dbo].[PS_DIRECT_DEPOSIT] ([EMPLID], [EFFDT], [EFF_STATUS], [SUPPR_DDP_ADVICE]) VALUES ('21345', CAST('2019-02-28 00:00:00' AS DateTime), 'A', 'N')
INSERT [dbo].[PS_DIRECT_DEPOSIT] ([EMPLID], [EFFDT], [EFF_STATUS], [SUPPR_DDP_ADVICE]) VALUES ('26110', CAST('1998-09-01 00:00:00' AS DateTime), 'A', 'N')
INSERT [dbo].[PS_DIRECT_DEPOSIT] ([EMPLID], [EFFDT], [EFF_STATUS], [SUPPR_DDP_ADVICE]) VALUES ('26110', CAST('2000-04-05 00:00:00' AS DateTime), 'A', 'N')
INSERT [dbo].[PS_DIRECT_DEPOSIT] ([EMPLID], [EFFDT], [EFF_STATUS], [SUPPR_DDP_ADVICE]) VALUES ('26110', CAST('2002-10-17 00:00:00' AS DateTime), 'A', 'N')
INSERT [dbo].[PS_DIRECT_DEPOSIT] ([EMPLID], [EFFDT], [EFF_STATUS], [SUPPR_DDP_ADVICE]) VALUES ('26110', CAST('2003-08-28 00:00:00' AS DateTime), 'A', 'N')
INSERT [dbo].[PS_DIRECT_DEPOSIT] ([EMPLID], [EFFDT], [EFF_STATUS], [SUPPR_DDP_ADVICE]) VALUES ('26110', CAST('2006-04-18 00:00:00' AS DateTime), 'A', 'N')
INSERT [dbo].[PS_DIRECT_DEPOSIT] ([EMPLID], [EFFDT], [EFF_STATUS], [SUPPR_DDP_ADVICE]) VALUES ('26110', CAST('2006-04-21 00:00:00' AS DateTime), 'A', 'N')
INSERT [dbo].[PS_DIRECT_DEPOSIT] ([EMPLID], [EFFDT], [EFF_STATUS], [SUPPR_DDP_ADVICE]) VALUES ('26110', CAST('2006-11-16 00:00:00' AS DateTime), 'A', 'N')
INSERT [dbo].[PS_DIRECT_DEPOSIT] ([EMPLID], [EFFDT], [EFF_STATUS], [SUPPR_DDP_ADVICE]) VALUES ('26110', CAST('2008-08-29 00:00:00' AS DateTime), 'A', 'N')
INSERT [dbo].[PS_DIRECT_DEPOSIT] ([EMPLID], [EFFDT], [EFF_STATUS], [SUPPR_DDP_ADVICE]) VALUES ('26110', CAST('2011-12-09 00:00:00' AS DateTime), 'A', 'N')
INSERT [dbo].[PS_DIRECT_DEPOSIT] ([EMPLID], [EFFDT], [EFF_STATUS], [SUPPR_DDP_ADVICE]) VALUES ('26110', CAST('2012-03-16 00:00:00' AS DateTime), 'A', 'N')
INSERT [dbo].[PS_DIRECT_DEPOSIT] ([EMPLID], [EFFDT], [EFF_STATUS], [SUPPR_DDP_ADVICE]) VALUES ('26110', CAST('2012-03-26 00:00:00' AS DateTime), 'A', 'N')
INSERT [dbo].[PS_DIRECT_DEPOSIT] ([EMPLID], [EFFDT], [EFF_STATUS], [SUPPR_DDP_ADVICE]) VALUES ('26110', CAST('2012-07-30 00:00:00' AS DateTime), 'A', 'N')
INSERT [dbo].[PS_DIRECT_DEPOSIT] ([EMPLID], [EFFDT], [EFF_STATUS], [SUPPR_DDP_ADVICE]) VALUES ('26110', CAST('2019-02-08 00:00:00' AS DateTime), 'A', 'N')
INSERT [dbo].[PS_DIRECT_DEPOSIT] ([EMPLID], [EFFDT], [EFF_STATUS], [SUPPR_DDP_ADVICE]) VALUES ('097432', CAST('2010-04-23 00:00:00' AS DateTime),'A', 'N')
INSERT [dbo].[PS_DIRECT_DEPOSIT] ([EMPLID], [EFFDT], [EFF_STATUS], [SUPPR_DDP_ADVICE]) VALUES ('097432', CAST('2010-07-16 00:00:00' AS DateTime),'A', 'N')
INSERT [dbo].[PS_DIRECT_DEPOSIT] ([EMPLID], [EFFDT], [EFF_STATUS], [SUPPR_DDP_ADVICE]) VALUES ('097432', CAST('2011-10-28 00:00:00' AS DateTime),'A', 'N')
INSERT [dbo].[PS_DIRECT_DEPOSIT] ([EMPLID], [EFFDT], [EFF_STATUS], [SUPPR_DDP_ADVICE]) VALUES ('097432', CAST('2014-11-05 00:00:00' AS DateTime),'A', 'N')
INSERT [dbo].[PS_DIRECT_DEPOSIT] ([EMPLID], [EFFDT], [EFF_STATUS], [SUPPR_DDP_ADVICE]) VALUES ('097432', CAST('2016-06-10 00:00:00' AS DateTime),'A', 'N')
INSERT [dbo].[PS_DIRECT_DEPOSIT] ([EMPLID], [EFFDT], [EFF_STATUS], [SUPPR_DDP_ADVICE]) VALUES ('1045632', CAST('2002-07-11 00:00:00' AS DateTime),'A', 'N')
INSERT [dbo].[PS_DIRECT_DEPOSIT] ([EMPLID], [EFFDT], [EFF_STATUS], [SUPPR_DDP_ADVICE]) VALUES ('1045632', CAST('2002-11-15 00:00:00' AS DateTime),'A', 'N')
INSERT [dbo].[PS_DIRECT_DEPOSIT] ([EMPLID], [EFFDT], [EFF_STATUS], [SUPPR_DDP_ADVICE]) VALUES ('1045632', CAST('2003-04-03 00:00:00' AS DateTime),'A', 'N')
INSERT [dbo].[PS_DIRECT_DEPOSIT] ([EMPLID], [EFFDT], [EFF_STATUS], [SUPPR_DDP_ADVICE]) VALUES ('1045632', CAST('2003-05-02 00:00:00' AS DateTime),'A', 'N')
INSERT [dbo].[PS_DIRECT_DEPOSIT] ([EMPLID], [EFFDT], [EFF_STATUS], [SUPPR_DDP_ADVICE]) VALUES ('1045632', CAST('2004-09-09 00:00:00' AS DateTime),'A', 'N')
INSERT [dbo].[PS_DIRECT_DEPOSIT] ([EMPLID], [EFFDT], [EFF_STATUS], [SUPPR_DDP_ADVICE]) VALUES ('1045632', CAST('2006-03-17 00:00:00' AS DateTime),'A', 'N')
INSERT [dbo].[PS_DIRECT_DEPOSIT] ([EMPLID], [EFFDT], [EFF_STATUS], [SUPPR_DDP_ADVICE]) VALUES ('1045632', CAST('2011-02-25 00:00:00' AS DateTime),'A', 'N')
INSERT [dbo].[PS_DIRECT_DEPOSIT] ([EMPLID], [EFFDT], [EFF_STATUS], [SUPPR_DDP_ADVICE]) VALUES ('1045632', CAST('2018-08-16 00:00:00' AS DateTime),'A', 'N')
INSERT [dbo].[PS_DIRECT_DEPOSIT] ([EMPLID], [EFFDT], [EFF_STATUS], [SUPPR_DDP_ADVICE]) VALUES ('100765', CAST('1998-09-01 00:00:00' AS DateTime),'A', 'N')
INSERT [dbo].[PS_DIRECT_DEPOSIT] ([EMPLID], [EFFDT], [EFF_STATUS], [SUPPR_DDP_ADVICE]) VALUES ('100765', CAST('1999-12-02 00:00:00' AS DateTime),'A', 'N')
INSERT [dbo].[PS_DIRECT_DEPOSIT] ([EMPLID], [EFFDT], [EFF_STATUS], [SUPPR_DDP_ADVICE]) VALUES ('100765', CAST('2001-08-23 00:00:00' AS DateTime),'A', 'N')
INSERT [dbo].[PS_DIRECT_DEPOSIT] ([EMPLID], [EFFDT], [EFF_STATUS], [SUPPR_DDP_ADVICE]) VALUES ('100765', CAST('2003-02-06 00:00:00' AS DateTime),'A', 'N')
INSERT [dbo].[PS_DIRECT_DEPOSIT] ([EMPLID], [EFFDT], [EFF_STATUS], [SUPPR_DDP_ADVICE]) VALUES ('100765', CAST('2007-02-02 00:00:00' AS DateTime),'A', 'N')
INSERT [dbo].[PS_DIRECT_DEPOSIT] ([EMPLID], [EFFDT], [EFF_STATUS], [SUPPR_DDP_ADVICE]) VALUES ('100765', CAST('2011-04-27 00:00:00' AS DateTime),'A', 'N')PS_DIR_DEP_DISTRIB
CREATE TABLE [dbo].[PS_DIR_DEP_DISTRIB]
(
[EMPLID] VARCHAR(11) COLLATE Latin1_General_BIN NOT NULL
, [EFFDT] DATETIME NOT NULL
, [PRIORITY] SMALLINT NOT NULL
, [ACCOUNT_NUM] VARCHAR(17) COLLATE Latin1_General_BIN NOT NULL
, [ACCOUNT_TYPE] VARCHAR(1) COLLATE Latin1_General_BIN NOT NULL
, [COUNTRY_CD] VARCHAR(3) COLLATE Latin1_General_BIN NOT NULL
, [IAT_IND] VARCHAR(1) COLLATE Latin1_General_BIN NOT NULL
, [BANK_CD] VARCHAR(11) COLLATE Latin1_General_BIN NOT NULL
, [BRANCH_EC_CD] VARCHAR(17) COLLATE Latin1_General_BIN NOT NULL
, [DEPOSIT_AMT] DECIMAL(10,2) NOT NULL
, [AMOUNT_PCT] DECIMAL(5,2) NOT NULL
, [PRENOTE_STATUS] VARCHAR(1) COLLATE Latin1_General_BIN NOT NULL
, [PRENOTE_DATE] DATETIME NULL
, [PRENOTE_REQD] VARCHAR(1) COLLATE Latin1_General_BIN NOT NULL
, [LAST_ACTION] VARCHAR(1) COLLATE Latin1_General_BIN NOT NULL
, [LAST_UPDATE_DATE] DATETIME NULL
, [DEPOSIT_TYPE] VARCHAR(1) COLLATE Latin1_General_BIN NOT NULL
, [NICK_NAME] VARCHAR(20) COLLATE Latin1_General_BIN NOT NULL
)INSERT [dbo].[PS_DIR_DEP_DISTRIB] ([EMPLID], [EFFDT], [PRIORITY], [ACCOUNT_NUM], [ACCOUNT_TYPE], [COUNTRY_CD], [IAT_IND], [BANK_CD], [BRANCH_EC_CD], [DEPOSIT_AMT], [AMOUNT_PCT], [PRENOTE_STATUS], [PRENOTE_DATE], [PRENOTE_REQD], [LAST_ACTION], [LAST_UPDATE_DATE], [DEPOSIT_TYPE], [NICK_NAME]) VALUES ('21345', CAST('2017-10-17 00:00:00' AS DateTime), 999, '591678', 'C', 'USA', 'N', '0224547', ' ', CAST(0.00 AS Decimal(10, 2)), CAST(0.00 AS Decimal(5, 2)), 'C', CAST('2017-10-25 00:00:00' AS DateTime), 'Y', 'C', CAST('2017-11-08 00:00:00' AS DateTime), 'B', 'Checking999')
INSERT [dbo].[PS_DIR_DEP_DISTRIB] ([EMPLID], [EFFDT], [PRIORITY], [ACCOUNT_NUM], [ACCOUNT_TYPE], [COUNTRY_CD], [IAT_IND], [BANK_CD], [BRANCH_EC_CD], [DEPOSIT_AMT], [AMOUNT_PCT], [PRENOTE_STATUS], [PRENOTE_DATE], [PRENOTE_REQD], [LAST_ACTION], [LAST_UPDATE_DATE], [DEPOSIT_TYPE], [NICK_NAME]) VALUES ('21345', CAST('2019-02-28 00:00:00' AS DateTime), 1, '12345', 'S', 'USA', 'N', '0224547', ' ', CAST(0.00 AS Decimal(10, 2)), CAST(50.00 AS Decimal(5, 2)), 'N', NULL, 'Y', 'W', CAST('2019-02-28 00:00:00' AS DateTime), 'P', 'Savings2')
INSERT [dbo].[PS_DIR_DEP_DISTRIB] ([EMPLID], [EFFDT], [PRIORITY], [ACCOUNT_NUM], [ACCOUNT_TYPE], [COUNTRY_CD], [IAT_IND], [BANK_CD], [BRANCH_EC_CD], [DEPOSIT_AMT], [AMOUNT_PCT], [PRENOTE_STATUS], [PRENOTE_DATE], [PRENOTE_REQD], [LAST_ACTION], [LAST_UPDATE_DATE], [DEPOSIT_TYPE], [NICK_NAME]) VALUES ('21345', CAST('2019-02-28 00:00:00' AS DateTime), 999, '59167824', 'C', 'USA', 'N', '0224547', ' ', CAST(0.00 AS Decimal(10, 2)), CAST(0.00 AS Decimal(5, 2)), 'C', CAST('2017-10-25 00:00:00' AS DateTime), 'Y', 'W', CAST('2019-02-28 00:00:00' AS DateTime), 'B', 'Checking999')
INSERT [dbo].[PS_DIR_DEP_DISTRIB] ([EMPLID], [EFFDT], [PRIORITY], [ACCOUNT_NUM], [ACCOUNT_TYPE], [COUNTRY_CD], [IAT_IND], [BANK_CD], [BRANCH_EC_CD], [DEPOSIT_AMT], [AMOUNT_PCT], [PRENOTE_STATUS], [PRENOTE_DATE], [PRENOTE_REQD], [LAST_ACTION], [LAST_UPDATE_DATE], [DEPOSIT_TYPE], [NICK_NAME]) VALUES ('26110', CAST('2019-02-08 00:00:00' AS DateTime), 999, '822328', 'C', 'USA', 'N', '8208302', ' ', CAST(0.00 AS Decimal(10, 2)), CAST(0.00 AS Decimal(5, 2)), 'C', CAST('2012-08-08 00:00:00' AS DateTime), 'N', 'W', CAST('2019-02-08 00:00:00' AS DateTime), 'B', 'Checking315')
INSERT [dbo].[PS_DIR_DEP_DISTRIB] ([EMPLID], [EFFDT], [PRIORITY], [ACCOUNT_NUM], [ACCOUNT_TYPE], [COUNTRY_CD], [IAT_IND], [BANK_CD], [BRANCH_EC_CD], [DEPOSIT_AMT], [AMOUNT_PCT], [PRENOTE_STATUS], [PRENOTE_DATE], [PRENOTE_REQD], [LAST_ACTION], [LAST_UPDATE_DATE], [DEPOSIT_TYPE], [NICK_NAME]) VALUES ('26110', CAST('2019-02-08 00:00:00' AS DateTime), 305, '16473', 'S', 'USA', 'N', '23988434', ' ', CAST(265.00 AS Decimal(10, 2)), CAST(0.00 AS Decimal(5, 2)), 'C', CAST('2011-12-14 00:00:00' AS DateTime), 'N', 'W', CAST('2019-02-08 00:00:00' AS DateTime), 'A', 'Savings305')
INSERT [dbo].[PS_DIR_DEP_DISTRIB] ([EMPLID], [EFFDT], [PRIORITY], [ACCOUNT_NUM], [ACCOUNT_TYPE], [COUNTRY_CD], [IAT_IND], [BANK_CD], [BRANCH_EC_CD], [DEPOSIT_AMT], [AMOUNT_PCT], [PRENOTE_STATUS], [PRENOTE_DATE], [PRENOTE_REQD], [LAST_ACTION], [LAST_UPDATE_DATE], [DEPOSIT_TYPE], [NICK_NAME]) VALUES ('26110', CAST('2012-07-30 00:00:00' AS DateTime), 315, '822328', 'C', 'USA', 'N', '8208302', ' ', CAST(0.00 AS Decimal(10, 2)), CAST(0.00 AS Decimal(5, 2)), 'C', CAST('2012-08-08 00:00:00' AS DateTime), 'N', 'C', CAST('2014-03-05 00:00:00' AS DateTime), 'B', 'Checking315')
INSERT [dbo].[PS_DIR_DEP_DISTRIB] ([EMPLID], [EFFDT], [PRIORITY], [ACCOUNT_NUM], [ACCOUNT_TYPE], [COUNTRY_CD], [IAT_IND], [BANK_CD], [BRANCH_EC_CD], [DEPOSIT_AMT], [AMOUNT_PCT], [PRENOTE_STATUS], [PRENOTE_DATE], [PRENOTE_REQD], [LAST_ACTION], [LAST_UPDATE_DATE], [DEPOSIT_TYPE], [NICK_NAME]) VALUES ('26110', CAST('2012-07-30 00:00:00' AS DateTime), 305, '16473', 'S', 'USA', 'N', '23988434', ' ', CAST(264.00 AS Decimal(10, 2)), CAST(0.00 AS Decimal(5, 2)), 'C', CAST('2011-12-14 00:00:00' AS DateTime), 'N', 'C', CAST('2014-03-05 00:00:00' AS DateTime), 'A', 'Savings305')
INSERT [dbo].[PS_DIR_DEP_DISTRIB] ([EMPLID], [EFFDT], [PRIORITY], [ACCOUNT_NUM], [ACCOUNT_TYPE], [COUNTRY_CD], [IAT_IND], [BANK_CD], [BRANCH_EC_CD], [DEPOSIT_AMT], [AMOUNT_PCT], [PRENOTE_STATUS], [PRENOTE_DATE], [PRENOTE_REQD], [LAST_ACTION], [LAST_UPDATE_DATE], [DEPOSIT_TYPE], [NICK_NAME]) VALUES ('26110', CAST('2012-03-26 00:00:00' AS DateTime), 325, '1032396072', 'C', 'USA', 'N', '3258678', ' ', CAST(0.00 AS Decimal(10, 2)), CAST(100.00 AS Decimal(5, 2)), 'C', CAST('2012-03-21 00:00:00' AS DateTime), 'Y', 'C', CAST('2012-04-04 00:00:00' AS DateTime), 'P', 'Checking325')
INSERT [dbo].[PS_DIR_DEP_DISTRIB] ([EMPLID], [EFFDT], [PRIORITY], [ACCOUNT_NUM], [ACCOUNT_TYPE], [COUNTRY_CD], [IAT_IND], [BANK_CD], [BRANCH_EC_CD], [DEPOSIT_AMT], [AMOUNT_PCT], [PRENOTE_STATUS], [PRENOTE_DATE], [PRENOTE_REQD], [LAST_ACTION], [LAST_UPDATE_DATE], [DEPOSIT_TYPE], [NICK_NAME]) VALUES ('097432', CAST('2016-06-10 00:00:00' AS DateTime), 999, '13404891', 'C', 'USA', 'N', '09146115', ' ', CAST(0.00 AS Decimal(10, 2)), CAST(0.00 AS Decimal(5, 2)), 'C', CAST('2011-11-02 00:00:00' AS DateTime), 'Y', 'O', CAST('2016-06-10 00:00:00' AS DateTime), 'B', 'Checking999')
INSERT [dbo].[PS_DIR_DEP_DISTRIB] ([EMPLID], [EFFDT], [PRIORITY], [ACCOUNT_NUM], [ACCOUNT_TYPE], [COUNTRY_CD], [IAT_IND], [BANK_CD], [BRANCH_EC_CD], [DEPOSIT_AMT], [AMOUNT_PCT], [PRENOTE_STATUS], [PRENOTE_DATE], [PRENOTE_REQD], [LAST_ACTION], [LAST_UPDATE_DATE], [DEPOSIT_TYPE], [NICK_NAME]) VALUES ('097432', CAST('2016-06-10 00:00:00' AS DateTime), 3, '13380349, 'S', 'USA', 'N', '09146115', ' ', CAST(50.00 AS Decimal(10, 2)), CAST(0.00 AS Decimal(5, 2)), 'C', CAST('2014-11-12 00:00:00' AS DateTime), 'Y', 'O', CAST('2016-06-10 00:00:00' AS DateTime), 'A', 'Savings3')
INSERT [dbo].[PS_DIR_DEP_DISTRIB] ([EMPLID], [EFFDT], [PRIORITY], [ACCOUNT_NUM], [ACCOUNT_TYPE], [COUNTRY_CD], [IAT_IND], [BANK_CD], [BRANCH_EC_CD], [DEPOSIT_AMT], [AMOUNT_PCT], [PRENOTE_STATUS], [PRENOTE_DATE], [PRENOTE_REQD], [LAST_ACTION], [LAST_UPDATE_DATE], [DEPOSIT_TYPE], [NICK_NAME]) VALUES ('097432', CAST('2016-06-10 00:00:00' AS DateTime), 2, '124648-06, 'S', 'USA', 'N', '21138494', ' ', CAST(100.00 AS Decimal(10, 2)), CAST(0.00 AS Decimal(5, 2)), 'C', CAST('2010-05-05 00:00:00' AS DateTime), 'Y', 'O', CAST('2016-06-10 00:00:00' AS DateTime), 'A', 'Savings2')
INSERT [dbo].[PS_DIR_DEP_DISTRIB] ([EMPLID], [EFFDT], [PRIORITY], [ACCOUNT_NUM], [ACCOUNT_TYPE], [COUNTRY_CD], [IAT_IND], [BANK_CD], [BRANCH_EC_CD], [DEPOSIT_AMT], [AMOUNT_PCT], [PRENOTE_STATUS], [PRENOTE_DATE], [PRENOTE_REQD], [LAST_ACTION], [LAST_UPDATE_DATE], [DEPOSIT_TYPE], [NICK_NAME]) VALUES ('097432', CAST('2014-11-05 00:00:00' AS DateTime), 999, '13404891', 'C', 'USA', 'N', '09146115', ' ', CAST(0.00 AS Decimal(10, 2)), CAST(0.00 AS Decimal(5, 2)), 'C', CAST('2011-11-02 00:00:00' AS DateTime), 'Y', 'O', CAST('2016-06-10 00:00:00' AS DateTime), 'B', 'Checking999')
INSERT [dbo].[PS_DIR_DEP_DISTRIB] ([EMPLID], [EFFDT], [PRIORITY], [ACCOUNT_NUM], [ACCOUNT_TYPE], [COUNTRY_CD], [IAT_IND], [BANK_CD], [BRANCH_EC_CD], [DEPOSIT_AMT], [AMOUNT_PCT], [PRENOTE_STATUS], [PRENOTE_DATE], [PRENOTE_REQD], [LAST_ACTION], [LAST_UPDATE_DATE], [DEPOSIT_TYPE], [NICK_NAME]) VALUES ('1045632', CAST('2018-08-16 00:00:00' AS DateTime), 999, '179410860', 'C', 'USA', 'N', '2345794', ' ', CAST(0.00 AS Decimal(10, 2)), CAST(0.00 AS Decimal(5, 2)), 'N', NULL, 'N', 'W', CAST('2018-08-16 00:00:00' AS DateTime), 'B', 'Checking999')
INSERT [dbo].[PS_DIR_DEP_DISTRIB] ([EMPLID], [EFFDT], [PRIORITY], [ACCOUNT_NUM], [ACCOUNT_TYPE], [COUNTRY_CD], [IAT_IND], [BANK_CD], [BRANCH_EC_CD], [DEPOSIT_AMT], [AMOUNT_PCT], [PRENOTE_STATUS], [PRENOTE_DATE], [PRENOTE_REQD], [LAST_ACTION], [LAST_UPDATE_DATE], [DEPOSIT_TYPE], [NICK_NAME]) VALUES ('1045632', CAST('2018-08-16 00:00:00' AS DateTime), 2, '821408', 'C', 'USA', 'N', '041318332', ' ', CAST(50.00 AS Decimal(10, 2)), CAST(0.00 AS Decimal(5, 2)), 'C', CAST('2018-08-29 00:00:00' AS DateTime), 'Y', 'C', CAST('2018-09-12 00:00:00' AS DateTime), 'A', 'Checking2')
INSERT [dbo].[PS_DIR_DEP_DISTRIB] ([EMPLID], [EFFDT], [PRIORITY], [ACCOUNT_NUM], [ACCOUNT_TYPE], [COUNTRY_CD], [IAT_IND], [BANK_CD], [BRANCH_EC_CD], [DEPOSIT_AMT], [AMOUNT_PCT], [PRENOTE_STATUS], [PRENOTE_DATE], [PRENOTE_REQD], [LAST_ACTION], [LAST_UPDATE_DATE], [DEPOSIT_TYPE], [NICK_NAME]) VALUES ('1045632', CAST('2011-02-25 00:00:00' AS DateTime), 310, '179410860', 'C', 'USA', 'N', '2345794', ' ', CAST(0.00 AS Decimal(10, 2)), CAST(0.00 AS Decimal(5, 2)), 'N', NULL, 'N', 'O', CAST('2011-02-25 00:00:00' AS DateTime), 'B', 'Checking310')
INSERT [dbo].[PS_DIR_DEP_DISTRIB] ([EMPLID], [EFFDT], [PRIORITY], [ACCOUNT_NUM], [ACCOUNT_TYPE], [COUNTRY_CD], [IAT_IND], [BANK_CD], [BRANCH_EC_CD], [DEPOSIT_AMT], [AMOUNT_PCT], [PRENOTE_STATUS], [PRENOTE_DATE], [PRENOTE_REQD], [LAST_ACTION], [LAST_UPDATE_DATE], [DEPOSIT_TYPE], [NICK_NAME]) VALUES ('1045632', CAST('2006-03-17 00:00:00' AS DateTime), 310, '209463', 'S', 'USA', 'N', '2345794', ' ', CAST(0.00 AS Decimal(10, 2)), CAST(0.00 AS Decimal(5, 2)), 'C', CAST('2002-11-20 00:00:00' AS DateTime), 'Y', 'O', CAST('2006-03-31 00:00:00' AS DateTime), 'B', 'Savings310')
INSERT [dbo].[PS_DIR_DEP_DISTRIB] ([EMPLID], [EFFDT], [PRIORITY], [ACCOUNT_NUM], [ACCOUNT_TYPE], [COUNTRY_CD], [IAT_IND], [BANK_CD], [BRANCH_EC_CD], [DEPOSIT_AMT], [AMOUNT_PCT], [PRENOTE_STATUS], [PRENOTE_DATE], [PRENOTE_REQD], [LAST_ACTION], [LAST_UPDATE_DATE], [DEPOSIT_TYPE], [NICK_NAME]) VALUES ('100765', CAST('2011-04-27 00:00:00' AS DateTime), 310, '040925', 'S', 'USA', 'N', ' 2358414', ' ', CAST(0.00 AS Decimal(10, 2)), CAST(0.00 AS Decimal(5, 2)), 'C', NULL, 'N', 'C', CAST('2014-03-05 00:00:00' AS DateTime), 'B', 'Savings310')
INSERT [dbo].[PS_DIR_DEP_DISTRIB] ([EMPLID], [EFFDT], [PRIORITY], [ACCOUNT_NUM], [ACCOUNT_TYPE], [COUNTRY_CD], [IAT_IND], [BANK_CD], [BRANCH_EC_CD], [DEPOSIT_AMT], [AMOUNT_PCT], [PRENOTE_STATUS], [PRENOTE_DATE], [PRENOTE_REQD], [LAST_ACTION], [LAST_UPDATE_DATE], [DEPOSIT_TYPE], [NICK_NAME]) VALUES ('100765', CAST('2017-02-02 00:00:00' AS DateTime), 300, '040925', 'S', 'USA', 'N', ' 2358414', ' ', CAST(290.00 AS Decimal(10, 2)), CAST(0.00 AS Decimal(5, 2)), 'C', NULL, 'N', 'C', CAST('2014-03-05 00:00:00' AS DateTime), 'A', 'Savings300')
INSERT [dbo].[PS_DIR_DEP_DISTRIB] ([EMPLID], [EFFDT], [PRIORITY], [ACCOUNT_NUM], [ACCOUNT_TYPE], [COUNTRY_CD], [IAT_IND], [BANK_CD], [BRANCH_EC_CD], [DEPOSIT_AMT], [AMOUNT_PCT], [PRENOTE_STATUS], [PRENOTE_DATE], [PRENOTE_REQD], [LAST_ACTION], [LAST_UPDATE_DATE], [DEPOSIT_TYPE], [NICK_NAME]) VALUES ('100765', CAST('2003-02-06 00:00:00' AS DateTime), 300, '040925', 'S', 'USA', 'N', ' 2358414', ' ', CAST(290.00 AS Decimal(10, 2)), CAST(0.00 AS Decimal(5, 2)), 'C', NULL, 'N', 'C', CAST('2014-03-05 00:00:00' AS DateTime), 'A', 'Savings300')
INSERT [dbo].[PS_DIR_DEP_DISTRIB] ([EMPLID], [EFFDT], [PRIORITY], [ACCOUNT_NUM], [ACCOUNT_TYPE], [COUNTRY_CD], [IAT_IND], [BANK_CD], [BRANCH_EC_CD], [DEPOSIT_AMT], [AMOUNT_PCT], [PRENOTE_STATUS], [PRENOTE_DATE], [PRENOTE_REQD], [LAST_ACTION], [LAST_UPDATE_DATE], [DEPOSIT_TYPE], [NICK_NAME]) VALUES ('100765', CAST('2001-08-23 00:00:00' AS DateTime), 300, '040925', 'S', 'USA', 'N', ' 2358414', ' ', CAST(0.00 AS Decimal(10, 2)), CAST(100.00 AS Decimal(5, 2)), 'C', NULL, 'N', 'C', CAST('2014-03-05 00:00:00' AS DateTime), 'P', 'Savings300')PS_EMPLOYEES
CREATE TABLE [dbo].[PS_EMPLOYEES]
(
[EMPLID] VARCHAR(11) COLLATE Latin1_General_BIN NOT NULL
, [EMPL_RCD] SMALLINT NOT NULL
, [BIRTHDATE] DATETIME NULL
, [BIRTHPLACE] VARCHAR(30) COLLATE Latin1_General_BIN NOT NULL
, [DT_OF_DEATH] DATETIME NULL
, [COUNTRY_NM_FORMAT] VARCHAR(3) COLLATE Latin1_General_BIN NOT NULL
, [NAME] VARCHAR(50) COLLATE Latin1_General_BIN NOT NULL
, [NAME_INITIALS] VARCHAR(6) COLLATE Latin1_General_BIN NOT NULL
, [NAME_PREFIX] VARCHAR(4) COLLATE Latin1_General_BIN NOT NULL
, [NAME_SUFFIX] VARCHAR(15) COLLATE Latin1_General_BIN NOT NULL
, [NAME_ROYAL_PREFIX] VARCHAR(15) COLLATE Latin1_General_BIN NOT NULL
, [NAME_ROYAL_SUFFIX] VARCHAR(15) COLLATE Latin1_General_BIN NOT NULL
, [NAME_TITLE] VARCHAR(30) COLLATE Latin1_General_BIN NOT NULL
, [LAST_NAME_SRCH] VARCHAR(30) COLLATE Latin1_General_BIN NOT NULL
, [FIRST_NAME_SRCH] VARCHAR(30) COLLATE Latin1_General_BIN NOT NULL
, [LAST_NAME] VARCHAR(30) COLLATE Latin1_General_BIN NOT NULL
, [FIRST_NAME] VARCHAR(30) COLLATE Latin1_General_BIN NOT NULL
, [MIDDLE_NAME] VARCHAR(30) COLLATE Latin1_General_BIN NOT NULL
, [SECOND_LAST_SRCH] VARCHAR(30) COLLATE Latin1_General_BIN NOT NULL
, [SECOND_LAST_NAME] VARCHAR(30) COLLATE Latin1_General_BIN NOT NULL
, [NAME_AC] VARCHAR(50) COLLATE Latin1_General_BIN NOT NULL
, [PREF_FIRST_NAME] VARCHAR(30) COLLATE Latin1_General_BIN NOT NULL
, [LAST_NAME_PREF_NLD] VARCHAR(1) COLLATE Latin1_General_BIN NOT NULL
, [COUNTRY] VARCHAR(3) COLLATE Latin1_General_BIN NOT NULL
, [ADDRESS1] VARCHAR(55) COLLATE Latin1_General_BIN NOT NULL
, [ADDRESS2] VARCHAR(55) COLLATE Latin1_General_BIN NOT NULL
, [ADDRESS3] VARCHAR(55) COLLATE Latin1_General_BIN NOT NULL
, [ADDRESS4] VARCHAR(55) COLLATE Latin1_General_BIN NOT NULL
, [CITY] VARCHAR(30) COLLATE Latin1_General_BIN NOT NULL
, [NUM1] VARCHAR(6) COLLATE Latin1_General_BIN NOT NULL
, [NUM2] VARCHAR(6) COLLATE Latin1_General_BIN NOT NULL
, [HOUSE_TYPE] VARCHAR(2) COLLATE Latin1_General_BIN NOT NULL
, [ADDR_FIELD1] VARCHAR(2) COLLATE Latin1_General_BIN NOT NULL
, [ADDR_FIELD2] VARCHAR(4) COLLATE Latin1_General_BIN NOT NULL
, [ADDR_FIELD3] VARCHAR(4) COLLATE Latin1_General_BIN NOT NULL
, [COUNTY] VARCHAR(30) COLLATE Latin1_General_BIN NOT NULL
, [STATE] VARCHAR(6) COLLATE Latin1_General_BIN NOT NULL
, [POSTAL] VARCHAR(12) COLLATE Latin1_General_BIN NOT NULL
, [GEO_CODE] VARCHAR(11) COLLATE Latin1_General_BIN NOT NULL
, [IN_CITY_LIMIT] VARCHAR(1) COLLATE Latin1_General_BIN NOT NULL
, [HOME_PHONE] VARCHAR(24) COLLATE Latin1_General_BIN NOT NULL
, [NATIONAL_ID_TYPE] VARCHAR(6) COLLATE Latin1_General_BIN NOT NULL
, [NATIONAL_ID] VARCHAR(20) COLLATE Latin1_General_BIN NOT NULL
, [SEX] VARCHAR(1) COLLATE Latin1_General_BIN NOT NULL
, [MAR_STATUS] VARCHAR(1) COLLATE Latin1_General_BIN NOT NULL
, [HIGHEST_EDUC_LVL] VARCHAR(2) COLLATE Latin1_General_BIN NOT NULL
, [FT_STUDENT] VARCHAR(1) COLLATE Latin1_General_BIN NOT NULL
, [MILITARY_STATUS] VARCHAR(1) COLLATE Latin1_General_BIN NOT NULL
, [US_WORK_ELIGIBILTY] VARCHAR(1) COLLATE Latin1_General_BIN NOT NULL
, [MILIT_SITUATN_FRA] VARCHAR(3) COLLATE Latin1_General_BIN NOT NULL
, [DISABLED] VARCHAR(1) COLLATE Latin1_General_BIN NOT NULL
, [DISABLED_VET] VARCHAR(1) COLLATE Latin1_General_BIN NOT NULL
, [ETHNIC_GROUP] VARCHAR(1) COLLATE Latin1_General_BIN NOT NULL
, [TWO_RACES_IND_USA] VARCHAR(1) COLLATE Latin1_General_BIN NOT NULL
, [CITIZENSHIP_STATUS] VARCHAR(1) COLLATE Latin1_General_BIN NOT NULL
, [ORIG_HIRE_DT] DATETIME NULL
, [PER_ORG] VARCHAR(3) COLLATE Latin1_General_BIN NOT NULL
, [BENEFIT_RCD_NBR] SMALLINT NOT NULL
, [CMPNY_SENIORITY_DT] DATETIME NULL
, [SERVICE_DT] DATETIME NULL
, [HOME_HOST_CLASS] VARCHAR(1) COLLATE Latin1_General_BIN NOT NULL
, [LAST_INCREASE_DT] DATETIME NULL
, [OWN_5PERCENT_CO] VARCHAR(1) COLLATE Latin1_General_BIN NOT NULL
, [BUSINESS_TITLE] VARCHAR(30) COLLATE Latin1_General_BIN NOT NULL
, [PROBATION_DT] DATETIME NULL
, [EFFDT] DATETIME NOT NULL
, [EFFSEQ] SMALLINT NOT NULL
, [HIRE_DT] DATETIME NULL
, [EXPECTED_RETURN_DT] DATETIME NULL
, [TERMINATION_DT] DATETIME NULL
, [LAST_DATE_WORKED] DATETIME NULL
, [REPORTS_TO] VARCHAR(8) COLLATE Latin1_General_BIN NOT NULL
, [SUPERVISOR_ID] VARCHAR(11) COLLATE Latin1_General_BIN NOT NULL
, [BUSINESS_UNIT] VARCHAR(5) COLLATE Latin1_General_BIN NOT NULL
, [DEPTID] VARCHAR(10) COLLATE Latin1_General_BIN NOT NULL
, [JOBCODE] VARCHAR(6) COLLATE Latin1_General_BIN NOT NULL
, [POSITION_NBR] VARCHAR(8) COLLATE Latin1_General_BIN NOT NULL
, [EMPL_STATUS] VARCHAR(1) COLLATE Latin1_General_BIN NOT NULL
, [ACTION] VARCHAR(3) COLLATE Latin1_General_BIN NOT NULL
, [ACTION_DT] DATETIME NULL
, [ACTION_REASON] VARCHAR(3) COLLATE Latin1_General_BIN NOT NULL
, [LOCATION] VARCHAR(10) COLLATE Latin1_General_BIN NOT NULL
, [JOB_ENTRY_DT] DATETIME NULL
, [DEPT_ENTRY_DT] DATETIME NULL
, [POSITION_ENTRY_DT] DATETIME NULL
, [SHIFT] VARCHAR(1) COLLATE Latin1_General_BIN NOT NULL
, [REG_TEMP] VARCHAR(1) COLLATE Latin1_General_BIN NOT NULL
, [FULL_PART_TIME] VARCHAR(1) COLLATE Latin1_General_BIN NOT NULL
, [FLSA_STATUS] VARCHAR(1) COLLATE Latin1_General_BIN NOT NULL
, [OFFICER_CD] VARCHAR(1) COLLATE Latin1_General_BIN NOT NULL
, [COMPANY] VARCHAR(3) COLLATE Latin1_General_BIN NOT NULL
, [PAYGROUP] VARCHAR(3) COLLATE Latin1_General_BIN NOT NULL
, [EMPL_TYPE] VARCHAR(1) COLLATE Latin1_General_BIN NOT NULL
, [HOLIDAY_SCHEDULE] VARCHAR(6) COLLATE Latin1_General_BIN NOT NULL
, [STD_HOURS] DECIMAL(6,2) NOT NULL
, [STD_HRS_FREQUENCY] VARCHAR(5) COLLATE Latin1_General_BIN NOT NULL
, [REG_REGION] VARCHAR(5) COLLATE Latin1_General_BIN NOT NULL
, [PAID_HOURS] DECIMAL(6,2) NOT NULL
, [PAID_FTE] DECIMAL(7,6) NOT NULL
, [PAID_HRS_FREQUENCY] VARCHAR(5) COLLATE Latin1_General_BIN NOT NULL
, [FTE] DECIMAL(7,6) NOT NULL
, [EEO_CLASS] VARCHAR(1) COLLATE Latin1_General_BIN NOT NULL
, [SAL_ADMIN_PLAN] VARCHAR(4) COLLATE Latin1_General_BIN NOT NULL
, [GRADE] VARCHAR(3) COLLATE Latin1_General_BIN NOT NULL
, [GRADE_ENTRY_DT] DATETIME NULL
, [STEP] SMALLINT NOT NULL
, [STEP_ENTRY_DT] DATETIME NULL
, [GL_PAY_TYPE] VARCHAR(6) COLLATE Latin1_General_BIN NOT NULL
, [COMP_FREQUENCY] VARCHAR(5) COLLATE Latin1_General_BIN NOT NULL
, [COMPRATE] DECIMAL(18,6) NOT NULL
, [CHANGE_AMT] DECIMAL(18,6) NOT NULL
, [CHANGE_PCT] DECIMAL(6,3) NOT NULL
, [ANNUAL_RT] DECIMAL(18,3) NOT NULL
, [MONTHLY_RT] DECIMAL(18,3) NOT NULL
, [DAILY_RT] DECIMAL(18,3) NOT NULL
, [HOURLY_RT] DECIMAL(18,6) NOT NULL
, [ANNL_BENEF_BASE_RT] DECIMAL(18,3) NOT NULL
, [SHIFT_RT] DECIMAL(18,6) NOT NULL
, [SHIFT_FACTOR] DECIMAL(4,3) NOT NULL
, [CURRENCY_CD] VARCHAR(3) COLLATE Latin1_General_BIN NOT NULL
, [DIRECTLY_TIPPED] VARCHAR(1) COLLATE Latin1_General_BIN NOT NULL
, [PAY_SYSTEM_FLG] VARCHAR(2) COLLATE Latin1_General_BIN NOT NULL
, [SETID_DEPT] VARCHAR(5) COLLATE Latin1_General_BIN NOT NULL
, [SETID_JOBCODE] VARCHAR(5) COLLATE Latin1_General_BIN NOT NULL
, [SETID_LOCATION] VARCHAR(5) COLLATE Latin1_General_BIN NOT NULL
, [SETID_SALARY] VARCHAR(5) COLLATE Latin1_General_BIN NOT NULL
, [GP_PAYGROUP] VARCHAR(10) COLLATE Latin1_General_BIN NOT NULL
, [GP_ELIG_GRP] VARCHAR(10) COLLATE Latin1_General_BIN NOT NULL
, [CUR_RT_TYPE] VARCHAR(5) COLLATE Latin1_General_BIN NOT NULL
, [GP_ASOF_DT_EXG_RT] VARCHAR(1) COLLATE Latin1_General_BIN NOT NULL
, [JOB_INDICATOR] VARCHAR(1) COLLATE Latin1_General_BIN NOT NULL
, [PAY_UNION_FEE] VARCHAR(1) COLLATE Latin1_General_BIN NOT NULL
, [UNION_CD] VARCHAR(3) COLLATE Latin1_General_BIN NOT NULL
, [BARG_UNIT] VARCHAR(4) COLLATE Latin1_General_BIN NOT NULL
, [UNION_SENIORITY_DT] DATETIME NULL
, [ENTRY_DATE] DATETIME NULL
, [LABOR_AGREEMENT] VARCHAR(6) COLLATE Latin1_General_BIN NOT NULL
, [EMPL_CTG] VARCHAR(6) COLLATE Latin1_General_BIN NOT NULL
, [EMPL_CTG_L1] VARCHAR(6) COLLATE Latin1_General_BIN NOT NULL
, [EMPL_CTG_L2] VARCHAR(6) COLLATE Latin1_General_BIN NOT NULL
, [SETID_LBR_AGRMNT] VARCHAR(5) COLLATE Latin1_General_BIN NOT NULL
, [WPP_STOP_FLAG] VARCHAR(1) COLLATE Latin1_General_BIN NOT NULL
, [LABOR_FACILITY_ID] VARCHAR(10) COLLATE Latin1_General_BIN NOT NULL
, [LBR_FAC_ENTRY_DT] DATETIME NULL
, [LAYOFF_EXEMPT_FLAG] VARCHAR(1) COLLATE Latin1_General_BIN NOT NULL
, [LAYOFF_EXEMPT_RSN] VARCHAR(11) COLLATE Latin1_General_BIN NOT NULL
, [VALUE_1_FRA] VARCHAR(5) COLLATE Latin1_General_BIN NOT NULL
, [VALUE_2_FRA] VARCHAR(5) COLLATE Latin1_General_BIN NOT NULL
, [VALUE_3_FRA] VARCHAR(5) COLLATE Latin1_General_BIN NOT NULL
, [VALUE_4_FRA] VARCHAR(5) COLLATE Latin1_General_BIN NOT NULL
, [VALUE_5_FRA] VARCHAR(5) COLLATE Latin1_General_BIN NOT NULL
, [GVT_SCD_RETIRE] DATETIME NULL
, [GVT_MAND_RET_DT] DATETIME NULL
, [GVT_SCD_TSP] DATETIME NULL
, [GVT_SCD_SEVPAY] DATETIME NULL
, [GVT_DT_LEI] DATETIME NULL
, [GVT_PAY_BASIS] VARCHAR(2) COLLATE Latin1_General_BIN NOT NULL
, [GVT_WGI_STATUS] VARCHAR(1) COLLATE Latin1_General_BIN NOT NULL
, [GVT_WGI_DUE_DATE] DATETIME NULL
, [GVT_INTRM_DAYS_WGI] SMALLINT NOT NULL
, [GVT_LOCALITY_ADJ] DECIMAL(7,2) NOT NULL
, [GVT_WORK_SCHED] VARCHAR(1) COLLATE Latin1_General_BIN NOT NULL
, [GVT_SEVPAY_PRV_WKS] SMALLINT NOT NULL
, [GVT_BIWEEKLY_RT] DECIMAL(9,2) NOT NULL
, [GVT_STEP] VARCHAR(2) COLLATE Latin1_General_BIN NOT NULL
, [GVT_RTND_PAY_PLAN] VARCHAR(2) COLLATE Latin1_General_BIN NOT NULL
, [GVT_RTND_SAL_PLAN] VARCHAR(4) COLLATE Latin1_General_BIN NOT NULL
, [GVT_RTND_GRADE] VARCHAR(3) COLLATE Latin1_General_BIN NOT NULL
, [GVT_RTND_STEP] SMALLINT NOT NULL
, [GVT_RTND_GVT_STEP] VARCHAR(2) COLLATE Latin1_General_BIN NOT NULL
, [GVT_RTND_GRADE_BEG] DATETIME NULL
, [GVT_RTND_GRADE_EXP] DATETIME NULL
, [GVT_TEMP_PRO_EXPIR] DATETIME NULL
, [GVT_TEMP_PSN_EXPIR] DATETIME NULL
, [GVT_DETAIL_EXPIRES] DATETIME NULL
, [GVT_SABBATIC_EXPIR] DATETIME NULL
, [GVT_TYPE_OF_APPT] VARCHAR(2) COLLATE Latin1_General_BIN NOT NULL
, [GVT_APPT_EXPIR_DT] DATETIME NULL
, [GVT_CAREER_CNV_DUE] DATETIME NULL
, [GVT_SUPV_PROB_DT] DATETIME NULL
, [GVT_SES_PROB_DT] DATETIME NULL
, [GVT_SEC_CLR_STATUS] VARCHAR(1) COLLATE Latin1_General_BIN NOT NULL
, [GVT_CLRNCE_STAT_DT] DATETIME NULL
, [EEO1CODE] VARCHAR(1) COLLATE Latin1_General_BIN NOT NULL
, [EEO4CODE] VARCHAR(1) COLLATE Latin1_General_BIN NOT NULL
, [EEO5CODE] VARCHAR(2) COLLATE Latin1_General_BIN NOT NULL
, [EEO6CODE] VARCHAR(1) COLLATE Latin1_General_BIN NOT NULL
, [EEO_JOB_GROUP] VARCHAR(4) COLLATE Latin1_General_BIN NOT NULL
, [JOB_FAMILY] VARCHAR(6) COLLATE Latin1_General_BIN NOT NULL
, [JOB_KNOWHOW_POINTS] INT NOT NULL
, [JOB_ACCNTAB_POINTS] INT NOT NULL
, [JOB_PROBSLV_POINTS] INT NOT NULL
, [JOB_POINTS_TOTAL] INT NOT NULL
, [JOB_KNOWHOW_PCT] DECIMAL(4,1) NOT NULL
, [JOB_ACCNTAB_PCT] DECIMAL(4,1) NOT NULL
, [JOB_PROBSLV_PCT] DECIMAL(4,1) NOT NULL
, [IPEDSSCODE] VARCHAR(1) COLLATE Latin1_General_BIN NOT NULL
, [GVT_ORG_TTL_DESCR] VARCHAR(70) COLLATE Latin1_General_BIN NOT NULL
, [MANAGER_ID] VARCHAR(11) COLLATE Latin1_General_BIN NOT NULL
, [EEO4_FUNCTION] VARCHAR(2) COLLATE Latin1_General_BIN NOT NULL
, [ASOFDATE] DATETIME NULL
, [FROMDATE] DATETIME NULL
, [JOBTITLE] VARCHAR(30) COLLATE Latin1_General_BIN NOT NULL
, [JOBTITLE_ABBRV] VARCHAR(10) COLLATE Latin1_General_BIN NOT NULL
, [DEPTNAME] VARCHAR(30) COLLATE Latin1_General_BIN NOT NULL
, [DEPTNAME_ABBRV] VARCHAR(10) COLLATE Latin1_General_BIN NOT NULL
, [REHIRE_DT] DATETIME NULL
, [WORK_PHONE] VARCHAR(24) COLLATE Latin1_General_BIN NOT NULL
, [NID_COUNTRY] VARCHAR(3) COLLATE Latin1_General_BIN NOT NULL
, [GVT_OVERTIME_RT] DECIMAL(7,3) NOT NULL
, [GVT_RTND_PAY_BASIS] VARCHAR(2) COLLATE Latin1_General_BIN NOT NULL
, [SEC_CLEARANCE_TYPE] VARCHAR(3) COLLATE Latin1_General_BIN NOT NULL
)CREATE NONCLUSTERED INDEX [PS0EMPLOYEES] ON [dbo].[PS_EMPLOYEES] ([NAME] ASC, [EMPLID] ASC, [EMPL_RCD] ASC)
CREATE NONCLUSTERED INDEX [PS1EMPLOYEES] ON [dbo].[PS_EMPLOYEES] ([LAST_NAME_SRCH] ASC, [EMPLID] ASC, [EMPL_RCD] ASC)
INSERT [dbo].[PS_EMPLOYEES] ([EMPLID], [EMPL_RCD], [BIRTHDATE], [BIRTHPLACE], [DT_OF_DEATH], [COUNTRY_NM_FORMAT], [NAME], [NAME_INITIALS], [NAME_PREFIX], [NAME_SUFFIX], [NAME_ROYAL_PREFIX], [NAME_ROYAL_SUFFIX], [NAME_TITLE], [LAST_NAME_SRCH], [FIRST_NAME_SRCH], [LAST_NAME], [FIRST_NAME], [MIDDLE_NAME], [SECOND_LAST_SRCH], [SECOND_LAST_NAME], [NAME_AC], [PREF_FIRST_NAME], [LAST_NAME_PREF_NLD], [COUNTRY], [ADDRESS1], [ADDRESS2], [ADDRESS3], [ADDRESS4], [CITY], [NUM1], [NUM2], [HOUSE_TYPE], [ADDR_FIELD1], [ADDR_FIELD2], [ADDR_FIELD3], [COUNTY], [STATE], [POSTAL], [GEO_CODE], [IN_CITY_LIMIT], [HOME_PHONE], [NATIONAL_ID_TYPE], [NATIONAL_ID], [SEX], [MAR_STATUS], [HIGHEST_EDUC_LVL], [FT_STUDENT], [MILITARY_STATUS], [US_WORK_ELIGIBILTY], [MILIT_SITUATN_FRA], [DISABLED], [DISABLED_VET], [ETHNIC_GROUP], [TWO_RACES_IND_USA], [CITIZENSHIP_STATUS], [ORIG_HIRE_DT], [PER_ORG], [BENEFIT_RCD_NBR], [CMPNY_SENIORITY_DT], [SERVICE_DT], [HOME_HOST_CLASS], [LAST_INCREASE_DT], [OWN_5PERCENT_CO], [BUSINESS_TITLE], [PROBATION_DT], [EFFDT], [EFFSEQ], [HIRE_DT], [EXPECTED_RETURN_DT], [TERMINATION_DT], [LAST_DATE_WORKED], [REPORTS_TO], [SUPERVISOR_ID], [BUSINESS_UNIT], [DEPTID], [JOBCODE], [POSITION_NBR], [EMPL_STATUS], [ACTION], [ACTION_DT], [ACTION_REASON], [LOCATION], [JOB_ENTRY_DT], [DEPT_ENTRY_DT], [POSITION_ENTRY_DT], [SHIFT], [REG_TEMP], [FULL_PART_TIME], [FLSA_STATUS], [OFFICER_CD], [COMPANY], [PAYGROUP], [EMPL_TYPE], [HOLIDAY_SCHEDULE], [STD_HOURS], [STD_HRS_FREQUENCY], [REG_REGION], [PAID_HOURS], [PAID_FTE], [PAID_HRS_FREQUENCY], [FTE], [EEO_CLASS], [SAL_ADMIN_PLAN], [GRADE], [GRADE_ENTRY_DT], [STEP], [STEP_ENTRY_DT], [GL_PAY_TYPE], [COMP_FREQUENCY], [COMPRATE], [CHANGE_AMT], [CHANGE_PCT], [ANNUAL_RT], [MONTHLY_RT], [DAILY_RT], [HOURLY_RT], [ANNL_BENEF_BASE_RT], [SHIFT_RT], [SHIFT_FACTOR], [CURRENCY_CD], [DIRECTLY_TIPPED], [PAY_SYSTEM_FLG], [SETID_DEPT], [SETID_JOBCODE], [SETID_LOCATION], [SETID_SALARY], [GP_PAYGROUP], [GP_ELIG_GRP], [CUR_RT_TYPE], [GP_ASOF_DT_EXG_RT], [JOB_INDICATOR], [PAY_UNION_FEE], [UNION_CD], [BARG_UNIT], [UNION_SENIORITY_DT], [ENTRY_DATE], [LABOR_AGREEMENT], [EMPL_CTG], [EMPL_CTG_L1], [EMPL_CTG_L2], [SETID_LBR_AGRMNT], [WPP_STOP_FLAG], [LABOR_FACILITY_ID], [LBR_FAC_ENTRY_DT], [LAYOFF_EXEMPT_FLAG], [LAYOFF_EXEMPT_RSN], [VALUE_1_FRA], [VALUE_2_FRA], [VALUE_3_FRA], [VALUE_4_FRA], [VALUE_5_FRA], [GVT_SCD_RETIRE], [GVT_MAND_RET_DT], [GVT_SCD_TSP], [GVT_SCD_SEVPAY], [GVT_DT_LEI], [GVT_PAY_BASIS], [GVT_WGI_STATUS], [GVT_WGI_DUE_DATE], [GVT_INTRM_DAYS_WGI], [GVT_LOCALITY_ADJ], [GVT_WORK_SCHED], [GVT_SEVPAY_PRV_WKS], [GVT_BIWEEKLY_RT], [GVT_STEP], [GVT_RTND_PAY_PLAN], [GVT_RTND_SAL_PLAN], [GVT_RTND_GRADE], [GVT_RTND_STEP], [GVT_RTND_GVT_STEP], [GVT_RTND_GRADE_BEG], [GVT_RTND_GRADE_EXP], [GVT_TEMP_PRO_EXPIR], [GVT_TEMP_PSN_EXPIR], [GVT_DETAIL_EXPIRES], [GVT_SABBATIC_EXPIR], [GVT_TYPE_OF_APPT], [GVT_APPT_EXPIR_DT], [GVT_CAREER_CNV_DUE], [GVT_SUPV_PROB_DT], [GVT_SES_PROB_DT], [GVT_SEC_CLR_STATUS], [GVT_CLRNCE_STAT_DT], [EEO1CODE], [EEO4CODE], [EEO5CODE], [EEO6CODE], [EEO_JOB_GROUP], [JOB_FAMILY], [JOB_KNOWHOW_POINTS], [JOB_ACCNTAB_POINTS], [JOB_PROBSLV_POINTS], [JOB_POINTS_TOTAL], [JOB_KNOWHOW_PCT], [JOB_ACCNTAB_PCT], [JOB_PROBSLV_PCT], [IPEDSSCODE], [GVT_ORG_TTL_DESCR], [MANAGER_ID], [EEO4_FUNCTION], [ASOFDATE], [FROMDATE], [JOBTITLE], [JOBTITLE_ABBRV], [DEPTNAME], [DEPTNAME_ABBRV], [REHIRE_DT], [WORK_PHONE], [NID_COUNTRY], [GVT_OVERTIME_RT], [GVT_RTND_PAY_BASIS], [SEC_CLEARANCE_TYPE]) VALUES ('21345', 0, CAST('1901-01-01 00:00:00' AS DateTime), ' ', NULL, '001', 'J SMITH', ' ', ' ', ' ', ' ', ' ', ' ', 'SMITH', 'J', 'Smith', 'J', 'D', ' ', ' ', ' ', ' ', '1', 'USA', '123 Main St, ' ', ' ', ' ', 'Anytown', ' ', ' ', ' ', ' ', ' ', ' ', 'Anytown', 'MS', '00000', ' ', ' ', ' ', 'PR', '000000000', 'M', 'M', 'O', 'N', '1', 'N', ' ', ' ', ' ', '1', 'N', ' ', CAST(N'2017-10-09 00:00:00' AS DateTime), 'EMP', 0, CAST('2017-10-09 00:00:00' AS DateTime), CAST('2017-10-29 00:00:00' AS DateTime), 'M', CAST('2018-01-01 00:00:00' AS DateTime), 'N', 'Assistant', NULL, CAST('2018-01-01 00:00:00' AS DateTime), 0, CAST('2017-10-09 00:00:00' AS DateTime), NULL, NULL, NULL, '56478', ' ', '50000', '54000', '54300', '9129', 'A', 'BSE', CAST('2018-01-11 00:00:00' AS DateTime), 'ANL', '50000', CAST('2017-10-09 00:00:00' AS DateTime), CAST('2017-10-09 00:00:00' AS DateTime), CAST('2017-10-09 00:00:00' AS DateTime), 'N', 'R', 'F', 'X', 'N', 'TGX', '50E', 'E', 'HOL1', CAST(40.00 AS Decimal(6, 2)), 'W', 'USA', CAST(0.00 AS Decimal(6, 2)), CAST(0.000000 AS Decimal(7, 6)), ' ', CAST(1.000000 AS Decimal(7, 6)), 'N', 'TGX', 'A13', CAST('2017-10-09 00:00:00' AS DateTime), 0, NULL, ' ', 'H', CAST(00.510000 AS Decimal(18, 6)), CAST(0.670000 AS Decimal(18, 6)), CAST(2.000 AS Decimal(6, 3)), CAST(100.800 AS Decimal(18, 3)), CAST(102.067 AS Decimal(18, 3)), CAST(140.080 AS Decimal(18, 3)), CAST(17.510000 AS Decimal(18, 6)), CAST(420.800 AS Decimal(18, 3)), CAST(0.000000 AS Decimal(18, 6)), CAST(0.000 AS Decimal(4, 3)), 'USD', 'N', 'NA', 'SHARE', 'SHARE', 'SHARE', 'SHARE', ' ', ' ', ' ', ' ', 'P', 'N', ' ', ' ', NULL, NULL, ' ', ' ', ' ', ' ', ' ', 'N', ' ', NULL, 'N', ' ', ' ', ' ', ' ', ' ', ' ', NULL, NULL, NULL, NULL, NULL, ' ', ' ', NULL, 0, CAST(0.00 AS Decimal(7, 2)), ' ', 0, CAST(0.00 AS Decimal(9, 2)), ' ', ' ', ' ', ' ', 0, ' ', NULL, NULL, NULL, NULL, NULL, NULL, ' ', NULL, NULL, NULL, NULL, ' ', NULL, '5', 'N', 'N', 'N', ' ', 'CLIN', 0, 0, 0, 0, CAST(0.0 AS Decimal(4, 1)), CAST(0.0 AS Decimal(4, 1)), CAST(0.0 AS Decimal(4, 1)), 'N', ' ', ' ', ' ', CAST('2019-01-21 00:00:00' AS DateTime), CAST('2019-01-21 00:00:00' AS DateTime), 'Assistant', 'Care', 'Practice', 'FP', CAST('2017-04-10 00:00:00' AS DateTime), ' ', 'USA', CAST(0.000 AS Decimal(7, 3)), ' ', ' ')
INSERT [dbo].[PS_EMPLOYEES] ([EMPLID], [EMPL_RCD], [BIRTHDATE], [BIRTHPLACE], [DT_OF_DEATH], [COUNTRY_NM_FORMAT], [NAME], [NAME_INITIALS], [NAME_PREFIX], [NAME_SUFFIX], [NAME_ROYAL_PREFIX], [NAME_ROYAL_SUFFIX], [NAME_TITLE], [LAST_NAME_SRCH], [FIRST_NAME_SRCH], [LAST_NAME], [FIRST_NAME], [MIDDLE_NAME], [SECOND_LAST_SRCH], [SECOND_LAST_NAME], [NAME_AC], [PREF_FIRST_NAME], [LAST_NAME_PREF_NLD], [COUNTRY], [ADDRESS1], [ADDRESS2], [ADDRESS3], [ADDRESS4], [CITY], [NUM1], [NUM2], [HOUSE_TYPE], [ADDR_FIELD1], [ADDR_FIELD2], [ADDR_FIELD3], [COUNTY], [STATE], [POSTAL], [GEO_CODE], [IN_CITY_LIMIT], [HOME_PHONE], [NATIONAL_ID_TYPE], [NATIONAL_ID], [SEX], [MAR_STATUS], [HIGHEST_EDUC_LVL], [FT_STUDENT], [MILITARY_STATUS], [US_WORK_ELIGIBILTY], [MILIT_SITUATN_FRA], [DISABLED], [DISABLED_VET], [ETHNIC_GROUP], [TWO_RACES_IND_USA], [CITIZENSHIP_STATUS], [ORIG_HIRE_DT], [PER_ORG], [BENEFIT_RCD_NBR], [CMPNY_SENIORITY_DT], [SERVICE_DT], [HOME_HOST_CLASS], [LAST_INCREASE_DT], [OWN_5PERCENT_CO], [BUSINESS_TITLE], [PROBATION_DT], [EFFDT], [EFFSEQ], [HIRE_DT], [EXPECTED_RETURN_DT], [TERMINATION_DT], [LAST_DATE_WORKED], [REPORTS_TO], [SUPERVISOR_ID], [BUSINESS_UNIT], [DEPTID], [JOBCODE], [POSITION_NBR], [EMPL_STATUS], [ACTION], [ACTION_DT], [ACTION_REASON], [LOCATION], [JOB_ENTRY_DT], [DEPT_ENTRY_DT], [POSITION_ENTRY_DT], [SHIFT], [REG_TEMP], [FULL_PART_TIME], [FLSA_STATUS], [OFFICER_CD], [COMPANY], [PAYGROUP], [EMPL_TYPE], [HOLIDAY_SCHEDULE], [STD_HOURS], [STD_HRS_FREQUENCY], [REG_REGION], [PAID_HOURS], [PAID_FTE], [PAID_HRS_FREQUENCY], [FTE], [EEO_CLASS], [SAL_ADMIN_PLAN], [GRADE], [GRADE_ENTRY_DT], [STEP], [STEP_ENTRY_DT], [GL_PAY_TYPE], [COMP_FREQUENCY], [COMPRATE], [CHANGE_AMT], [CHANGE_PCT], [ANNUAL_RT], [MONTHLY_RT], [DAILY_RT], [HOURLY_RT], [ANNL_BENEF_BASE_RT], [SHIFT_RT], [SHIFT_FACTOR], [CURRENCY_CD], [DIRECTLY_TIPPED], [PAY_SYSTEM_FLG], [SETID_DEPT], [SETID_JOBCODE], [SETID_LOCATION], [SETID_SALARY], [GP_PAYGROUP], [GP_ELIG_GRP], [CUR_RT_TYPE], [GP_ASOF_DT_EXG_RT], [JOB_INDICATOR], [PAY_UNION_FEE], [UNION_CD], [BARG_UNIT], [UNION_SENIORITY_DT], [ENTRY_DATE], [LABOR_AGREEMENT], [EMPL_CTG], [EMPL_CTG_L1], [EMPL_CTG_L2], [SETID_LBR_AGRMNT], [WPP_STOP_FLAG], [LABOR_FACILITY_ID], [LBR_FAC_ENTRY_DT], [LAYOFF_EXEMPT_FLAG], [LAYOFF_EXEMPT_RSN], [VALUE_1_FRA], [VALUE_2_FRA], [VALUE_3_FRA], [VALUE_4_FRA], [VALUE_5_FRA], [GVT_SCD_RETIRE], [GVT_MAND_RET_DT], [GVT_SCD_TSP], [GVT_SCD_SEVPAY], [GVT_DT_LEI], [GVT_PAY_BASIS], [GVT_WGI_STATUS], [GVT_WGI_DUE_DATE], [GVT_INTRM_DAYS_WGI], [GVT_LOCALITY_ADJ], [GVT_WORK_SCHED], [GVT_SEVPAY_PRV_WKS], [GVT_BIWEEKLY_RT], [GVT_STEP], [GVT_RTND_PAY_PLAN], [GVT_RTND_SAL_PLAN], [GVT_RTND_GRADE], [GVT_RTND_STEP], [GVT_RTND_GVT_STEP], [GVT_RTND_GRADE_BEG], [GVT_RTND_GRADE_EXP], [GVT_TEMP_PRO_EXPIR], [GVT_TEMP_PSN_EXPIR], [GVT_DETAIL_EXPIRES], [GVT_SABBATIC_EXPIR], [GVT_TYPE_OF_APPT], [GVT_APPT_EXPIR_DT], [GVT_CAREER_CNV_DUE], [GVT_SUPV_PROB_DT], [GVT_SES_PROB_DT], [GVT_SEC_CLR_STATUS], [GVT_CLRNCE_STAT_DT], [EEO1CODE], [EEO4CODE], [EEO5CODE], [EEO6CODE], [EEO_JOB_GROUP], [JOB_FAMILY], [JOB_KNOWHOW_POINTS], [JOB_ACCNTAB_POINTS], [JOB_PROBSLV_POINTS], [JOB_POINTS_TOTAL], [JOB_KNOWHOW_PCT], [JOB_ACCNTAB_PCT], [JOB_PROBSLV_PCT], [IPEDSSCODE], [GVT_ORG_TTL_DESCR], [MANAGER_ID], [EEO4_FUNCTION], [ASOFDATE], [FROMDATE], [JOBTITLE], [JOBTITLE_ABBRV], [DEPTNAME], [DEPTNAME_ABBRV], [REHIRE_DT], [WORK_PHONE], [NID_COUNTRY], [GVT_OVERTIME_RT], [GVT_RTND_PAY_BASIS], [SEC_CLEARANCE_TYPE]) VALUES ('26110', 0, CAST('1902-01-01 00:00:00' AS DateTime), ' ', NULL, '001', 'B SAMS', ' ', ' ', ' ', ' ', ' ', ' ', 'SAMS', 'B', 'Sams', 'S', 'D', ' ', ' ', ' ', ' ', '1', 'USA', '456 Main St, ' ', ' ', ' ', 'Anytown1', ' ', ' ', ' ', ' ', ' ', ' ', 'Anytown1', 'MS', '00000', ' ', ' ', ' ', 'PR', '000000000', 'M', 'M', 'O', 'N', '1', 'N', ' ', ' ', ' ', '1', 'N', ' ', CAST(N'2017-10-09 00:00:00' AS DateTime), 'EMP', 0, CAST('2017-10-09 00:00:00' AS DateTime), CAST('2017-10-29 00:00:00' AS DateTime), 'M', CAST('2018-01-01 00:00:00' AS DateTime), 'N', 'Clerk', NULL, CAST('2018-01-01 00:00:00' AS DateTime), 0, CAST('2017-10-09 00:00:00' AS DateTime), NULL, NULL, NULL, '56478', ' ', '50000', '54000', '54300', '9129', 'A', 'BSE', CAST('2018-01-11 00:00:00' AS DateTime), 'ANL', '50000', CAST('2017-10-09 00:00:00' AS DateTime), CAST('2017-10-09 00:00:00' AS DateTime), CAST('2017-10-09 00:00:00' AS DateTime), 'N', 'R', 'F', 'X', 'N', 'TGX', '50E', 'E', 'HOL1', CAST(30.00 AS Decimal(6, 2)), 'W', 'USA', CAST(0.00 AS Decimal(6, 2)), CAST(0.000000 AS Decimal(7, 6)), ' ', CAST(1.000000 AS Decimal(7, 6)), 'N', 'TGX', 'A13', CAST('2017-10-09 00:00:00' AS DateTime), 0, NULL, ' ', 'H', CAST(00.510000 AS Decimal(18, 6)), CAST(0.670000 AS Decimal(18, 6)), CAST(2.000 AS Decimal(6, 3)), CAST(100.800 AS Decimal(18, 3)), CAST(102.067 AS Decimal(18, 3)), CAST(140.080 AS Decimal(18, 3)), CAST(17.510000 AS Decimal(18, 6)), CAST(420.800 AS Decimal(18, 3)), CAST(0.000000 AS Decimal(18, 6)), CAST(0.000 AS Decimal(4, 3)), 'USD', 'N', 'NA', 'SHARE', 'SHARE', 'SHARE', 'SHARE', ' ', ' ', ' ', ' ', 'P', 'N', ' ', ' ', NULL, NULL, ' ', ' ', ' ', ' ', ' ', 'N', ' ', NULL, 'N', ' ', ' ', ' ', ' ', ' ', ' ', NULL, NULL, NULL, NULL, NULL, ' ', ' ', NULL, 0, CAST(0.00 AS Decimal(7, 2)), ' ', 0, CAST(0.00 AS Decimal(9, 2)), ' ', ' ', ' ', ' ', 0, ' ', NULL, NULL, NULL, NULL, NULL, NULL, ' ', NULL, NULL, NULL, NULL, ' ', NULL, '5', 'N', 'N', 'N', ' ', 'CLIN', 0, 0, 0, 0, CAST(0.0 AS Decimal(4, 1)), CAST(0.0 AS Decimal(4, 1)), CAST(0.0 AS Decimal(4, 1)), 'N', ' ', ' ', ' ', CAST('2019-01-21 00:00:00' AS DateTime), CAST('2019-01-21 00:00:00' AS DateTime), 'Clerk', 'Care', 'Practice', 'FP', CAST('2017-04-10 00:00:00' AS DateTime), ' ', 'USA', CAST(0.000 AS Decimal(7, 3)), ' ', ' ')
INSERT [dbo].[PS_EMPLOYEES] ([EMPLID], [EMPL_RCD], [BIRTHDATE], [BIRTHPLACE], [DT_OF_DEATH], [COUNTRY_NM_FORMAT], [NAME], [NAME_INITIALS], [NAME_PREFIX], [NAME_SUFFIX], [NAME_ROYAL_PREFIX], [NAME_ROYAL_SUFFIX], [NAME_TITLE], [LAST_NAME_SRCH], [FIRST_NAME_SRCH], [LAST_NAME], [FIRST_NAME], [MIDDLE_NAME], [SECOND_LAST_SRCH], [SECOND_LAST_NAME], [NAME_AC], [PREF_FIRST_NAME], [LAST_NAME_PREF_NLD], [COUNTRY], [ADDRESS1], [ADDRESS2], [ADDRESS3], [ADDRESS4], [CITY], [NUM1], [NUM2], [HOUSE_TYPE], [ADDR_FIELD1], [ADDR_FIELD2], [ADDR_FIELD3], [COUNTY], [STATE], [POSTAL], [GEO_CODE], [IN_CITY_LIMIT], [HOME_PHONE], [NATIONAL_ID_TYPE], [NATIONAL_ID], [SEX], [MAR_STATUS], [HIGHEST_EDUC_LVL], [FT_STUDENT], [MILITARY_STATUS], [US_WORK_ELIGIBILTY], [MILIT_SITUATN_FRA], [DISABLED], [DISABLED_VET], [ETHNIC_GROUP], [TWO_RACES_IND_USA], [CITIZENSHIP_STATUS], [ORIG_HIRE_DT], [PER_ORG], [BENEFIT_RCD_NBR], [CMPNY_SENIORITY_DT], [SERVICE_DT], [HOME_HOST_CLASS], [LAST_INCREASE_DT], [OWN_5PERCENT_CO], [BUSINESS_TITLE], [PROBATION_DT], [EFFDT], [EFFSEQ], [HIRE_DT], [EXPECTED_RETURN_DT], [TERMINATION_DT], [LAST_DATE_WORKED], [REPORTS_TO], [SUPERVISOR_ID], [BUSINESS_UNIT], [DEPTID], [JOBCODE], [POSITION_NBR], [EMPL_STATUS], [ACTION], [ACTION_DT], [ACTION_REASON], [LOCATION], [JOB_ENTRY_DT], [DEPT_ENTRY_DT], [POSITION_ENTRY_DT], [SHIFT], [REG_TEMP], [FULL_PART_TIME], [FLSA_STATUS], [OFFICER_CD], [COMPANY], [PAYGROUP], [EMPL_TYPE], [HOLIDAY_SCHEDULE], [STD_HOURS], [STD_HRS_FREQUENCY], [REG_REGION], [PAID_HOURS], [PAID_FTE], [PAID_HRS_FREQUENCY], [FTE], [EEO_CLASS], [SAL_ADMIN_PLAN], [GRADE], [GRADE_ENTRY_DT], [STEP], [STEP_ENTRY_DT], [GL_PAY_TYPE], [COMP_FREQUENCY], [COMPRATE], [CHANGE_AMT], [CHANGE_PCT], [ANNUAL_RT], [MONTHLY_RT], [DAILY_RT], [HOURLY_RT], [ANNL_BENEF_BASE_RT], [SHIFT_RT], [SHIFT_FACTOR], [CURRENCY_CD], [DIRECTLY_TIPPED], [PAY_SYSTEM_FLG], [SETID_DEPT], [SETID_JOBCODE], [SETID_LOCATION], [SETID_SALARY], [GP_PAYGROUP], [GP_ELIG_GRP], [CUR_RT_TYPE], [GP_ASOF_DT_EXG_RT], [JOB_INDICATOR], [PAY_UNION_FEE], [UNION_CD], [BARG_UNIT], [UNION_SENIORITY_DT], [ENTRY_DATE], [LABOR_AGREEMENT], [EMPL_CTG], [EMPL_CTG_L1], [EMPL_CTG_L2], [SETID_LBR_AGRMNT], [WPP_STOP_FLAG], [LABOR_FACILITY_ID], [LBR_FAC_ENTRY_DT], [LAYOFF_EXEMPT_FLAG], [LAYOFF_EXEMPT_RSN], [VALUE_1_FRA], [VALUE_2_FRA], [VALUE_3_FRA], [VALUE_4_FRA], [VALUE_5_FRA], [GVT_SCD_RETIRE], [GVT_MAND_RET_DT], [GVT_SCD_TSP], [GVT_SCD_SEVPAY], [GVT_DT_LEI], [GVT_PAY_BASIS], [GVT_WGI_STATUS], [GVT_WGI_DUE_DATE], [GVT_INTRM_DAYS_WGI], [GVT_LOCALITY_ADJ], [GVT_WORK_SCHED], [GVT_SEVPAY_PRV_WKS], [GVT_BIWEEKLY_RT], [GVT_STEP], [GVT_RTND_PAY_PLAN], [GVT_RTND_SAL_PLAN], [GVT_RTND_GRADE], [GVT_RTND_STEP], [GVT_RTND_GVT_STEP], [GVT_RTND_GRADE_BEG], [GVT_RTND_GRADE_EXP], [GVT_TEMP_PRO_EXPIR], [GVT_TEMP_PSN_EXPIR], [GVT_DETAIL_EXPIRES], [GVT_SABBATIC_EXPIR], [GVT_TYPE_OF_APPT], [GVT_APPT_EXPIR_DT], [GVT_CAREER_CNV_DUE], [GVT_SUPV_PROB_DT], [GVT_SES_PROB_DT], [GVT_SEC_CLR_STATUS], [GVT_CLRNCE_STAT_DT], [EEO1CODE], [EEO4CODE], [EEO5CODE], [EEO6CODE], [EEO_JOB_GROUP], [JOB_FAMILY], [JOB_KNOWHOW_POINTS], [JOB_ACCNTAB_POINTS], [JOB_PROBSLV_POINTS], [JOB_POINTS_TOTAL], [JOB_KNOWHOW_PCT], [JOB_ACCNTAB_PCT], [JOB_PROBSLV_PCT], [IPEDSSCODE], [GVT_ORG_TTL_DESCR], [MANAGER_ID], [EEO4_FUNCTION], [ASOFDATE], [FROMDATE], [JOBTITLE], [JOBTITLE_ABBRV], [DEPTNAME], [DEPTNAME_ABBRV], [REHIRE_DT], [WORK_PHONE], [NID_COUNTRY], [GVT_OVERTIME_RT], [GVT_RTND_PAY_BASIS], [SEC_CLEARANCE_TYPE]) VALUES ('097432', 0, CAST('1903-01-01 00:00:00' AS DateTime), ' ', NULL, '001', 'H ADAM', ' ', ' ', ' ', ' ', ' ', ' ', 'ADAM', 'H', 'Adam', 'H', 'A', ' ', ' ', ' ', ' ', '1', 'USA', '789 Main St, ' ', ' ', ' ', 'Anytown1', ' ', ' ', ' ', ' ', ' ', ' ', 'Anytown1', 'MS', '00000', ' ', ' ', ' ', 'PR', '000000000', 'M', 'M', 'O', 'N', '1', 'N', ' ', ' ', ' ', '1', 'N', ' ', CAST(N'2017-10-09 00:00:00' AS DateTime), 'EMP', 0, CAST('2017-10-09 00:00:00' AS DateTime), CAST('2017-10-29 00:00:00' AS DateTime), 'M', CAST('2018-01-01 00:00:00' AS DateTime), 'N', 'Secretary', NULL, CAST('2018-01-01 00:00:00' AS DateTime), 0, CAST('2017-10-09 00:00:00' AS DateTime), NULL, NULL, NULL, '56478', ' ', '50000', '54000', '54300', '9129', 'A', 'BSE', CAST('2018-01-11 00:00:00' AS DateTime), 'ANL', '50000', CAST('2017-10-09 00:00:00' AS DateTime), CAST('2017-10-09 00:00:00' AS DateTime), CAST('2017-10-09 00:00:00' AS DateTime), 'N', 'R', 'F', 'X', 'N', 'TGX', '50E', 'E', 'HOL1', CAST(30.00 AS Decimal(6, 2)), 'W', 'USA', CAST(0.00 AS Decimal(6, 2)), CAST(0.000000 AS Decimal(7, 6)), ' ', CAST(1.000000 AS Decimal(7, 6)), 'N', 'TGX', 'A13', CAST('2017-10-09 00:00:00' AS DateTime), 0, NULL, ' ', 'H', CAST(00.510000 AS Decimal(18, 6)), CAST(0.670000 AS Decimal(18, 6)), CAST(2.000 AS Decimal(6, 3)), CAST(100.800 AS Decimal(18, 3)), CAST(102.067 AS Decimal(18, 3)), CAST(140.080 AS Decimal(18, 3)), CAST(17.510000 AS Decimal(18, 6)), CAST(420.800 AS Decimal(18, 3)), CAST(0.000000 AS Decimal(18, 6)), CAST(0.000 AS Decimal(4, 3)), 'USD', 'N', 'NA', 'SHARE', 'SHARE', 'SHARE', 'SHARE', ' ', ' ', ' ', ' ', 'P', 'N', ' ', ' ', NULL, NULL, ' ', ' ', ' ', ' ', ' ', 'N', ' ', NULL, 'N', ' ', ' ', ' ', ' ', ' ', ' ', NULL, NULL, NULL, NULL, NULL, ' ', ' ', NULL, 0, CAST(0.00 AS Decimal(7, 2)), ' ', 0, CAST(0.00 AS Decimal(9, 2)), ' ', ' ', ' ', ' ', 0, ' ', NULL, NULL, NULL, NULL, NULL, NULL, ' ', NULL, NULL, NULL, NULL, ' ', NULL, '5', 'N', 'N', 'N', ' ', 'CLIN', 0, 0, 0, 0, CAST(0.0 AS Decimal(4, 1)), CAST(0.0 AS Decimal(4, 1)), CAST(0.0 AS Decimal(4, 1)), 'N', ' ', ' ', ' ', CAST('2019-01-21 00:00:00' AS DateTime), CAST('2019-01-21 00:00:00' AS DateTime), 'Secretary', 'Care', 'Practice', 'FP', CAST('2017-04-10 00:00:00' AS DateTime), ' ', 'USA', CAST(0.000 AS Decimal(7, 3)), ' ', ' ')
INSERT [dbo].[PS_EMPLOYEES] ([EMPLID], [EMPL_RCD], [BIRTHDATE], [BIRTHPLACE], [DT_OF_DEATH], [COUNTRY_NM_FORMAT], [NAME], [NAME_INITIALS], [NAME_PREFIX], [NAME_SUFFIX], [NAME_ROYAL_PREFIX], [NAME_ROYAL_SUFFIX], [NAME_TITLE], [LAST_NAME_SRCH], [FIRST_NAME_SRCH], [LAST_NAME], [FIRST_NAME], [MIDDLE_NAME], [SECOND_LAST_SRCH], [SECOND_LAST_NAME], [NAME_AC], [PREF_FIRST_NAME], [LAST_NAME_PREF_NLD], [COUNTRY], [ADDRESS1], [ADDRESS2], [ADDRESS3], [ADDRESS4], [CITY], [NUM1], [NUM2], [HOUSE_TYPE], [ADDR_FIELD1], [ADDR_FIELD2], [ADDR_FIELD3], [COUNTY], [STATE], [POSTAL], [GEO_CODE], [IN_CITY_LIMIT], [HOME_PHONE], [NATIONAL_ID_TYPE], [NATIONAL_ID], [SEX], [MAR_STATUS], [HIGHEST_EDUC_LVL], [FT_STUDENT], [MILITARY_STATUS], [US_WORK_ELIGIBILTY], [MILIT_SITUATN_FRA], [DISABLED], [DISABLED_VET], [ETHNIC_GROUP], [TWO_RACES_IND_USA], [CITIZENSHIP_STATUS], [ORIG_HIRE_DT], [PER_ORG], [BENEFIT_RCD_NBR], [CMPNY_SENIORITY_DT], [SERVICE_DT], [HOME_HOST_CLASS], [LAST_INCREASE_DT], [OWN_5PERCENT_CO], [BUSINESS_TITLE], [PROBATION_DT], [EFFDT], [EFFSEQ], [HIRE_DT], [EXPECTED_RETURN_DT], [TERMINATION_DT], [LAST_DATE_WORKED], [REPORTS_TO], [SUPERVISOR_ID], [BUSINESS_UNIT], [DEPTID], [JOBCODE], [POSITION_NBR], [EMPL_STATUS], [ACTION], [ACTION_DT], [ACTION_REASON], [LOCATION], [JOB_ENTRY_DT], [DEPT_ENTRY_DT], [POSITION_ENTRY_DT], [SHIFT], [REG_TEMP], [FULL_PART_TIME], [FLSA_STATUS], [OFFICER_CD], [COMPANY], [PAYGROUP], [EMPL_TYPE], [HOLIDAY_SCHEDULE], [STD_HOURS], [STD_HRS_FREQUENCY], [REG_REGION], [PAID_HOURS], [PAID_FTE], [PAID_HRS_FREQUENCY], [FTE], [EEO_CLASS], [SAL_ADMIN_PLAN], [GRADE], [GRADE_ENTRY_DT], [STEP], [STEP_ENTRY_DT], [GL_PAY_TYPE], [COMP_FREQUENCY], [COMPRATE], [CHANGE_AMT], [CHANGE_PCT], [ANNUAL_RT], [MONTHLY_RT], [DAILY_RT], [HOURLY_RT], [ANNL_BENEF_BASE_RT], [SHIFT_RT], [SHIFT_FACTOR], [CURRENCY_CD], [DIRECTLY_TIPPED], [PAY_SYSTEM_FLG], [SETID_DEPT], [SETID_JOBCODE], [SETID_LOCATION], [SETID_SALARY], [GP_PAYGROUP], [GP_ELIG_GRP], [CUR_RT_TYPE], [GP_ASOF_DT_EXG_RT], [JOB_INDICATOR], [PAY_UNION_FEE], [UNION_CD], [BARG_UNIT], [UNION_SENIORITY_DT], [ENTRY_DATE], [LABOR_AGREEMENT], [EMPL_CTG], [EMPL_CTG_L1], [EMPL_CTG_L2], [SETID_LBR_AGRMNT], [WPP_STOP_FLAG], [LABOR_FACILITY_ID], [LBR_FAC_ENTRY_DT], [LAYOFF_EXEMPT_FLAG], [LAYOFF_EXEMPT_RSN], [VALUE_1_FRA], [VALUE_2_FRA], [VALUE_3_FRA], [VALUE_4_FRA], [VALUE_5_FRA], [GVT_SCD_RETIRE], [GVT_MAND_RET_DT], [GVT_SCD_TSP], [GVT_SCD_SEVPAY], [GVT_DT_LEI], [GVT_PAY_BASIS], [GVT_WGI_STATUS], [GVT_WGI_DUE_DATE], [GVT_INTRM_DAYS_WGI], [GVT_LOCALITY_ADJ], [GVT_WORK_SCHED], [GVT_SEVPAY_PRV_WKS], [GVT_BIWEEKLY_RT], [GVT_STEP], [GVT_RTND_PAY_PLAN], [GVT_RTND_SAL_PLAN], [GVT_RTND_GRADE], [GVT_RTND_STEP], [GVT_RTND_GVT_STEP], [GVT_RTND_GRADE_BEG], [GVT_RTND_GRADE_EXP], [GVT_TEMP_PRO_EXPIR], [GVT_TEMP_PSN_EXPIR], [GVT_DETAIL_EXPIRES], [GVT_SABBATIC_EXPIR], [GVT_TYPE_OF_APPT], [GVT_APPT_EXPIR_DT], [GVT_CAREER_CNV_DUE], [GVT_SUPV_PROB_DT], [GVT_SES_PROB_DT], [GVT_SEC_CLR_STATUS], [GVT_CLRNCE_STAT_DT], [EEO1CODE], [EEO4CODE], [EEO5CODE], [EEO6CODE], [EEO_JOB_GROUP], [JOB_FAMILY], [JOB_KNOWHOW_POINTS], [JOB_ACCNTAB_POINTS], [JOB_PROBSLV_POINTS], [JOB_POINTS_TOTAL], [JOB_KNOWHOW_PCT], [JOB_ACCNTAB_PCT], [JOB_PROBSLV_PCT], [IPEDSSCODE], [GVT_ORG_TTL_DESCR], [MANAGER_ID], [EEO4_FUNCTION], [ASOFDATE], [FROMDATE], [JOBTITLE], [JOBTITLE_ABBRV], [DEPTNAME], [DEPTNAME_ABBRV], [REHIRE_DT], [WORK_PHONE], [NID_COUNTRY], [GVT_OVERTIME_RT], [GVT_RTND_PAY_BASIS], [SEC_CLEARANCE_TYPE]) VALUES ('1045632', 0, CAST('1904-01-01 00:00:00' AS DateTime), ' ', NULL, '001', 'O OLO', ' ', ' ', ' ', ' ', ' ', ' ', 'OLO', 'O', 'Olo', 'V', 'A', ' ', ' ', ' ', ' ', '1', 'USA', '111 Main St, ' ', ' ', ' ', 'Anytown1', ' ', ' ', ' ', ' ', ' ', ' ', 'Anytown1', 'MS', '00000', ' ', ' ', ' ', 'PR', '000000000', 'M', 'M', 'O', 'N', '1', 'N', ' ', ' ', ' ', '1', 'N', ' ', CAST(N'2017-10-09 00:00:00' AS DateTime), 'EMP', 0, CAST('2017-10-09 00:00:00' AS DateTime), CAST('2017-10-29 00:00:00' AS DateTime), 'M', CAST('2018-01-01 00:00:00' AS DateTime), 'N', 'Tester', NULL, CAST('2018-01-01 00:00:00' AS DateTime), 0, CAST('2017-10-09 00:00:00' AS DateTime), NULL, NULL, NULL, '56478', ' ', '50000', '54000', '54300', '9129', 'A', 'BSE', CAST('2018-01-11 00:00:00' AS DateTime), 'ANL', '50000', CAST('2017-10-09 00:00:00' AS DateTime), CAST('2017-10-09 00:00:00' AS DateTime), CAST('2017-10-09 00:00:00' AS DateTime), 'N', 'R', 'F', 'X', 'N', 'TGX', '50E', 'E', 'HOL1', CAST(30.00 AS Decimal(6, 2)), 'W', 'USA', CAST(0.00 AS Decimal(6, 2)), CAST(0.000000 AS Decimal(7, 6)), ' ', CAST(1.000000 AS Decimal(7, 6)), 'N', 'ADG', 'A13', CAST('2017-10-09 00:00:00' AS DateTime), 0, NULL, ' ', 'H', CAST(00.510000 AS Decimal(18, 6)), CAST(0.670000 AS Decimal(18, 6)), CAST(2.000 AS Decimal(6, 3)), CAST(104.600 AS Decimal(18, 3)), CAST(102.067 AS Decimal(18, 3)), CAST(140.080 AS Decimal(18, 3)), CAST(17.510000 AS Decimal(18, 6)), CAST(420.800 AS Decimal(18, 3)), CAST(0.000000 AS Decimal(18, 6)), CAST(0.000 AS Decimal(4, 3)), 'USD', 'N', 'NA', 'SHARE', 'SHARE', 'SHARE', 'SHARE', ' ', ' ', ' ', ' ', 'P', 'N', ' ', ' ', NULL, NULL, ' ', ' ', ' ', ' ', ' ', 'N', ' ', NULL, 'N', ' ', ' ', ' ', ' ', ' ', ' ', NULL, NULL, NULL, NULL, NULL, ' ', ' ', NULL, 0, CAST(0.00 AS Decimal(7, 2)), ' ', 0, CAST(0.00 AS Decimal(9, 2)), ' ', ' ', ' ', ' ', 0, ' ', NULL, NULL, NULL, NULL, NULL, NULL, ' ', NULL, NULL, NULL, NULL, ' ', NULL, '5', 'N', 'N', 'N', ' ', 'CLIN', 0, 0, 0, 0, CAST(0.0 AS Decimal(4, 1)), CAST(0.0 AS Decimal(4, 1)), CAST(0.0 AS Decimal(4, 1)), 'N', ' ', ' ', ' ', CAST('2019-01-21 00:00:00' AS DateTime), CAST('2019-01-21 00:00:00' AS DateTime), 'Tester', 'Care', 'Practice', 'FP', CAST('2017-04-10 00:00:00' AS DateTime), ' ', 'USA', CAST(0.000 AS Decimal(7, 3)), ' ', ' ')
INSERT [dbo].[PS_EMPLOYEES] ([EMPLID], [EMPL_RCD], [BIRTHDATE], [BIRTHPLACE], [DT_OF_DEATH], [COUNTRY_NM_FORMAT], [NAME], [NAME_INITIALS], [NAME_PREFIX], [NAME_SUFFIX], [NAME_ROYAL_PREFIX], [NAME_ROYAL_SUFFIX], [NAME_TITLE], [LAST_NAME_SRCH], [FIRST_NAME_SRCH], [LAST_NAME], [FIRST_NAME], [MIDDLE_NAME], [SECOND_LAST_SRCH], [SECOND_LAST_NAME], [NAME_AC], [PREF_FIRST_NAME], [LAST_NAME_PREF_NLD], [COUNTRY], [ADDRESS1], [ADDRESS2], [ADDRESS3], [ADDRESS4], [CITY], [NUM1], [NUM2], [HOUSE_TYPE], [ADDR_FIELD1], [ADDR_FIELD2], [ADDR_FIELD3], [COUNTY], [STATE], [POSTAL], [GEO_CODE], [IN_CITY_LIMIT], [HOME_PHONE], [NATIONAL_ID_TYPE], [NATIONAL_ID], [SEX], [MAR_STATUS], [HIGHEST_EDUC_LVL], [FT_STUDENT], [MILITARY_STATUS], [US_WORK_ELIGIBILTY], [MILIT_SITUATN_FRA], [DISABLED], [DISABLED_VET], [ETHNIC_GROUP], [TWO_RACES_IND_USA], [CITIZENSHIP_STATUS], [ORIG_HIRE_DT], [PER_ORG], [BENEFIT_RCD_NBR], [CMPNY_SENIORITY_DT], [SERVICE_DT], [HOME_HOST_CLASS], [LAST_INCREASE_DT], [OWN_5PERCENT_CO], [BUSINESS_TITLE], [PROBATION_DT], [EFFDT], [EFFSEQ], [HIRE_DT], [EXPECTED_RETURN_DT], [TERMINATION_DT], [LAST_DATE_WORKED], [REPORTS_TO], [SUPERVISOR_ID], [BUSINESS_UNIT], [DEPTID], [JOBCODE], [POSITION_NBR], [EMPL_STATUS], [ACTION], [ACTION_DT], [ACTION_REASON], [LOCATION], [JOB_ENTRY_DT], [DEPT_ENTRY_DT], [POSITION_ENTRY_DT], [SHIFT], [REG_TEMP], [FULL_PART_TIME], [FLSA_STATUS], [OFFICER_CD], [COMPANY], [PAYGROUP], [EMPL_TYPE], [HOLIDAY_SCHEDULE], [STD_HOURS], [STD_HRS_FREQUENCY], [REG_REGION], [PAID_HOURS], [PAID_FTE], [PAID_HRS_FREQUENCY], [FTE], [EEO_CLASS], [SAL_ADMIN_PLAN], [GRADE], [GRADE_ENTRY_DT], [STEP], [STEP_ENTRY_DT], [GL_PAY_TYPE], [COMP_FREQUENCY], [COMPRATE], [CHANGE_AMT], [CHANGE_PCT], [ANNUAL_RT], [MONTHLY_RT], [DAILY_RT], [HOURLY_RT], [ANNL_BENEF_BASE_RT], [SHIFT_RT], [SHIFT_FACTOR], [CURRENCY_CD], [DIRECTLY_TIPPED], [PAY_SYSTEM_FLG], [SETID_DEPT], [SETID_JOBCODE], [SETID_LOCATION], [SETID_SALARY], [GP_PAYGROUP], [GP_ELIG_GRP], [CUR_RT_TYPE], [GP_ASOF_DT_EXG_RT], [JOB_INDICATOR], [PAY_UNION_FEE], [UNION_CD], [BARG_UNIT], [UNION_SENIORITY_DT], [ENTRY_DATE], [LABOR_AGREEMENT], [EMPL_CTG], [EMPL_CTG_L1], [EMPL_CTG_L2], [SETID_LBR_AGRMNT], [WPP_STOP_FLAG], [LABOR_FACILITY_ID], [LBR_FAC_ENTRY_DT], [LAYOFF_EXEMPT_FLAG], [LAYOFF_EXEMPT_RSN], [VALUE_1_FRA], [VALUE_2_FRA], [VALUE_3_FRA], [VALUE_4_FRA], [VALUE_5_FRA], [GVT_SCD_RETIRE], [GVT_MAND_RET_DT], [GVT_SCD_TSP], [GVT_SCD_SEVPAY], [GVT_DT_LEI], [GVT_PAY_BASIS], [GVT_WGI_STATUS], [GVT_WGI_DUE_DATE], [GVT_INTRM_DAYS_WGI], [GVT_LOCALITY_ADJ], [GVT_WORK_SCHED], [GVT_SEVPAY_PRV_WKS], [GVT_BIWEEKLY_RT], [GVT_STEP], [GVT_RTND_PAY_PLAN], [GVT_RTND_SAL_PLAN], [GVT_RTND_GRADE], [GVT_RTND_STEP], [GVT_RTND_GVT_STEP], [GVT_RTND_GRADE_BEG], [GVT_RTND_GRADE_EXP], [GVT_TEMP_PRO_EXPIR], [GVT_TEMP_PSN_EXPIR], [GVT_DETAIL_EXPIRES], [GVT_SABBATIC_EXPIR], [GVT_TYPE_OF_APPT], [GVT_APPT_EXPIR_DT], [GVT_CAREER_CNV_DUE], [GVT_SUPV_PROB_DT], [GVT_SES_PROB_DT], [GVT_SEC_CLR_STATUS], [GVT_CLRNCE_STAT_DT], [EEO1CODE], [EEO4CODE], [EEO5CODE], [EEO6CODE], [EEO_JOB_GROUP], [JOB_FAMILY], [JOB_KNOWHOW_POINTS], [JOB_ACCNTAB_POINTS], [JOB_PROBSLV_POINTS], [JOB_POINTS_TOTAL], [JOB_KNOWHOW_PCT], [JOB_ACCNTAB_PCT], [JOB_PROBSLV_PCT], [IPEDSSCODE], [GVT_ORG_TTL_DESCR], [MANAGER_ID], [EEO4_FUNCTION], [ASOFDATE], [FROMDATE], [JOBTITLE], [JOBTITLE_ABBRV], [DEPTNAME], [DEPTNAME_ABBRV], [REHIRE_DT], [WORK_PHONE], [NID_COUNTRY], [GVT_OVERTIME_RT], [GVT_RTND_PAY_BASIS], [SEC_CLEARANCE_TYPE]) VALUES ('100765', 0, CAST('1905-01-01 00:00:00' AS DateTime), ' ', NULL, '001', 'K MILO', ' ', ' ', ' ', ' ', ' ', ' ', 'MILO', 'K', 'Milo', 'V', 'A', ' ', ' ', ' ', ' ', '1', 'USA', '222 Main St, ' ', ' ', ' ', 'Anytown1', ' ', ' ', ' ', ' ', ' ', ' ', 'Anytown1', 'MS', '00000', ' ', ' ', ' ', 'PR', '000000000', 'M', 'M', 'O', 'N', '1', 'N', ' ', ' ', ' ', '1', 'N', ' ', CAST(N'2017-10-09 00:00:00' AS DateTime), 'EMP', 0, CAST('2017-10-09 00:00:00' AS DateTime), CAST('2017-10-29 00:00:00' AS DateTime), 'M', CAST('2018-01-01 00:00:00' AS DateTime), 'N', 'Leader', NULL, CAST('2018-01-01 00:00:00' AS DateTime), 0, CAST('2017-10-09 00:00:00' AS DateTime), NULL, NULL, NULL, '56478', ' ', '50000', '54000', '54300', '9129', 'A', 'BSE', CAST('2018-01-11 00:00:00' AS DateTime), 'ANL', '50000', CAST('2017-10-09 00:00:00' AS DateTime), CAST('2017-10-09 00:00:00' AS DateTime), CAST('2017-10-09 00:00:00' AS DateTime), 'N', 'R', 'F', 'X', 'N', 'TGX', '50E', 'E', 'HOL1', CAST(30.00 AS Decimal(6, 2)), 'W', 'USA', CAST(0.00 AS Decimal(6, 2)), CAST(0.000000 AS Decimal(7, 6)), ' ', CAST(1.000000 AS Decimal(7, 6)), 'N', 'ADG', 'A13', CAST('2017-10-09 00:00:00' AS DateTime), 0, NULL, ' ', 'H', CAST(00.510000 AS Decimal(18, 6)), CAST(0.670000 AS Decimal(18, 6)), CAST(2.000 AS Decimal(6, 3)), CAST(104.600 AS Decimal(18, 3)), CAST(102.067 AS Decimal(18, 3)), CAST(140.080 AS Decimal(18, 3)), CAST(17.510000 AS Decimal(18, 6)), CAST(420.800 AS Decimal(18, 3)), CAST(0.000000 AS Decimal(18, 6)), CAST(0.000 AS Decimal(4, 3)), 'USD', 'N', 'NA', 'SHARE', 'SHARE', 'SHARE', 'SHARE', ' ', ' ', ' ', ' ', 'P', 'N', ' ', ' ', NULL, NULL, ' ', ' ', ' ', ' ', ' ', 'N', ' ', NULL, 'N', ' ', ' ', ' ', ' ', ' ', ' ', NULL, NULL, NULL, NULL, NULL, ' ', ' ', NULL, 0, CAST(0.00 AS Decimal(7, 2)), ' ', 0, CAST(0.00 AS Decimal(9, 2)), ' ', ' ', ' ', ' ', 0, ' ', NULL, NULL, NULL, NULL, NULL, NULL, ' ', NULL, NULL, NULL, NULL, ' ', NULL, '5', 'N', 'N', 'N', ' ', 'CLIN', 0, 0, 0, 0, CAST(0.0 AS Decimal(4, 1)), CAST(0.0 AS Decimal(4, 1)), CAST(0.0 AS Decimal(4, 1)), 'N', ' ', ' ', ' ', CAST('2019-01-21 00:00:00' AS DateTime), CAST('2019-01-21 00:00:00' AS DateTime), 'Leader', 'Care', 'Practice', 'FP', CAST('2017-04-10 00:00:00' AS DateTime), ' ', 'USA', CAST(0.000 AS Decimal(7, 3)), ' ', ' ')PS_GHS_DIR_DEP_VND
CREATE TABLE [dbo].[PS_GHS_DIR_DEP_VND]
(
[SETID] VARCHAR(5) COLLATE Latin1_General_BIN NOT NULL
, [VENDOR_ID] VARCHAR(10) COLLATE Latin1_General_BIN NOT NULL
, [LAST_NAME] VARCHAR(30) COLLATE Latin1_General_BIN NOT NULL
, [FIRST_NAME] VARCHAR(30) COLLATE Latin1_General_BIN NOT NULL
, [EMPLID] VARCHAR(11) COLLATE Latin1_General_BIN NOT NULL
)INSERT [dbo].[PS_GHS_DIR_DEP_VND] ([SETID], [VENDOR_ID], [LAST_NAME], [FIRST_NAME], [EMPLID]) VALUES ('SHARE', '12345A', 'SMITH', 'J', '21345')
INSERT [dbo].[PS_GHS_DIR_DEP_VND] ([SETID], [VENDOR_ID], [LAST_NAME], [FIRST_NAME], [EMPLID]) VALUES ('SHARE', '1272B', 'SAMS', 'B', '26110')
INSERT [dbo].[PS_GHS_DIR_DEP_VND] ([SETID], [VENDOR_ID], [LAST_NAME], [FIRST_NAME], [EMPLID]) VALUES ('SHARE', '3471B', 'ADAM', 'H', '097432')
INSERT [dbo].[PS_GHS_DIR_DEP_VND] ([SETID], [VENDOR_ID], [LAST_NAME], [FIRST_NAME], [EMPLID]) VALUES ('SHARE', '3870A', 'OLO', 'O', '1045632')
INSERT [dbo].[PS_GHS_DIR_DEP_VND] ([SETID], [VENDOR_ID], [LAST_NAME], [FIRST_NAME], [EMPLID]) VALUES ('SHARE', '1272B', 'MILO', 'K', '100765') - kdrymer - Thursday, March 21, 2019 11:52 AM
You need to learn how to identify salient data. When I say sample data, it means not only that we don't need all of the rows, but we also don't need all of the columns in all of the tables. We just need enough information to create the situation you are trying to solve. Here's what you should have posted.
CREATE TABLE #Sample_Data
(
EMPLID VARCHAR(11) COLLATE Latin1_General_BIN NOT NULL
, VENDOR_ID VARCHAR(10) COLLATE Latin1_General_BIN NOT NULL
, LAST_NAME VARCHAR(30) COLLATE Latin1_General_BIN NOT NULL
, FIRST_NAME VARCHAR(30) COLLATE Latin1_General_BIN NOT NULL
, BANK_CD VARCHAR(11) COLLATE Latin1_General_BIN NOT NULL
, ACCOUNT_NUM VARCHAR(17) COLLATE Latin1_General_BIN NOT NULL
, ACCOUNT_TYPE VARCHAR(1) COLLATE Latin1_General_BIN NOT NULL
, [PRIORITY] SMALLINT NOT NULL
, LAST_UPDATE_DATE DATETIME NULL
, EFFDT DATETIME NOT NULL
)INSERT #Sample_Data(EMPLID, VENDOR_ID, FIRST_NAME, LAST_NAME, BANK_CD, ACCOUNT_NUM, ACCOUNT_TYPE, [PRIORITY], LAST_UPDATE_DATE, EFFDT)
VALUES
('21345','12345A','J','Smith','0224547','591678','C',999,'2017-11-08','2017-10-17')
, ('21345','12345A','J','Smith','0224547','59167824','C',999,'2019-02-28','2019-02-28')
, ('26110','1272B','S','Sams','8208302','822328','C',999,'2019-02-08','2019-02-08')
, ('26110','1272B','S','Sams','8208302','822328','C',315,'2014-03-05','2012-07-30')
, ('26110','1272B','S','Sams','3258678','1032396072','C',325,'2012-04-04','2012-03-26')
, ('097432','3471B','H','Adam','09146115','13404891','C',999,'2016-06-10','2016-06-10')
, ('097432','3471B','H','Adam','09146115','13404891','C',999,'2016-06-10','2014-11-05')
, ('1045632','3870A','V','Olo','2345794','179410860','C',999,'2018-08-16','2018-08-16')
, ('1045632','3870A','V','Olo','2345794','179410860','C',310,'2011-02-25','2011-02-25')
, ('1045632','3870A','V','Olo','2345794','209463','S',310,'2006-03-31','2006-03-17')
, ('100765','1272B','V','Milo',' 2358414','040925','S',310,'2014-03-05','2011-04-27')
, ('100765','1272B','V','Milo',' 2358414','040925','S',300,'2014-03-05','2001-08-23')SELECT *
FROM #Sample_DataJ. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply