none
Query Builder RRS feed

  • Question

  • I need a trend report but the query will not parse in the dataset or query builder.

    My query is to get a count of standing scaffolds from the beginning of the year and works fine in SQL.

    What am I doing wrong?

    Phil

    WITH CTE_Table(dataDate) AS (SELECT     dateadd(day, - 1, getdate())
                                                                        UNION ALL
                                                                        SELECT     dateadd(day, - 1, dataDate)
                                                                        FROM         CTE_Table
                                                                        WHERE     datediff(day, dataDate, dateadd(day, DATEDIFF(DAY, GETDATE(), DATEADD(yy, DATEDIFF(yy, 0, getdate()), 0)), getdate())) < 0)
        SELECT     dataDate, COUNT(ScaffID) scaffs
         FROM         CTE_Table LEFT JOIN
                                RequestInfo ON dataDate > RequestInfo.phase1CompleteDate
         WHERE     (phase1CompleteDate <= dataDate) AND (workfaceReleasedDate >= dataDate OR
                                workfaceReleasedDate IS NULL) AND contractID = 14 AND disciplineID = 3 AND deleted = 'False'
         GROUP BY dataDate
         ORDER BY dataDate

    Friday, February 20, 2015 10:05 AM

All replies

  • Sorted it!

    Just put some false info in the dataset to create it then went into configure and put my query in.

    Select

    getdate() dataDate, 0 scaffs

    It didn't like it when I clicked finish but worked :-)

    Phil

    • Proposed as answer by philmarsd Friday, February 20, 2015 5:04 PM
    Friday, February 20, 2015 5:04 PM