views:

19

answers:

0

I have read so many posts and tried so many alternatives but I just can't work out the best way to achieve the following :

I have four tables, issues, issuestatus, customers and customerupdates

Issues

IssueID
StatusID
CustomerID
IssueDetails

IssueStatus

StatusID
Status

Customers

CustomerID
CustomerName

CustomerUpdates

UpdateID
IssueID
UpdateDetails
Update

I want to produce a query that returns by Customer, total number of open issues, and number of issues without an update in last 2 days.

I know it would be easier to add a field to the Issue table that got updated when a new CustomerUpdate was added by unfortunately I can't change the front end that adds a new update, nor can I alter the table structure to add more fields.

Any help anyone can give would be great as this is driving me crazy.