Last post Dec 18, 2017 12:39 PM by mgebhard
Dec 18, 2017 11:33 AM|JagjitSingh|LINK
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.
Dec 18, 2017 12:34 PM|Omkar Mhaiskar|LINK
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 =
WHEN Month(@date) BETWEEN 4 AND 12
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.
Dec 18, 2017 12:39 PM|mgebhard|LINK
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
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.