none
Query showing last 6 months not to include current month

    Question

  • I am running a query for a report I am tasked with creating and need information on the last 6 month of data not to include the current month. I saw the thread "Last 3 Months - Current Month" but that doesn't seem to fit with my situation.
    Monday, January 13, 2014 6:10 PM

Answers

  • mariner,

    So the current month being Jan 2014, yu would need data for the first 6 months of last 12 months right? So that would mean Jan 2013 - June 2013 ?? If my understanding is right, the below query gives the required result..

    DECLARE @EndDate DATETIME
    DECLARE @StartDate DATETIME
    
    SET @EndDate = dateadd(month, datediff(month,0, CURRENT_TIMESTAMP)-6,0);
    SET @StartDate = DATEADD(month, - 6, @EndDate)
    --SELECT * FROM tbl_name WHERE column_name>=@startdate and column_name<@EndDate
    select @startdate as startdate,@enddate as enddate


    Thanks,
    Jay
    <If the post was helpful mark as 'Helpful' and if the post answered your query, mark as 'Answered'>

    • Marked as answer by marinert19651 Thursday, January 16, 2014 4:39 PM
    Thursday, January 16, 2014 7:36 AM

All replies

  • Try:

    DECLARE @EndDate DATE
    	,@StartDate DATE;
    
    SET @EndDate = dateadd(month, datediff(month, '19000101', CURRENT_TIMESTAMP), '19000101');
    SET @StartDate = DATEADD(month, - 6, @EndDate)
    
    
    SELECT * FROM TransactionData
    WHERE TrDate >=@StartDate AND TrDate < @EndDate -- last 6 months of data


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, January 13, 2014 6:27 PM
  • use a filter like below

    WHERE dateField > = DATEADD(mm,DATEDIFF(mm,0,GETDATE())-6,0)
    AND dateField < DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)

    also see

    http://visakhm.blogspot.in/2012/12/different-ways-to-implement-date-range.html


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Monday, January 13, 2014 6:31 PM
  • Awesome thanks that worked, now I need to know the six month period Prior to that, not to include current month or the six months which were just calculated. I hope that makes sense.

    Thanks

    Monday, January 13, 2014 11:36 PM
  • So, do you mean you want from 12 months ago to 6 months ago? If yes, can you figure this out based on the solution I posted?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, January 14, 2014 3:56 PM
  • I have the last 6 month period which works fine, now I need the 6 month period before this last 6 month period. What we are trying to figure out the trend from the last 6 months to the previous 6 months.

    Thanks

    Tuesday, January 14, 2014 4:24 PM
  • Ok, so what exactly is your problem now? You can run your procedure with two different sets of dates to get data for last 6 months and for the previous 6 months.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, January 14, 2014 5:28 PM
  • mariner,

    this would help:

    DECLARE @EndDate DATE
    DECLARE @StartDate DATE
    
    SET @EndDate = dateadd(month, datediff(month,0, CURRENT_TIMESTAMP)-6,0);
    SET @StartDate = DATEADD(month, - 6, @EndDate)
    SELECT * FROM tbl_name WHERE column_name>=@startdate and column_name<@EndDate


    Thanks,
    Jay
    <If the post was helpful mark as 'Helpful' and if the post answered your query, mark as 'Answered'>

    Tuesday, January 14, 2014 7:05 PM
  • Jay, The last query gives me the last 12 months of data and I need the 1st 6 months in the 12 month period data. I hope this isn't too confusion Thanks Mariner
    Wednesday, January 15, 2014 10:04 PM
  •  
    DECLARE @EndDate DATE
    DECLARE @StartDate DATE

    select @EndDate=DATEADD(m, -6, current_timestamp)
    select @StartDate = DATEADD(month, - 12, current_timestamp)
    print @StartDate
    print @EndDate

    SELECT * FROM tbl_name WHERE column_name>=@startdate and column_name<@EndDate

    --Prashanth
    Wednesday, January 15, 2014 10:10 PM
  • Almost there, but it cannot contain the current month
    Thursday, January 16, 2014 1:05 AM
  • mariner,

    So the current month being Jan 2014, yu would need data for the first 6 months of last 12 months right? So that would mean Jan 2013 - June 2013 ?? If my understanding is right, the below query gives the required result..

    DECLARE @EndDate DATETIME
    DECLARE @StartDate DATETIME
    
    SET @EndDate = dateadd(month, datediff(month,0, CURRENT_TIMESTAMP)-6,0);
    SET @StartDate = DATEADD(month, - 6, @EndDate)
    --SELECT * FROM tbl_name WHERE column_name>=@startdate and column_name<@EndDate
    select @startdate as startdate,@enddate as enddate


    Thanks,
    Jay
    <If the post was helpful mark as 'Helpful' and if the post answered your query, mark as 'Answered'>

    • Marked as answer by marinert19651 Thursday, January 16, 2014 4:39 PM
    Thursday, January 16, 2014 7:36 AM