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

T-SQL to return data in different years

Kleinrock Vint
1#
Kleinrock Vint Published in 2018-01-12 08:34:13Z

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.

Abdul Rasheed
2#
Abdul Rasheed Reply to 2018-01-12 09:01:10Z

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.

You need to login account before you can post.

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

© 2016 Powered by mzan.com design MATCHINFO