colin158


Hi,

Using SQL Server 2005 Standard Ed.

First off, here is the application.

  • An airport baggage handling system distributes bags using multiple conveyors.
  • Bag counts are logged every 15 seconds. There is a count for each conveyor.
  • I receive a cumulative count from the Baggage handling system, what I need to do is figure out the difference between the current count and the previous count and log this in the table as the 'delta' to give me the count for that 15 second period.

I am a newbie so advice on how best to achieve this would be greatly appreciated.

Here is the code to create a test table with 3 conveyors: The last row of the test table is how the data would come in, then I envision using a trigger to populate the 'Delta' columns.

Code Snippet

CREATE TABLE #t

(

RecordID int not null unique clustered,

Conv1Accum smallint NULL,

Conv1Delta smallint NULL,

Conv2Accum smallint NULL,

Conv2Delta smallint NULL,

Conv3Accum smallint NULL,

Conv3Delta smallint NULL,

CountTimeStamp datetime NOT NULL

)

go

set nocount on

insert into #t values(1, 2, 2, 1, 1, 1, 1, '2007-06-29 07:00:00.000')

insert into #t values(2, 4, 2, 5, 4, 2, 1, '2007-06-29 07:00:15.000')

insert into #t values(3, 6, 2, 15, 10, 5, 3, '2007-06-29 07:00:30.000')

insert into #t values(4, 10, 4, 16, 1, 5, 0, '2007-06-29 07:00:45.000')

insert into #t values(5, 15, 5, 16, 0, 20, 15, '2007-06-29 07:01:00.000')

insert into #t values(6, 16, NULL, 20, NULL, 22, NULL, '2007-06-29 07:01:15.000')

--Please insert your code here!

drop table #t

go

I am thinking of a trigger on INSERT that does the task along the lines of the following pseudocode:

Code Snippet

DECLARE @MaxColumns INT

DECLARE @AccumColumnID INT

DECLARE @DeltaColumnID INT

DECLARE @CurrentVal INT

DECLARE @PreviousVal INT

DECLARE @CurrentRow INT

DECLARE @i INT

--Initialise

SET @AccumColumnID = 2 --Since Column 1 is the RecordID

SELECT @MaxColumns =

FOR @i=2 to @MaxColumns BY 2 DO --For i having values of 2,4,6,8,10 ...MaxColumns

SET @CurrentRow = @@IDENTITY,

SELECT Column(i+1)=(SELECT Column(i) from CurrentTable WHERE RecordID = @CurrentRow) - (SELECT Column(i) from CurrentTable WHERE RecordID = @CurrentRow-1)

IF @i<(MaxColumns-2) THEN SET i=i+2

ELSE

Return

However this just seems way too convoluted and messy.

Any suggestions

  • Is using the same table with extra columnns for the delta's a wise move or would it be better to go with a completely separate table populated only by the delta values

Please let me know if I can give any more info to explain better

Thanks for your time and help in advance.

Colin.





Re: calculating the delta between current row and previous row for multiple columns.

Manivannan.D.Sekaran


here you go..

Code Snippet

CREATE TABLE #t

(

RecordID int not null unique clustered,

Conv1Accum smallint NULL,

Conv1Delta smallint NULL,

Conv2Accum smallint NULL,

Conv2Delta smallint NULL,

Conv3Accum smallint NULL,

Conv3Delta smallint NULL,

CountTimeStamp datetime NOT NULL

)

go

set nocount on

insert into #t values(1, 2, 2, 1, 1, 1, 1, '2007-06-29 07:00:00.000')

insert into #t values(2, 4, 2, 5, 4, 2, 1, '2007-06-29 07:00:15.000')

insert into #t values(3, 6, 2, 15, 10, 5, 3, '2007-06-29 07:00:30.000')

insert into #t values(4, 10, 4, 16, 1, 5, 0, '2007-06-29 07:00:45.000')

insert into #t values(5, 15, 5, 16, 0, 20, 15, '2007-06-29 07:01:00.000')

insert into #t values(6, 16, NULL, 20, NULL, 22, NULL, '2007-06-29 07:01:15.000')

Update #t

Set

Conv1Delta = Data.Conv1Delta,

Conv2Delta = Data.Conv2Delta,

Conv3Delta = Data.Conv3Delta

From

#T Main

Join

(

Select

Curr.RecordID

,Curr.Conv1Accum

,Curr.Conv1Accum - Prev.Conv1Accum Conv1Delta

,Curr.Conv2Accum

,Curr.Conv2Accum - Prev.Conv2Accum Conv2Delta

,Curr.Conv3Accum

,Curr.Conv3Accum - Prev.Conv3Accum Conv3Delta

,Curr.CountTimeStamp

from

#t Curr

Join #t Prev On

Curr.CountTimeStamp = Dateadd(SS,15,Prev.CountTimeStamp)

) as Data On Main.RecordID = Data.RecordID

Select * From #t

drop table #t

go







Re: calculating the delta between current row and previous row for multiple columns.

DaleJ

Here you are: (#t changed to baggage to allow trigger creation)

Note though, that if the row with recordid - 1 doesn't exist then nothing is updated and the delta's remain null.

you may want to define a default on the delta columns to make them zero or similar if you don't want null.

Code Snippet

create trigger mytrig

on baggage

after insert

as

begin

update t1

set conv1delta = (t1.conv1accum - t3.conv1accum),

conv2delta = (t1.conv2accum - t3.conv2accum),

conv3delta = (t1.conv3accum - t3.conv3accum)

from baggage t1

inner join inserted t2

on t1.recordid = t2.recordid

inner join baggage t3

on t1.recordid - 1 = t3.recordid

end

go







Re: calculating the delta between current row and previous row for multiple columns.

colin158

Manivannan, Thanks for the solution, it works well, however is there any way you can make it so that the the code is modular i.e. it does not matter how many columns the table has or what they are called, it just iterates through the columns and creates the delta's. I am looking to be able to re-use this code for different systems whch will have varying numbers of conveyors and varying conveyor (column) names.

Thanks again for your help.

Colin






Re: calculating the delta between current row and previous row for multiple columns.

DaleJ

I'd suggest changing your table structure from having to conv1xxxx, conv2xxxx, etc.

to RecordID, ConveyorNbr, Accum, Delta, AsOf.

That will allow ultimate flexibility in variations of conveyors.






Re: calculating the delta between current row and previous row for multiple columns.

Arnie Rowland

When you have such a denormalized schema, it becomes difficult to use many processes efficiently.

As Mani suggested, you really, really should re-examine your schema and correct the design mistakes BEFORE you get to production.

These kinds of eggregious design mistakes have a way of continuing to 'bite' you over the entire project life cycle. They will most likely cost much more in maintenance costs over the project life-cycle than the time/cost to correct them now.






Re: calculating the delta between current row and previous row for multiple columns.

colin158

Hi Dale,

Thanks for the advice - I agree totally. It was a messy way of storing the data. I am going to change to the schema you suggest but now I have another question. How do I trigger a stored procedure every 15 seconds

Thanks

Colin






Re: calculating the delta between current row and previous row for multiple columns.

DaleJ

That most likely will need to originate from an app outside SS.

SS Agent will let you schedule once per minute, but no less.