Applying multiple date filters in sql query

  • Hello all,

    You expert opinion is much appreciated.

    I am running a report that covers the last two fiscal years. I want all the data for the last two years. The exclusion criteria is below.

    Exclude the Consultants who had not had any sales ONLY in the last year. Something like the below.

    WHERE ActivityDate<01-Sep-2016

    AND (ConsultantName IS NOT NULL and ActivityDate>’01-04-2016’)

    The first line means I want all the activity before September this year (including previous years)

    BUT

    exclude the consultants who had null activity only for 2016.

    I know the above filter is not correct. What is the right way of doing it please?

    Many thanks in advance.

  • Infock12 (9/28/2016)


    Hello all,

    You expert opinion is much appreciated.

    I am running a report that covers the last two fiscal years. I want all the data for the last two years. The exclusion criteria is below.

    Exclude the Consultants who had not had any sales ONLY in the last year. Something like the below.

    WHERE ActivityDate<01-Sep-2016

    AND (ConsultantName IS NOT NULL and ActivityDate>’01-04-2016’)

    The first line means I want all the activity before September this year (including previous years)

    BUT

    exclude the consultants who had null activity only for 2016.

    I know the above filter is not correct. What is the right way of doing it please?

    Many thanks in advance.

    In the future, it helps if you can include more of the query, because the approach will vary depending whether your are grouping or not. You should also use a consistent formatting for your date strings and preferably a format that is independent of location. It's not clear whether '01-04-2016' is January 4th, 2016 (US) or April 1, 2016 (UK). The format '20160104' is unambiguous.

    -- GROUPING

    SELECT <your field list here>

    FROM your_table

    WHERE ActivityDate< '01-Sep-2016'

    AND ConsultantName IS NOT NULL

    GROUP BY <your grouping sets here>

    HAVING MAX(ActivityDate)>'01-04-2016'

    -- Non-grouped

    ;

    WITH CTE AS (

    SELECT <your field list here>, MAX(ActivityDate) OVER(PARTITION BY ConsultantID) AS LastActivityDate

    WHERE ActivityDate<'01-Sep-2016'

    AND ConsultantName IS NOT NULL

    )

    SELECT *

    FROM CTE

    WHERE LastActivityDate>'01-04-2016'

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Thanks Drew, this is very helpful. Yes I should have been more clear. I am not grouping anything. It is a set of two tables connected through a union query. I will try this option and let you know how I get on.

    Mark

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

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