Running/Cumulative Avg Based on Datediff

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Running/Cumulative Avg Based on Datediff

Author

Topic

vcs1161
Starting Member

10 Posts

Posted - 2013-09-06 : 10:22:45

I need to go through thousands of records lines to determine that average time difference in records. This is based on records with the same item number and location number that the average is grouped by. It is a bit tricky because some transactions can have multiple in and out times while a few can have another set for the same record line on a different day. So I need to make sure I get the in and out time sets that occur on that same day for that record.

I have this piece of code that seems to work so I can see it is capturing the right in and out times for each record line. But I would like to know if there is a way that I can do a running average based on these grouped with the same location number and item number to get a summary result instead of the detail as I am doing here. Here is what I have that I would like to replace with a summary approach.

This temp table is based on results I already have from TableB and it begins by mapping that with the master database TableA.

create table #all_record_times
select distinct
TableD.record_num,
TableD.location_num,
TableC.location_facility,
TableA.item_num,
TableD.record_time,
TableD.time_code,
(select count (distinct record_time)
from TableD where record_num = td.record_num
and location_num = td. location_num and record_time <= td. record_time and time_code = 'PB'),
null (record_end_time) /*this will be the “PL” time done during the following update*/
from
TableA,
TableB,
TableC,
TableD td1
where
TableA.Time between Date1 and Date2
and TableA.item_num = TableB.item_num
and TableA.transact_num = TableC.transact_num
and TableB.loc_id = TableC.loc_id
and TableC.record_num = TableD.record_num
and TableD.time_code = 'pb' /*begin time*/
and
(TableD.record_time = (select min(record_time) from TableD
where time_code _code = 'pb' and record_num = td1.record_num
and location_num = td1.location_num ))
and TableD.location_num = TableB.location_num

order by TableD.record_num,TableD.location_num,TableD.record_time

update #all_record_times
set
record_end_time = record_time
from
#all_record_times art,
TableD
where
TableD.time_code = 'pl' /*end time*/
and TableD.record_num = art.record_num
and TableD.location_num = art.location_num

and TableD.record_time in (select min(record_time)
FROM TableD WHERE
Record_num = art.record_num
and location_num = art.location_num

and record_time >= art.record_time
and convert(smalldatetime, convert(varchar(12), record_time,101)) =convert(smalldatetime, convert(varchar(12), art.record_time,101))
GROUP BY record_num, location_num,record_time,convert(smalldatetime, convert(varchar(12), record_time,101))
)

After a summary code is compiled, I would like to work with this result set:

Item_num, location_num, avg total time

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-10 : 08:20:57

see some methods here

http://www.codeproject.com/Articles/300785/Calculating-simple-running-totals-in-SQL-Server

if you want specific help. please post some sample data and explain what you want as output
its quite difficult to make out what you want from code you posted.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources