CODEWITHSUNDEEP
sqldatereporting-servicescharts
EileenS
EileenS

Reputation: 43

Creating SSRS Chart when record active falls between two dates

I'm having some problems trying to work out how to represent some date determined data in a SSRS Chart (2008)

In my query, each person has a start date, an end date and a position type = permanent or temporary. The position record is active on or between the positionstart and end date. I've already filtered my query to only include people who have an active position between two parameter dates.

What I want the line chart to do is show number of people with CountDistinct of PositionId for active positions on any given month, with one line for permanent and one for temporary. 

Field names: 
PeopleId
PositionId
PositionStartDate
PositionEndDate
PositionType

Parameters are named StartDate and EndDate

I'm quite stuck!

All help appreciated, Thanks Eils

Upvotes: 0

Views: 348

Answers (1)

Jamie F
Jamie F

Reputation: 23809

I use SQL Common Table Expressions (CTEs) to generate the time table that I need, and then join or subquery other tables as needed:

;
WITH    MonthsCTE
          AS (
               SELECT
                1 AS MonthID ,
                DATEADD(MONTH, DATEDIFF(month, 0, @StartDate), 0) AS MonthStart ,
                DATEADD(MONTH, DATEDIFF(month, 0, @StartDate) + 1, 0) AS NextMonthStart
               UNION ALL
               SELECT
                MonthId + 1 ,
                DATEADD(MONTH, 1, MonthStart) ,
                DATEADD(MONTH, 1, NextMonthStart)
               FROM
                MonthsCTE
               WHERE
                NextMonthStart < @EndDate
             ),
        PositionTypes
          AS (
               SELECT DISTINCT
                PositionType
               FROM
                PeopleTable
             )
    SELECT
        MonthStart ,
        PositionType ,
        (
          SELECT
            COUNT(*)
          FROM
            PeopleTable
          WHERE
            PeopleTable.PositionType = pType.PositionType
            AND PositionStartDate < MonthsCTE.NextMonthStart
            AND PositionEndDate >= MonthsCTE.MonthStart
        ) AS PersonCount
    FROM
        MonthsCTE
        CROSS JOIN PositionTypes AS PType

You should be able to use the results of this query in an SSRS chart pretty easily.

Upvotes: 0

Related Questions