Вопрос:

создание таблицы моментальных снимков из контрольного журнала в MS SQL

sql-server database

74 просмотра

1 ответ

11 Репутация автора

Я столкнулся с той же проблемой, что и здесь. Создание таблицы снимков из записей аудита . Код ниже частично решит мою проблему

    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;

Как получить снимок таблицы на основе записей из таблицы контрольного журнала в данный момент. Копирование фактической таблицы во временную и обновление значений из нее на основе содержания аудита - это решение? Мой английский оставляет желать лучшего! Спасибо!!!

Структура первичной таблицы:

[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,

и структура таблицы аудита:

[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

Пользователи могут изменять значения в первичной таблице, включая удаление целых строк, а таблица Audit перехватывает все модификации. Мне нужно составить отчет с содержимым первичной таблицы на определенную дату назад во времени. Я думаю, что имена столбцов в таблице аудита являются выразительными, тип имеет три значения «U», «I», «D» для обновления, вставки и удаления действий. Другая проблема заключается в том, что если таблица аудита содержит изменения для строк в первичной таблице, а дата снимка ниже, чем updateDate в Audit, то мне нужно выбрать OldValue или NewValue. Верно? Спасибо @ Nick.McDermaid за ваш ответ !!

Автор: BodoShow Источник Размещён: 22.08.2016 08:47

Ответы (1)


0 плюса

11 Репутация автора

Я нашел крайне уродливое решение, но я думаю, что оно отлично работает

 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

Если у кого-то есть лучшее решение или он может улучшить этот код, пожалуйста, помогите мне. Также я готов изменить дизайн таблицы Audit, чтобы упростить этот процесс. Благодарю вас!

Автор: BodoShow Размещён: 25.08.2016 03:13
Вопросы из категории :
32x32