Project Reporting Conundrum

  • I am in the middle of writing an SSRS when (surprise,surprise), the customer wants this funky addition to the end of every table row.  They want a square for every month of the current year and going out to 2+ years. (As a side note the example they presented me was an Excel file which they had been statically maintaining every month for the last couple of years.  The PM's had finally gotten tired of updating Excel and figured they would pass the task on to me.)  My thought was to CROSS APPLY a pivoted dataset onto my current base dataset.  My problem is then updating the final dataset to correctly show when a project is in pre-planning, or is being worked, or should be completed.  Below is my entire query, pivot work and all.  I just cannot seem to figure out how to correctly update the calendar columns.  PLEASE HELP!


    --Declare needed variables AND remove any temp tables that will be used
    DECLARE @test-2 date = '2018-08-29';
    IF OBJECT_ID('tempdb..#base') IS NOT NULL BEGIN DROP TABLE #base END;
    IF OBJECT_ID('tempdb..#result1') IS NOT NULL BEGIN DROP TABLE #result1 END;
    IF OBJECT_ID('tempdb..#result2') IS NOT NULL BEGIN DROP TABLE #result2 END;
    IF OBJECT_ID('tempdb..##SEPresults2') IS NOT NULL BEGIN DROP TABLE ##SEPresults2 END;
    CREATE TABLE #result1 ([N] tinyint, MonthId tinyint, MonthVar VARCHAR(15));
    CREATE TABLE #base (ProjectId int, ProjectDesc VARCHAR(25), ProjectPrePlanStart date, ProjectWorkStart date, ProjectComplete date)
    DECLARE @startMonth int, @startYear int, @startDate date, @endDate date, @monthCount int, @remMonths int, @years int
    DECLARE @cols NVARCHAR(MAX), @query NVARCHAR(MAX)

    --insert the "base" data
    INSERT INTO #base(ProjectId, ProjectDesc, ProjectPrePlanStart, ProjectWorkStart, ProjectComplete)
    VALUES
    (1,'foo','2018-05-06','2018-07-02','2018-09-15'),
    (2,'bar','2018-06-19','2018-10-11','2019-02-14'),
    (3,'far','2019-01-13','2019-07-20','2020-01-23'),
    (4,'fiz','2020-01-27','2020-08-30','2021-04-02')

    DECLARE @tblMonth TABLE (MonthId tinyint, MonthVar VARCHAR(3))
    --because this will be pivoted, each month entry needs to be unique
    insert into @tblMonth(MonthId,MonthVar)
    VALUES (1,'1J'),(2,'2F'),(3,'3M'),(4,'4A'),(5,'5M'),(6,'6J'),(7,'7J'),(8,'8A'),(9,'9S'),(10,'10O'),(11,'11N'),(12,'12D')

    --if the month is roughly 1/2 over roll over to next month. This is expected to run at the end of the current month or at the beginning.
    select @startMonth = CASE WHEN DATEDIFF(DAY,@test,EOMONTH(@test)) < 15 THEN MONTH(DATEADD(MONTH,1,@test)) ELSE MONTH(@test) END
    select @startYear = CASE WHEN @startMonth > 1 THEN YEAR(@test) ELSE YEAR(DATEADD(YEAR,1,@test)) END
    select @startDate = CONVERT(DATE,CONVERT(CHAR(4),@startYear) + '-' + CONVERT(VARCHAR(2),@startMonth) + '-01')
    select @endDate = CONVERT(CHAR(4),YEAR(DATEADD(YEAR,2,@startDate))) + '-12-31'
    select @monthCount = DATEDIFF(MONTH,@startDate, @endDate)
    select @remMonths = @monthCount % 12
    select @years = @monthCount / 12

    --check that the variables are behaving as required
    --select @startMonth as startMonth, @startYear as startYear, @startDate as startDate, @endDate as endDate
    --    , @monthCount as monthCount, @remMonths as remainingMonthsinYear, @years as Years
    /* totally stolen from Mr. Jeff Moden: http://www.sqlservercentral.com/articles/Tally+Table/72993/ */
    ; WITH E1(N) AS (
          SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
          SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
          SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
          ),        
       E2(N) AS (SELECT 1 FROM E1 a, E1 b)
         , cteTally(N) AS (
                     SELECT 0 UNION ALL
                     SELECT TOP (@monthCount) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E2
          )
    insert into #result1(N,MonthId,MonthVar)
    select * from cteTally t cross apply @tblMonth m
    where (t.N = 0 and m.MonthId >= @startMonth)
    UNION
    select * from cteTally t cross apply @tblMonth m
    where t.N <> 0 and t.N <= @years

    --Update the MonthVar to show the year
    UPDATE #result1
    SET MonthVar = MonthVar + '-' + CONVERT(VARCHAR,@startYear)
    where N = 0
    UPDATE #result1
    SET MonthVar = MonthVar + '-' + CONVERT(VARCHAR,@startYear+1)
    where N = 1
    UPDATE #result1
    SET MonthVar = MonthVar + '-' + CONVERT(VARCHAR,@startYear+2)
    where N = 2

    select @cols = STUFF((SELECT ',' + QUOTENAME(MonthVar)
           from #result1
           order by N,MonthId
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)')
       ,1,1,'')

    --using global temp table to be able to join the result to another table
    set @query = N'
                SELECT ' + @cols + N' into ##SEPresults2 from
         (
          select 0 as MonthId, MonthVar
          from #result1
        ) x
        pivot
        (
          max(MonthId)
          for MonthVar in (' + @cols + N')
        ) p '

    exec sp_executesql @query;
    select * into #result2
    from ##SEPresults2
    DROP TABLE ##SEPresults2

    select *
    from #base
    cross apply #result2

    --Correct Response
    IF OBJECT_ID('tempdb..#correctResponse') IS NOT NULL BEGIN DROP TABLE #correctResponse END;
    CREATE TABLE #correctResponse
    (ProjectId int, ProjectDesc varchar(25), ProjectPrePlanStart date, ProjectWorkStart date,
    ProjectComplete date, [9S-2018] tinyint, [10O-2018] tinyint, [11N-2018] tinyint, [12D-2018] tinyint,
    [1J-2019] tinyint, [2F-2019] tinyint, [3M-2019] tinyint, [4A-2019] tinyint, [5M-2019] tinyint,
    [6J-2019] tinyint, [7J-2019] tinyint, [8A-2019] tinyint, [9S-2019] tinyint, [10O-2019] tinyint,
    [11N-2019] tinyint,[12D-2019] tinyint,[1J-2020] tinyint, [2F-2020] tinyint, [3M-2020] tinyint,
    [4A-2020] tinyint, [5M-2020] tinyint, [6J-2020] tinyint, [7J-2020] tinyint, [8A-2020] tinyint,
    [9S-2020] tinyint, [10O-2020] tinyint,[11N-2020] tinyint,[12D-2020] tinyint)

    insert into #correctResponse
    SELECT 1,'foo','2018-05-06','2018-07-02','2018-09-15',2,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 UNION ALL
    SELECT 2,'bar','2018-06-19','2018-10-11','2019-02-14',1,1,2,2,2,2,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 UNION ALL
    SELECT 3,'far','2019-01-13','2019-07-20','2020-01-23',0,0,0,0,1,1,1,1,1,1,1,2,2,2,2,2,2,0,0,0,0,0,0,0,0,0,0,0 UNION ALL
    SELECT 4,'fiz','2020-01-27','2020-08-30','2021-04-02',0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,1,1,1,1,1,1,2,2,2,2

    select * from #correctResponse

    Thanks!

  • Wow, that's a lot of code 🙂  I'm having trouble trying to find out where the data is coming from in the #CorrectResponse table.  Can you provide what the values mean (2 in 9S-2018 for Foo and then subsequent 0s) as well as what the 0,1 and 2 represent?

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Thanks for the question! The mass of code was to make sure that I would give anybody who attempts to answer the Conundrum the full code I am working with. No assumptions needed. The data in the #correctResponse table is what I am hoping to output from the mass of code.  I just can't seem to get to the "correct response" from the pivoted date columns.  The "0" represents anytime the project is either before "Pre-Work" or after "ProjectComplete".  The "1" represents when the project is in "Pre-Work" and the "2" represents when the project is being "Worked".

    Does that answer your question or do I need to go into more detail?  I have been bashing my head against this for a couple of days now and if no one has any advice I will most likely go to a cursor solution (insert shudder here).  Something I would like to avoid as much as possible.

    Thanks!

  • ok, I think I have what you need.  I changed the names of the columns so the ordering would be correct (I used YYYY-MMMonthName instead of MMMonthName-YYYY).  I also used Jeff Moden's tally table but made it into a table function so I could pass in start and end numbers.  I've attached that as well.  Attachment didn't make it, so it is below

    --Declare needed variables AND remove any temp tables that will be used
    DECLARE @test-2 date = '2018-08-29';
    IF OBJECT_ID('tempdb..#base') IS NOT NULL BEGIN DROP TABLE #base END;
    IF OBJECT_ID('tempdb..#result1') IS NOT NULL BEGIN DROP TABLE #result1 END;
    IF OBJECT_ID('tempdb..#result2') IS NOT NULL BEGIN DROP TABLE #result2 END;
    IF OBJECT_ID('tempdb..##SEPresults2') IS NOT NULL BEGIN DROP TABLE ##SEPresults2 END;
    CREATE TABLE #result1 ([N] tinyint, MonthId tinyint, MonthVar VARCHAR(15));
    CREATE TABLE #base (ProjectId int, ProjectDesc VARCHAR(25), ProjectPrePlanStart date, ProjectWorkStart date, ProjectComplete date)
    DECLARE @startMonth int, @startYear int, @startDate date, @endDate date, @monthCount int, @remMonths int, @years int
    DECLARE @cols NVARCHAR(MAX), @query NVARCHAR(MAX)

    --insert the "base" data
    INSERT INTO #base(ProjectId, ProjectDesc, ProjectPrePlanStart, ProjectWorkStart, ProjectComplete)
    VALUES
    (1,'foo','2018-05-06','2018-07-02','2018-09-15'),
    (2,'bar','2018-06-19','2018-10-11','2019-02-14'),
    (3,'far','2019-01-13','2019-07-20','2020-01-23'),
    (4,'fiz','2020-01-27','2020-08-30','2021-04-02')

    DECLARE @tblMonth TABLE (MonthId tinyint, MonthVar VARCHAR(3))
    --because this will be pivoted, each month entry needs to be unique
    -- Use Tally table function to populate
    insert into @tblMonth(MonthId,MonthVar)
    select N, right('00' + Cast(N as varchar(2)), 2) + Left(DATENAME(m, Cast(N as varchar(2)) + '/1/2018'),1)
    from dbo.Tally (1,12)

    --if the month is roughly 1/2 over roll over to next month. This is expected to run at the end of the current month or at the beginning.
    select @startMonth = CASE WHEN DATEDIFF(DAY,@test,EOMONTH(@test)) < 15 THEN MONTH(DATEADD(MONTH,1,@test)) ELSE MONTH(@test) END
    select @startYear = CASE WHEN @startMonth > 1 THEN YEAR(@test) ELSE YEAR(DATEADD(YEAR,1,@test)) END
    select @startDate = CONVERT(DATE,CONVERT(CHAR(4),@startYear) + '-' + CONVERT(VARCHAR(2),@startMonth) + '-01')
    select @endDate = CONVERT(CHAR(4),YEAR(DATEADD(YEAR,2,@startDate))) + '-12-31'
    select @monthCount = DATEDIFF(MONTH,@startDate, @endDate)
    select @remMonths = @monthCount % 12
    select @years = @monthCount / 12

    insert into #result1(N,MonthId,MonthVar)
    select 0, N, right('00' + Cast(N as varchar(2)), 2) + Left(DATENAME(m, Cast(N as varchar(2)) + '/1/' + cast(@StartYear as varchar(4))),1)
    from dbo.Tally (1,12)
    where N >= 9
    union all
    select Y.N, T.N, right('00' + Cast(t.N as varchar(2)), 2) + Left(DATENAME(m, Cast(T.N as varchar(2)) + '/1/' + cast(@StartYear + Y.N as varchar(4))),1)
    from dbo.Tally (1,12) T
        cross apply dbo.Tally (1,2) Y

    alter table #result1 add MonthDate date

    --Update the MonthVar to show the year
    UPDATE #result1
    SET MonthVar = cast(@startYear + N as VARCHAR(4)) + '-' + MonthVar
        , MonthDate = cast(Cast(MonthID as varchar(2)) + '/1/' + cast(@startYear + N as char(4)) as date)

    --Correct Response
    IF OBJECT_ID('tempdb..#Result3') IS NOT NULL BEGIN DROP TABLE #Result3 END;
            select MonthId, MonthVar, MonthDate, ProjectID, ProjectDesc, ProjectPrePlanStart, ProjectWorkStart, ProjectComplete,
                cast(
                    case when MonthDate > ProjectComplete then 0
                         when MonthDate < ProjectPrePlanStart then 0
                         when MonthDate > ProjectWorkStart then 2
                         when MonthDate < ProjectWorkStart then 1
                    else 0
                    end as int) ProjectStatus    
             INTO #Result3 
             from #result1 r
                cross apply #base

    SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.MonthVar)
        FROM #result3 c
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)')
       ,1,1,'')

    set @query = 'SELECT ProjectID, ProjectDesc, ProjectPrePlanStart, ProjectWorkStart, ProjectComplete , ' + @cols + ' from
        (
          select ProjectID, ProjectDesc, ProjectPrePlanStart, ProjectWorkStart, ProjectComplete ,ProjectStatus,MonthVar
          from #result3
        ) x
        pivot
        (
          max([ProjectStatus])
          for MonthVar in (' + @cols + ')
        ) p '

    execute(@query)

    --Correct Response
    IF OBJECT_ID('tempdb..#correctResponse') IS NOT NULL BEGIN DROP TABLE #correctResponse END;
    CREATE TABLE #correctResponse
    (ProjectId int, ProjectDesc varchar(25), ProjectPrePlanStart date, ProjectWorkStart date,
    ProjectComplete date, [9S-2018] tinyint, [10O-2018] tinyint, [11N-2018] tinyint, [12D-2018] tinyint,
    [1J-2019] tinyint, [2F-2019] tinyint, [3M-2019] tinyint, [4A-2019] tinyint, [5M-2019] tinyint,
    [6J-2019] tinyint, [7J-2019] tinyint, [8A-2019] tinyint, [9S-2019] tinyint, [10O-2019] tinyint,
    [11N-2019] tinyint,[12D-2019] tinyint,[1J-2020] tinyint, [2F-2020] tinyint, [3M-2020] tinyint,
    [4A-2020] tinyint, [5M-2020] tinyint, [6J-2020] tinyint, [7J-2020] tinyint, [8A-2020] tinyint,
    [9S-2020] tinyint, [10O-2020] tinyint,[11N-2020] tinyint,[12D-2020] tinyint)

    insert into #correctResponse
    SELECT 1,'foo','2018-05-06','2018-07-02','2018-09-15',2,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 UNION ALL
    SELECT 2,'bar','2018-06-19','2018-10-11','2019-02-14',1,1,2,2,2,2,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 UNION ALL
    SELECT 3,'far','2019-01-13','2019-07-20','2020-01-23',0,0,0,0,1,1,1,1,1,1,1,2,2,2,2,2,2,0,0,0,0,0,0,0,0,0,0,0 UNION ALL
    SELECT 4,'fiz','2020-01-27','2020-08-30','2021-04-02',0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,1,1,1,1,1,1,2,2,2,2

    select * from #correctResponse
        

    -- Tally Table Function

    CREATE FUNCTION [dbo].[Tally]
      (
      @pMin BIGINT
      ,@pMax BIGINT
      )
    RETURNS TABLE
    WITH SCHEMABINDING
    AS
    RETURN
     
      WITH T1(F) AS
      (
       SELECT 1 UNION ALL
       SELECT 1 UNION ALL
       SELECT 1 UNION ALL
       SELECT 1 UNION ALL
       SELECT 1 UNION ALL
       SELECT 1 UNION ALL
       SELECT 1 UNION ALL
       SELECT 1 UNION ALL
       SELECT 1 UNION ALL
       SELECT 1
      ),
      T2(F) AS
       (SELECT 1 FROM T1 A, T1 B),
      T3(F) AS
       (SELECT 1 FROM T2 A, T2 B),
      T4(F) AS
       (SELECT 1 FROM T3 A, T3 B),
      cteTally(N) AS
       (
       SELECT TOP ((@pMax-(((ABS(@pMin)+@pMin)/2)))+1)
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
        + ((((ABS(@pMin)+@pMin)/2))-1)
       FROM T4
       )
    SELECT
      N
    FROM
      cteTally T
    ;

    GO

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Thank you sir.  It is much more elegant than the solution I was working on.

  • logitestus - Thursday, August 30, 2018 11:05 AM

    Thank you sir.  It is much more elegant than the solution I was working on.

    I'm not elegant is the right word 🙂 , but you are welcome

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply