StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>This is where I find CTEs (Common Table Expressions) useful. They allow you to break the problem apart into steps that you can easily solve. Let's apply that here.</p> <p>First, get the max status for each service/day:</p> <pre><code> SELECT CatID, SvcID, MAX(StatTypeID) As MaxStatus FROM v_StatusEvents WHERE EventTimeStamp >= cast(cast(current_timestamp as Date) as datetime) GROUP BY CatID, SvcID </code></pre> <p>Now that we have this information, we can find that most recent time today that each of these events occured:</p> <pre><code>WITH StatusInfo As ( SELECT CatID, SvcID, MAX(StatTypeID) As MaxStatus FROM v_StatusEvents WHERE EventTimeStamp >= cast(cast(current_timestamp as Date) as datetime) GROUP BY CatID, SvcID ) SELECT se.CatID, se.SvcID, se.StatTypeID, MAX(EventTimeStamp) As EventTimeStamp FROM v_StatusEvents se INNER JOIN StatusInfo si ON se.CatID = si.CatID AND se.SvcID = si.SvcID AND se.StatTypeID = si.MaxStatus WHERE EventTimeStamp >= cast(cast(current_timestamp as Date) as datetime) </code></pre> <p>You might choose instead to use the sequence ID to narrow it down here, in case you could have two events with the same timestamp for a service. Now that we have this information, we can go back to the table one more time to pick up any other fields we might want (in this case, sequence ID):</p> <pre><code>WITH StatusInfo As ( SELECT CatID, SvcID, MAX(StatTypeID) As MaxStatus FROM v_StatusEvents WHERE EventTimeStamp >= cast(cast(current_timestamp as Date) as datetime) GROUP BY CatID, SvcID ), StatusAndTimeInfo As ( SELECT se.CatID, se.SvcID, se.StatTypeID, MAX(EventTimeStamp) As EventTimeStamp FROM v_StatusEvents se INNER JOIN StatusInfo si ON se.CatID = si.CatID AND se.SvcID = si.SvcID AND se.StatTypeID = si.MaxStatus WHERE EventTimeStamp >= cast(cast(current_timestamp as Date) as datetime) ) SELECT se.* FROM v_StatusEvents se INNER JOIN StatusAndTimeInfo sati ON se.CatID = sati.CatID AND se.SvcID = sati.SvcID AND se.StatTypeID = sati.StatTypeID AND se.EventTimeStamp = sati.EventTimeStamp WHERE EventTimeStamp >= cast(cast(current_timestamp as Date) as datetime) </code></pre> <p>Note again that you might prefer to use the SeID (which I presume is a sequence ID) on this last iteration rather than timestamp. Note also that this is NOT the only way to solve this problem, or even likely the fastest. In fact, it would be possible to re-write this using only subqueries or joins. But this is any easy method you can use to get something that works, and can be easily understood later.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload