Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Sub Query

Posted on 2013-06-21
5
Medium Priority
?
339 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 800 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 49

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 49

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 49

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

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…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
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.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

670 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