上个月从1月开始 [英] Previous month from January

问题描述

我的报告总计了用户选择的月份的不同事件，并且还包括前几个月的总数。 日期参数分为月份和年份。 用户从下拉列表中选择月份，然后从单独的下拉列表中选择
年份。 然后，报告会根据所选的月份和年份以及上个月呈现每种类型的类型和编号。 此报告正常工作，直到用户选择1月。 如果用户从月份
参数中选择1月，从年份参数中选择2019，我需要在2018年12月和2019年1月列出。 正如选择2019年2月一样，可以呈现2月和1月的数据。 表和分组执行countDistinct聚合。  Month参数
是一个值列表：Jan 1，Feb 2等。 我尝试将一个表达式添加到January值但没有成功。 我不反对让查询执行计算，如果这是最好的。 

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. 

月参数值

JAN      1

JAN     1

FEB      2

FEB     2

MAR    3

MAR    3

等。

年份参数从单独的查询中获取值。

Year parameter gets values from a separate query.

主要查询：

推荐答案

嗨JJ-Dub，

Hi JJ-Dub,

根据你的描述，我想我们可以调整查询来解决问题。

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

试试以下，

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 
  

希望这对你有用。

祝你好运，

Lukas

这篇关于上个月从1月开始的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！