# 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
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 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 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