calculate 12 months of data from a given date from a user

Question

calculate 12 months of data from a given date from a user

SSC Veteran

Points: 213

December 15, 2022 at 5:58 pm

I have a query that it seems to be off dont know what wrong but I need to show last 12 months of data based off a user input for a date.

my query is this. My results show different in SSRS.

12 months data

/*Declare @Date as Date = '11/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 >= dateadd(YEAR, -1, @Date)
   and tblPropsFreeRent.intProp IN(@Prop)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80330 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 2

We will need your help in order to be able to help you, so please help us!

😎

It would be greatly appreciated if you could provide the DDL (create table) script, sample data as an insert statement, the desired output from the sample data, and what you have tried so far.

ScottPletcher SSC Guru Points: 101096 More actions · Answer 3

If you want only 12 months of data, then you'll need to restrict the ending date too. Assuming you want to include the month that @Date is in, then:

WHERE

tblPropsFreeRent.dtMonth >= dateadd(MONTH, -11, @Date) AND

tblPropsFreeRent.dtMonth < dateadd(MONTH, 1, @Date)

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".