I'm trying to create a report in SQL Server Reporting Services that shows the availability of multiple sites. The sites are monitored by a tool that uses a SQL Server 2012 database to store the data.
I've created a query that shows me the results and I'm wondering if I can rewrite this query in a single select without the subqueries.
My actual query looks like
SELECT t1.CHECKID ,t1.CHECKNAME ,t2.Startdate ,t2.Enddate ,t2.StatusID ,t2.Month ,t2.Year FROM (SELECT CHECKID, CHECKNAME FROM CHECKS ) AS t1 LEFT JOIN (SELECT STATUSCHANGES.CHECKID , STATUSCHANGES.DATETIME AS Startdate , MIN(STATUSCHANGES_1.DATETIME) AS Enddate , STATUSCHANGES.STATID AS StatusID , CHECKS.CHECKNAME , STATUS.STATUSNAME , DATEPART(MONTH,STATUSCHANGES.DATETIME) as Month , DATEPART(YEAR,STATUSCHANGES.DATETIME) as Year FROM STATUSCHANGES INNER JOIN STATUSCHANGES AS STATUSCHANGES_1 ON STATUSCHANGES.CHECKID = STATUSCHANGES_1.CHECKID AND STATUSCHANGES.DATETIME < STATUSCHANGES_1.DATETIME INNER JOIN CHECKS ON STATUSCHANGES_1.CHECKID = CHECKS.CHECKID INNER JOIN STATUS ON STATUSCHANGES.STATID = STATUS.STATUSID GROUP BY STATUSCHANGES.CHECKID, STATUSCHANGES.DATETIME, STATUSCHANGES.STATID, CHECKS.CHECKNAME, STATUS.STATUSNAME HAVING (STATUSCHANGES.STATID IN (3)) AND DATEPART(Q,STATUSCHANGES.DATETIME) = 2 ) AS t2 ON t1.CHECKID = t2.CHECKID ORDER BY t2.Startdate
Is it possible to integrate the first subquery
t1 in the from clause of subquery