Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 t2?

share|improve this question

migrated from stackoverflow.com Jun 17 '13 at 13:32

This question came from our site for professional and enthusiast programmers.

1 Answer 1

It's hard without the tables and data to test on but I believe this will work for you. I added the subquery T1 as a RIGHT OUTER JOIN to T2, since I've moved the table to the "right" side of the query. I moved it to the right to avoid any issues with LEFT OUTER JOINing the whole rest of the query. I aliased it as RCHECKS (RIGHT OUTER JOIN version of CHECKS).

Also why do you have a HAVING clause? Those particular statements will work just fine in the WHERE clause and save you quite a bit of time. The HAVING clause is specifically for checks against aggregates. My guess is if you move the checks into the WHERE clause you query will go much faster. I've included a modified version of the query at the bottom using a WHERE clause and no subquery at all.

SELECT 
     t2.CHECKID
    ,t2.CHECKNAME
    ,t2.Startdate
    ,t2.Enddate
    ,t2.StatusID
    ,t2.Month
    ,t2.Year
FROM 
(SELECT        
        RCHECKS.CHECKID
        , RCHECKS.CHECKNAME
        , 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
    RIGHT OUTER JOIN CHECKS RCHECKS
        ON CHECKS.CHECKID = STATUSCHANGES.CHECKID
    GROUP BY 
        RCHECKS.CHECKID, 
        RCHECKS.CHECKNAME,
        STATUSCHANGES.DATETIME, 
        STATUSCHANGES.STATID, 
        CHECKS.CHECKNAME, 
        STATUS.STATUSNAME
    HAVING 
        (STATUSCHANGES.STATID IN (3)) 
        AND DATEPART(Q,STATUSCHANGES.DATETIME) = 2
    ) AS t2
    ORDER BY t2.Startdate

    SELECT        
        RCHECKS.CHECKID
        , RCHECKS.CHECKNAME
        , STATUSCHANGES.DATETIME AS Startdate
        , MIN(STATUSCHANGES_1.DATETIME) AS Enddate
        , STATUSCHANGES.STATID AS StatusID
        , 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
    RIGHT OUTER JOIN CHECKS RCHECKS
        ON CHECKS.CHECKID = STATUSCHANGES.CHECKID
    WHERE STATUSCHANGES.STATID IN (3) 
      AND DATEPART(Q,STATUSCHANGES.DATETIME) = 2
    GROUP BY 
        RCHECKS.CHECKID, 
        RCHECKS.CHECKNAME,
        STATUSCHANGES.DATETIME, 
        STATUSCHANGES.STATID, 
        CHECKS.CHECKNAME, 
        STATUS.STATUSNAME
    ORDER BY STATUSCHANGES.DATETIME
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.