Dear EE community,
Developed a system here for the company I work for. It is a telesales and out calling notes logging system. It pulls off a list of all customers, depending on the Part Code(s) used and then displays a list of all that have ordered. This list is pumped into an asp page and the results are displayed, 50 per page. This however, isn't the main issue.
As you can see from the SELECT statement, the main values retrieved are the Customer's Unique Ref No, Title, Forename, Surname, Telephone, their last order and the date a last note was made on their customer sheet.
The problem, therein lies with this left join:
left join outcall.callnotes on outcall.callnotes.Customer
_URN = ijtd_export.customer.Custo
Because, obviously, I still want customers to be displayed in the list, even if a note hasn't been put on their sheet. It works great but takes several minutes to pull off all the data from our linux SQL server.
Is there a quicker way of achieving this? At the moment, I have dropped this column from the system altogether as it was just too slow.
Thoughts and feelings would be most appreciated. If you need any other details, I will keep updating this thread. Thanks!
IT Engineer for Bentham Ltd t/a Inkjets & Toners
join ijtd_export.cartitem on ijtd_export.cartitem.Cart_URN = ijtd_export.cart.Cart_URN
join ijtd_export.customer on ijtd_export.cart.Customer_URN = ijtd_export.customer.Customer_URN
join ijtd_export.product on ijtd_export.product.Product_URN = ijtd_export.cartitem.Product_URN
left join outcall.callnotes on outcall.callnotes.Customer_URN = ijtd_export.customer.Customer_URN
cart.Cart_Status = 'Order' and
cart.Cart_Order_Group = '1' and
cart.Cart_Order_DateTime between '"&IncStartDate&"' and '"&IncEndDate&"' and
cart.Cart_Order_DateTime between '"&ExcStartDate&"' and '"&ExcEndDate&"' and
product.Product_Code IN ("&SPartCodes&") and
NOT Customer_Surname like '' and
NOT customer.Customer_Marketing like 'DEAD' and
NOT customer.Customer_Telephone_Prefs like 'Do not call' and
NOT EXISTS (select NULL from cart join cartitem on cartitem.Cart_URN = cart.Cart_URN join product on cartitem.Product_URN = product.Product_URN where cart.Customer_URN = customer.Customer_URN)