March 7, 2012 at 2:57 am
I have googled this to death and don't seem to be able to nail it so any help appreciated
basically... i have a table of performance records, upon which each case is categorised into various performance buckets and this is saved as a view VwPerformanceGroup
i've then tried to write a set of nested queries which summarise these according to each financial quarter (apr-mar)
now the problem - i want to create a moving average which calculates the percentage of passes in each bucket over the last 4 records (i.e. the last 4 financial quarters) but i'm going wrong somewhere as i have onyl managed a running total across the entire set
SELECT
COUNT(Z.FINANCIAL_QUARTER)AS RANKQ
,Z.FINANCIAL_QUARTER
,SUM(Z2.PSUNDER13) AS RUNNING_UNDER_13
FROM
(SELECT
RANK =COUNT(*)
,A.FINANCIAL_DECIDED AS FINANCIAL_QUARTER
,COUNT(A.LTGDCCASE)AS Q_COUNT
,COUNT(DATE_PR_TARGET)AS PPA_COUNT
,SUM (A.UNMET) AS UNMET_PPA
,SUM(A.PSFUNDER13) AS PSUNDER13
,SUM(A.PSFOVER13) AS PSFOVER13
,SUM(A.AGREED) AS AGREED
,SUM(A.OUTSIDE) AS OUTSIDE
FROM
(SELECT
LTGDCCASE
,FINANCIAL_DECIDED
,DATE_PR_TARGET
,CASE WHEN (PSF_PERFORMANCE_GROUP=1) THEN 1 ELSE 0 END AS PSFUNDER13
,CASE WHEN (PSF_PERFORMANCE_GROUP=2) THEN 1 ELSE 0 END AS PSFOVER13
,CASE WHEN (AGREEDTIME_PERFORMANCE_GROUP=1) THEN 1 ELSE 0 END AS AGREED
,CASE WHEN (AGREEDTIME_PERFORMANCE_GROUP=2) THEN 1 ELSE 0 END AS OUTSIDE
,CASE WHEN (PERFORMANCE_GROUP=4) THEN 1 ELSE 0 END AS UNMET
FROMVWPERFORMANCEGROUP)A
GROUP BYFINANCIAL_DECIDED )Z,
(SELECT
A2.FINANCIAL_DECIDED AS FINANCIAL_QUARTER
,COUNT(A2.LTGDCCASE)AS Q_COUNT
,COUNT(A2.DATE_PR_TARGET)AS PPA_COUNT
,SUM (A2.UNMET) AS UNMET_PPA
,SUM(A2.PSFUNDER13) AS PSUNDER13
,SUM(A2.PSFOVER13) AS PSFOVER13
,SUM(A2.AGREED) AS AGREED
,SUM(A2.OUTSIDE) AS OUTSIDE
FROM
(SELECT
LTGDCCASE
,FINANCIAL_DECIDED
,DATE_PR_TARGET
,CASE WHEN (PSF_PERFORMANCE_GROUP=1) THEN 1 ELSE 0 END AS PSFUNDER13
,CASE WHEN (PSF_PERFORMANCE_GROUP=2) THEN 1 ELSE 0 END AS PSFOVER13
,CASE WHEN (AGREEDTIME_PERFORMANCE_GROUP=1) THEN 1 ELSE 0 END AS AGREED
,CASE WHEN (AGREEDTIME_PERFORMANCE_GROUP=2) THEN 1 ELSE 0 END AS OUTSIDE
,CASE WHEN (PERFORMANCE_GROUP=4) THEN 1 ELSE 0 END AS UNMET
FROMVWPERFORMANCEGROUP)A2
GROUP BYFINANCIAL_DECIDED )Z2
WHERE Z.FINANCIAL_QUARTER >= Z2.FINANCIAL_QUARTER AND RANK IN (Z.RANK , Z.RANK-4)
GROUP BY Z.FINANCIAL_QUARTER
ORDER BY Z.FINANCIAL_QUARTER DESC
March 7, 2012 at 4:49 am
Would me more than happy to help if you could provide some DDL and data setup for your question. Please refer to the link at the bottom of my signature.
March 7, 2012 at 6:30 am
hello and many thanks
sorry newbie
the set up and the query so far is below
i've used just one of the buckets ('resolved in 13 weeks') with (currently) a running count across the whole timescale
i need to restrict this to a rolling count over of each previous 4 records and calculate this as a percentage of the total
i.e. 2011Q3 should look back at Q3,Q2,Q1 and Q4 of 2010 = 8+9+6+5 = 28 records
under 13 weeks = 6+2+0+2 = 10 = 36% and so on
its driving me nuts but I have managed to get somewhere by editing the query below to WHERE Z.FINANCIAL_QUARTER >= Z2.FINANCIAL_QUARTER and Z2.FINANCIAL_QUARTER >= (Z.FINANCIAL_QUARTER -4) this calculates a cumulative running count for each year i.e. Q1 alone then Q1+Q2 then Q1+Q2+Q3 and finally Q1+Q2+Q3+Q4 before resetting - presumably because the numbers are not continous and nor can i get the Q_count to summarise across those groups correctly
thanks again
f
CREATE TABLE TESTRUNNER
(
FINANCIAL_QUARTER int identity (1,1) PRIMARY KEY CLUSTERED
,Q_COUNT int
,PPA_COUNT int
,UNMET_PPA int
,PSUNDER13 int
,PSFOVER13 int
,AGREED int
,OUTSIDE int
)
SET IDENTITY_INSERT TESTRUNNER ON
INSERT INTO TESTRUNNER
(FINANCIAL_QUARTER,
Q_COUNT,PPA_COUNT,
UNMET_PPA,
PSUNDER13,
PSFOVER13,
AGREED ,
OUTSIDE)
SELECT '200701','6','0','0','3','3','3','3' UNION ALL
SELECT '200702','1','0','0','0','1','0','1' UNION ALL
SELECT '200703','5','0','0','0','5','0','5' UNION ALL
SELECT '200704','8','0','0','1','7','1','7' UNION ALL
SELECT '200801','6','0','0','1','5','1','5' UNION ALL
SELECT '200802','8','0','0','3','5','3','5' UNION ALL
SELECT '200803','6','0','0','0','6','0','6' UNION ALL
SELECT '200804','5','0','0','2','3','2','3' UNION ALL
SELECT '200901','11','0','0','7','4','7','4' UNION ALL
SELECT '200902','7','0','0','3','4','3','4' UNION ALL
SELECT '200903','6','0','0','2','4','2','4' UNION ALL
SELECT '200904','6','0','0','1','5','1','5' UNION ALL
SELECT '201001','5','2','0','3','0','5','0' UNION ALL
SELECT '201002','6','2','0','1','3','3','3' UNION ALL
SELECT '201003','9','2','0','5','2','7','2' UNION ALL
SELECT '201004','5','2','0','2','1','4','1' UNION ALL
SELECT '201101','6','2','0','0','4','2','4' UNION ALL
SELECT '201102','9','3','0','2','4','5','4' UNION ALL
SELECT '201103','8','1','0','6','1','7','1' UNION ALL
SELECT '201104','16','9','2','4','5','11','5' UNION ALL
SELECT '201201','2','0','0','1','1','1','1' UNION ALL
SELECT '201202','1','0','0','0','1','0','1' UNION ALL
SELECT '201203','1','1','0','0','0','1','0'
SET IDENTITY_INSERT TESTrunner OFF
SELECT
COUNT(Z.FINANCIAL_QUARTER)AS RANKQ
,Z.FINANCIAL_QUARTER
,SUM(Z2.PSUNDER13) AS RUNNING_UNDER_13
FROM
(SELECT
FINANCIAL_QUARTER
,Q_COUNT
,PPA_COUNT
,UNMET_PPA
,PSUNDER13
,PSFOVER13
,AGREED
,OUTSIDE
FROM TESTRUNNER )Z,
(SELECT
FINANCIAL_QUARTER
,Q_COUNT
,PPA_COUNT
,UNMET_PPA
,PSUNDER13
,PSFOVER13
,AGREED
,OUTSIDE
FROM TESTRUNNER )Z2
WHERE Z.FINANCIAL_QUARTER >= Z2.FINANCIAL_QUARTER
GROUP BY Z.FINANCIAL_QUARTER
ORDER BY Z.FINANCIAL_QUARTER DESC
March 7, 2012 at 7:13 am
I don't understand the whole of your question, but I can see that the one of main problem for you is how to list previous four quarters for each of listed quarters.
Here is example of how it can be done:
-- you need incrementing quarter number
-- you can add it into your main table if you are allowed
-- you can have permanent table to hold it
-- or, as here just temp table
-- you may try CTE, but I think #table will be better performing option
-- (especially if used with indexes)
SELECT FINANCIAL_QUARTER,
QN,
QN - 3 AS P4QN -- pre-calc q.n. of 4 q ago...
INTO #QN
FROM (
SELECT FINANCIAL_QUARTER,
ROW_NUMBER() OVER (ORDER BY FINANCIAL_QUARTER) AS QN --Quarter seq number
FROM TESTrunner
-- WHERE [your period condition if needed]
) q
-- if your set is large, you may benefit from having index on it
CREATE UNIQUE CLUSTERED INDEX ix_#QN ON #QN(FINANCIAL_QUARTER ASC)
SELECT t.FINANCIAL_QUARTER, p4q.FINANCIAL_QUARTER AS PREV_FINANCIAL_QUARTER
from TESTrunner t
INNER JOIN #QN q1
ON q1.FINANCIAL_QUARTER = t.FINANCIAL_QUARTER
INNER JOIN #QN p4q
ON p4q.QN BETWEEN q1.P4QN AND q1.QN
ORDER BY t.FINANCIAL_QUARTER, p4q.FINANCIAL_QUARTER
You can optimise the above code futher...
March 7, 2012 at 11:49 am
success!
many thanks
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply