Offset SUM of Data by -1 month of User Inputs-CodePudding

I need to modify a piece of code where the user input 3 fields: Year, Start Month, and End Month. It will then SUM the number of pallets that were sent within the start/end month date range.

I'd like to modify it so that it pulls information one month prior to what the user entered.

Example 1:

@Year: 2022, @StartMonth: 1, @EndMonth: 5

It should pull data from December 1 2021 to April 30 2022.

Example 2:

@Year: 2021, @StartMonth: 1, @EndMonth: 12

It should pull data from December 1 2020 to November 30, 2021.

I included the original code and I am unsure how to modify it because of how many inputs the user has to enter.

Original Code:
@Year int, @StartMonth int, @EndMonth int
SELECT        SUM(Pallets) AS Expr1, DATEPART(mm, Date) AS Expr2, DATEPART(yyyy, Date) AS Expr3
FROM            dbo.PalettesData
WHERE        (DATEPART(mm, Date) BETWEEN @StartMonth AND @EndMonth) AND (DATEPART(yyyy, Date) = @Year)
GROUP BY DATEPART(mm, Date), DATEPART(yyyy, Date)

CodePudding user response：

In scenario like above, i always prefer to construct dates from @StartMonth , @EndMonth and @Year variables.

Below code demonstrate how to get dates from provided variables.

Declare @StartMonth int=1 , @EndMonth int=10 , @Year int = 2020
---Expected resultset should be between 2019-12-01 till 2020-09-30
select 
dateadd(month,-1,cast(cast(@Year as varchar) case when @StartMonth <10 then '-0' else '-' end cast(@StartMonth as varchar) '-01' as date)) startdate,
dateadd(day,-1,cast(cast(@Year as varchar) case when @EndMonth <10 then '-0' else '-' end cast(@EndMonth as varchar) '-01' as date)) as EndDate

Having said that, you can modify your where clause as below ( Since sample data is not provided, I couldn't test the where clause. ):

WHERE Date BETWEEN 
dateadd(month,-1,cast(cast(@Year as varchar) case when @StartMonth <10 then '-0' else '-' end cast(@StartMonth as varchar) '-01' as date))
AND
dateadd(day,-1,cast(cast(@Year as varchar) case when @EndMonth <10 then '-0' else '-' end cast(@EndMonth as varchar) '-01' as date))