September 28, 2016 at 7:37 am
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.
September 28, 2016 at 8:15 am
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
September 28, 2016 at 12:17 pm
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy