Comparing values on consecutive dates to return a table 2

[GoatieEddie]

Hi all,

I think this should be easy but just think where to start!

I have a VERY large file that includes a bunch of clients and the daily value of their account. So three columns: CLIENT, DATE, VALUE

For various reasons, the extract isn't run each day so the dates will not be consecutive.

What I want to do is pull out the various occurrences when the difference in value of one client from one date to the previously loaded date is greater than a certain amount.

eg Client A date1 - Client A nearest date to date1 >100

I want to loop the whole client base and time base and end up with a table showing CLIENT DATE DIFFERENCE. Clients could appear more than once in this table.

I am pretty good at loops etc but don't really know where to start with the comparisons within a database. The file is far too big for excel otherwise I would do it in there!

Can someone start me off in the right direction?

Many thanks,
GE

 

[MajP]

No loops needed. Build a query. Something like this

SELECT A.strCode, A.dtmStartDate, B.dtmStartDate
FROM tblCheckDates AS A INNER JOIN tblCheckDates AS B ON A.strCode = B.strCode
WHERE (((A.dtmStartDate)<([b].[dtmStartDate]-100)))
ORDER BY A.strCode, A.dtmStartDate, B.dtmStartDate;

 

[GoatieEddie]

Thanks MajP but

Re-reading my explanation I think I may not have been clear re what I am comparing

Perhaps better with an example

For each day there is a record for a client, I want to compare the value column with the value from the nearest previous date and check for it being over a certain amount/

e.g.

Client Date Value
A 1/1 1000
A 2/1 1010
A 10/1 1200
B 1/1 1000
B 2/1 1150
B 10/1 1160

For Client A: It would check 2/1 against 1/1 and return nothing. But it would then check 10/1 against 2/1 (the nearest previous date) and return A 10/1 190 (ie 1200-1010)
In this example it would also retun B 2/1 1150

It's not knowing when the previous date is (as I mentioned it isn't always currentdate-1) that is throwing me.

Thanks in advance.

 

[MajP]

Yeah, I misread what you where doing. I thought you wanted to return all entries prior to a given date within a set amount of time.

I am kind of an idiot when it comes to queries, but I know this can be done in one query. You might want to post in there. I did it, but it was a little ugly. Not hard, but ugly.

my first query returns every record and all records prior to it. Kind of like before.

SELECT A.strCode, A.dtmStartDate, B.dtmStartDate, A.dtmStartDate-B.dtmStartDate AS DateDiff, A.intValue, B.intValue
FROM tblCheckDates AS A INNER JOIN tblCheckDates AS B ON A.strCode = B.strCode
WHERE (((A.dtmStartDate)>([b].[dtmStartDate])))
ORDER BY A.strCode, A.dtmStartDate;

My second table takes the minimum date differences to find the amount of time to the closest previous date

SELECT qryPriorDates.strCode, qryPriorDates.A.dtmStartDate, Min(qryPriorDates.DateDiff) AS MinOfDateDiff
FROM qryPriorDates
GROUP BY qryPriorDates.strCode, qryPriorDates.A.dtmStartDate;

My third query then joins the minimum time to the original query to return only the prior date. And I calculate the value difference here

SELECT qryPriorDates.strCode, qryPriorDates.A.dtmStartDate, qryPriorDates.B.dtmStartDate, [qryPriordates].[a.intvalue]-[qryPriorDates].[b.intvalue] AS ValDiff
FROM qryPriorDates INNER JOIN qryMinDateDiff ON (qryPriorDates.DateDiff = qryMinDateDiff.MinOfDateDiff) AND (qryPriorDates.strCode = qryMinDateDiff.strCode) AND (qryPriorDates.A.dtmStartDate = qryMinDateDiff.dtmStartDate)
WHERE ((([qryPriordates].[a.intvalue]-[qryPriorDates].[b.intvalue])>100))

The result is a table that shows every date that the previous value was 100 less.

Sql is the way to go with this, you just need someone who can code it better.

 

[GoatieEddie]

Thanks a lot for the reply. I will check it out tomorrow and use the other forum if need be. If it works or comes close I will even come back and give you a star!

thanks
GE

 

[MajP]

Post over there in the queries forum. There are some people that can do anything with SQL. I am a pretty good coder, but it is almost always much faster and efficient in SQL than to loop.

 

[PHV]

Something like this (SQL code):
SELECT Q.Client, Q.Date, Q.Value, Q.PreviousDate, T.Value AS PreviousValue
FROM (SELECT A.Client, A.Date, A.Value, Max(B.Date) AS PreviousDate
FROM tblAccounts AS A INNER JOIN tblAccounts AS B ON A.Client = B.Client AND A.Date > B.Date
GROUP BY A.Client, A.Date, A.Value
) AS Q INNER JOIN tblAccounts AS T ON Q.Client = T.Client AND Q.PreviousDate = T.Date
WHERE Abs(Q.Value-T.Value)>100

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MajP]

Like I said, the smart guys can do it in one query. That one is beyond me.