weeblebiker
Registered User.
- Local time
- Yesterday, 21:15
- Joined
- May 27, 2010
- Messages
- 70
Hi.
The Question is "How many times has a [Serial #] been repaired and come back for repair in 0 to 90 days, 91 to 180 days, in a date range". The purpose is to quantify design or process changes impact on longevity.
so I have a repair table that has all the info in it.
Then a simple Date Range select query that returns all fields Between begin date and end date.
than the date diff query:
Then the date diff sum query:
The date diff query runs within 2 seconds regardless of date range.
when I run the date diff sum query it take around 16 seconds to return results fora 1 year period and over 30 second to return results over a 2 year period. :banghead:
I suspect there is some looping back through every record in the select date range query for every sum statement thingy going on, or worse, even going back to the table for every query term in every layer of queries. how do I not do this?
do I need to make a "virtual" table of the query output for the next query to look at?
and how?
The Question is "How many times has a [Serial #] been repaired and come back for repair in 0 to 90 days, 91 to 180 days, in a date range". The purpose is to quantify design or process changes impact on longevity.
so I have a repair table that has all the info in it.
Then a simple Date Range select query that returns all fields Between begin date and end date.
than the date diff query:
Code:
SELECT [XRS-3 Repair Date Range Query].[Serial #], [XRS-3 Repair Date Range Query].[Date in], (SELECT Max([Date in]) FROM [XRS-3 Repair Date Range Query] AS Alias WHERE Alias.[Date in] < [XRS-3 Repair Date Range Query].[Date in] AND Alias.[Serial #] = [XRS-3 Repair Date Range Query].[Serial #]) AS PrevEnd, DateDiff("d",[PrevEnd],[Date in]) AS [Date Diff], [XRS-3 Repair Date Range Query].[Customer #], [XRS-3 Repair Date Range Query].Customer
FROM [XRS-3 Repair Date Range Query]
ORDER BY [XRS-3 Repair Date Range Query].[Serial #], [XRS-3 Repair Date Range Query].[Date in];
Code:
SELECT Sum(IIf([Date Diff] Between 1 And 90,1,0)) AS [under 90 days], Sum(IIf([Date Diff] Between 91 And 180,1,0)) AS [3 to 6 Months], Sum(IIf([Date Diff] Between 181 And 365,1,0)) AS [6 Months to 1 Year], Sum(IIf([Date Diff] Between 366 And 730,1,0)) AS [1 to 2 Years], Sum(IIf([Date Diff]>730,1,0)) AS [2 Plus years]
FROM [XRS-3 Repair Date Diff];
The date diff query runs within 2 seconds regardless of date range.
when I run the date diff sum query it take around 16 seconds to return results fora 1 year period and over 30 second to return results over a 2 year period. :banghead:
I suspect there is some looping back through every record in the select date range query for every sum statement thingy going on, or worse, even going back to the table for every query term in every layer of queries. how do I not do this?
do I need to make a "virtual" table of the query output for the next query to look at?
and how?
Last edited: