incremental data loading

Question

incremental data loading

ninel

Ten Centuries

Points: 1027
More actions
July 7, 2010 at 10:30 am

#223052

My company has a process that runs every 30 minutes or so. This process loads data into the database.
I need to write a process that captures cumulative data from a particular column and compares it to the last load.
So for example: Load 1, employee 123 had a total of 25 seconds. Load 2 this same employee had a total of 30 seconds.
On each load, I need to calculate the difference of the total time for each employee. Load1 25sec, Load2 30sec --> The increment will be 5 sec.
I only need to calculate the current load with the last load.
Is there any easy way to do this? I'm stumped.
Thanks,
Ninel

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Mike01 SSChampion Points: 11395 More actions · Answer 1

Is there a way to distinguish the different loads? What do you mean by an employee had 25 seconds, then 30 seconds? Do you have any sample tables and data?

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

ninel Ten Centuries Points: 1027 More actions · Answer 2

I can keep track of each load with a LoadId.

I work for a call center and we track employees time. An employee can be on the phone, or waiting for a phone call, finishing up a call, etc. Each separate action is captured in different columns. So let's say we're looking only at one column "TimeOnPhone".

On first load of day employee 123 has 25 minutes in this column. Next load he has 30minutes. So on this load the increment is 5 minutes. I need to calculate this difference on all loads, but I just care about the difference on current load and last load.

Mike01 SSChampion Points: 11395 More actions · Answer 3

I assume that en employee could exist in one load and not in the next (lunch break, etc.). If this is the case, then what would the calculation be for that? Would you only show times between loads or between load 1 and Load 5 when the employee showed up again?

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Mike01 SSChampion Points: 11395 More actions · Answer 4

You can use a CTE. This assumes that an employee can not exist in a specific load, so that won't count against the totals. See Employee 345. They don't exist in the 4th load, so the cumulative differences are calced between the 3rd load and the 5th load. If this is different, then in the query, you will have to add CurRow.LoadID = NextRow.LoadID + 1

create table #table1

(

LoadID int not null,

EmployeeID int not null,

TimeOnPhone int not null default 0,

TimeWaiting int not null default 0,

TimeFinishingUp int not null default 0,

primary key (LoadID, EmployeeID )

)

insert into #table1

select 1,123,25,0,0 union all

select 1,234,25,0,0 union all

select 1,345,25,0,0 union all

select 1,456,25,0,0 union all

select 2,123,30,0,0 union all

select 2,234,0,10,0 union all

select 2,345,0,0,10 union all

select 2,456,25,0,0 union all

select 3,123,30,0,0 union all

select 3,234,0,10,0 union all

select 3,345,0,0,10 union all

select 3,456,35,0,0 union all

select 4,123,30,0,0 union all

select 4,234,0,10,0 union all

select 4,456,35,0,0 union all

select 5,123,30,0,0 union all

select 5,234,0,10,0 union all

select 5,345,0,0,10 union all

select 5,456,35,0,0

;with cte (LoadID, EmployeeID, TimeOnPhone, TimeWaiting, TimeFinishingUp, RowID )

as (select LoadID, EmployeeID, TimeOnPhone, TimeWaiting, TimeFinishingUp,

ROW_NUMBER() OVER (Partition by EmployeeID ORDER BY EMPloyeeID, LoadID ASC) AS ROWID

from #table1)

select CurRow.LoadID, CurRow.EmployeeID, CurRow.TimeOnPhone - IsNull(NextRow.TimeOnPhone,0) TimeOnPhone,

CurRow.TimeWaiting - IsNull(NextRow.TimeWaiting,0) TimeWaiting,

CurRow.TimeFinishingUp - IsNull(NextRow.TimeFinishingUp,0) TimeFinishingUp

from cte CurRow

left outer join cte NextRow

on CurRow.EmployeeID = NextRow.EmployeeID

and CurRow.RowID = NextRow.RowID + 1

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/