I wrote a couple of functions AGGREGATEin the CLR
abj.median
abj.percentile
A bit interesting problem. The functions in the structure are very similar, except for a small difference in how the results are calculated AND, PERCENTILE2 parameters are required, and the median is only one.
A common parameter for both functions is the field name. The percentile function also carries a value to determine which percentile (10, 75, 90, etc.)
This command works great.
;
WITH p1 AS (
SELECT WAITTIMES_DAY / 7.0 AS waitWeeks,
abj.fyq(surg_sx_date) as fiscalYear,
SURG_SITE_ZONE
FROM dbo.Surgery
)
SELECT *
FROM p1 p
PIVOT (abj.median(waitweeks)
FOR fiscalYear IN ( [2013/14-Q1], [2013/14-Q2], [2013/14-Q3], [2013/14-Q4] )) b
This command does not work with INCORRECT SYNTAX NEAR '90'. Expecting '.', ID, or QUOTED_ID.
;
WITH p1 AS (
SELECT WAITTIMES_DAY / 7.0 AS waitWeeks,
abj.fyq(surg_sx_date) as fiscalYear,
SURG_SITE_ZONE
FROM dbo.Surgery
)
SELECT *
FROM p1 p
PIVOT (abj.percentile(waitweeks,90)
FOR fiscalYear IN ( [2013/14-Q1], [2013/14-Q2], [2013/14-Q3], [2013/14-Q4] )) b
Has anyone encountered this Wierdness before and how they fixed it (except for the breakdown and recorded the PERCENTILE function with only one parameter, and the second defaults to 90).
thank
Sven