Solved

SQL Sub Query

Posted on 2013-06-21
5
331 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
  • 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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Suggested Solutions

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

762 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now