complicated query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to find all records where two fields have changed by 10 percent
year to date

I have weekly data of 40,000 unique names my table looks like this
date -element- subs- utilization
every element is time stamped with a date that changes once a week i want to
filter out and count the element field where the subs field and the
utilization field has changed by a 10 percent

any ideas would be appreciated
 
Dave said:
I am trying to find all records where two fields have changed by 10 percent
year to date

I have weekly data of 40,000 unique names my table looks like this
date -element- subs- utilization
every element is time stamped with a date that changes once a week i want to
filter out and count the element field where the subs field and the
utilization field has changed by a 10 percent
Click to expand...


If the dates for each element are exactly 7 days from one
to the next, then you can use something like:

SELECT T.date, T.element, T.subs, T.utilization,
P.subs As Psubs, P.utilization As Putil
FROM table As T INNER JOIN table As P
ON T.date = P.date+7
And T.element = P.element
WHERE Abs(T.subs - P.subs) > .1 * P.subs
OR Abs(T.utilization - P.utilization) > .1 * P.utilization
 
thanks I can make them exactly one week apart

here is my exact layout i was unclear as to what the t.date and the
p.whatever meant I figured the t was my table

SELECT [US-DS].Date, [US-DS].Element, [US-DS].Subs, [US-DS].[98th_w/DOCSIS]
FROM [US-DS]
WITH OWNERACCESS OPTION;
 
You can use an expression to calculate the Sunday of any
date's week:
dt+1 - Weekday(dt)
so you do not have to change the data in the table (might be
faster if you do??).

The T an P in my suggested query are both aliases for your
data table/query and are required because the query is
joining two copies of the same table/query (in the FROM
clause).

Using your field names and the date calculation above, the
query would be:

SELECT T.date, T.Element, T.Subs, T.[98th_w/DOCSIS],
P.SubsAs Psubs, P.[98th_w/DOCSIS] As Putil
FROM table As T INNER JOIN table As P
ON (T.date+1-WeekDay(T.date)) = (P.date+8-WeekDay(P.date))
And T.Element = P.Element
WHERE Abs(T.Subs- P.Subs) > .1 * P.Subs
OR Abs(T.[98th_w/DOCSIS] - P.[98th_w/DOCSIS])
.1 * P.[98th_w/DOCSIS]
Click to expand...

I still don't know the name of the query you posted so you
need to replace my use of "table" with the name you are
using.
--
Marsh
MVP [MS Access]

thanks I can make them exactly one week apart

here is my exact layout i was unclear as to what the t.date and the
p.whatever meant I figured the t was my table

SELECT [US-DS].Date, [US-DS].Element, [US-DS].Subs, [US-DS].[98th_w/DOCSIS]
FROM [US-DS]
WITH OWNERACCESS OPTION;

Marshall Barton said:
If the dates for each element are exactly 7 days from one
to the next, then you can use something like:

SELECT T.date, T.element, T.subs, T.utilization,
P.subs As Psubs, P.utilization As Putil
FROM table As T INNER JOIN table As P
ON T.date = P.date+7
And T.element = P.element
WHERE Abs(T.subs - P.subs) > .1 * P.subs
OR Abs(T.utilization - P.utilization) > .1 * P.utilization
Click to expand...
Click to expand...
 
Marshall,

that is so cool , of a table consisting of 600,000 entries and 30 weeks i
have narrowed down the changes to 4000 elements, I noticed there was an OR
statement between subs and util if i want to include both which is what i did
would AND work ok?
SELECT T.date, T.Element, T.Subs, T.[98th_w/DOCSIS],P.Subs As Psubs,
P.[98th_w/DOCSIS] As Putil
FROM [US-DS] As T INNER JOIN [US-DS] As P ON (T.date+1-WeekDay(T.date)) =
(P.date+8-WeekDay(P.date)) And T.Element = P.Element
WHERE Abs(T.Subs- P.Subs) > .2 * P.Subs AND Abs(T.[98th_w/DOCSIS] -
P.[98th_w/DOCSIS])> .2 * P.[98th_w/DOCSIS]

thanks so much

Marshall Barton said:
You can use an expression to calculate the Sunday of any
date's week:
dt+1 - Weekday(dt)
so you do not have to change the data in the table (might be
faster if you do??).

The T an P in my suggested query are both aliases for your
data table/query and are required because the query is
joining two copies of the same table/query (in the FROM
clause).

Using your field names and the date calculation above, the
query would be:

SELECT T.date, T.Element, T.Subs, T.[98th_w/DOCSIS],
P.SubsAs Psubs, P.[98th_w/DOCSIS] As Putil
FROM table As T INNER JOIN table As P
ON (T.date+1-WeekDay(T.date)) = (P.date+8-WeekDay(P.date))
And T.Element = P.Element
WHERE Abs(T.Subs- P.Subs) > .1 * P.Subs
OR Abs(T.[98th_w/DOCSIS] - P.[98th_w/DOCSIS])
> .1 * P.[98th_w/DOCSIS]

I still don't know the name of the query you posted so you
need to replace my use of "table" with the name you are
using.
--
Marsh
MVP [MS Access]

thanks I can make them exactly one week apart

here is my exact layout i was unclear as to what the t.date and the
p.whatever meant I figured the t was my table

SELECT [US-DS].Date, [US-DS].Element, [US-DS].Subs, [US-DS].[98th_w/DOCSIS]
FROM [US-DS]
WITH OWNERACCESS OPTION;

Marshall Barton said:
Dave wrote:

I am trying to find all records where two fields have changed by 10 percent
year to date

I have weekly data of 40,000 unique names my table looks like this
date -element- subs- utilization
every element is time stamped with a date that changes once a week i want to
filter out and count the element field where the subs field and the
utilization field has changed by a 10 percent


If the dates for each element are exactly 7 days from one
to the next, then you can use something like:

SELECT T.date, T.element, T.subs, T.utilization,
P.subs As Psubs, P.utilization As Putil
FROM table As T INNER JOIN table As P
ON T.date = P.date+7
And T.element = P.element
WHERE Abs(T.subs - P.subs) > .1 * P.subs
OR Abs(T.utilization - P.utilization) > .1 * P.utilization
Click to expand...
Click to expand...
Click to expand...
 
600,000 records? How was the query's performance? I
suspect it might not be acceptable because the Join
condition date expression precludes taking advantage of an
index on the date field. If the query is too slow, then you
could make a backup of the table and use an UPDATE query to
change all the dates to their corresponding Sunday. This
would allow the Join condidition to be simplified to date =
date + 7 and maybe indexing the element and date fields will
improve performance.

You can use AND in the WHERE clause if you only want to find
records where the difference is greater than 20% in BOTH
Subs and [98th_w/DOCSIS]. I thought you said you wanted to
select weeks where either one was > 20% Use whichever one
meets your needs.
--
Marsh
MVP [MS Access]

that is so cool , of a table consisting of 600,000 entries and 30 weeks i
have narrowed down the changes to 4000 elements, I noticed there was an OR
statement between subs and util if i want to include both which is what i did
would AND work ok?
SELECT T.date, T.Element, T.Subs, T.[98th_w/DOCSIS],P.Subs As Psubs,
P.[98th_w/DOCSIS] As Putil
FROM [US-DS] As T INNER JOIN [US-DS] As P ON (T.date+1-WeekDay(T.date)) =
(P.date+8-WeekDay(P.date)) And T.Element = P.Element
WHERE Abs(T.Subs- P.Subs) > .2 * P.Subs AND Abs(T.[98th_w/DOCSIS] -
P.[98th_w/DOCSIS])> .2 * P.[98th_w/DOCSIS]


Marshall Barton said:
You can use an expression to calculate the Sunday of any
date's week:
dt+1 - Weekday(dt)
so you do not have to change the data in the table (might be
faster if you do??).

The T an P in my suggested query are both aliases for your
data table/query and are required because the query is
joining two copies of the same table/query (in the FROM
clause).

Using your field names and the date calculation above, the
query would be:

SELECT T.date, T.Element, T.Subs, T.[98th_w/DOCSIS],
P.SubsAs Psubs, P.[98th_w/DOCSIS] As Putil
FROM table As T INNER JOIN table As P
ON (T.date+1-WeekDay(T.date)) = (P.date+8-WeekDay(P.date))
And T.Element = P.Element
WHERE Abs(T.Subs- P.Subs) > .1 * P.Subs
OR Abs(T.[98th_w/DOCSIS] - P.[98th_w/DOCSIS])
> .1 * P.[98th_w/DOCSIS]

I still don't know the name of the query you posted so you
need to replace my use of "table" with the name you are
using.

thanks I can make them exactly one week apart

here is my exact layout i was unclear as to what the t.date and the
p.whatever meant I figured the t was my table

SELECT [US-DS].Date, [US-DS].Element, [US-DS].Subs, [US-DS].[98th_w/DOCSIS]
FROM [US-DS]
WITH OWNERACCESS OPTION;

:

Dave wrote:

I am trying to find all records where two fields have changed by 10 percent
year to date

I have weekly data of 40,000 unique names my table looks like this
date -element- subs- utilization
every element is time stamped with a date that changes once a week i want to
filter out and count the element field where the subs field and the
utilization field has changed by a 10 percent


If the dates for each element are exactly 7 days from one
to the next, then you can use something like:

SELECT T.date, T.element, T.subs, T.utilization,
P.subs As Psubs, P.utilization As Putil
FROM table As T INNER JOIN table As P
ON T.date = P.date+7
And T.element = P.element
WHERE Abs(T.subs - P.subs) > .1 * P.subs
OR Abs(T.utilization - P.utilization) > .1 * P.utilization
Click to expand...
Click to expand...
Click to expand...
 
Back
Top