CODEWITHSUNDEEP
sql-servert-sql
Arian Caen Holt
Arian Caen Holt

Reputation: 119

T-SQL to return data in different years

I'm creating a stored procedure that will return data about Product and its Usage.

No  Product     Usage (Jan 2017)    Usage (Feb 2017)
=====================================================
1   Paper       0.00                0.00
2   Ink         0.00                0.00

What I have do is below query (a part of longer query)

DECLARE 
@StartPeriod DATETIME =  N'01/01/2017 00:00:00',
@EndPeriod DATETIME = N'12/30/2018 00:00:00'

DECLARE @query VARCHAR(MAX),
        @StartMonth int = MONTH(@StartPeriod),
        @EndMonth int = MONTH(@EndPeriod),
        @StartYear int = YEAR(@StartPeriod),
        @EndYear int = YEAR(@EndPeriod),
        @Period datetime = @StartPeriod

while (@StartYear < @EndYear OR (@StartYear = @EndYear AND @StartMonth <= @EndMonth))
begin
    set @query = @query + ' 
        case when (
            select  Consumption
            from    tbl_Consumption c
            where   c.ProductID = a.ProductID
                    and DATEPART(YEAR, StartPeriod) = DATEPART(YEAR, ''' + CONVERT(VARCHAR, @Period) + ''')
                    and DATEPART(MONTH, StartPeriod) = DATEPART(MONTH, ''' +  CONVERT(VARCHAR, @Period) + ''')
        ) is null then 0.00
        else (
            select  Consumption
            from    tbl_Consumption c
            where   c.ProductID = a.ProductID
                    and DATEPART(YEAR, StartPeriod) = DATEPART(YEAR, ''' + CONVERT(VARCHAR, @Period) + ''')
                    and DATEPART(MONTH, StartPeriod) = DATEPART(MONTH, ''' +  CONVERT(VARCHAR, @Period) + ''')
        )

        end as [Usage (' + LEFT(DateName(month, DateAdd(month, @Period, -1)),3) + ' ' + CONVERT(VARCHAR, @Period) +')],'

    set @Period = DATEADD(MONTH, 1, @Period)
    set @StartMonth = @StartMonth + 1
    if @StartMonth > 12
    begin
        set @StartYear = @StartYear + 1
    end
end

The problem is when the year in parameter StartPeriod is less than EndPeriod, it will only return the data of month in 2017 only. For example, the above code only returns data from January to December 2017, but it should be return the data from January to December 2018 too. I'm using SQL Server 2014.

Please help. Thank you very much.

Upvotes: 0

Views: 64

Answers (1)

Abdul Rasheed
Abdul Rasheed

Reputation: 6719

Here in your logic, you have to reset the @StartMonth to January when @StartMonth > 12

if @StartMonth > 12
begin
    set @StartYear = @StartYear + 1
    set @StartMonth = 1 --reset the month to Jan
end

Note :- But this dynamic sql method is not so good, you can user either cross tab / matrix reports to display this type of data in reports or something like PIVOT to get in query itself.

Upvotes: 1

Related Questions