Sum of Amount

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

Answer 1

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.

Answer 2

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.