Solved

Query to sum data by Quarter

Posted on 2010-11-10
2
444 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This very simple solution applies to a narrow cross-section of the "needs to close" variety. In this case, the full message in Event Viewer was in applog, Event ID 1000: Faulting application iexplore.exe, version 8.0.6001.18702, faulting module …
The canonical version of this article is on my web site here: http://iconoun.com/articles/collisions/ A companion presentation is available here: http://iconoun.com/articles/collisions/Unicode_Presentation.pdf
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

895 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

15 Experts available now in Live!

Get 1:1 Help Now