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.
| Author |
Topic |
|
mrgr8avill
Starting Member
16 Posts |
 Posted - 2007-07-20 : 10:34:19
|
| Hello,Thanks in advance for reading this. This one is getting me. A sample table:tracking (int),Statref (int), statTime (smalldatetime), statcode (int)Tracking - the foreign surrogate key to the main tableStatref - the unique surrogate key within this table -- increasing integerStatcode - a number indicating the status of the widgetStattime - the time the status code was enteredThe table is kind of "3d" in that it maintains status records ongoing for each tracking. In other words, an ORDER BY Tracking, status would reveal several entries for each tracking, each indicating a progressing status and time. Statuses of 0 are new and Statuses above 600 are to be ignored.Now the hard part -- I need to aggregate this data into the following:The minimum, maximum, and average times to present since an active widget came online; andThe minimum, maximum, and average times to present each widget has been in its present status.Now, I think I got lucky because the unique identifier would logically be the lowest for the first entry and highest for the last entry. This allows me to get "close" in two queries:For the min, max, and Avg times in present status:SELECT max(datediff(s,statustime,getdate())) as hightimeinstatus, min(datediff(s,statustime,getdate())) as lowtimeinstatus,avg(datediff(s,statustime,getdate())) as avgtimeinstatus,from statuses where statusref in (select max(statusref) from statuses group by tracking)And for the total times in system:SELECT max(datediff(s,statustime,getdate())) as hightime, min(datediff(s,statustime,getdate())) as lowtime,avg(datediff(s,statustime,getdate())) as avgtime,from statuses where statusref in (select min(statusref) from statuses group by tracking)My problems: 1) how to limit the aggregation to those records where MAX(Statuscode) for the group is <600, and2) I would like to do a SELECT INTO by merging these two queries into a single one, but so help me I get lost in circular logic every time I try. I would very much appreciate any help anyone could provide on how to get these two queries merged into one, or any other way of doing this.THANKS!!Shannon |
|
|
mattyblah
Starting Member
49 Posts |
Posted - 2007-07-20 : 11:25:44
|
| having should be able to filter the max(statuscode) < 600. still not sure I understand what you're trying to do, perhaps you can provide some example data and expected results. |
 |
|
|
mrgr8avill
Starting Member
16 Posts |
Posted - 2007-07-20 : 11:51:01
|
| Sure -- thanks!The best analogy of this table would be a FedEx tracking record. Here, TRACKING refers to the tracking number of the package, and statuscode refers to each update along the path to delivery. Status codes of 0 are PICKED UP and status codes of 600 are DELIVERED.Tracking StatusRef StatusCode StatusTime ChangedBy----------- ----------- ----------- ----------------------- -----------247 240 0 2007-07-18 18:07:00 1247 248 100 2007-07-19 12:03:00 2247 249 102 2007-07-19 14:08:00 2248 241 0 2007-07-18 18:07:00 1248 250 100 2007-07-19 04:02:00 1249 242 0 2007-07-18 18:07:00 1249 251 600 2007-07-19 01:01:00 1250 243 0 2007-07-18 18:07:00 1Okay -- this is sorted by tracking, statusref (statusref and tracking have no direct relation despite their closeness in value)As you can see, there can be multiple entries for each Tracking field. Each unique tracking represents one main widget record. I will refer to the groups of unique tracking numbers as "tracking groups"This is the "english" logic of what I need to do:1) ignore all tracking groups for which the highest status code is >= 600. This would "knock out" the rows related to tracking #249 in the sample table:Tracking StatusRef StatusCode StatusTime ChangedBy----------- ----------- ----------- ----------------------- -----------247 240 0 2007-07-18 18:07:00 1247 248 100 2007-07-19 12:03:00 2247 249 102 2007-07-19 14:08:00 2248 241 0 2007-07-18 18:07:00 1248 250 100 2007-07-19 04:02:00 1250 243 0 2007-07-18 18:07:00 12) from that result set, find the highest and the lowest status code for each tracking group -- discard the rest. This would "knock out" the row with statusref 249, as its status code is neither the lowest nor the highest:Tracking StatusRef StatusCode StatusTime ChangedBy----------- ----------- ----------- ----------------------- -----------247 240 0 2007-07-18 18:07:00 1247 248 100 2007-07-19 12:03:00 2248 241 0 2007-07-18 18:07:00 1248 250 100 2007-07-19 04:02:00 1249 242 0 2007-07-18 18:07:00 1250 243 0 2007-07-18 18:07:00 13) Calculate the max(), min(), avg() time from when status code 0 was entered for each record to now.4) Calculate the max() min() avg() time from the highest status code to now (if zero is the highest, then that is included).What all this is attempting to do is find out aggregate information as to how long each active order has been in the system, and then to find out how long each active order has been in its present status. It is to give a situational awareness of how long things are taking to move.If the data were in several tables, I could figure it out with joins, but trying to attack such a limited amount of data from so many different angles with a single query is killing my brain. Maybe I just have to do it in steps with coded logic instead of a single SELECT INTO?Thanks again for your time, and in advance for any assistance you can provide. |
|
|
|
mrgr8avill
Starting Member
16 Posts |
Posted - 2007-07-20 : 12:14:29
|
| I think this works in two queries:select max(datediff(s,statustime,getdate())) as maxtime, min(datediff(s,statustime,getdate())) as mintime, avg(datediff(s,statustime,getdate())) as avgtime from statuses where statusref in (select min(statusref) from statuses group by tracking having max(statusref) < 600)and select max(datediff(s,statustime,getdate())) as maxtimein status, min(datediff(s,statustime,getdate())) as mintimeinstatus, avg(datediff(s,statustime,getdate())) as avgtimeinstatus from statuses where statusref in (select max(statusref) from statuses group by tracking having max(statusref) < 600)the difference between the two just really being the MAX(Statusref) and MIN(Statusref) in the IN subquery.Because the IN subqueries are different, is there any way to put these tow queries into a single one for SELECT INTO?THANKS!! |
|
|
|
mrgr8avill
Starting Member
16 Posts |
Posted - 2007-07-20 : 12:36:16
|
| Okay -- how to combine the results from the sample query in my last post:select top 1(select max(datediff(s,statustime,getdate())) from statuses where statusref in (select min(statusref) from statuses group by tracking having max(statusref) < 600)) as maxtime,(select min(datediff(s,statustime,getdate())) from statuses where statusref in (select min(statusref) from statuses group by tracking having max(statusref) < 600)) as mintime,(select avg(datediff(s,statustime,getdate())) from statuses where statusref in (select min(statusref) from statuses group by tracking having max(statusref) < 600)) as avgtime,(select max(datediff(s,statustime,getdate())) from statuses where statusref in (select max(statusref) from statuses group by tracking having max(statusref) < 600)) as maxtimeinstatus,(select min(datediff(s,statustime,getdate())) from statuses where statusref in (select max(statusref) from statuses group by tracking having max(statusref) < 600)) as mintimeinstatus,(select avg(datediff(s,statustime,getdate())) from statuses where statusref in (select max(statusref) from statuses group by tracking having max(statusref) < 600)) as avgimeinstatusfrom statusesThis seems REALLY convoluted.... but it works (I think). |
|
|
|
|
|
|
|
|
Obtaining Aggregated data from a group within tabl