How to show 12 months of data based on a selected date

Question

How to show 12 months of data based on a selected date

Anonymous

I have a query that works except for one thing i want it to show me data for the past 12 months based on a date a user selects in a ssrs report.`

`Declare @apl as Date = '1/1/2022' ;
Declare @prop as int = 8000 ;

--*/
SELECT
tblPropsFreeRent.intProp,
tblPropsFreeRent.dtMonth,
DATENAME(m, tblPropsFreeRent.dtMonth) + ' ' + DATENAME(yyyy, tblPropsFreeRent.dtMonth) AS strDate,

		intNumTimes AS intNumTimes,  
		tblPropsFreeRent.mnyAmount AS curAmount,  
		intMoveIns AS intMoveIns,  
		W.fltOcc AS fltOcc  
	FROM tblPropsFreeRent  
	LEFT JOIN SovranMisc.dbo.tblPropsLateFeeWaive W ON tblPropsFreeRent.intProp = W.intProp AND tblPropsFreeRent.dtMonth = W.dtMonth  
	WHERE  
	---tblPropsFreeRent.dtMonth = @Date  
	  
	DATEDIFF(Month,tblPropsFreeRent.dtMonth,GETDATE()) <=13  
	--tblPropsFreeRent.dtMonth >= DateADD(mm,DATEDIFF(mm,0,@Date)-12,0)

and tblPropsFreeRent.intProp IN(@Prop);

Accepted answer

2 additional answers

Your answer

Answer 1

Naomi Nosonovsky 8,431

In this case I think you can just use

dateColumn >=dateadd(month, - 12, @apl )

where @apl is the date selected from Date Picker.

Anonymous

2022-12-14T21:53:23.327+00:00

That worked thank you.

Answer 2

Jingyang Li 5,896 Volunteer Moderator

(putyourdatecolumnhereOrBuildaDate)>= DATEadd(Month,-12,GETDATE())

Jingyang Li 5,896 Reputation points Volunteer Moderator

2022-12-14T16:35:59.863+00:00

What is the data type of tblPropsFreeRent.dtMonth?
Anonymous

2022-12-14T16:59:38.527+00:00

it date field
Jingyang Li 5,896 Reputation points Volunteer Moderator

2022-12-14T17:05:09.52+00:00

tblPropsFreeRent.dtMonth>= DATEadd(Month,-12,GETDATE())

Answer 3

Naomi Nosonovsky 8,431

tblPropsFreeRent.dtMonth>= DATEADD(Month,-12,CAST(GETDATE() as date))

Anonymous

2022-12-14T17:39:43.183+00:00

what if i want the user to select a month and based off that selection it will give them 12 months of data previously?
Naomi Nosonovsky 8,431 Reputation points

2022-12-14T17:53:18.467+00:00

declare @apl date = datefromparts(year(CURRENT_TIMESTAMP), @Month, 1)

select ... from myTable where dateColumn >=dateadd(month, - 12, @apl )
Anonymous

2022-12-14T19:14:20.157+00:00

ok whats @Month because i get an error there?
Naomi Nosonovsky 8,431 Reputation points

2022-12-14T19:28:35.533+00:00

The @Month parameter you pass (e.g. 1, 2, or 12)
Anonymous

2022-12-14T20:06:14.637+00:00

It still i need have a way for user to select a date from date picker and give me the dat for last 12 months based off what date they select trying make it dynamic.

Share via

How to show 12 months of data based on a selected date

2 additional answers

Your answer