Need help with a SQL Statement
-
I've got 2 tables, one contains call records indexed by a callid, the 2nd table, which has that same callid contains journal entries related to the callid which are stored as a text datatype. In my select, i want to bring back the call record information from the first table based on callid, but only the last journal entry made to the journal table and not the full list of journal entries. I've tried using the MAX command, but SQL doesn't seem to like that on a text datatype, as i get the following error:
- The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
Here's what i have, which brings back every journal record:
SELECT
cl.CustID as Customer,
cl.CallStatus as Status,
RIGHT(cl.CallID, 4) AS HEAT,
cl.PVCS,
cl.Title,
cl.CallDesc AS Description,
cl.RecvdDate AS Date,
CONVERT(datetime,
CONVERT(varchar(10),j.EntryDate) + ' ' +
CONVERT(varchar(8),j.EntryTime)) AS JournalEntered,
j.EntryText AS Journal
FROM CallLog cl, Journal j
WHERE
cl.callid = j.callid
AND
cl.CallStatus IN ('Triage')
ORDER BY cl.CallStatus desc, cl.CustID, cl.CallID asc
Any suggestions are greatly appreciated.
thanks,
Ray
-
What in the Journal table determines the "lastest" entry and what is its column definition.
-
Divide and conquer
SELECT
cl.CustID as Customer,
cl.CallStatus as Status,
RIGHT(cl.CallID, 4) AS HEAT,
cl.PVCS,
cl.Title,
cl.CallDesc AS Description,
cl.RecvdDate AS Date,
JournalEntered,
j2.EntryText AS Journal
FROM
CallLog cl
left join
(select callid, max(EntryDate + EntryTime) JournalEntered
from Journal
group by callid ) lastj
on cl.callid = lastj.callid
left join
Journal j2
on lastj.callid = j2.callid
WHERE
cl.CallStatus IN ('Triage')
ORDER BY cl.CallStatus desc, cl.CustID, cl.CallID asc
* Noel -
The Journal table has a unique datetime stamp on each journal entry as well as a unique journal identifier called heatseq.
Thanks for the reply, Noel. I tried your suggestion, however instead of getting just 1 record in the result set (which is what I would expect) i get 3 (because there are 3 journal entries).
Thanks for the responses, guys.
Ray
-
Raymond,
I overlooked that! and I am not able to test it but this should "kill" my bug
SELECT
cl.CustID as Customer,
cl.CallStatus as Status,
RIGHT(cl.CallID, 4) AS HEAT,
cl.PVCS,
cl.Title,
cl.CallDesc AS Description,
cl.RecvdDate AS Date,
JournalEntered,
(select EntryText from Journal j2 where lastj.callid = j2.callid ) AS Journal
FROM
CallLog cl
left join
(select callid, max(EntryDate + EntryTime) JournalEntered
from Journal
group by callid ) lastj
on cl.callid = lastj.callid
WHERE
cl.CallStatus IN ('Triage')
ORDER BY cl.CallStatus desc, cl.CustID, cl.CallID asc
hth
* Noel -
That'll do it! Thanks, Noel!
-
Cheers!
* Noel
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply