asked on

subtract one totals query from another

I have two queries -
I need to be able to subtract query 1
query 1:

SELECT CLX_DESC as 'Application', COUNT(A.CLE_EVID)as 'Count',
      2 'OrderBy',AVG(DATEDIFF(SS, A.CLE_TIME, B.CLE_TIME)) 'TotalLength', UTILITY_NAME 'Utility'
      FROM CL_EVENTS_IDX WITH (NOLOCK)
      INNER JOIN CL_EVENTS A WITH (NOLOCK) ON CLE_IDX_KEY = CLE_ID
      INNER JOIN UTILITY_INFO WITH (NOLOCK) ON CLE_IDX_UCODE = UTILITY_KEY
      INNER JOIN CL_EVENTS B WITH (NOLOCK) ON A.CLE_ID = B.CLE_ID AND A.CLE_CALLID = B.CLE_CALLID
      INNER JOIN CL_APPID_XREF WITH (NOLOCK) on A.CLE_EVID = CLX_APPID
      WHERE A.CLE_TYPE = 508 AND B.CLE_TYPE = 536 and a.CLE_EVID = b.CLE_EVID
      AND (A.CLE_EVID = 12 OR A.CLE_EVID = 13)
      --AND A.CLE_TIME between '2012-02-05 07:30' and '2012-02-05 07:45'
      GROUP BY UTILITY_NAME, CLX_DESC
      ORDER BY OrderBy,Application

from query 2
query 2
SELECT 'All Calls' AS 'Application', COUNT(CLE_IDX_OFFHOOK) AS 'Count',
      isnull(avg(DATEDIFF(ss, CLE_IDX_OFFHOOK, CLE_IDX_ONHOOK)),0) 'TotalLength', 1 'OrderBy', UTILITY_NAME 'Utility'
      FROM CL_EVENTS_IDX WITH (NOLOCK) INNER JOIN UTILITY_INFO WITH (NOLOCK) ON CLE_IDX_UCODE = UTILITY_KEY
      GROUP BY UTILITY_NAME

in the final return - i need to have both the original results of query 2 and the results of (query 2 - query 1)

sachitjain

SELECT 'All Calls' AS 'Application',
            COUNT(CLE_IDX_OFFHOOK) AS 'Count',
            isnull(avg(DATEDIFF(ss, CLE_IDX_OFFHOOK, CLE_IDX_ONHOOK)),0) 'TotalLength',
            1 'OrderBy',
            UTILITY_NAME 'Utility'
FROM CL_EVENTS_IDX WITH (NOLOCK) INNER JOIN UTILITY_INFO WITH (NOLOCK) ON CLE_IDX_UCODE = UTILITY_KEY
GROUP BY UTILITY_NAME
Except
SELECT CLX_DESC as 'Application',
            COUNT(A.CLE_EVID)as 'Count',
            AVG(DATEDIFF(SS, A.CLE_TIME, B.CLE_TIME)) 'TotalLength',
            2 'OrderBy',
            UTILITY_NAME 'Utility'
FROM CL_EVENTS_IDX WITH (NOLOCK)
      INNER JOIN CL_EVENTS A WITH (NOLOCK) ON CLE_IDX_KEY = CLE_ID
      INNER JOIN UTILITY_INFO WITH (NOLOCK) ON CLE_IDX_UCODE = UTILITY_KEY
      INNER JOIN CL_EVENTS B WITH (NOLOCK) ON A.CLE_ID = B.CLE_ID AND A.CLE_CALLID = B.CLE_CALLID
      INNER JOIN CL_APPID_XREF WITH (NOLOCK) on A.CLE_EVID = CLX_APPID
WHERE A.CLE_TYPE = 508 AND B.CLE_TYPE = 536 and a.CLE_EVID = b.CLE_EVID
      AND (A.CLE_EVID = 12 OR A.CLE_EVID = 13)
      --AND A.CLE_TIME between '2012-02-05 07:30' and '2012-02-05 07:45'
GROUP BY UTILITY_NAME, CLX_DESC
ORDER BY OrderBy,Application

Tagom

ASKER

This solution DOES NOT change the totals returned. It seems to make no difference what so ever.

any other suggestions

sachitjain

I did not understand

You want to subtract your second resultset from first one or subtract column values. If the second one then which column value you want to subtract from which column value?

Tagom

ASKER

I need to subtract columns,
COUNT(CLE_IDX_OFFHOOK) AS 'Count' - COUNT(A.CLE_EVID)as 'Count'

The rest of the columns remain the same

ASKER CERTIFIED SOLUTION

sachitjain

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Tagom

ASKER

okay, going with this query I have culled out the columns to exactly what I need, (removed the y.app.....)
However the x.count - y.count 'count' returns a null
I have tried a few changes none are working -

select x.[Application], (x.[Count] - y.[Count]) AS 'Count', x.TotalLength, x.[OrderBy], x.[Utility]
from
      (SELECT 'All Calls' AS 'Application',
                        COUNT(CLE_IDX_OFFHOOK) AS 'Count',
                        isnull(avg(DATEDIFF(ss, CLE_IDX_OFFHOOK, CLE_IDX_ONHOOK)),0) 'TotalLength',
                        1 'OrderBy',
                        UTILITY_NAME 'Utility'
      FROM CL_EVENTS_IDX WITH (NOLOCK) INNER JOIN UTILITY_INFO WITH (NOLOCK) ON CLE_IDX_UCODE = UTILITY_KEY
      
      GROUP BY UTILITY_NAME) x
    left JOIN
      (SELECT CLX_DESC as 'Application',
                        COUNT(A.CLE_EVID)as 'Count',
                        AVG(DATEDIFF(SS, A.CLE_TIME, B.CLE_TIME)) 'TotalLength',
                        2 'OrderBy',
                        UTILITY_NAME 'Utility'
      FROM CL_EVENTS_IDX WITH (NOLOCK)
              INNER JOIN CL_EVENTS A WITH (NOLOCK) ON CLE_IDX_KEY = CLE_ID
              INNER JOIN UTILITY_INFO WITH (NOLOCK) ON CLE_IDX_UCODE = UTILITY_KEY
              INNER JOIN CL_EVENTS B WITH (NOLOCK) ON A.CLE_ID = B.CLE_ID AND A.CLE_CALLID = B.CLE_CALLID
              INNER JOIN CL_APPID_XREF WITH (NOLOCK) on A.CLE_EVID = CLX_APPID
      WHERE A.CLE_TYPE = 508 AND B.CLE_TYPE = 536 and a.CLE_EVID = b.CLE_EVID
              AND (A.CLE_EVID = 12 OR A.CLE_EVID = 13)
              --AND A.CLE_TIME between '2012-02-05 07:30' and '2012-02-05 07:45'
      GROUP BY UTILITY_NAME, CLX_DESC) y
      ON X.[Application] = Y.[Application]
      GROUP BY x.[Application],x.TotalLength, x.[OrderBy], x.[Utility],x.Count,y.Count
ORDER BY X.OrderBy, x.[Application]

Open in new window

sachitjain

This query would only work if you have applications names as common in 2 resultsets. Is it the case?