locked
Previous month from January RRS feed

  • Question

  • I have a report that totals different incidents for the month a user chooses and also includes the previous months totals as well.  The date parameters are separated into month and year.  The user selects the month from a drop down list, then the year from a separate drop down list.  The report then renders the Type and number for each type from the month and year chosen and the prior month.  This report works fine until the user selects January.  If a user selects January from the month parameter and 2019 from the year parameter, I need December 2018 and January 2019 to list.  Just as choosing February 2019 renders February and January's data.  The table and grouping perform the countDistinct aggregate.  The Month parameter is a list of values: Jan 1, Feb 2, etc.  I tried adding an expression to the January value without success.  I'm not opposed to having the query perform the calculations if that's best. 

    Month Parameter Values

    JAN     1

    FEB     2

    MAR    3

    etc.

    Year parameter gets values from a separate query.

    Main QUERY:

       MV_Incident.IncidentNumber 
      ,MV_Incident.IncidentDate
      ,MV_Incident.IncidentTypeCode

    FROM
      MV_Incident
    WHERE
    (datepart(year,MV_Incident.IncidentDate) = @YearParam or datepart(year,MV_Incident.IncidentDate) = @YearParam)
    AND
    (datepart(month,MV_Incident.IncidentDate) = @monthParam or datepart(month,MV_Incident.IncidentDate) = @monthParam-1)
    order by years ASC

    Friday, April 12, 2019 2:26 AM

All replies

  • Hi JJ-Dub,

    According to your description, I think we could adjust the query to resolve the issue.

    Try following,

    IF @monthParam=1
            SELECT
                MV_Incident.IncidentNumber 
                ,MV_Incident.IncidentDate
                ,MV_Incident.IncidentTypeCode
    
            FROM
                MV_Incident
            WHERE
                (datepart(year,MV_Incident.IncidentDate) = (@YearParam-1) and datepart(month,MV_Incident.IncidentDate) = 12)
                or
                (datepart(year,MV_Incident.IncidentDate) = @YearParam and datepart(month,MV_Incident.IncidentDate) = @monthParam)
            order by years ASC 
        ELSE
            SELECT
                MV_Incident.IncidentNumber 
                ,MV_Incident.IncidentDate
                ,MV_Incident.IncidentTypeCode
    
            FROM
                MV_Incident
            WHERE
                datepart(year,MV_Incident.IncidentDate) = @YearParam 
                AND
                (datepart(month,MV_Incident.IncidentDate) = @monthParam or datepart(month,MV_Incident.IncidentDate) = @monthParam-1)
            order by years ASC 
      

    Hope this works for you.

    Best regards,

    Lukas


    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, April 12, 2019 8:19 AM
  • Hi JJ-Dub,

    Did my proposal work as you wanted? 

    If not, we would be happy to know your feedback :)

    Lukas


    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, April 16, 2019 2:12 AM