Solved

SQL Sub Query

Posted on 2013-06-21
5
335 Views
Last Modified: 2013-06-21
Hi,

Easy one for you lot I am sure..

I am super inexperienced with this sql, and as a result, for this sort of query I would create a separate tmp table at the start, and count that tmp table when calculating results. I know that is an inefficient way of doing it so would like to utilize the sub query. Could somebody please correct the following for me?

SELECT
SUM(OutstandingReserves)      AS      [OutstandingReserves],
SUM(Payments)            AS      [Payments],
SUM(TotalIncurred)            AS      [TotalIncurred],
SUM(NotificationsMTD)      AS      [NotificationsMTD],

COUNT (ClaimNumber) WHERE
(SELECT ClaimNumber WHERE Status IN ('open','reopened')
FROM mi.Archive_tblFinanceSummary_2013_03_01))
                                                   AS [CountOpenClaims]


FROM mi.Archive_tblFinanceSummary_2013_03_01


Thanks!
0
Comment
Question by:BananaFury
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 200 total points
ID: 39265521
As you have not said what you are trying to achieve here ( what that sql meant to do).

I guess you are after either one of the below code

scenario 1:

SELECT 
SUM(OutstandingReserves)      AS      [OutstandingReserves],
SUM(Payments)            AS      [Payments],
SUM(TotalIncurred)            AS      [TotalIncurred],
SUM(NotificationsMTD)      AS      [NotificationsMTD],

COUNT (ClaimNumber) As CountOpenClaims
FROM mi.Archive_tblFinanceSummary_2013_03_01 T
WHERE T.ClaimNumber IN (SELECT ClaimNumber WHERE Status IN ('open','reopened')
FROM mi.Archive_tblFinanceSummary_2013_03_01)) 

Open in new window


scenario 2:

SELECT 
SUM(OutstandingReserves)      AS      [OutstandingReserves],
SUM(Payments)            AS      [Payments],
SUM(TotalIncurred)            AS      [TotalIncurred],
SUM(NotificationsMTD)      AS      [NotificationsMTD],

COUNT (CASE WHEN ClaimNumber IN ('open','reopened') THEN 1 ELSE 0 END ) AS [CountOpenClaims]
FROM mi.Archive_tblFinanceSummary_2013_03_01

Open in new window

0
 

Author Closing Comment

by:BananaFury
ID: 39265656
Perfect, thanks so much!
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39265682
In looking at your existing query I would interpret the intention the following way
- a "scenario 1.5" ;)  it does not appear to require a subquery
SELECT
      SUM(OutstandingReserves) AS [OutstandingReserves]
    , SUM(Payments)            AS [Payments]
    , SUM(TotalIncurred)       AS [TotalIncurred]
    , SUM(NotificationsMTD)    AS [NotificationsMTD]
    , COUNT(ClaimNumber)       AS [CountOpenClaims]
FROM mi.Archive_tblFinanceSummary_2013_03_01
WHERE STATUS IN ( 'open', 'reopened' )

Open in new window

Scenario 1.5  would sum() data for all information in that table with status  'open', 'reopened' and the count() would be of only those too.

Neo_jarvis has proposed (in scenario 2) a different method which could produce a different result, the sum() results would be of all data, but the count would be of only those with status  'open', 'reopened' due to the case expression.

with respect to this:
>>I would create a separate tmp table at the start, and count that tmp table when calculating results.
If you have the confidence to build a temp table and then query from that;
it is really just a small jump to avoid the temp table and query without one

Whilst in SQL there are very few absolutes - it is likely that always building a temp table isn't the most efficient way. (i.e. It takes time/effort to produce that temp table which could be devoted to directly producing the wanted result.)

is [Archive_tblFinanceSummary_2013_03_01] such a table? (it does not appear to be a temp table - but a permanent archive)

btw:  in your existing code and scenario 1 above,  
place the from before the where in the subquery
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39265686
really....

there are syntax errors in both your existing query and scenario 1 ???
oh well - too late it seems.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39265707
@Neo_jarvis: I hasten to add: "uncharacteristically"
 - no offence intended to any participant,
just watch out for the from which happens after the where
0

Featured Post

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

Suggested Solutions

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

738 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