Retrieving Last Record (SQL)

Dear EE community,

I've tried searching this site and Google for related solutions although I have yet to find one that works for me. So it's just as easy to write a new question I guess.

The system is for Telesales Out Calling. The .asp page retrieves a list of all customers based on an entered Part Code (product.Product_Code IN '"&SPartCodes&"'.......) and then displays them alphabetically, with their telephone no, date of last order, creation date of note and the call back category of the note.

The categories for call backs are:

<% If pm.GetItem ("CallBackStatus") = "1" Then Response.Write("01 not in") Else
If pm.GetItem ("CallBackStatus") = "2" Then Response.Write("02 call back later today") Else If ..................................nip>
"3" = 03 call back tomorrow
"4" = 04 call back next week
"5" = 05 call back in a month
"6" = 06 call back in two months
"7" = 07 call back on another date
"8" = not interested / didn't buy
"9" = do not call again
"10" = order made

End If %>

Now, pretty self explanatory I'm sure as to what the system is doing. Nonetheless, I'm trying to get SQL to retrieve the latest record for a particular call back back status. I can't use Max, as that would only display the higher number. And Top 1 doesn't work either because the SQL query is nested in the asp page.

Also, it's important to mention that ALL notes have a NoteID. Reckon I should exploit this in any way?

Thanks in advance.

Luke, Bentham LTD t/a Inkjets & Toners

Code Snippet:

         
           
             1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:

           
           
             ----------
SQL
----------
(simplified to make it easier to read)
 
SELECT ijtd_export.cart.Customer_URN
       ijtd_export.customer.Customer_Title
       ijtd_export.customer.Customer_Forename
       ijtd_export.customer.Customer_Surname
       ijtd_export.customer.Customer_Telephone
    max(ijtd_export.cart.Cart_Order_DateTime)
    max(outcall.callnotes.CreatedDate)
       outcall.callnotes.CallBackStatus
 
FROM ijtd_export.cart
 
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
 
 
WHERE
 
cart.Cart_Order_DateTime BETWEEN '"&IncStartDate&"' AND '"&IncEndDate&"' AND
cart.Cart_Status = 'Order' AND
product.Product_Code IN '"&SPartCodes&"' AND
cart.Cart_Order_Group = '1' AND
NOT Customer_Surname LIKE '' AND
NOT Customer_Marketing LIKE 'DEAD' AND
NOT Customer_Telephone_Prefs LIKE 'Do not call'

           
         

Open in New Window

Select All

Solution: Retrieving Last Record (SQL)

yeah, sorry about that - should be LIMIT 1 at the end, not TOP 1...

Popular Tags

Browse All Tags