Query that compares a fields value with the same field over last 5 days

colmkav

unread,

May 24, 2012, 7:06:47 AM5/24/12

to

Hi,

I want to writue a query that takes results of a previous query and
wants to return all those with a value of 0 but where the previous
last 5 records of that field have no zero values.

How should I approach this? Can it be done in a query?

thx
Colm

Bob Barrows

unread,

May 24, 2012, 7:10:52 AM5/24/12

to

I think I understand what you want, but I'd rather be sure. Could you show
us some sample output from the first query (in tabular format, ommitting the
irrelevant columns from your sample - just show enough rows to illustrate
the problem), and show us what your desired output would be from the sample
(again in tabular format)?

colmkav

unread,

May 24, 2012, 7:29:54 AM5/24/12

to

This is an example of what the input table/query results look like

Client-id AvgCreated transactions (number) Created transactions
(number) SumOfTrx EUR amount Processing date
321 61.25 37 3323.764519 10-May-12
321 61.25 64 3368.360543 11-May-12
321 61.25 70 2089.86874 12-May-12
321 61.25 8 319.7241606 13-May-12
321 61.25 55 4633.419834 14-May-12
321 61.25 40 5408.707927 15-May-12
321 61.25 25 3101.684803 16-May-12
321 61.25 119 8539.954168 17-May-12
321 61.25 165 4005.622306 18-May-12
321 61.25 9 447.8219273 19-May-12
321 61.25 8 457 20-May-12
321 61.25 75 8387.034963 21-May-12
321 61.25 43 1224.691638 22-May-12
321 61.25 0 4351.174432 23-May-12

The output for this after the query would be

321 61.25 0 4351.174432 23-May-12

Because the value of created trans on 23 May was 0 but the previous 5
days had values

Bob Barrows

unread,

May 24, 2012, 8:45:29 AM5/24/12

to

Good thing I asked. Hmmm, I assume your query is set up to provide a record
for each client-id for each day with no gaps. I will also assume that
[Processing date] is a Date/Time field rather than Text. If so, something
like this should suit:

select Client-id,[AvgCreated transactions], [Created transactions],
[SumOfTrx EUR amount], [Processing date]
FROM savedquery as oq
WHERE [Created transactions]=0 AND NOT EXISTS
(SELECT * FROM savedquery WHERE Client-id=oq.Client-id and
[Processing date] >= oq.[Processing date] - 5 and
[Processing date] < oq.[Processing date]
and [Created transactions] = 0)

If [Processing date] is not Date/Time, you will need to convert it to that
datatype before doing the date arithmetic:
CDate([Processing date]) - 5

colmkav

unread,

May 25, 2012, 10:24:30 AM5/25/12

to

> CDate([Processing date]) - 5- Hide quoted text -
>
> - Show quoted text -

Thanks. I have tried to recreate this and have this:

SELECT *
FROM [Flow patterns - 2] AS SQ
WHERE ( [SumOfSumOfCreated transactions (number)]=0 AND NOT EXISTS
( SELECT * FROM [Flow patterns - 2]
WHERE ( SQ.[Client-id]=[Flow patterns - 2].[Client-id] AND
[Processing date] >= (SQ.[Processing date] - 5) and
[Processing date] < SQ.[Processing date]
and [SumOfSumOfCreated transactions (number)] = 0) ) );

Only thing is that it initially looks like its going to execute
quickly (SQL query bar reaches the end) but then it hangs there for
28mins before any results appear. Then it seems to have a problem in
me doing any kind of filters on the results (ie it freezes for about
10minutes). There are about125 final results. Any explanation why this
might be?

colmkav

unread,

Jun 1, 2012, 9:30:12 AM6/1/12

to

On May 24, 2:45 pm, "Bob Barrows" <reb01...@NOyahooSPAM.com> wrote:

> CDate([Processing date]) - 5- Hide quoted text -
>
> - Show quoted text -

Hi again,

thanks for your help so far. Regarding the gap in dates. There are
unfortunately some clients that do have missing dates and so these
should be treated as 0 for those dates. Can you think of a query to
overcome this issue? Currently the queries ignores them as so treats
them the same as if there were transactions on those dates.

regards
Colm