?
Solved

subtract one totals query from another

Posted on 2012-08-30
7
Medium Priority
?
465 Views
Last Modified: 2012-09-13
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)
0
Comment
Question by:Tagom
  • 4
  • 3
7 Comments
 
LVL 12

Expert Comment

by:sachitjain
ID: 38352902
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
0
 

Author Comment

by:Tagom
ID: 38367975
This solution DOES NOT change the totals returned. It seems to make no difference what so ever.

any other suggestions
0
 
LVL 12

Expert Comment

by:sachitjain
ID: 38368281
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?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Tagom
ID: 38368659
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
0
 
LVL 12

Accepted Solution

by:
sachitjain earned 1500 total points
ID: 38370928
Then I guess Application should be the common column between 2 resultsets. Based on this assumption, following could be your query

select x.[Application], x.[Count] - y.[Count] 'CountDiff', x.TotalLength, y.TotalLength, x.[OrderBy], y.[OrderBy], x.[Utility], y.[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]
ORDER BY X.OrderBy, x.[Application]
0
 

Author Comment

by:Tagom
ID: 38372268
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

0
 
LVL 12

Expert Comment

by:sachitjain
ID: 38375324
This query would only work if you have applications names as common in 2 resultsets. Is it the case?
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question