SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Running/Cumulative Avg Based on Datediff
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vcs1161
Starting Member

10 Posts

Posted - 09/06/2013 :  10:22:45  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 09/10/2013 :  08:20:57  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000