Home T-SQL to return data in different years
Reply: 0

T-SQL to return data in different years

user2271
1#
user2271 Published in July 21, 2018, 9:47 am

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.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.324177 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO