April 18, 2008 at 2:23 am
Hi
I'm trying to do a query on a call logging database, but want to return the call number with the latest history update.
SELECT Call_No, Call_Log_Date, Call_Resolve_Time, Call_Downtime,
Call_Resolve_Downtime, Call_Description,
ACTIONS_DESC AS Last_Action, HISTORY_ORDER , RV_CALL.Partition
FROM RV_CALL
INNER JOIN CL_PROBLEM_HISTORY
ON RV_CALL.Call_No = CL_PROBLEM_HISTORY.CALL_NUMBER
WHERE Problem_Type_Composite = 'Reque/Confi/New Sta'
AND Call_Last_Action_Date = HISTORY_DATE
The problem is I'm joining the latest history update from another table and the last action date has two or three history entries, but I only want the last or first.
For example, returned values for query might be:
Call No Call Desc Last Action History Order
------ -------- ----------- ------------
18 Blah Blah Call Closed by user 7
18 Blah Blah Automated e-mail on call closure 8
20 Ping Pong Advised that this is now resolved 5
20 Ping Pong Call Closed by user 6
20 Ping Pong Automated e-mail on call closure 7
I only want to get something like:
Call No Call Desc Last Action History Order
------ -------- ----------- ------------
18 Blah Blah Call Closed by user 7
20 Ping Pong Advised that this is now resolved 5
I've tried using MAX(HISTORY_ORDER) in various places to no avail and I'd rather not do this using a cursor or creating another table.
Thanks,
Akeel.
April 18, 2008 at 2:46 am
[font="Verdana"]
Have you tried your query this way? It work well for me. This query will return the last History Orders. And you need the first one then just only replace the Max with Min.
SELECT Call_No
,Call_Log_Date
,Call_Resolve_Time
,Call_Downtime
,Call_Resolve_Downtime
,Call_Description
,ACTIONS_DESC AS Last_Action
,Max(HISTORY_ORDER)
,RV_CALL.Partition
FROM RV_CALL INNER JOIN CL_PROBLEM_HISTORY
ON RV_CALL.Call_No = L_PROBLEM_HISTORY.CALL_NUMBER
WHERE Problem_Type_Composite = 'Reque/Confi/New Sta'
AND Call_Last_Action_Date = HISTORY_DATE
GROUP BY Call_No
,Call_Log_Date
,Call_Resolve_Time
,Call_Downtime
,Call_Resolve_Downtime
,Call_Description
,ACTIONS_DESC AS Last_Action
,RV_CALL.Partition
confirm on this,
Mahesh
[/font]
MH-09-AM-8694
April 18, 2008 at 2:58 am
Hi Mahesh,
Thanks for replying.
I have already tried this and it works partially, but only if I remove the call description and the action description as they're string data types, they cannot be sorted in the Group by clause. If I remove them, then I don't really get the data that I need, I just get the numbers and not the text descriptions of what the actions were, or what the call description is.
Akeel.
April 18, 2008 at 4:52 am
Ok, maybe give this a try...
It's not tested, since you didn't give table structure or sample data
SELECT Call_No,
Call_Log_Date,
Call_Resolve_Time,
Call_Downtime,
Call_Resolve_Downtime,
Call_Description,
ACTIONS_DESC AS Last_Action,
LatestHistory,
RV_CALL.Partition
FROM RV_CALL
INNER JOIN CL_PROBLEM_HISTORY ON RV_CALL.Call_No = CL_PROBLEM_HISTORY.CALL_NUMBER
INNER JOIN
(SELECT Call_Number, Max(History_Order) LatestHistory FROM CL_PROBLEM_HISTORY group by Call_Number) AS LatestAction
ON CL_PROBLEM_HISTORY.Call_number = LatestAction.Call_number
AND CL_PROBLEM_HISTORY.History_order = LatestAction.LatestHistory
WHERE Problem_Type_Composite = 'Reque/Confi/New Sta'
AND Call_Last_Action_Date = HISTORY_DATE
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 18, 2008 at 4:57 am
Hi Akeel..
You can try the below..
Because i did the same in my project while i was in that kind of situation like you met..
Its worked well..
Just add the column name as u want from the problem history table..
SELECT Call_No, Call_Log_Date, Call_Resolve_Time, Call_Downtime,
Call_Resolve_Downtime, Call_Description,
ACTIONS_DESC AS Last_Action, HISTORY_ORDER , RV_CALL.Partition,
(select Top 1 from CL_PROBLEM_HISTORY where CALL_NUMBER = RV_CALL.Call_No order by HistoryOrder desc)
FROM RV_CALL
WHERE Problem_Type_Composite = 'Reque/Confi/New Sta'
AND Call_Last_Action_Date = HISTORY_DATE
Ramkumar . K
Senior Developer
######################
No Surrender... No Give Up....
######################
April 18, 2008 at 5:06 am
That will work. Just be aware that it has the potential to become a major performance problem when the tables get large.
The subquery in the select clause runs once for every row in the outer result set. It is esentially a cursor in disguise.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 18, 2008 at 5:21 am
Thanks for replying guys. Gilamonster, that worked perfectly, thanks.
It was really annoying me and I knew it would be pretty straightforward.
April 18, 2008 at 5:28 am
Hi Gail..
Greetings..
Thanks for your kind notification against my reply..
I have doubt with Linked server concept..
Can u help in that????
Specified Below :
I am using the below statements to create a linked server with the Excel file and also to view the contents of that...
I would like to insert new data into that excel file from Sql server 2005 using liked server concept.
Is it possible to insert data in that way..?
Can any describe that or offer any other better solution??
Example:
--Use Test
--
--EXEC sp_addlinkedserver 'ImportData',
--
--'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0',
--
--'C:\ExcelData1.xls',
--
--NULL,
--
--'Excel 8.0'
SELECT *
FROM OPENQUERY(ImportData, 'SELECT * FROM [EmployeeDataTesting$]')
Ramkumar . K
Senior Developer
######################
No Surrender... No Give Up....
######################
April 18, 2008 at 5:53 am
Please create a new thread for your question.
Thanks
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply