• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 451
  • Last Modified:

Query to sum data by Quarter

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
RishiSingh05
Asked:
RishiSingh05
  • 2
1 Solution
 
cyberkiwiCommented:
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
 
cyberkiwiCommented:

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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now