Script Help DM MAIL alert

I am trying to get my script to show data for a customer OVER XX DAYS instead of exactly XX days.
DECLARE @min_days_old int, 
@invoice_class_to_ignore varchar(10),
@oldest_invoice_date datetime,
@customer_id varchar(12),
@invoice_no varchar(10),
@subj varchar(200),
@recipient_list varchar(500),
@rows int
SET @recipient_list = 'coryj@electricalsalesinc.com'
SET @min_days_old = 90
SET @invoice_class_to_ignore = 'FINANCE'
-- create table to store the results
IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME = 'customer_list' AND xtype = 'u')
DROP TABLE customer_list
CREATE TABLE customer_list(customer_id varchar(12), invoice_no varchar(10), invoice_date datetime)
DECLARE c CURSOR FOR 
SELECT customer_id, min(invoice_date) 
FROM p21_view_invoice_hdr ih
WHERE paid_in_full_flag = 'N'
and invoice_class <> @invoice_class_to_ignore
--ignore credits, debits, adjustments, downpayment invoices
and invoice_adjustment_type NOT IN ('C', 'D', 'P', 'A')
 
GROUP BY customer_id 
HAVING MIN(CONVERT(varchar, invoice_date,101)) = CONVERT(varchar, (GETDATE() - @min_days_old), 101)
OPEN c
FETCH NEXT FROM c INTO @customer_id, @oldest_invoice_date
WHILE @@fetch_status = 0
BEGIN
SELECT @invoice_no = invoice_no FROM p21_view_invoice_hdr WHERE @oldest_invoice_date = invoice_date
INSERT INTO customer_list VALUES (@customer_id, @invoice_no, @oldest_invoice_date)
FETCH NEXT FROM c INTO @customer_id, @oldest_invoice_date 
END
SET @subj = 'Customer is ' + CAST(@min_days_old as varchar(5)) + ' Days Past Due'
SELECT @rows = COUNT(*) from customer_list
if @rows > 0
BEGIN
 
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DB MAIL',
@Recipients = @recipient_list,
@importance ='High',
@Subject = @Subj,
@query = 'SELECT * FROM customer_list'
--clean up
DROP TABLE customer_list
CLOSE c
DEALLOCATE c

Open in new window

LVL 1
JiggensAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JimFiveCommented:
Change

HAVING MIN(CONVERT(varchar, invoice_date,101)) = CONVERT(varchar, (GETDATE() - @min_days_old), 101)

to

HAVING MIN(CONVERT(varchar, invoice_date,101)) <= CONVERT(varchar, (GETDATE() - @min_days_old), 101)
0
JiggensAuthor Commented:
Ok i made that change, i dont know why i didnt see that but now i am getting an error.

Msg 22050, Level 16, State 1, Line 0
Error formatting query, probably invalid parameters
Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 478
Query execution failed: Msg 208, Level 16, State 1, Server SQLSERVER, Line 1
Invalid object name 'customer_list'.
0
JimFiveCommented:
In line 44 of your posted code qualify  your table name:

Change: @query = 'SELECT * FROM customer_list'

to
@query = 'SELECT * FROM [DatabaseName].[dbo.].[customer_list]'

0
JiggensAuthor Commented:
I dont think thats the problem, i think it has to do something with the date conversion because it works with certain numbers.  What do you think?
0
JimFiveCommented:
First I think that your cursor and everything could be replaced by the below query:

select customer_id, invoice_no, oldest_invoice_date
from p21_view_invoice_hdr ih
inner join (select customer_id, min(invoice_date) as oldest_invoice_date from p21_view_invoice_header) m
on ih.invoice_date = m.oldest_invoice_date and ih.customer_id = m.customer_id
WHERE paid_in_full_flag = 'N'
and invoice_class <> @invoice_class_to_ignore
--ignore credits, debits, adjustments, downpayment invoices
and invoice_adjustment_type NOT IN ('C', 'D', 'P', 'A')
and m.oldest_invoice_date < = GetDate()-@min_days_old


Second, I think that this:
HAVING MIN(CONVERT(varchar, invoice_date,101)) <= CONVERT(varchar, (GETDATE() - @min_days_old), 101)

Should perhaps be:
Having Min(invoice_date) <= GetDate() - @min_days_old

I don't think there's any reason to convert them both into varchars to compare which won't work properly anyway.
--
JimFive
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.