Solved

Query to sum data by Quarter

Posted on 2010-11-10
2
443 Views
Last Modified: 2012-05-10
The sql below displays four columns of data: ID / Month / Cohort / Amt

Month is in the format of yyyymm (eg. 200901, 200902).  

I would like to change the query so that the data is rolled up by quarter eg. 2009 Q1, 2009 Q2.

SELECT "PaidClaimsAustin" AS Id, [SMART-MedicalCost].Month, [SMART-MappingRateCellToCohort].Cohort, Sum([WCPPaidNet]/[%Complete]) AS Amt
FROM ([SMART-MedicalCost] LEFT JOIN [%complete] ON [SMART-MedicalCost].Month = [%complete].MthCompleted) LEFT JOIN [SMART-MappingRateCellToCohort] ON [SMART-MedicalCost].RateCell = [SMART-MappingRateCellToCohort].RateCell
GROUP BY "PaidClaimsAustin", [SMART-MedicalCost].Month, [SMART-MappingRateCellToCohort].Cohort, [%complete].Site, [SMART-MappingRateCellToCohort].SDA
HAVING ((([%complete].Site) Like "*Texas") AND (([SMART-MappingRateCellToCohort].SDA)="Travis"));


A similar question was posted here:
http://www.experts-exchange.com/Microsoft/Applications/Q_26602361.html
0
Comment
Question by:RishiSingh05
  • 2
2 Comments
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34106717
The filter should really be in there WHERE clause not HAVING, since it is working on base fields not an aggregate.  It will perform better.
0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
ID: 34106776

SELECT "PaidClaimsAustin" AS Id, left([SMART-MedicalCost].Month,4) & " Q" & int((right([SMART-MedicalCost].Month,2)-1)/3)+1, [SMART-MappingRateCellToCohort].Cohort, Sum([WCPPaidNet]/[%Complete]) AS Amt

FROM ([SMART-MedicalCost] LEFT JOIN [%complete] ON [SMART-MedicalCost].Month = [%complete].MthCompleted) LEFT JOIN [SMART-MappingRateCellToCohort] ON [SMART-MedicalCost].RateCell = [SMART-MappingRateCellToCohort].RateCell

WHERE ((([%complete].Site) Like "*Texas") AND (([SMART-MappingRateCellToCohort].SDA)="Travis"))

GROUP BY "PaidClaimsAustin", left([SMART-MedicalCost].Month,4) & " Q" & int((right([SMART-MedicalCost].Month,2)-1)/3)+1, [SMART-MappingRateCellToCohort].Cohort, [%complete].Site, [SMART-MappingRateCellToCohort].SDA;

Open in new window

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

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

758 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

20 Experts available now in Live!

Get 1:1 Help Now