Post reply

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

  • December 15, 2022 at 5:58 pm

    #4126156

    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)

  • December 15, 2022 at 11:13 pm

    #4126260

    The dateadd function looks OK if you want the data for the year before the input date.

    You don't say what is wrong with your  query.  How are they different in SSRS?  Can you cut and paste a picture or describe the issue?

Viewing 2 posts - 1 through 1 (of 1 total)

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