RishiSingh05
asked on
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-MappingRateCellToCo hort].Coho rt, Sum([WCPPaidNet]/[%Complet e]) AS Amt
FROM ([SMART-MedicalCost] LEFT JOIN [%complete] ON [SMART-MedicalCost].Month = [%complete].MthCompleted) LEFT JOIN [SMART-MappingRateCellToCo hort] ON [SMART-MedicalCost].RateCe ll = [SMART-MappingRateCellToCo hort].Rate Cell
GROUP BY "PaidClaimsAustin", [SMART-MedicalCost].Month, [SMART-MappingRateCellToCo hort].Coho rt, [%complete].Site, [SMART-MappingRateCellToCo hort].SDA
HAVING ((([%complete].Site) Like "*Texas") AND (([SMART-MappingRateCellTo Cohort].SD A)="Travis "));
A similar question was posted here:
https://www.experts-exchange.com/questions/26602361/Change-a-query-to-roll-up-data-by-quarter.html
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,
FROM ([SMART-MedicalCost] LEFT JOIN [%complete] ON [SMART-MedicalCost].Month = [%complete].MthCompleted) LEFT JOIN [SMART-MappingRateCellToCo
GROUP BY "PaidClaimsAustin", [SMART-MedicalCost].Month,
HAVING ((([%complete].Site) Like "*Texas") AND (([SMART-MappingRateCellTo
A similar question was posted here:
https://www.experts-exchange.com/questions/26602361/Change-a-query-to-roll-up-data-by-quarter.html
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.