Query to combine some rows and leave others.

[douglas.fagerstrom]

I have a large table which I need to consolidate for reporting purposes. The Table is a data dump from another system. The ROUTE_ID being "Read" is normally a one day task, however, they can span two, three, or even four days including weekends (generally weekends are not worked but there are exceptions. I need to leave the one day routes in the table and show the multi-day routes as a single record. The whole data set contains about 50,000 which is updated every work day. In addition to the table below there are another 20-25 fields I have not shown to keep this a little less dramatic.


The is below a sample of the original data set.

INDEX	ROUTE_ID	RD_DT	ROUTE_TIME	Read_Rate	ActualReads	TotalSkipReads	Total_Reads	SC01	SC02
1	01B5054T	03-Jul-14	6.2	22	124	9	133
2	01B5057T	01-Apr-14	3.0	13	34	4	38	1
3	01B5057T	02-Apr-14	10.5	11	104	15	119	1	3
10	01B5063T	01-Oct-13	8.0	26	196	10	206	1	4
11	01B5063T	04-Jul-13	4.3	38	153	7	160	2
12	01B5063T	05-Jul-13	1.8	29	44	8	52
15	01C1000W	27-Sep-13	3.9	77	292	5	297	2
16	01C1000W	01-Jul-13	3.8	81	293	16	309	2
17	01C1000W	02-Oct-14	2.6	58	142	10	152	1	4

16356	33R1118W	31-Jan-15	1.3	68	90	1	91	1
16357	33R1118W	02-Feb-15	6.1	60	359	11	370	2	5
16358	33R1118W	04-Feb-15	0.2	60	12	1	13		1

In the above data set I need to see the following when the query is run.

INDEX	ROUTE_ID	RD_DT	ROUTE_TIME	Read_Rate	ActualReads	TotalSkipReads	Total_Reads	SC01	SC02	Days
1	01B5054T	03-Jul-14	6.2	22	124	9	133			1
2	01B5057T	01-Apr-14	13.5	12	138	19	157	2	3	2
10	01B5063T	01-Oct-13	8.0	26	196	10	206	1	4	1
11	01B5063T	04-Jul-13	6.1	35	197	15	212	2		2
15	01C1000W	27-Sep-13	3.9	77	292	5	297	2		1
16	01C1000W	01-Jul-13	3.8	81	293	16	309	2		1
17	01C1000W	02-Oct-14	2.6	58	142	10	152	1	4	1
16356	33R1118W	31-Jan-15	7.6	62	461	13	474	3	6	3

Read_Rate = Total_Reads/ROUTE_TIME

The other fields are added together when the rows are combined.

I got some help and have this SQL working good, it finds and combines the 2 day routes, but leaves doesn't leave the one day route and also doesn't set the [Days]. I was hoping to make this happen all in one query but my results are always either all records or only the multi-day routes.

SELECT A.INDEX, B.INDEX, A.Route_ID, Right$([A.Route_ID],1) AS RouteType, A.RD_DT, B.RD_DT, A.FirstReadTime, A.LastReadTime, B.FirstReadTime, B.LastReadTime, [SumTotalReads]/([SumHours]+([SumMinutes]/60)) AS ReadRate, [A].[ActualReads]+[B].[ActualReads] AS SumReads, [A].[TotalSkipReads]+[B].[TotalSkipReads] AS SumSkipReads, [SumReads]+[SumSkipReads] AS SumTotalReads, [A].[SC01]+[B].[SC01] AS SumSC01, A.SC02+B.SC02 AS SumSC02


FROM CNTRANS_REAL_LOCAL_QUERY AS A INNER JOIN CNTRANS_REAL_LOCAL_QUERY AS B ON A.Route_ID=B.Route_ID


WHERE (((A.EXCLUDE_PD)=No)
And ((B.EXCLUDE_PD)=No)
And ((B.RD_DT)=A.RD_DT+1))
Or (((B.RD_DT)=A.RD_DT+2))
Or (((B.RD_DT)=A.RD_DT+3))
Or (((B.RD_DT)=A.RD_DT+4))
Or (((B.RD_DT)=A.RD_DT+5))
Or (((B.RD_DT)=A.RD_DT+6));

Of course the more info the query compiles and the more accurate it is the better. One nicey-nice would be to show the first [RD_DT] (Start Read) and the last [RD_DT] (End Read) info for multi-day read sets.

Thanks!

[June7]

Did you try an aggregate (GROUP BY) Totals query?

Or build a report and use Sorting & Grouping features with aggregate calcs in footer sections.

[douglas.fagerstrom]

Yes, but the Route_ID is the same and the Date is RD_DT and RD_DT +1, I didn't see where the Group By function would work for this operation.

[June7]

Maybe:

SELECT Min(Index) AS Ind, Route_ID, Min(RD_DT), Sum(Route_Time) AS RT, Avg(Read_Rate) AS Rate, Sum(ActualReads) AS Reads, Sum(TotalSkipReads) AS Skip, Sum(Total_Reads) AS Total, Sum(SC01) AS SumSC01, Sum(SC02) AS SumSC02, Count(Index) AS Days GROUP BY Route_ID;

[douglas.fagerstrom]

Maybe:

SELECT Min(Index) AS Ind, Route_ID, Min(RD_DT), Sum(Route_Time) AS RT, Avg(Read_Rate) AS Rate, Sum(ActualReads) AS Reads, Sum(TotalSkipReads) AS Skip, Sum(Total_Reads) AS Total, Sum(SC01) AS SumSC01, Sum(SC02) AS SumSC02, Count(Index) AS Days GROUP BY Route_ID;

The problem of Grouping by [Route_ID] is that I will have the same Route_ID on occasion. The first two digits are a Cycle ID, then the next 5 are the "Route" and the last is the route type. so in the Data Set I can have the same Cycle and Route # many times but I only want to combine the records where the [Route_ID]'s are equal and the RD_DT (Read Date) are less than seven days apart. It could be read over 3 or 4 different days including weekends.

The index number also does not always go in sequence so I may have the same route over two days but the sequence number is 50 and 200 respectively.

Cheers!

[June7]

Less than 7 days apart? By that criteria I expect some records will fall into more than one cycle.

[douglas.fagerstrom]

No. Look at the first table above. Records 10, 11 and 12. All have the same Route_I'd, but record 10 is not within a 7 day window and would be left alone but 11 and 12 are within 7 days and should be combined or merged. If there was a 9 with the same route_I'd and rd_dt of 30 sep 13 then 9 & 10 would be combined into one record and 11 & 12 would be another.

[June7]

That is criteria for a record that is dependent on values in other records of same table.

Not an easy query to build and requires nested subquery or domain aggregate function. Examples of subqueries: http://allenbrowne.com/subquery-01.html

Your requirement is particularly difficult and I am afraid over my head. In English: Sum this record with other records having a date that is within 7 days of the date of this record and has the same Route_ID.

I tried web search and only found another thread with same question, unanswered.

[IrogSinta]

As June7 said, you have to use nested subqueries along with domain aggregate functions. Here are the SQL strings for the queries:
Qry1:

SELECT T1.*, (SELECT Min(T2.RD_DT)
FROM NameOfTable As T2
WHERE T2.ROUTE_ID=T1.ROUTE_ID AND DateDiff("d", T2.RD_DT, T1.RD_DT)<=7) AS RD_DT1, (SELECT Max(RD_DT)
FROM NameOfTable As T2
WHERE T2.ROUTE_ID=T1.ROUTE_ID AND DateDiff("d", T1.RD_DT, T2.RD_DT)<=7) AS RD_DT2
FROM NameOfTable AS T1;

Qry2

SELECT First(Qry1.INDEX) AS [INDEX], Qry1.ROUTE_ID, Qry1.RD_DT1 AS RD_DT, IIf([RD_DT2]<>[RD_DT1],[RD_DT2],"") AS RD_DT_End, Sum(Qry1.ROUTE_TIME) AS ROUTE_TIME, [Total_Reads]/[ROUTE_TIME] AS Read_Rate, Sum(Qry1.ActualReads) AS ActualReads, Sum(Qry1.TotalSkipReads) AS TotalSkipReads, Sum(Qry1.Total_Reads) AS Total_Reads, Sum(Qry1.SC01) AS SC01, Sum(Qry1.SC02) AS SC02
FROM Qry1
GROUP BY Qry1.ROUTE_ID, Qry1.RD_DT1, IIf([RD_DT2]<>[RD_DT1],[RD_DT2],"")
ORDER BY First(Qry1.INDEX);

And here's the output:


You only have to add the other 20-25 more fields to the 2nd query. Have fun :-)

Ron

[June7]

Thanks for stepping in Iroq. I was certainly struggling with this one. However, the query uses SQL aggregate functions, domain aggregate functions are DSum, DMin, DMax, DLookup, DAvg, etc.

And yes, I did mean domain aggregate in my other post. Sometimes a domain aggregate is the only way to get desired result. I didn't think that was the case here but could have been an option.

[IrogSinta]

Thanks for correcting what I said, June7. I meant to say just "aggregate functions" but I was just looking at your post while I was typing mine and thought nothing of it. :-)

Ron

[douglas.fagerstrom]

Thanks both, I am giving this a shot now...I'll post the results.

[douglas.fagerstrom]

Qry1 is working but is taking a very very long time to run and hsa not finished (15 min plus). Any suggestions?

[IrogSinta]

First suggestion would be to try adding indexes to the fields, ROUTE_ID and RD_DT.
If you cannot, then my other suggestion would be to add a field called RD_DT2 to the table and loop through it via a recordset to update the new field with the earliest date of each 7 day period. Then you can just use the 2nd query and have it based off of this table.

Ron