quote:

---

Originally posted by longdiver

Subject: How to get latest record for each item per day when there are multiple entries per day?

Hi,

This is my first post on sqlteam.com.

I've been trying to work out this problem for awhile, and figured I really need help.

I am working with a legacy defect tracking system that has limited reporting capabilities. I am trying to create a dashboard view of historical defect status over the lifetime of the data by calling a SQL query from Excel. The data returned from the query will be used to present a line graph of the historical defect status count (count of defects per status type per day) over the lifetime of the selected project.

The table containing the data (issuehistory) contains the following mandatory fields:
issueid, projected, history, created.

An example of the issuehistory table would be like this:

historyid |issueid |projectid |history |created
1 |1 |1 |status changed - Created |31/01/2008 18:32:46
2 |1 |1 |Status changed - Created |31/01/2008 18:33:40
3 |2 |2 |status changed - Created |31/01/2008 21:52:40
4 |1 |1 |Status changed – fixed |01/02/2008 21:22:40
5 |1 |1 |status changed - closed |01/21/2008 22:52:22
6 |3 |1 |status changed - Created |01/02/2008 11:18:59
7 |3 |1 |Status changed - fixed |16/03/2008 12:40:01
8 |4 |2 |status changed - Created |01/02/2008 11:18:59
9 |4 |2 |Status changed - fixed |16/03/2008 12:40:01

Now what I want is the latest record for each issueid per day to be returned.
NOTE: an IssueID could be updated multiple times each day - Only the last updated record (for each issueid) should be returned for the day that there is multiple data entries.

Example:
IssueID 1 has two status (history) updates on 31/01/2008.
The SQL query would return the data for the last status update for issuedID 1 on the 31/01/2008.
The above SQL should be recursive so that it is applied to all issueID's for each date.

So my expected results would be:

historyid |issueid |projectid |history |created
2 |1 |1 |Status changed - Created |31/01/2008 18:33:40
3 |2 |2 |status changed - Created |31/01/2008 21:52:40
5 |1 |1 |status changed - closed |01/21/2008 22:52:22
6 |3 |1 |status changed - Created |01/02/2008 11:18:59
7 |3 |1 |Status changed - fixed |16/03/2008 12:40:01
8 |4 |2 |status changed - Created |01/02/2008 11:18:59
9 |4 |2 |Status changed - fixed |16/03/2008 12:40:01

To make matters more complex I also need to be able to select data for a specific project (projectid). The selection of the project will be performed in Excel and this value will be used as a parameter in the SQL query.

So using the above example but this time also specifying that I only want data returned for Projectid=1 my expected results would be:

historyid |issueid |projectid |history |created
2 |1 |1 |Status changed - Created |31/01/2008 18:33:40
5 |1 |1 |status changed - closed |01/21/2008 22:52:22
6 |3 |1 |status changed - Created |01/02/2008 11:18:59
7 |3 |1 |Status changed - fixed |16/03/2008 12:40:01


To date I have the created the following SQL query that pulls back the last status (history) value for each issueID.

SELECT  issueid,projectid,history,created
FROM
(SELECT    ROW_NUMBER() OVER ( PARTITION BY issueid 
 ORDER BY created Desc ) AS rn,
      	issueid, projectid, history, created
            FROM gemini_issuehistory
        ) a
WHERE   rn = 1

;

I am now reaching my limits of SQL knowledge and would appreciate any help.

Additional Information:
Database: SQL 2005
Excel: 2003

Thanks in advance.

---