Use SQL results to make a new query

The question is related with telephony info, while i'm using Genesys http://www.genesyslab.com, but i preferred to post question here because is a SQL related question. I've two tables:

(Table 1: "CallerID") One table containing CallerID and ConnID (identifies a call). (Table 2: "Callinfo") The other table contains, among others, the CallerID, the date of the call incoming (stored in int format with a way to translate 开发者_StackOverflowit) and a Status row, which corresponds with an action i will explain below. One example row is

ConnID                StartTime   Status
--------------------- ----------- -----------
30119914212527698     1300286888  2

Everytime a call enters both tables are updated. What i need to do is to get all the calls that entered the last two days with status 2 or 4 (those are calls that weren't answered) from Callinfo and get the CallerID from CallerID table. After that i need to check if the same CallerID has new registers in Callinfo table checking by its ConnID. If those new registers have a status different from 2 o 4 (means success in the comunication) i should remove the CallerID of first query.

The purpose of the query is to call those clients that tried to call within last two days but couldn't.

The first query is working ok, you should ignore DATEDIFF function.

DECLARE @twoDays BIGINT
SET @twoDays = 172800   --48hs expressed in seconds

SELECT DISTINCT ConnID, StartTime, CID.CallerID, Status FROM Callinfo
INNER JOIN dbo.CallerID CID
    ON Callinfo.ConnID = CID.ConnID
WHERE 
    (Status = 2 OR Status = 4)
    AND 
    StartTime > DATEDIFF(second, '1970-01-01 00:01:00', GETUTCDATE())- @twoDays

I don't know how to use the results and check if there are new calls with status different of 2 and 4.

I'm working on SQL Server 2005.

The following sequence can clear up: If the situation is like this with caller A:

1/1 12pm: A call OK 
1/1 1pm: A call FAIL 

then i should get the result. But if the situation is like this:

1/1 12pm: A call OK 
1/1 1pm: A call FAIL 
2/1 12pm: A call OK 

then i should not get A as a result. So, when i check on 3/1 i wont get the result because the last call from A was ok, and on 4/1 caller A shouldn't appear on first query.

---

Something like this should be faster.

DECLARE @twoDaysAgo DateTime
SET @twoDaysAgo = DATEADD(day,-2,GETDATE())

WITH MissedCalls AS
(
  SELECT DISTINCT ConnID, StartTime, CID.CallerID, Status FROM Callinfo
  JOIN dbo.CallerID CID ON Callinfo.ConnID = CID.ConnID
  WHERE Status IN (2,4)
    AND StartTime > @twoDaysAgo
), NotMissedCalls AS
(
   SELECT DISTINCT ConnID, StartTime, CID.CallerID, Status 
   FROM Callinfo
   JOIN dbo.CallerID CID ON Callinfo.ConnID = CID.ConnID
   WHERE Status not in (2,4)
    AND StartTime > @twoDaysAgo
    -- following line may speed up or slow down depending on table sizes and indexes
    AND CID.CallerID in (SELECT CALLERID FROM MissedCalls)
)
SELECT *
FROM MissedCalls
WHERE NOT CallerID IN (SELECT CallerID FROM NotMissedCalls)

NOTE: I did not test so I might have typos. Just to be safe I put in all columns for all queries but I expect this can be improved. (Less columns should be marginally faster.)

---

This solves the new problem.

DECLARE @twoDaysAgo DateTime
SET @twoDaysAgo = DATEADD(day,-2,GETDATE())

WITH MissedCalls AS
(
  SELECT ConnID, Max(StartTime) as LastFailDate, CID.CallerID  FROM Callinfo
  JOIN dbo.CallerID CID ON Callinfo.ConnID = CID.ConnID
  WHERE Status IN (2,4)
    AND StartTime > @twoDaysAgo
  GROUP BY ConnID, CID.CallerID
), NotMissedCalls AS
(
   SELECT ConnID,Max(StartTime) as LastGoodDate,CID.CallerID 
   FROM Callinfo
   JOIN dbo.CallerID CID ON Callinfo.ConnID = CID.ConnID
   WHERE Status not in (2,4)
    AND StartTime > @twoDaysAgo
    -- following line may speed up or slow down depending on table sizes and indexes
    AND CID.CallerID in (SELECT CALLERID FROM MissedCalls)
   GROUP BY ConnID, CID.CallerID
), CallList AS
(
   SELECT LastFailDate, LastGoodDate, Bad.ConnID, Bad.CallerID
   FROM MissedCalls Bad
   JOIN NotMissedCalls Good ON Bad.ConnID = Good.ConnID AND Bad.CallerID = Good.CallerID
)
SELECT LastFailDate, ConnID, CallerID
FROM CallList
WHERE (LastGoodDate > LastFailDate) OR (LastGoodDate IS NULL)

---

I think I have a working base.

DECLARE @twoDays BIGINT
SET @twoDays = 172800   --48hs expressed in seconds

SELECT DISTINCT ConnID, StartTime, CID.CallerID, Status FROM Callinfo
INNER JOIN dbo.CallerID CID
    ON Callinfo.ConnID = CID.ConnID
WHERE 
    (Status = 2 OR Status = 4)
    AND 
    StartTime > DATEDIFF(second, '1970-01-01 00:01:00', GETUTCDATE())- @twoDays
    AND CID.CallerID NOT IN (
        SELECT DISTINCT CID.CallerID FROM Callinfo
        INNER JOIN dbo.CallerID CID
        ON Callinfo.ConnID = CID.ConnID
        WHERE 
            (Status <> 2 AND Status <> 4)    
        AND 
            StartTime > DATEDIFF(second, '1970-01-01 00:01:00', GETUTCDATE())- @twoDays
        )

This query will remove the callerID when it appears in the second query: that is to say a caller that manage to call during the last 2 days.

Flaw: It can't detect if A calls day 1, recall day 2 and fails (range is day 1 to day 3). But he would raise on the next check when you would be trying day 2 to day 4.