SQL query, correct way of writing

[Arg81]

Hey guys,

Here's a quick table layout;

Parent Table
ID No

Child Table
ID No, Rev No, RequiredDate

Basically the child table will have matching ID No, and then Rev No would be 1, 2, 3, 4 etc etc to infinity (normally 10!!) - this is programmatically assigned (looks at the last Rev No for that ID No, then + 1 to it)

What I need to do, is select all ID Numbers from the parent table that have a RequiredDate < next 7 days for the latest Rev No (the highest).
This is so I can create a popup to the user to tell them what ID Numbers need working on as their RequiredDate is less than a week away.

Not sure how to properly write this out, I'm hoping it can be done in one simple SQL query, so that the results look like;

ID No RequiredDate
27 - 10/01/07
54 - 12/01/07
135 - 14/01/07


The issue I have at the moment when trying to do this, is that all previous Rev No for the ID have a requiredDate, but obviously I need the latest and only the latest Rev No, and all other Rev No for that ID to be ignored....

Any help much appriciated, thanks.
Luke

 

[cjard]

So there is nothing to say whether work has been done or not?

Also, we have one of two states for the Rev Date:

Null, if no feedback has been received - which means this work cannot be done because it is never due
A date - which is what we test...

..but because there can be no two revisions with two null received dates, we dont really care about the revision number.. We can work solely off the max due date..

SELECT ... FROM ... GROUP BY ID HAVING MAX(due_date) >= now AND MAX(due_date) <= DATEADD(day,7,now)



Again, it's a bit intensive because it needs a sort/group of the data every time.. And its a bit difficult to see what's where, done etc, because there is no way of tracking whether it's done or not

 

[Arg81]

I use an Outlook email addin which sends an email to the Account Manager and Technician at each stage - i.e. when a new project starts, when work has been done and when feedback has been added. The email lists all the fields the user has entered, and this is the way that Account Managers track the work done and when they need to add feedback (Obviously the customer needs to have given the AM feedback before he / she enters it)

It's a weird way of tracking I know. but the old system was in Lotus 95 and wasn't even relational. The information was entered a month after the project was completed, so there was no "real time" interaction at all with the system...

 

[cjard]

Note.. info added by editing the post