Databases
StackOverflow2013
Postsdbo
POQuery Based on Current Status of a Sub Item
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POQuery Based on Current Status of a Sub Item
    text
    copied!<p>Trying to write a query to display the current status of services in a company department. What I have written is just not working. I feel like I am going in the wrong direction.</p> <pre><code>SELECT MAX(v_StatusEvents.EventTimeStamp) as EventTimeStamp , MAX(v_StatusEvents.StatusTypeID) as StatusTypeID , v_StatusEvents.ServiceID , v_StatusEvents.StatusTypeDescription , v_StatusEvents.ServiceName , v_StatusEvents.CategoryName FROM v_StatusEvents WHERE v_StatusEvents.CategoryID = 100 AND YEAR(v_StatusEvents.EventTimeStamp) = YEAR(getdate()) AND MONTH(v_StatusEvents.EventTimeStamp) = MONTH(getdate()) AND DAY(v_StatusEvents.EventTimeStamp) = DAY(getdate()) GROUP BY v_StatusEvents.ServiceID , v_StatusEvents.StatusTypeDescription , v_StatusEvents.ServiceName , v_StatusEvents.CategoryName </code></pre> <p>I have three CATEGORIES: (100 - Internet, 101 - Applications, and 102 - Network).<br> Each CATEGORY contains SERVICES.<br> As an example, I have three SERVICES that belong to the CATEGORY Interenet: (50 - Internal, 51 - External, 52 - Development).<br> Each SERVICE will always have at least one status record for the current date.<br> The CURRENT STATUS will be set to one of three different STATUS TYPES values: 1 = no issue, 2 = disruption, 3 = critical. </p> <p>I want to show the highest STATUS TYPE for each category for today.</p> <p>Here is a sample record set for today's date.</p> <pre><code>SeID CatID EventTimeStamp SvcID StatTypeID 201 100 11/11/2012 12:01am 52 1 202 100 11/11/2012 12:01am 51 1 203 100 11/11/2012 12:01am 50 1 204 100 11/11/2012 08:00am 51 3 205 100 11/11/2012 10:50am 50 2 206 100 11/11/2012 11:00am 50 1 207 100 11/11/2012 11:25am 52 2 </code></pre> <p>As you can see, there was a disruption problem with the Internal web site at 10:50m, but it was resolved at 11:00am. </p> <p>There is an ongoing critical issue with the External web site that has not yet been resolved. I would like the for the query to return the value 3 because this is the highest CURRENT STATUS for a SERVICE that has not been resolved. </p> <p>(If all services had "no issue", I would expect the query to return the value 1)</p> <p>Thanks,<br> crjunk</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