Retrieving a piece of datalogged equipment by most recent time

The TSQL below all works except the bolded part at the end. I'm want to grab only the most recently logged piece of equipment not the most recent and all past ones as well which is what I've got doing minus the bolded part below. But I don't know how to say get this Equipment ID etc and only the most recently logged one to find its present location. The bolded part below is just there to show what I want it to do I know you can use an aggregate in a where clause. So in the first table listed tblRdrLog there is a column Time that I want to do this on so a.Time. I don't want to display a.Time just reference.

String dbsql = " SELECT a.EquipmentID " +
" , f.Subcategory " +
" , c.Area " +
" , d.Room " +
" FROM tblRdrLog a " +
" JOIN tblRdrInfo b ON a.ReaderID = b.ReaderID " +
" JOIN tblRdrArea c ON b.AreaID = c.AreaID " +
" JOIN tblRdrRm d ON b.RoomID = d.RoomID " +
" JOIN tblEquipInfo e ON a.EquipmentID = e.EquipmentID " +
" JOIN tblEquipSubcat f ON e.SubcategoryID = f.SubcategoryID " +
" WHERE a.EquipmentID IN (SELECT a.EquipmentID " +
" FROM tblEquipInfo a " +
" JOIN tblEquipCat b ON a.CategoryID = b.CategoryID " +
" JOIN tblEquipSubcat c ON a.SubcategoryID = c.SubcategoryID " +
" LEFT OUTER JOIN tblEquipMake d ON a.MakeID = d.MakeID " +
" LEFT OUTER JOIN tblEquipModel e ON a.ModelID = e.ModelID " +
" JOIN tblStatus f ON a.StatusID = f.StatusID " +
" WHERE b.CategoryID = '" + this.ddlCategory.SelectedValue.ToString() + "' ";

if (!"".Equals(this.ddlSubcategory.SelectedValue.ToString()))
dbsql += " AND c.SubcategoryID = '" + this.ddlSubcategory.SelectedValue.ToString() + "' ";

#region Advanced Search Criteria

// Check whether advanced search submitted
if (adv)
{
if (!"".Equals(this.tbSerialNo.Text.ToString()))
dbsql += " AND a.SerialNo = '" + this.tbSerialNo.Text.ToString() + "' ";
if (!"".Equals(this.ddlMake.SelectedValue.ToString()))
dbsql += " AND d.MakeID = '" + this.ddlMake.SelectedValue.ToString() + "' ";
if (!"".Equals(this.ddlModel.SelectedValue.ToString()))
dbsql += " AND e.ModelID = '" + this.ddlModel.SelectedValue.ToString() + "' ";
if (!"".Equals(this.ddlStatus.SelectedValue.ToString()))
dbsql += " AND f.StatusID = '" + this.ddlStatus.SelectedValue.ToString() + "' ";
}

#endregion

dbsql += " ) " +
" AND a.Time = max(a.Time) " +
"";

It is possible that I am missing something. The SQL is very hard to read in this format.

But, I think if you just add a TOP 1 to the first select so SELECT top 1 a.Equipment

and add ORDER BY a.TIME desc at the end.

That would give you the most recently logged item meeting your criteria.

|||

wow that's sweet.

two questions.

1) I'm getting the most currently logged item now but I'm only getting one piece of equipment when I should be getting two if I just search by Category?

2) Do you have some sql that I can look at to tighten up my format. I've only be using SQL for a month or less. So this is all new and only going off what I've seen and read.

String dbsql = " SELECT TOP 1 a.EquipmentID " +
" , f.Subcategory " +
" , c.Area " +
" , d.Room " +
" , a.Time " +
" FROM tblRdrLog a " +
" JOIN tblRdrInfo b ON a.ReaderID = b.ReaderID " +
" JOIN tblRdrArea c ON b.AreaID = c.AreaID " +
" JOIN tblRdrRm d ON b.RoomID = d.RoomID " +
" JOIN tblEquipInfo e ON a.EquipmentID = e.EquipmentID " +
" JOIN tblEquipSubcat f ON e.SubcategoryID = f.SubcategoryID " +
" WHERE a.EquipmentID IN (SELECT a.EquipmentID " +
" FROM tblEquipInfo a " +
" JOIN tblEquipCat b ON a.CategoryID = b.CategoryID " +
" JOIN tblEquipSubcat c ON a.SubcategoryID = c.SubcategoryID " +
" LEFT OUTER JOIN tblEquipMake d ON a.MakeID = d.MakeID " +
" LEFT OUTER JOIN tblEquipModel e ON a.ModelID = e.ModelID " +
" JOIN tblStatus f ON a.StatusID = f.StatusID " +
" WHERE b.CategoryID = '" + this.ddlCategory.SelectedValue.ToString() + "' ";

if (!"".Equals(this.ddlSubcategory.SelectedValue.ToString()))
dbsql += " AND c.SubcategoryID = '" + this.ddlSubcategory.SelectedValue.ToString() + "' ";

#region Advanced Search Criteria

// Check whether advanced search submitted
if (adv)
{
// Protection from null pointers which can occur when using *.Equals("")
if (!"".Equals(this.tbSerialNo.Text.ToString()))
dbsql += " AND a.SerialNo = '" + this.tbSerialNo.Text.ToString() + "' ";

// Protection from null pointers which can occur when using *.Equals("")
if (!"".Equals(this.ddlMake.SelectedValue.ToString()))
dbsql += " AND d.MakeID = '" + this.ddlMake.SelectedValue.ToString() + "' ";

// Protection from null pointers which can occur when using *.Equals("")
if (!"".Equals(this.ddlModel.SelectedValue.ToString()))
dbsql += " AND e.ModelID = '" + this.ddlModel.SelectedValue.ToString() + "' ";

// Protection from null pointers which can occur when using *.Equals("")
if (!"".Equals(this.ddlStatus.SelectedValue.ToString()))
dbsql += " AND f.StatusID = '" + this.ddlStatus.SelectedValue.ToString() + "' ";
}

#endregion

dbsql += " ) " +
" ORDER BY a.Time desc " +
"";

|||

wow that's sweet.

two questions.

1) I'm getting the most currently logged item now but I'm only getting one piece of equipment when I should be getting two if I just search by Category?

so all soldering irons, function generators etc if they are currently being tracked should be returned with their most currently know location determined by time if Category is the only control used.

2) Do you have some sql that I can look at to tighten up my format. I've only be using SQL for a month or less. So this is all new and only going off what I've seen and read.

String dbsql = " SELECT TOP 1 a.EquipmentID " +
" , f.Subcategory " +
" , c.Area " +
" , d.Room " +
" , a.Time " +
" FROM tblRdrLog a " +
" JOIN tblRdrInfo b ON a.ReaderID = b.ReaderID " +
" JOIN tblRdrArea c ON b.AreaID = c.AreaID " +
" JOIN tblRdrRm d ON b.RoomID = d.RoomID " +
" JOIN tblEquipInfo e ON a.EquipmentID = e.EquipmentID " +
" JOIN tblEquipSubcat f ON e.SubcategoryID = f.SubcategoryID " +
" WHERE a.EquipmentID IN (SELECT a.EquipmentID " +
" FROM tblEquipInfo a " +
" JOIN tblEquipCat b ON a.CategoryID = b.CategoryID " +
" JOIN tblEquipSubcat c ON a.SubcategoryID = c.SubcategoryID " +
" LEFT OUTER JOIN tblEquipMake d ON a.MakeID = d.MakeID " +
" LEFT OUTER JOIN tblEquipModel e ON a.ModelID = e.ModelID " +
" JOIN tblStatus f ON a.StatusID = f.StatusID " +
" WHERE b.CategoryID = '" + this.ddlCategory.SelectedValue.ToString() + "' ";

if (!"".Equals(this.ddlSubcategory.SelectedValue.ToString()))
dbsql += " AND c.SubcategoryID = '" + this.ddlSubcategory.SelectedValue.ToString() + "' ";

#region Advanced Search Criteria

// Check whether advanced search submitted
if (adv)
{
// Protection from null pointers which can occur when using *.Equals("")
if (!"".Equals(this.tbSerialNo.Text.ToString()))
dbsql += " AND a.SerialNo = '" + this.tbSerialNo.Text.ToString() + "' ";

// Protection from null pointers which can occur when using *.Equals("")
if (!"".Equals(this.ddlMake.SelectedValue.ToString()))
dbsql += " AND d.MakeID = '" + this.ddlMake.SelectedValue.ToString() + "' ";

// Protection from null pointers which can occur when using *.Equals("")
if (!"".Equals(this.ddlModel.SelectedValue.ToString()))
dbsql += " AND e.ModelID = '" + this.ddlModel.SelectedValue.ToString() + "' ";

// Protection from null pointers which can occur when using *.Equals("")
if (!"".Equals(this.ddlStatus.SelectedValue.ToString()))
dbsql += " AND f.StatusID = '" + this.ddlStatus.SelectedValue.ToString() + "' ";
}

#endregion

dbsql += " ) " +
" ORDER BY a.Time desc " +
"";

|||

I'd need some more information.

So do you have multiple rows per EquipmentID in tblRdrLog and you just want the one row with the greatest time for each EquipmentID? (but the result can have 1 to many equipmentIDs)

If so, there are a couple of ways for you to do it. Can you answer one more question?

Does the time field change over the life of a row in tblRdrLog? Or is it just inserted when the row is created? I am asking because I am wondering if I can use the find the MAX(ReaderID) instead of the MAX(Time).

Ultimately it won't make a huge difference, but I want to give you the best solution for your problem.

|||

1) Ya, absolutely. There are multiple rows per EquipmentID in tblRdrLog... The search form has Category and Type controls... If they choose Electronics for a Category and don't choose the Type of equipment they are looking for should return all Electronic equipment by using only their most recently logged record.

(Datalogs items for adminstration use by just inserting to see log life of equipment or in this case for public institutional use only the most recent log is shown to display current positioning)

2) No, the Time for each row is static. Once a piece of equipment is logged a row is created and a time is inserted for all time.

|||

First, you should modify your code to use stored procedures or views and use parameterized commands. Your code currently is open to SQL injection attacks and it is very easy to break. Using a SP or view also provides some additional layer of security and it makes debugging the query easier. You can run your query in the backend and try various ways to solve the problem easily.

Lastly, for this issue it will be good if you can post some sample DDL (which shows constraints, indexes, references etc), data and expected results. It will then be easier to suggest the correct query. You have access to the data and know the expected results but it is often harder to articulate that to someone else who can't see it. So best is to post a simpler repro to get the correct solution.

|||

I think I would do something like this. (add one more join to a derived table)

SELECT a.EquipmentID " +
, f.Subcategory " +
, c.Area " +
, d.Room " +
FROM tblRdrLog a " +
JOIN tblRdrInfo b ON a.ReaderID = b.ReaderID " +
JOIN tblRdrArea c ON b.AreaID = c.AreaID " +
JOIN tblRdrRm d ON b.RoomID = d.RoomID " +
JOIN tblEquipInfo e ON a.EquipmentID = e.EquipmentID " +
JOIN tblEquipSubcat f ON e.SubcategoryID = f.SubcategoryID " +
JOIN (SELECT EquipmentID, MAX(ReaderID) FROM tblRdrLog GROUP BY EquipmentID) as LastEntry
ON a.READERID = LastEntry.ReaderID
...

|||sorry I'll post some more stuff. as for the rest I only know half of what your talking about. I tried to use stored procedures but couldn't figure them out and I'm under a time restraint so instead of panicing I just did the only way I could. I'm not sure what a DDL is? I'll post some stuff in a second.|||

so your trying to grab the most recent entry by the log tables primary key, ReaderID, that is associated with a specific EquipmentID? How come READERID is capped after ON at the bottom any specific reason?

I didn't know you could Join to a nested select what does that do for the results produced? or basically how does that work?

|||

Works great! Thanks for the help. I change it just a bit and added a nested select. I'm sure there is a much smaller cleaner way of doing this and I'd love to see it but now I can push on and come back to this if I have time and clean it up.

Thanks again, here's the last version.

String dbsql = " SELECT a.EquipmentID " +
" , f.Subcategory " +
" , c.Area " +
" , d.Room " +
" , a.Time " +
" FROM tblRdrLog a " +
" JOIN tblRdrInfo b ON a.ReaderID = b.ReaderID " +
" JOIN tblRdrArea c ON b.AreaID = c.AreaID " +
" JOIN tblRdrRm d ON b.RoomID = d.RoomID " +
" JOIN tblEquipInfo e ON a.EquipmentID = e.EquipmentID " +
" JOIN tblEquipSubcat f ON e.SubcategoryID = f.SubcategoryID " +
" JOIN (SELECT EquipmentID " +
" , MAX(LogID) AS LogID " +
" FROM tblRdrLog GROUP BY EquipmentID) AS LastEntry ON a.LogID = LastEntry.LogID " +
" WHERE a.EquipmentID IN (SELECT a.EquipmentID " +
" FROM tblEquipInfo a " +
" JOIN tblEquipCat b ON a.CategoryID = b.CategoryID " +
" JOIN tblEquipSubcat c ON a.SubcategoryID = c.SubcategoryID " +
" LEFT OUTER JOIN tblEquipMake d ON a.MakeID = d.MakeID " +
" LEFT OUTER JOIN tblEquipModel e ON a.ModelID = e.ModelID " +
" JOIN tblStatus f ON a.StatusID = f.StatusID " +
" WHERE b.CategoryID = '" + this.ddlCategory.SelectedValue.ToString() + "' ";

if (!"".Equals(this.ddlSubcategory.SelectedValue.ToString()))
dbsql += " AND c.SubcategoryID = '" + this.ddlSubcategory.SelectedValue.ToString() + "' ";

#region Advanced Search Criteria

// Check whether advanced search submitted
if (adv)
{
// Protection from null pointers which can occur when using *.Equals("")
if (!"".Equals(this.tbSerialNo.Text.ToString()))
dbsql += " AND a.SerialNo = '" + this.tbSerialNo.Text.ToString() + "' ";

// Protection from null pointers which can occur when using *.Equals("")
if (!"".Equals(this.ddlMake.SelectedValue.ToString()))
dbsql += " AND d.MakeID = '" + this.ddlMake.SelectedValue.ToString() + "' ";

// Protection from null pointers which can occur when using *.Equals("")
if (!"".Equals(this.ddlModel.SelectedValue.ToString()))
dbsql += " AND e.ModelID = '" + this.ddlModel.SelectedValue.ToString() + "' ";

// Protection from null pointers which can occur when using *.Equals("")
if (!"".Equals(this.ddlStatus.SelectedValue.ToString()))
dbsql += " AND f.StatusID = '" + this.ddlStatus.SelectedValue.ToString() + "' ";
}

#endregion

dbsql += " ) " +
"";