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.