We help IT Professionals succeed at work.
Troubleshooting Question

I have a table that contains vendors and the dates they were on the delinquency list. I need to find the vendors that were on the list at a given date and are not on the list today.

71 Views
Last Modified: 2020-10-23
I have a table that each day the list of delinquent vendors are added via a script to the table with the current date.  I need to be able to query the table to select vendors that were on the list for a given date that are not on the list for today.  For example,
100 vendors were added to the list with a date of 9/25/2020.  95 vendors were added with a date of 9/26/2020.  

I need to create a query for a SSRS report that has two date parameters.  The user would select 9/25/2020 and 9/26/2020 and the query would return the 5 vendors who were on the list for 9/25/2020 and not on the list for the second data parameter of 9/26/220.
Comment
Watch Question

Author

Commented:
    A little more into.  

Vendor 1 has records in the table for 9/20, 9/21, 9/22, 9/23, 9/24, 9/25 and 9/26
Vendor 2 has records in the table for 9/20, 9/21, 9/22, 9/23, 9/24, 9/25 but not 9/26
Vendor 3 has records in the table for 9/20, 9/21, 9/22, 9/23, 9/24, 9/25 and 9/26  

The query would accept two parameters: Date1 and Date2
The user would filter with Date1=9/25/ and Date2= 9/26.

The query return only vendor 2 since it was on the list on 9/25 but not on the list for 9/26.

Thanks!
David Johnson, CDSimple Geek from the '70s
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
A lot of this depends on whether you store the date the vendor is delinquent  or not. or the date the status has changed. 

Author

Commented:
Each night the total delinquent list is appended to the table with today's date. Essentially I'm trying to have a query that selects all of the records for one day, a second query that selects all of the records for a later date and then find all of the records that are in query 1 and not in query 2.  I need to do this in one query so I can use it for a SSRS report.  I believe it should be a query with a subquery but I'm having difficulty writing the query.  

Sorry if my explanation is not clear.
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
select *
  from myTable
 where vendorDate>=@date1 and vendorDate<@date2
you can use query above tom cover all...

Author

Commented:
Thanks for the reply.  I think that will get me the records that are in both tables.  I need to find the records that are in the table for @date1 and not in the table for @date2.

Thanks!
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
try

select t1.*
  from Table1 t1
  left join Table2 t2 on t1.vendorID=t2.VendorID and t2.vDate=@date2
 where t1.vDate=@date1 and t2.VendorID is null

Author

Commented:
HainKurt,

Thanks for the info.  All of the data is in one table.  How would I modify your query to just select from one table?  Also, I think I'm having an issue with the date fields.  The field is defined as datetime.  Perhaps that's also causing me to not return any rows?
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
try

with
t1 as (select * from myTable where cast(vDate as date)=@date1),
t2 as (select * from myTable where cast(vDate as date)=@date2)
select t1.*
  from t1
  left join t2 on t1.vendorID=t2.VendorID and t2.vDate=@date2
 where t1.vDate=@date1 and t2.VendorID is null 

Author

Commented:
Sorry, I'm still having issues.  I get "Incorrect syntax near 'cast', expected 'AS'."
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
fixed it, should be cast(vDate as date)

Author

Commented:
I hard coded the dates and replace date and vendor and date with the actual table field names:

with
t1 as (select * from tblRandRLapsedDonorReportDriverDaily where cast(ListDate, date)='9/20/2020'),
t2 as (select * from tblRandRLapsedDonorReportDriverDaily where cast(ListDate, date)='10/3/2020')
select t1.*
  from t1
  left join t2 on t1.Constituent_ID=t2.Constituent_ID and t2.ListDate='10/3/2020'
 where t1.ListDate='9/20/2020' and t2.Constituent_ID is null

Perhaps my editing has caused the issue.

Author

Commented:
One other bit of info.  The listdate field is datetime in the table.
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
if you hardcode, then use

convert(date, '9/20/2020', 101)

to convert it to proper date...
and if your parameter is date, then it is ok in the query, no need to convert to anything...

Author

Commented:
I guess it's obvious I don't know what I'm doing wrong.  I have:

1. with
2. t1 as (select * from dbo.tblRandRLapsedDonorReportDriverDaily where Convert(date,'9/20/2020',101),
3. t2 as (select * from dbo.tblRandRLapsedDonorReportDriverDaily where Convert(date,'10/3/2020',101)
4. select t1.*
5. from t1
6. left join t2 on t1.Constituent_ID=t2.Constituent_ID and t2.ListDate=Convert(date,'10/3/2020',101)
7. where t1.ListDate=Convert(date,'9/20/2020') and t2.Constituent_ID is null

I get:
Msg 4145, Level 15, State 1, Line 2
An expression of non-boolean type specified in a context where a condition is expected, near ','.
Msg 4145, Level 15, State 1, Line 3
An expression of non-boolean type specified in a context where a condition is expected, near ','.

I appreciate the help!
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
try
with
 t1 as (select * from dbo.tblRandRLapsedDonorReportDriverDaily 
         where ListDate=Convert(date,'9/20/2020',101))
 t2 as (select * from dbo.tblRandRLapsedDonorReportDriverDaily
         where ListDate=Convert(date,'10/3/2020',101))
select t1.*
  from t1
  left join t2 on t1.Constituent_ID=t2.Constituent_ID
 where t2.Constituent_ID is null
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
please refresh the page, I modified queries...
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
if your ListDate  is DateTime then you can use Cast for now
if the result is ok, we can write a better version
with
 t1 as (select * from dbo.tblRandRLapsedDonorReportDriverDaily 
         where cast(ListDate as date)=Convert(date,'9/20/2020',101))
 t2 as (select * from dbo.tblRandRLapsedDonorReportDriverDaily
         where cast(ListDate as date)=Convert(date,'10/3/2020',101))
select t1.*
  from t1
  left join t2 on t1.Constituent_ID=t2.Constituent_ID
 where t2.Constituent_ID is null

Author

Commented:
The query ran but t didn't return any data.  It seems related to the dates.  I tried changing the code to convert the date in the table rather than the parameter but it seems to run very slow.   Maybe I should look into changing the date type to date rather than datetime in the source table in our warehouse?  
Also, it seems it's not returning the correct data.  Perhaps I have the dates backwards.  Our data warehouse refresh just started so I'll need to wait until the morning to continue testing.  I think we're close.  I'll work on it in the morning and then accept the solution.

with
 t1 as (select * from dbo.tblRandRLapsedDonorReportDriverDaily
         where Convert(date,ListDate,101)='9/20/2020'),
 t2 as (select * from dbo.tblRandRLapsedDonorReportDriverDaily
         where Convert(date,ListDate,101)='10/3/2020')
select t1.*
  from t1
  left join t2 on t1.Constituent_ID=t2.Constituent_ID
 where t2.Constituent_ID is null
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
There's a more efficient way to do this, with less complex code too:

SELECT *
FROM tblRandRLapsedDonorReportDriverDaily t1
WHERE ListDate >= '20200920' /*first date*/ AND ListDate < '20200921' /*first date + 1 day*/ AND
    NOT EXISTS(SELECT 1 FROM tblRandRLapsedDonorReportDriverDaily t2 WHERE t2.Constituent_ID  = t1.Constituent_ID  AND t2.ListDate >= '20201003' /*second date*/ AND t2.ListDate < '20201004' /*second date plus 1 day*/)

NEVER use a function on a table column if it can be avoided.  Any function will prevent index seeks and could cause the query to run much slower.

HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
you should have an index on date column
also I suggest using this format...

is this running faster?

with
 t1 as (select * from dbo.tblRandRLapsedDonorReportDriverDaily
         where ListDate>=convert(date, '9/20/2020',101) and ListDate < dateadd(day, 1, convert(date, '9/20/2020',101) )
 t2 as (select * from dbo.tblRandRLapsedDonorReportDriverDaily
         where ListDate>=convert(date, '10/3/2020',101) and ListDate < dateadd(day, 1, convert(date, '10/3/2020',101) )
select t1.*
  from t1
  left join t2 on t1.Constituent_ID=t2.Constituent_ID
 where t2.Constituent_ID is null

Author

Commented:
I'll give it a try in the morning!

Author

Commented:
Thanks to both of you.  I'll test both solutions in the morning and post the results.  Also, I'll look into changing the ListDate field to date rather than datetime as that seems to be causing some of the issues.
Thanks,
Andy
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
if you do not care about time part, you should convert that field to date
and put an index on (ListDate, Constituent_ID)

and use

with
 t1 as (select *
          from dbo.tblRandRLapsedDonorReportDriverDaily
         where ListDate = convert(date, '9/20/2020', 101))
 t2 as (select *
          from dbo.tblRandRLapsedDonorReportDriverDaily
         where ListDate = convert(date, '10/3/2020', 101))
select t1.*
  from t1
  left join t2 on t1.Constituent_ID = t2.Constituent_ID
 where t2.Constituent_ID is null

Author

Commented:
ok, will do.

Thanks!
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
I've adjusted by query to properly account for the Constituent_ID.  That code also deals with the datetime issue.

HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
@Scott Pletcher 

there is an issue on second part

AND t2.ListDate = '20201003' /*second date*/ AND t2.ListDate < '20201004' /*second date plus 1 day*/)
also, author has one parameter, so "20201004" is not a parameter...
it should be calculated based on the parameter, like dateadd(day, 1, @date2)
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Naturally the OP will need to change the hard-coded dates to whatever parameter / variable is used to pass the dates in.  That's trivial enough that I ignored that part.

The key thingis to avoid using a function on the column.  Then SQL can do a single seek on an index of ( ListDate, Constituent_ID )  or ( Constituent_ID, ListDate ).  Based on the very limited info available so far, I think the first would be better, but either would support the query.  
awking00Information Technology Specialist
CERTIFIED EXPERT

Commented:
The except operator will give you the Constituent_IDs you want
select Constituent_ID from dbo.tblRandRLapsedDonorReportDriverDaily where Convert(date,'10/3/2020',101)
except
select Constituent_ID from dbo.tblRandRLapsedDonorReportDriverDaily where Convert(date,'10/3/2020',101);

If you want the additional info just
select * from dbo.tblRandRLapsedDonorReportDriverDaily where Constituent_ID in
(select Constituent_ID from dbo.tblRandRLapsedDonorReportDriverDaily where Convert(date,'10/3/2020',101)
 except
 select Constituent_ID from dbo.tblRandRLapsedDonorReportDriverDaily where Convert(date,'10/3/2020',101));


awking00Information Technology Specialist
CERTIFIED EXPERT

Commented:
Sorry, missed a couple of items -
select * from dbo.tblRandRLapsedDonorReportDriverDaily where Constituent_ID in
(select Constituent_ID from dbo.tblRandRLapsedDonorReportDriverDaily where ListDate = Convert(date,'10/3/2020',101)
 except
 select Constituent_ID from dbo.tblRandRLapsedDonorReportDriverDaily where ListDate = Convert(date,'10/3/2020',101))
and ListDate = Convert(date,'10/3/2020',101);

awking00Information Technology Specialist
CERTIFIED EXPERT

Commented:
Still had a copy and paste error -
select * from dbo.tblRandRLapsedDonorReportDriverDaily where Constituent_ID in
(select Constituent_ID from dbo.tblRandRLapsedDonorReportDriverDaily where ListDate = Convert(date,'10/3/2020',101)
 except
 select Constituent_ID from dbo.tblRandRLapsedDonorReportDriverDaily where ListDate = Convert(date,'09/20/2020',101))
and ListDate = Convert(date,'10/3/2020',101);

Author

Commented:
I made a change to awking00's code to deal with the dates but it returns too many rows:

select * from dbo.tblRandRLapsedDonorReportDriverDaily where Constituent_ID in
(select Constituent_ID from dbo.tblRandRLapsedDonorReportDriverDaily where Convert(date,ListDate,101)='10/3/2020'
 except
 select Constituent_ID from dbo.tblRandRLapsedDonorReportDriverDaily where Convert(date,ListDate,101)='9/20/2020'
and Convert(date,ListDate,101)='10/3/2020');

I'm expecting 213 rows but it returns 233,387 rows.  Perhaps my changes to the date processing is causing the result?  It is faster than the code below.

This code returns the correct number of rows but is very slow (14 minutes), most likely due to having to convert the date:

with
 t1 as (select * from dbo.tblRandRLapsedDonorReportDriverDaily
         where Convert(date,ListDate,101)='9/20/2020'),
 t2 as (select * from dbo.tblRandRLapsedDonorReportDriverDaily
         where Convert(date,ListDate,101)='10/2/2020')
select t1.*
  from t1
  left join t2 on t1.Constituent_ID=t2.Constituent_ID
 where t2.Constituent_ID is null

I'm going to look into changing the ListDate field from datetime to date after a meeting this morning.

awking00Information Technology Specialist
CERTIFIED EXPERT

Commented:
Well, I think one reason is that I got it backwards.
select * from dbo.tblRandRLapsedDonorReportDriverDaily where Constituent_ID in
(select Constituent_ID from dbo.tblRandRLapsedDonorReportDriverDaily where ListDate = Convert(date,'09/20/2020',101)
 except
 select Constituent_ID from dbo.tblRandRLapsedDonorReportDriverDaily where ListDate = Convert(date,'10/3/2020',101))
and ListDate = Convert(date,'09/20/2020',101);
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
This code returns the correct number of rows but is very slow (14 minutes), most likely due to having to convert the date

did you check the query here 

+ you need an index on date (ListDate, Constituent_ID) if you want to make it faster...
whats the primary key of this table?
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Why are you so determined to use a function on the column?  It's not needed and it will cause performance errors, not to mention possible conversion errors.

Good luck with that approach.
Sr. System Analyst
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

Commented:
I've been tied up all day in meetings and will try to continue on the project later tonight.

Scott, I was having an issue with the query not returning any records when I used just the date as the filter.  I was only able to retrieve records if I used >=9/2/2020 and <9/3/2020.  That didn't seem like it would work when I used the code in SSRS since I wanted the user to pick the two dates in the report.
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
because you are using DateTime in your database and they have time values inside...
therefore you should use (>=@DATE and < @DATE+1) in the query...
If you convert that column into date datatype, you will lose time part, then you dont need conversion or use 2 conditions in your query and can just use "="
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Sorry.

I believe you can just add 1 day to the SSRS date parameter, in the style I originally showed:

WHERE ListDate >= @FirstDate AND ListDate < DATEADD(DAY, 1, @FirstDate) AND
...t2.ListDate >= @SecondDate AND t2.ListDate < DATEADD(DAY, 1, @SecondDate)

This may seem "awkward", but it will perform vastly better with a matching index. 

Author

Commented:
To all,

Sorry for looking like I had abandoned the question after everyone was so helpful.  I've been out sick until now.  Feeling better and I'm back at it.  I'll accept the solution from HainKurt.  Awking00 I couldn't get the except statement to work.

Again thanks to everyone for the help!