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?
 
JimFiveConnect With a Mentor Commented:
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
 
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
All Courses

From novice to tech pro — start learning today.