question

WillFaulkner-2982 avatar image
0 Votes"
WillFaulkner-2982 asked WillFaulkner-2982 commented

Amending a query to show date before but not date after

Hello

I have a simple report, written for a specific group of people, that shows the volume of telephone calls completed by people within certain dates.

The specific group of people are gathered within the script using IN, so for example:

SELECT name, callcompleted, date
from table
WHERE name IN ('william faulkner', 'richard ford')
And date between @datea and @dateb


This gives an output:

william faulkner y 16/6/21
richard ford y 15/6/21 etc



My issue is that one of the people - say richard ford - left the group on 1/6/21 for another group and so still works for the same company, uses the same database and so his calls still appear in the above report. I want his calls excluded from the report from 1/6/21



Is it possible to amend the query above to continue to show the output of william faulkner but to exclude the output of richard ford after 1/6/21 - but still include the input of richard ford before 1/6/21?


any suggestions advice would be very helpful - thanks



sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered WillFaulkner-2982 commented

Please try:

 SELECT name, callcompleted, date
 from table
 WHERE name IN ('william faulkner', 'richard ford')
 And date between @datea and 1/6/21
 union all
 SELECT name, callcompleted, date
 from table
 WHERE name IN ('william faulkner','other')--Exclude richard ford
 And date between 1/6/21 and @dateb

If you have any question, please feel free to let me know.


Regards
Echo


If the answer is helpful, please click "Accept Answer" and upvote it.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks very much for the speedy and helpful answer - much appreciated

0 Votes 0 ·