How to filter my results so it shows the last four months of data – sql

I’m trying to create a stored procedure that gets the last 4 months worth of results from the below query but I’m unsure how to do this.

This is what I have done so far:

  • Can I use ASP.NET Membership with SQL Server Compact Edition?
  • What is the meaning of the prefix N in T-SQL statements?
  • Php SQL Server table value parameter
  • Is it possible to automate SQL Server 2008 profiler?
  • Java & SQL Server exception: The statement did not return a result set
  • Most efficient T-SQL way to pad a varchar on the left to a certain length?
  • Declare @Number varchar(30) = '12'
    Select
    month = month(EndDate),
    YEAR = YEAR(EndDate),
    SUM(DownloadUnits) as downloads,
    SUM(UploadUnits) as uploads,
    number
    from testTable
    where number=@Number
    GROUP BY MONTH(EndDate), Year(Enddate),number
    

    How can I filter it out so that when I pass month parameter (I haven’t created it yet) it filters out the results so it only shows the last four months? (I have hard coded the number parameter for testing)

    3 Solutions collect form web for “How to filter my results so it shows the last four months of data – sql”

    If you need to get whole months then you will need to get the first of the month 4 months ago.

    You can get the first of the current month using:

    SELECT DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '19000101');
    

    Adapting this slightly will give you the first of the month 4 months ago:

    SELECT DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()) - 4, '19000101');
    

    Then you can just apply this filter to your query:

    WHERE   EndDate >= DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()) - 4, '19000101')
    

    Or if you need to pass the number of months a parameter (it should be an INT not a varchar by the way):

    WHERE   EndDate >= DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()) - @Number, '19000101')
    

    If you pass a date parameter, just replace GETDATE() with your parameter name.

    The last N months from now meet the condition

    where EndDate >= dateadd(month, -@DEDUCT_MONTHS, cast(getdate() as DATE))
    

    Removing the cast will enforce the current time as a constraint as opposed to midnight N months ago.

    DECLARE @StartDate date
    SET @StartDate=Dateadd(Month, Datediff(Month, 0, DATEADD(m, -6, getdate())), 0) 
    --first day of the month, (current month-6 month)
    

    The script above will return with the first day of the month – 6 month ago .
    This solution is from stackoverflow somewhere, unfortunately I dont seem to find the original post.. 🙁

    To understand how it works try to reverse-engineer as per follows:

    DECLARE @StartDate1 date
    SET @StartDate1= DATEADD(m, -6, getdate())
    PRINT @Startdate1
    
    
    DECLARE @StartDate2 int
    SET @StartDate2= Datediff(Month, 0, DATEADD(m, -6, getdate()))
    PRINT @Startdate2
    
    
    DECLARE @StartDate3 date
    SET @StartDate3=Dateadd(Month, 1369, 0)
    PRINT @Startdate3
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.