locked
Sum of Amount RRS feed

  • Question

  • User-1499457942 posted

    Hi

     I have data like below . I want if user has entered month = 2 and year = 2016 then it should show sum from Month = 4 , Year = 2015 to Month = 2 , Year = 2016 i.e 10210000.

    If user has entered month = 9 and year = 2015 then it should show sum from Month = 4 , Year = 2015 to Month = 9 , Year = 2015 i.e 3990000.

    Location Month Year Amount
    DSL 4 2015 530000
    DSL 5 2015 530000
    DSL 6 2015 530000
    DSL 7 2015 800000
    DSL 8 2015 800000
    DSL 9 2015 800000
    DSL 10 2015 1300000
    DSL 11 2015 1300000
    DSL 12 2015 1300000
    DSL 1 2016 1160000
    DSL 2 2016 1160000
    DSl 3 2016 1160000
    Monday, December 18, 2017 11:33 AM

Answers

  • User726159118 posted

    Hi

    based on input Create first date of parameter month. Passed this date to below query which will return you year from which you have to start.

    declare @date datetime
    set @date = '2011/02/01'
    
    SELECT QM_FIN_YEAR =
            CASE
                WHEN Month(@date) BETWEEN 4 AND 12
                    THEN CONVERT(VARCHAR(4),YEAR(@date)) 
                WHEN Month(@date) BETWEEN 1 AND 3
                    THEN CONVERT(VARCHAR(4),YEAR(@date) - 1) 
    End ORDER by 1 DESC

    Based on This set your where conditions and fetch data.

    Thanks
    Omkar.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, December 18, 2017 12:34 PM

All replies

  • User726159118 posted

    Hi

    based on input Create first date of parameter month. Passed this date to below query which will return you year from which you have to start.

    declare @date datetime
    set @date = '2011/02/01'
    
    SELECT QM_FIN_YEAR =
            CASE
                WHEN Month(@date) BETWEEN 4 AND 12
                    THEN CONVERT(VARCHAR(4),YEAR(@date)) 
                WHEN Month(@date) BETWEEN 1 AND 3
                    THEN CONVERT(VARCHAR(4),YEAR(@date) - 1) 
    End ORDER by 1 DESC

    Based on This set your where conditions and fetch data.

    Thanks
    Omkar.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, December 18, 2017 12:34 PM
  • User475983607 posted

    I suggest that you redesign the schema and add a date field then you can simply use the the between clause.  The way the table is currently designed you'll need to convert the month and year field to a date using an SQL Date function like DATEFROMPARTS.

    SELECT SUM(Amount)
    FROM theTable
    WHERE DATEFROMPARTS(Year, Month, 1) BETWEEN DATEFROMPARTS(2015, 4, 1) AND DATEFROMPARTS(2015, 9, 1)

    I imagine you'll pass the start and end dates to the script.  At this point is not not clear how you get the end date.

    Monday, December 18, 2017 12:39 PM