Difficult query for service call system

[ThaBizness]

I have a service call application which in one table records the service call number and the time the call was placed. In another table I have the service call details and time a technician worked on it. In the details table there can be multiple entries for one service call because a tech might call or work on it multiple times before its closed.

SrvCall Tbl
svcNum
dateOpened
svcStatus

Details Table
svcNum
dateworked
timeworked
memodetails


I want to make a query where I can see each individual ticket, the time it was opened, and the time of the first contact from the other table. The ticket number and the time opened is in one table and the details and time the ticket was worked on in another.

How would I go about this?

 

[sfreeman@co.mer]

Simple query....

Try this:

SELECT
tblSrvCall.svcNum
, tblSrvCall.dateOpened
, tblSrvCall.svcStatus
, tblDetails.dateworked
, tblDetails.timeworked
, tblDetails.memodetails

FROM
tblSrvCall

INNER JOIN
tblDetails

ON
tblSrvCall.svcNum = tblDetails.svcNum;


HTH

 

[ThaBizness]

Thanks but...

If I do that then I get multiple entries for the same call in the result set if that ticket was worked on multiple times throughout the day. Example - Say I worked on SVC#1001 on 9/15/04 at 8AM, 9:45AM, and 11:30AM. Each time i worked on the call would be a new entry in the time details table so there would be 3 entries there with different times. I would get records for each of those times on the call with your query. I want only the first time the call was worked on for each individual ticket.

 

[sfreeman@co.mer]

OK, then two queries:

First to get just the first entry,

SELECT
tblSrvCall.svcNum
, First(tblDetails.timeworked)

AS
FirstOftimeworked

FROM
tblSrvCall

INNER JOIN
tblDetails

ON
tblSrvCall.svcNum = tblDetails.svcNum

GROUP BY
tblSrvCall.svcNum;


Second to get the other data:

SELECT
ServiceCall.svcNum
, ServiceCall.FirstOftimeworked
, tblDetails.dateworked
, tblDetails.memodetails

FROM
ServiceCall

INNER JOIN
tblDetails

ON
ServiceCall.svcNum = tblDetails.svcNum

GROUP BY
ServiceCall.svcNum
, ServiceCall.FirstOftimeworked
, tblDetails.dateworked
, tblDetails.memodetails;


there may be a more elegant way to do this, but quick and dirty, here you go.

HTH

 

[ThaBizness]

I get a error in the select statement for First.

 

[KKilfoil]

Try "MIN" rather than "FIRST"

 

[ThaBizness]

Now i get a error "You Tried to execute a query that doesn't include the specified expression 'CallNumber' as part of a aggregate function"

 

[ThaBizness]

I will post my SQL Statement. Please help. The error i get is in the message above

SELECT dbo_SCCalls.CallNumber, dbo_SCCalls.Date, dbo_SCCalls.Status, Min(dbo_TimeHistory.T_DateWorked), Min(dbo_TimeHistory.T_StartTime)
AS FirstOfTimeWork
FROM dbo_SCCalls INNER JOIN dbo_TimeHistory ON dbo_SCCalls.CallNumber = dbo_TimeHistory.T_SCNumber
WHERE (((dbo_SCCalls.Status)="I"));