asked on

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

Jon_Raymond

ASKER

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.

Jon_Raymond

ASKER

Adjusted points to 100

ASKER CERTIFIED SOLUTION

Gustavo Perez Buenrostro

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jon_Raymond

ASKER

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

Jon_Raymond

ASKER

Excellent, Thank you.

Jon_Raymond

ASKER

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

Jon_Raymond

ASKER

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.

Gustavo Perez Buenrostro

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.

Jon_Raymond

ASKER

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

Gustavo Perez Buenrostro

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

Jon_Raymond

ASKER

Ok, very good. That appears to work well. Thanks again.