building Snapshot table from audit trail in ms sql

Question

I'm faced with the same problem related here Building a snapshot table from audit records. The code bellow solve partially my problem

    Select * into #temp from (
SELECT Audit.PrimaryKeyValue as ID,Audit.FieldName,OldValue FROM audit left       JOIN (
SELECT Audit.FieldName,Audit.PrimaryKeyValue, MAX(UpdateDate) AS dateadded FROM audit GROUP BY FieldName,PrimaryKeyValue  
) maxtimestamp ON audit.FieldName = maxtimestamp.FieldName AND audit.updateDate = maxtimestamp.dateadded
where PrimaryKeyField='Id' and cast(UpdateDate as date)<=@Data) src
pivot(
max(src.OldValue)
for FieldName in (Centrala,ID_Grup,Pi, Ci, Pmt, Pneta, Rpp, Pd, UD, Suport1, Suport2, Suport3, Stare, 
Motiv, Observatii, Comentarii, Un, Data_ADD, Modified_Date, Scada, Fuel_base)
) piv;

How to obtain a snapshot of a table based on records from audit trail table at a given moment. By copying actual table in a temp one and by updating values from it based on content of audit is a solution? My English is poor! Thanks!!!

The structure of the primary table is:

[ID] [int] IDENTITY(1,1) NOT NULL,
[Centrala] [int] NOT NULL,
[ID_grup] [nvarchar](50) NULL,
[Pi] [float] NULL,
[Ci] [float] NULL,
[Pmt] [float] NULL,
[Pneta] [float] NULL,
[Rpp] [float] NULL,
[Pd] [float] NULL,
[UD] [nvarchar](50) NULL,
[Suport1] [nvarchar](255) NULL,
[Suport2] [nvarchar](255) NULL,
[Suport3] [nvarchar](255) NULL,
[Stare] [int] NULL,
[Motiv] [nvarchar](max) NULL,
[Observatii] [nvarchar](max) NULL,
[Comentarii] [nvarchar](max) NULL,
[Un] [varchar](10) NULL,
[Data_ADD] [date] NULL,
[Modified_Date] [date] NULL,
[Scada] [nvarchar](100) NULL,
[Fuel_base] [nvarchar](255) NULL,

and the structure of the Audit table is:

[AuditID] [int] IDENTITY(1,1) NOT NULL,
[Type] [char](1) NULL,
[TableName] [varchar](128) NULL,
[PrimaryKeyField] [varchar](1000) NULL,
[PrimaryKeyValue] [varchar](1000) NULL,
[FieldName] [varchar](128) NULL,
[OldValue] [varchar](1000) NULL,
[NewValue] [varchar](1000) NULL,
[UpdateDate] [datetime] NULL,
[UserName] [varchar](128) NULL

Users can modify values in primary table inclusive deleting entire rows and Audit table catch all modifications.I have to do a report with content of primary table at certain date back in time. I think the columns name in Audit table are expressive, Type has three values 'U','I','D' for update,insert and delete actions. Another problem is that if the Audit table contain modification for rows in primary table and the date for snapshot is lower than updateDate in Audit then I have to choose OldValue else NewValue. It is correct? Thank you @Nick.McDermaid for your reply!!

Answer 1

I found an extremely ugly solution, but I think it works fine by now

 Declare @data date     
 select @data='2016.02.2'
 select * into #Grup1 from Grupuri
 --Apply to actual values most oldest values from Audit
 select * into #temp from ( 
 SELECT
 Audit.PrimaryKeyValue as ID,Audit.FieldName,OldValue
 FROM audit inner JOIN (
 SELECT Audit.FieldName,Audit.PrimaryKeyValue, min(UpdateDate) AS dateadded               FROM audit GROUP BY FieldName,PrimaryKeyValue  
 ) maxtimestamp ON audit.FieldName = maxtimestamp.FieldName AND   audit.updateDate = maxtimestamp.dateadded
 where PrimaryKeyField='Id' and TableName='Grupuri' ) src 
 pivot(
 max(src.OldValue)
 for FieldName in (Centrala,ID_Grup,Pi, Ci, Pmt, Pneta, Rpp, Pd, UD, Suport1,    Suport2, Suport3, Stare, 
 Motiv, Observatii, Comentarii, Un, Data_ADD, Modified_Date, Scada,    Fuel_base)
 ) piv;

 UPDATE #Grup1 SET  Pi= (case When b.Pi is not null then b.Pi else #Grup1.Pi   end), 
               Ci=case When b.Ci is not null then b.Ci else #Grup1.Ci end, 
               Pmt=case When b.Pmt is not null then b.Pmt else #Grup1.Pmt end,
               Pneta=case When b.Pneta is not null then b.Pneta else #Grup1.Pneta end, 
               Rpp=case When b.Rpp is not null then b.Rpp else #Grup1.Rpp end, 
               Pd=case When b.Pd is not null then b.Pd else #Grup1.Pd end, 
               UD=case When b.Ud is not null then b.Ud else NULL end, 
               Suport1=case When b.Suport1 is not null then b.Suport1 else #Grup1.Suport1 end, 
               Suport2=case When b.Suport2 is not null then b.Suport2 else #Grup1.Suport2 end, 
               Suport3=case When b.Suport3 is not null then b.Suport3 else #Grup1.Suport3 end, 
               Stare=case When b.Stare is not null then b.Stare else #Grup1.Stare end, 
               Motiv=case When b.Motiv is not null then b.Motiv else #Grup1.Motiv end, 
               Observatii=case When b.Observatii is not null then b.Observatii else #Grup1.Observatii end, 
               Comentarii=case When b.Comentarii is not null then b.Comentarii else #Grup1.Comentarii end,
               Un=case When b.Un is not null then b.Un else #Grup1.Un end, 
               Scada= case When b.Scada is not null then b.Scada else #Grup1.Scada end, 
               Fuel_base=case When b.Fuel_base is not null then b.Fuel_base    else #Grup1.Fuel_base end  
 FROM   #temp b WHERE  #Grup1.id = b.id
 --Apply new values updated up to @data
 select * into #temp1 from ( 
 SELECT
 Audit.PrimaryKeyValue as ID,Audit.FieldName,NewValue
 FROM audit left JOIN (
 SELECT Audit.FieldName,Audit.PrimaryKeyValue, MAX(UpdateDate) AS dateadded   FROM audit GROUP BY FieldName,PrimaryKeyValue  
 ) maxtimestamp ON audit.FieldName = maxtimestamp.FieldName AND    audit.updateDate = maxtimestamp.dateadded
  where PrimaryKeyField='Id' and TableName='Grupuri' 
  and cast(UpdateDate as date) <=@Data) src 
  pivot(
  max(src.NewValue)
  for FieldName in (Centrala,ID_Grup,Pi, Ci, Pmt, Pneta, Rpp, Pd, UD,   Suport1, Suport2, Suport3, Stare, 
  Motiv, Observatii, Comentarii, Un, Data_ADD, Modified_Date, Scada,    Fuel_base)) piv;

 UPDATE #Grup1 SET  Pi= (case When b.Pi is not null then b.Pi else #Grup1.Pi   end), 
               Ci=case When b.Ci is not null then b.Ci else #Grup1.Ci end, 
               Pmt=case When b.Pmt is not null then b.Pmt else #Grup1.Pmt end,
               Pneta=case When b.Pneta is not null then b.Pneta else #Grup1.Pneta end, 
               Rpp=case When b.Rpp is not null then b.Rpp else #Grup1.Rpp end, 
               Pd=case When b.Pd is not null then b.Pd else #Grup1.Pd end, 
               UD=case When b.Ud is not null then b.Ud else '-' end, 
               Suport1=case When b.Suport1 is not null then b.Suport1 else #Grup1.Suport1 end, 
               Suport2=case When b.Suport2 is not null then b.Suport2 else #Grup1.Suport2 end, 
               Suport3=case When b.Suport3 is not null then b.Suport3 else #Grup1.Suport3 end, 
               Stare=case When b.Stare is not null then b.Stare else #Grup1.Stare end, 
               Motiv=case When b.Motiv is not null then b.Motiv else #Grup1.Motiv end, 
               Observatii=case When b.Observatii is not null then b.Observatii else #Grup1.Observatii end, 
               Comentarii=case When b.Comentarii is not null then b.Comentarii else #Grup1.Comentarii end,
               Un=case When b.Un is not null then b.Un else #Grup1.Un end, 
               Scada= case When b.Scada is not null then b.Scada else #Grup1.Scada end, 
               Fuel_base=case When b.Fuel_base is not null then b.Fuel_base   else #Grup1.Fuel_base end  
   FROM   #temp1 b
   WHERE  #Grup1.id = b.id

   Delete from #Grup1 where Data_ADD>@data 

   Select * from #Grup1
   union
   Select Old_ID,Centrala,ID_Grup,Pi, Ci, Pmt, Pneta, Rpp, Pd, UD,    Suport1,Suport2, Suport3, Stare, Motiv, 
   Observatii, Comentarii, Un, Data_ADD, Modified_Date, Scada, Fuel_base From     DeletedGrupuri where Deleted<=@Data and Old_ID is not null order by ID
  drop table #temp
  drop table #temp1
  drop table #Grup1

If someone has a better solution or can improve this code please help me. Also I'm open to modify the design of table Audit to simplify this process. Thank You!