T-Sql Scripting Help!

• Daniel.Bayliss

  Old Hand

  Points: 303

  More actions

  January 30, 2017 at 1:40 am

  #321212

  Hi all,

  I’m having a little trouble with coming up with a good way to amend an audit table (see enclosed spread sheet).

  Could someone provide me with a way of scripting the required table amendment? I need the script to be dynamic to account for multiple amendments as an employee could change team every month.

  Thanks in advance

• Thom A

  SSC Guru

  Points: 99143

  More actions

  January 30, 2017 at 3:18 am

  #1925555

  Welcome to SSC.

  Posting your data in a Excel probably isn't going to get you as many responses as you'd like, the best way is to always provide your DDL and DLM (have a look at the link in my signature). This means that we don't have to make any assumptions about your data (for example the datatypes). For example, at first I hadn't noticed that your dates included times as you were displaying them 9only the fact that you called the column date_and_time did I then by clicking on a cell.

  Unfortunately, as you're using SQL Server 2008 you don't have access to LEAD/LAg, but i believe this works:
  CREATE TABLE #Employee (Date_and_time DATETIME,
        Table_name VARCHAR(50),
        Field_name VARCHAR(50),
        Personnel_number INT,
        Old_value VARCHAR(20),
        New_value VARCHAR(20));
GO

  INSERT INTO #Employee
VALUES ('20120629 16:00:40.000','Maint_Employee_Details','Cost_centre','1064','2220','2220'),
   ('20121105 10:36:11.000','Maint_Employee_Details','Cost_centre','1064','2220','3333'),
   ('20120629 16:00:40.000','Maint_Employee_Details','Department','1064','H','H'),
   ('20121105 10:36:11.000','Maint_Employee_Details','Department','1064','H','HR'),
   ('20120629 16:00:40.000','Personnel_Details','Manager','1064','284','284'),
   ('20120807 15:44:41.000','Personnel_Details','Manager','1064','284','1074'),
   ('20121105 10:36:11.000','Personnel_Details','Manager','1064','1074','666'),
   ('20120629 16:00:40.000','Maint_Employee_Details','Post_number','1064','TST007T','TST007T'),
   ('20121105 10:36:11.000','Maint_Employee_Details','Post_number','1064','TST007T','FARMTST'),
   ('20120626 12:36:18.000','Personnel_Details','Section','1064','TSu','TSu');
GO

  SELECT *
FROM #Employee;

  SELECT E.Personnel_number,
   E.Date_and_time AS DateFrom,
   E2.Date_and_time AS DateTo,
   E.Field_name,
   E.new_value AS [Value]
FROM #Employee E
  OUTER APPLY (SELECT TOP 1 *
      FROM #Employee oa
      WHERE oa.Personnel_number = E.Personnel_number
       AND oa.Table_name = E.Table_name
       AND oa.Field_name = E.Field_name
       AND oa.Date_and_time > E.Date_and_time
      ORDER BY oa.Date_and_time ASC) E2;

  GO
DROP TABLE #Employee;

  To those who look at my insert and cringe, yes I know I put quotations around my integers. I was lazy as I used the OP's Excel spreadsheet to create the statement and just wrapped everything with quotes.
  

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• Daniel.Bayliss

  Old Hand

  Points: 303

  More actions

  January 30, 2017 at 3:37 am

  #1925557

  Thom A - Monday, January 30, 2017 3:18 AM

  Welcome to SSC.

  Posting your data in a Excel probably isn't going to get you as many responses as you'd like, the best way is to always provide your DDL and DLM (have a look at the link in my signature). This means that we don't have to make any assumptions about your data (for example the datatypes). For example, at first I hadn't noticed that your dates included times as you were displaying them 9only the fact that you called the column date_and_time did I then by clicking on a cell.

  Unfortunately, as you're using SQL Server 2008 you don't have access to LEAD/LAg, but i believe this works:
  CREATE TABLE #Employee (Date_and_time DATETIME,
        Table_name VARCHAR(50),
        Field_name VARCHAR(50),
        Personnel_number INT,
        Old_value VARCHAR(20),
        New_value VARCHAR(20));
GO

  INSERT INTO #Employee
VALUES ('20120629 16:00:40.000','Maint_Employee_Details','Cost_centre','1064','2220','2220'),
   ('20121105 10:36:11.000','Maint_Employee_Details','Cost_centre','1064','2220','3333'),
   ('20120629 16:00:40.000','Maint_Employee_Details','Department','1064','H','H'),
   ('20121105 10:36:11.000','Maint_Employee_Details','Department','1064','H','HR'),
   ('20120629 16:00:40.000','Personnel_Details','Manager','1064','284','284'),
   ('20120807 15:44:41.000','Personnel_Details','Manager','1064','284','1074'),
   ('20121105 10:36:11.000','Personnel_Details','Manager','1064','1074','666'),
   ('20120629 16:00:40.000','Maint_Employee_Details','Post_number','1064','TST007T','TST007T'),
   ('20121105 10:36:11.000','Maint_Employee_Details','Post_number','1064','TST007T','FARMTST'),
   ('20120626 12:36:18.000','Personnel_Details','Section','1064','TSu','TSu');
GO

  SELECT *
FROM #Employee;

  SELECT E.Personnel_number,
   E.Date_and_time AS DateFrom,
   E2.Date_and_time AS DateTo,
   E.Field_name,
   E.new_value AS [Value]
FROM #Employee E
  OUTER APPLY (SELECT TOP 1 *
      FROM #Employee oa
      WHERE oa.Personnel_number = E.Personnel_number
       AND oa.Table_name = E.Table_name
       AND oa.Field_name = E.Field_name
       AND oa.Date_and_time > E.Date_and_time
      ORDER BY oa.Date_and_time ASC) E2;

  GO
DROP TABLE #Employee;

  To those who look at my insert and cringe, yes I know I put quotations around my integers. I was lazy as I used the OP's Excel spreadsheet to create the statement and just wrapped everything with quotes.
  

  Thank you very much for your help.
  I will remember to add the SQL Code in the future instead of inserting an Excel file, thanks for the advice.