February 17, 2009 at 11:41 am
I have created a view based on some aggregated fields. I have tried to apply some aggregate statements to check the data but I keep getting nothing returned back. I think my syntax is correct and I can see the data I want to aggregate when I open the view. The script creating the view and my aggregate statement are below. Any help would be much appreciated.
--This is my aggregate script
SELECT count(actualActivity),PODname
FROM vw_ZZPBC_Actuals
WHERE CLUSTER = 'HARNESS' AND
provider LIKE 'IMPERIAL*'
GROUP BY PODname
--- script creating view
ALTER VIEW vw_ZZPBC_Actuals
AS
SELECT
CASE WHEN groupTogether = 1 THEN 'OTHER' ELSE PBC_Providers.providerName END AS provider,
ISNULL(GP_PRACTICE.GP_PRACTICE_CODE,'PCT') AS practiceCode,
GP_PRACTICE.PRACTICE_LONG_NAME AS practiceName,
ISNULL(PracticeClusters.cluster,'PCT') AS cluster,
PODs.PODname,
activityMonth,
specialty_Code,
HRG_Code,
SUM(activity) AS actualActivity,
SUM(cost) AS actualCost
FROM
vw_zzPBC_Report_Actuals INNER JOIN
PBC_Providers ON vw_zzPBC_Report_Actuals.providerCode = PBC_Providers.providerCode LEFT OUTER JOIN
BRENTREPODB02.Olympus.dbo.GP_PRACTICE GP_PRACTICE ON
vw_zzPBC_Report_Actuals.practiceCode = GP_PRACTICE.GP_PRACTICE_CODE COLLATE SQL_Latin1_General_CP1_CI_AS LEFT OUTER JOIN
PracticeClusters ON vw_zzPBC_Report_Actuals.practiceCode = PracticeClusters.practiceCode LEFT OUTER JOIN
PODs ON vw_zzPBC_Report_Actuals.POD = PODs.PODcode
GROUP BY
CASE WHEN groupTogether = 1 THEN 'OTHER' ELSE PBC_Providers.providerName END,
ISNULL(GP_PRACTICE.GP_PRACTICE_CODE,'PCT'),
GP_PRACTICE.PRACTICE_LONG_NAME,
ISNULL(PracticeClusters.cluster,'PCT'),
PODs.PODname,
activityMonth,
specialty_Code,
HRG_Code
February 17, 2009 at 2:46 pm
The way it looks to me, you should only be expecting to get a Count of 1 returned by your 'aggregate of aggregate' query. This is because all Count does is look at the number of rows returned, and you've already done a group by on the podname field in your view. So there should be only one row to 'count'.
If you're not getting *any* rows returned, I would start by removing the aggregate piece of the query with the Count, and just confirming that your criteria are working properly. I suspect they're not, since % is the wildcard character in SQL Server, not *...
February 18, 2009 at 3:10 am
Thanks, you were right about the wild card. It now returns what I want.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply