Hello, I need some help with a SQL Server Query in Microsoft SQL Server 2008.
I have two tables, one with device information, and the other with device history. The device history is keyed to the device information id column.
In the devicehistory table I have 4 columns: id, compid, startalarmdate, endalarmdate. I have a script in my application that adds an entry when one of my devices goes into alarm, it puts in the id of the device into the compid column. It also writes the current datetime in the startalarmdate column and leaves the endalarmdate column null. when the device goes back to normal state it runs an update query and updates the endalarmdate column from null to the current datetime.
What I need is to build a query that will use the information that I have stored in both tables to display a downtime by day for each device. I envision having the name of the device in the first column of the table, then a total downtime for the device for the month, then to the right of that have each day of the month as a column. Days that have no downtime will need to have null or zero value for that day. Also, the downtime will be displayed in hour format with a decimal, ex 2.2 hours.
The tricky part is that some of my devices will be in alarm for multiple days and obviously will have a null value in the endalarmdate. The query will have to recognize that and fill in the appropriate amount of downtime.
If necessary I can export the tables and send to you if that would be easier.
To start I have exported out the 2 tables to csv.