• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 457
  • Last Modified:

Trigger update to aggregated table

I'm importing 30 fields of data to tableA every hour or so.  One of the fields is the date and time the data was logged.  I want to also archive this data to another table, but change the date and time to a date only, i.e. all times are converted to 0 and I keep the date as the value.  But, I have to sum all the other fields to do this.  So, I tried a trigger on Table1 to aggregate the INSERTED table data into timeless date records and then insert that data to table2 the aggregated archive table.  The problem is that a date may already exist in table2.  So, I have to add that date's data as well.  I can do this below, but I don't know how to remove the old records from table2 that I need to replace.  Any ideas?  I assume this must be a standard process that is often needed.

      INSERT dbo.Table2
            (
            StaffID,
            Archivetime,
            State,
            Amount
            )
            SELECT
                  i.StaffID,
                  CONVERT(datetime,CONVERT(int,i.archivetime)) Archivetime,
                  i.State,
                  SUM(ISNULL(i.Amount,0)) + SUM(ISNULL(a.Amount,0)) As Amount

            FROM INSERTED i
            LEFT OUTER JOIN dbo.Table2 a ON i.StaffID=a.StaffID
                  AND i.State= a.State
                  AND  CONVERT(datetime,CONVERT(int,i.archivetime))=a.ArchiveTime
            Group By i.StaffID, CONVERT(datetime,CONVERT(int,i.archivetime)), i.State

      DELETE a
      FROM dbo.Table2 a
      JOIN INSERTED s ON a.StaffID=s.StaffID
            AND a.State = s.State
      WHERE
            a.ArchiveTime = CONVERT(datetime,CONVERT(int,a.archivetime))
            AND a.Amount <a.Amount-s.Amount
0
Jon_Raymond
Asked:
Jon_Raymond
  • 8
  • 3
1 Solution
 
Jon_RaymondAuthor Commented:
Sorry, not 30 fields. Just 4 and the tables also have autonumber key fields, though there are 30 possible states for each StaffID.  This is in SQL Server 7.
0
 
Jon_RaymondAuthor Commented:
Adjusted points to 100
0
 
Gustavo Perez BuenrostroCommented:
Try code shown below and let me know if it works as you expect:

create trigger TGx on Table1
for insert
as
begin
  if (select count(table2.StaffID)
        from table2
        join inserted
          on table2.StaffID
            =inserted.StaffID and
             cast(convert(varchar,table2.Archivetime,112) as datetime)
            =cast(convert(varchar,inserted.Archivetime,112) as datetime) and
             table2.State
            =inserted.State)=0
    begin
      insert table2

      select inserted.StaffID
             ,cast(convert(varchar,inserted.Archivetime,112) as datetime)
             ,inserted.State
             ,isnull(inserted.Amount,0)
         from inserted
      end

  else
    begin
      update table2
         set table2.Amount=table2.Amount+isnull(inserted.Amount,0)
        from inserted
       where table2.StaffID
            =inserted.StaffID and
             cast(convert(varchar,table2.Archivetime,112) as datetime)
            =cast(convert(varchar,inserted.Archivetime,112) as datetime) and
             table2.State
            =inserted.State
    end
end
0
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
Jon_RaymondAuthor Commented:
Returns Error 260: Disallowed implicit conversion from datatype datetime to datatype int table 'table2' Column 'StaffID' use CONVERT function to run this query.

But, I'm still playing with it
0
 
Jon_RaymondAuthor Commented:
Excellent, Thank you.
0
 
Jon_RaymondAuthor Commented:
Here are the changes I made:

CREATE TRIGGER dbo.trgUpdateAgg ON [tmData]
for insert
as
begin
  if (select count(*)
        from tmDataAgg
        join inserted
          on cast(convert(int,tmDataAgg.Archivetime)as datetime)=cast(convert(int,inserted.Archivetime)as datetime)
      AND tmDataAgg.StaffID=inserted.StaffID
      AND tmDataAgg.State=inserted.State)
      =0
    begin
      insert tmDataAgg
      (
      ArchiveTime,
      StaffID,
      State,
      Amount
      )

      select
      convert(DateTime,convert(int,inserted.Archivetime),101) Archivetime,
      inserted.StaffID,
             inserted.State,
             isnull(inserted.Amount,0) Amount
         from inserted
      end

  else
    begin
      update tmDataAgg
         set tmDataAgg.Amount=tmDataAgg.Amount+isnull(inserted.Amount,0)
        from inserted
       where tmDataAgg.StaffID
            =inserted.StaffID and
             cast(convert(int,tmDataAgg.Archivetime)as datetime)
            =cast(convert(int,inserted.Archivetime)as datetime) and
             tmDataAgg.State
            =inserted.State
    end
end
0
 
Jon_RaymondAuthor Commented:
qpbuenrostro,  I reacted too quickly.  I thought you answer was the solution.  But, it only works under certain conditions.  If I have multiple records in Table1 (or rather the INSERT table) with the same date (not time) then I need to add them up into one record to insert or update to table2.  This doesn't happen.  It just adds the last record.

I have to be able to add all records from table1 (or the INSERT table) that have the same dates and also add then to any existing record in table2 with that same date, or just insert them to table2 if no existing record matches the date.

You can't use aggregation with an UPDATE so I thought it would be necessary to delete the records and replace them with aggregated records.
0
 
Gustavo Perez BuenrostroCommented:
Jon_Raymond,
Be careful when removing time portion from datetime datatype. Check next situation:

set nocount on
select 'Today'=getdate()
select 'Your Code'=convert(DateTime,convert(int,getdate()),101)
select 'My code'=cast(convert(varchar,getdate(),112) as datetime)

/* Results
Today                      
---------------------------
2000-01-19 12:27:34.630

Your Code                  
---------------------------
2000-01-20 00:00:00.000

My code                    
---------------------------
2000-01-19 00:00:00.000
*/

PD: Post more information about your last comment. Let me help you.
0
 
Jon_RaymondAuthor Commented:
I see.  Thanks for that tip. Here's my new version of the first part ofthe trigger.  This works OK.

      IF
      (
            SELECT COUNT(*)
            FROM StaffTimesAgg a
            JOIN Inserted i
                  on CAST(convert(varchar,a.ArchiveTime,112)AS datetime)=CAST(convert(varchar,i.ArchiveTime,112) AS datetime)
                  AND a.StaffID=i.StaffID
                  AND a.State=i.State
      )=0
            BEGIN
                  INSERT StaffTimesAgg
                        (
                        ArchiveTime,
                        StaffID,
                        State,
                        Amount
                        )
      
                  SELECT
                        cast(convert(varchar,i.ArchiveTime,112) as datetime) Archivetime,
                        i.StaffID,
                        i.State,
                        SUM(ISNULL(i.Amount,0)) Amount
                  FROM Inserted i
                  GROUP BY cast(convert(varchar,i.ArchiveTime,112) as datetime), i.StaffID, i.State
            END
      ELSE


Then for the other condition when there are matching records in table2 I need to aggregate records from both the inserted table and table2 where 3 fields match (one of the 3 fields is a conversion of the date field).  So, i tried a cursor which i can't get to work:

            --DEALLOCATE i
            DECLARE @Amount Float
            DECLARE @StaffID int
            DECLARE @Archivetime datetime
            DECLARE @State int
            DECLARE i CURSOR FOR
            SELECT cast(convert(varchar,ArchiveTime,112), StaffID, State, Amount FROM Inserted
            OPEN i
            FETCH NEXT FROM i INTO @Archivetime, @StaffID, @State, @Amount
            WHILE @@FETCH_STATUS = 0
            --BEGIN
                  UPDATE a
                  SET a.Amount=ISNULL(a.Amount,0)+ISNULL(@Amount,0)
                  FROM dbo.StaffTimesAgg a
                  WHERE
                        a.StaffID=@StaffID
                        AND cast(convert(varchar,a.ArchiveTime,112)as datetime)=cast(convert(varchar,@ArchiveTime,112)as datetime)
                        AND a.State=@State
                  FETCH NEXT FROM i INTO @Archivetime, @StaffID, @State, @Amount
            CLOSE i
            DEALLOCATE i
0
 
Gustavo Perez BuenrostroCommented:
Ok, I understood your problem “Multirow Considerations”. Check code above:

create TRIGGER dbo.trgUpdateAgg ON StaffTimes
for insert
as
begin
  if (select count(*)
        from StaffTimesAgg a
             join Inserted i
               on CAST(convert(varchar,a.ArchiveTime,112)AS datetime)
                 =CAST(convert(varchar,i.ArchiveTime,112)AS datetime)
              AND a.StaffID=i.StaffID
              AND a.State=i.State)=0
    BEGIN
      INSERT StaffTimesAgg (ArchiveTime
                           ,StaffID
                           ,State
                           ,Amount)
      SELECT cast(convert(varchar,i.ArchiveTime,112) as datetime) Archivetime
            ,i.StaffID
            ,i.State
            ,SUM(ISNULL(i.Amount,0)) Amount
        FROM Inserted i
    GROUP BY cast(convert(varchar,i.ArchiveTime,112) as datetime)
            ,i.StaffID
            ,i.State
    END
  else
    begin
      declare @Archivetime datetime
             ,@StaffID int
             ,@State int
             ,@Amount Float
      declare cr_inserted cursor local
      for select cast(convert(varchar,ArchiveTime,112) as datetime)
                ,StaffID
                ,State
                ,sum(isnull(Amount,0))
            from inserted
         group by cast(convert(varchar,ArchiveTime,112) as datetime)
                 ,StaffID
                 ,State
      open cr_inserted
      while (1=1)
        begin
          fetch next from cr_inserted into
          @Archivetime,@StaffID,@State,@Amount
          if @@fetch_status<>0 break
          update StaffTimesAgg
             set Amount=Amount+@Amount
           where Archivetime
                =@Archivetime and
                 StaffID=@StaffID and
                 State=@State
        end
    end
end
0
 
Jon_RaymondAuthor Commented:
Ok, very good.  That appears to work well.  Thanks again.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 8
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now