More efficient way

Question

More efficient way

Bill_CCAC

Valued Member

Points: 70
More actions
October 19, 2010 at 10:12 am

#229791

Hi All. I'm hoping for ideas on how to make either my query more efficient of a better way of collecting the same result set. This query currently takes the better part of an hour to run which is not at all acceptable but it does return the right results. I'm a report writer, not a DBA and I don't have access to the server to fine tune.
DECLARE @Start_Date As Datetime
DECLARE @End_Date As Datetime
SET @Start_Date = '04/01/2010'
SET @End_Date = '07/01/2010'
SELECT '1to18' AS 'AgeGroup', COUNT(Clients.Client_Number) AS 'AllPerAgeGroup'
,(SELECT COUNT(Clients1.Client_Number)
FROM dbo.AdHoc_Referrals AS Referrals1 INNER JOIN
dbo.AdHoc_Clients As Clients1 ON Referrals1.Client_Id = Clients1.Client_Id INNER JOIN
dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items1 ON Clients1.Client_Id = Billing_PSI_Line_Items1.Client_Id
WHERE (Referrals1.Client_Application_Date BETWEEN @Start_Date AND @End_Date)
AND (DATEDIFF(yy, Clients1.DOB, GETDATE()) <= 18)
AND (DATEDIFF(dd, Referrals1.Client_Application_Date, Billing_PSI_Line_Items1.Visit_Date) <= 3)
AND (Referrals.Site_Code = Referrals1.Site_Code)
AND ((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.Visit_Date
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) IS NOT NULL)
AND((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) = Billing_PSI_Line_Items1.PSI_Line_Item_Id)
GROUP BY Referrals1.Site_Code) AS 'TotalWithin3DaysPerAgeGroup'
, Referrals.Site_Code
, Referrals.Site_Code_Description
FROM dbo.AdHoc_Referrals AS Referrals INNER JOIN
dbo.AdHoc_Clients AS Clients ON Referrals.Client_Id = Clients.Client_Id INNER JOIN
dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items ON Clients.Client_Id = Billing_PSI_Line_Items.Client_Id
WHERE (Referrals.Client_Application_Date BETWEEN @Start_Date AND @End_Date)
AND (DATEDIFF(yy, Clients.DOB, GETDATE()) <= 18)
AND ((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.Visit_Date
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) IS NOT NULL)
AND ((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) = Billing_PSI_Line_Items.PSI_Line_Item_Id)
GROUP BY Referrals.Site_Code, Referrals.Site_Code_Description
UNION
SELECT '19to24' AS 'AgeGroup', COUNT(Clients.Client_Number) AS 'AllPerAgeGroup'
,(SELECT COUNT(Clients1.Client_Number)
FROM dbo.AdHoc_Referrals AS Referrals1 INNER JOIN
dbo.AdHoc_Clients As Clients1 ON Referrals1.Client_Id = Clients1.Client_Id INNER JOIN
dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items1 ON Clients1.Client_Id = Billing_PSI_Line_Items1.Client_Id
WHERE (Referrals1.Client_Application_Date BETWEEN @Start_Date AND @End_Date)
AND (DATEDIFF(yy, Clients1.DOB, GETDATE()) >= 19 AND DATEDIFF(yy, Clients1.DOB, GETDATE()) <= 24)
AND (DATEDIFF(dd, Referrals1.Client_Application_Date, Billing_PSI_Line_Items1.Visit_Date) <= 3)
AND (Referrals.Site_Code = Referrals1.Site_Code)
AND ((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.Visit_Date
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) IS NOT NULL)
AND((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) = Billing_PSI_Line_Items1.PSI_Line_Item_Id)
GROUP BY Referrals1.Site_Code) AS 'TotalWithin3DaysPerAgeGroup'
, Referrals.Site_Code
, Referrals.Site_Code_Description
FROM dbo.AdHoc_Referrals AS Referrals INNER JOIN
dbo.AdHoc_Clients AS Clients ON Referrals.Client_Id = Clients.Client_Id INNER JOIN
dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items ON Clients.Client_Id = Billing_PSI_Line_Items.Client_Id
WHERE (Referrals.Client_Application_Date BETWEEN @Start_Date AND @End_Date)
AND (DATEDIFF(yy, Clients.DOB, GETDATE()) >= 19 AND DATEDIFF(yy, Clients.DOB, GETDATE()) <= 24)
AND ((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.Visit_Date
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) IS NOT NULL)
AND ((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) = Billing_PSI_Line_Items.PSI_Line_Item_Id)
GROUP BY Referrals.Site_Code, Referrals.Site_Code_Description
UNION
SELECT '25to29' AS 'AgeGroup', COUNT(Clients.Client_Number) AS 'AllPerAgeGroup'
,(SELECT COUNT(Clients1.Client_Number)
FROM dbo.AdHoc_Referrals AS Referrals1 INNER JOIN
dbo.AdHoc_Clients As Clients1 ON Referrals1.Client_Id = Clients1.Client_Id INNER JOIN
dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items1 ON Clients1.Client_Id = Billing_PSI_Line_Items1.Client_Id
WHERE (Referrals1.Client_Application_Date BETWEEN @Start_Date AND @End_Date)
AND (DATEDIFF(yy, Clients1.DOB, GETDATE()) >= 25 AND DATEDIFF(yy, Clients1.DOB, GETDATE()) <= 29)
AND (DATEDIFF(dd, Referrals1.Client_Application_Date, Billing_PSI_Line_Items1.Visit_Date) <= 3)
AND (Referrals.Site_Code = Referrals1.Site_Code)
AND ((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.Visit_Date
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) IS NOT NULL)
AND((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) = Billing_PSI_Line_Items1.PSI_Line_Item_Id)
GROUP BY Referrals1.Site_Code) AS 'TotalWithin3DaysPerAgeGroup'
, Referrals.Site_Code
, Referrals.Site_Code_Description
FROM dbo.AdHoc_Referrals AS Referrals INNER JOIN
dbo.AdHoc_Clients AS Clients ON Referrals.Client_Id = Clients.Client_Id INNER JOIN
dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items ON Clients.Client_Id = Billing_PSI_Line_Items.Client_Id
WHERE (Referrals.Client_Application_Date BETWEEN @Start_Date AND @End_Date)
AND (DATEDIFF(yy, Clients.DOB, GETDATE()) >= 25 AND DATEDIFF(yy, Clients.DOB, GETDATE()) <= 29)
AND ((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.Visit_Date
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) IS NOT NULL)
AND ((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) = Billing_PSI_Line_Items.PSI_Line_Item_Id)
GROUP BY Referrals.Site_Code, Referrals.Site_Code_Description
UNION
SELECT '30to34' AS 'AgeGroup', COUNT(Clients.Client_Number) AS 'AllPerAgeGroup'
,(SELECT COUNT(Clients1.Client_Number)
FROM dbo.AdHoc_Referrals AS Referrals1 INNER JOIN
dbo.AdHoc_Clients As Clients1 ON Referrals1.Client_Id = Clients1.Client_Id INNER JOIN
dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items1 ON Clients1.Client_Id = Billing_PSI_Line_Items1.Client_Id
WHERE (Referrals1.Client_Application_Date BETWEEN @Start_Date AND @End_Date)
AND (DATEDIFF(yy, Clients1.DOB, GETDATE()) >= 30 AND DATEDIFF(yy, Clients1.DOB, GETDATE()) <= 34)
AND (DATEDIFF(dd, Referrals1.Client_Application_Date, Billing_PSI_Line_Items1.Visit_Date) <= 3)
AND (Referrals.Site_Code = Referrals1.Site_Code)
AND ((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.Visit_Date
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) IS NOT NULL)
AND((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) = Billing_PSI_Line_Items1.PSI_Line_Item_Id)
GROUP BY Referrals1.Site_Code) AS 'TotalWithin3DaysPerAgeGroup'
, Referrals.Site_Code
, Referrals.Site_Code_Description
FROM dbo.AdHoc_Referrals AS Referrals INNER JOIN
dbo.AdHoc_Clients AS Clients ON Referrals.Client_Id = Clients.Client_Id INNER JOIN
dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items ON Clients.Client_Id = Billing_PSI_Line_Items.Client_Id
WHERE (Referrals.Client_Application_Date BETWEEN @Start_Date AND @End_Date)
AND (DATEDIFF(yy, Clients.DOB, GETDATE()) >= 30 AND DATEDIFF(yy, Clients.DOB, GETDATE()) <= 34)
AND ((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.Visit_Date
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) IS NOT NULL)
AND ((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) = Billing_PSI_Line_Items.PSI_Line_Item_Id)
GROUP BY Referrals.Site_Code, Referrals.Site_Code_Description
UNION
SELECT '35to39' AS 'AgeGroup', COUNT(Clients.Client_Number) AS 'AllPerAgeGroup'
,(SELECT COUNT(Clients1.Client_Number)
FROM dbo.AdHoc_Referrals AS Referrals1 INNER JOIN
dbo.AdHoc_Clients As Clients1 ON Referrals1.Client_Id = Clients1.Client_Id INNER JOIN
dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items1 ON Clients1.Client_Id = Billing_PSI_Line_Items1.Client_Id
WHERE (Referrals1.Client_Application_Date BETWEEN @Start_Date AND @End_Date)
AND (DATEDIFF(yy, Clients1.DOB, GETDATE()) >= 35 AND DATEDIFF(yy, Clients1.DOB, GETDATE()) <= 39)
AND (DATEDIFF(dd, Referrals1.Client_Application_Date, Billing_PSI_Line_Items1.Visit_Date) <= 3)
AND (Referrals.Site_Code = Referrals1.Site_Code)
AND ((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.Visit_Date
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) IS NOT NULL)
AND((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) = Billing_PSI_Line_Items1.PSI_Line_Item_Id)
GROUP BY Referrals1.Site_Code) AS 'TotalWithin3DaysPerAgeGroup'
, Referrals.Site_Code
, Referrals.Site_Code_Description
FROM dbo.AdHoc_Referrals AS Referrals INNER JOIN
dbo.AdHoc_Clients AS Clients ON Referrals.Client_Id = Clients.Client_Id INNER JOIN
dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items ON Clients.Client_Id = Billing_PSI_Line_Items.Client_Id
WHERE (Referrals.Client_Application_Date BETWEEN @Start_Date AND @End_Date)
AND (DATEDIFF(yy, Clients.DOB, GETDATE()) >= 35 AND DATEDIFF(yy, Clients.DOB, GETDATE()) <= 39)
AND ((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.Visit_Date
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) IS NOT NULL)
AND ((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) = Billing_PSI_Line_Items.PSI_Line_Item_Id)
GROUP BY Referrals.Site_Code, Referrals.Site_Code_Description
UNION
SELECT '40to44' AS 'AgeGroup', COUNT(Clients.Client_Number) AS 'AllPerAgeGroup'
,(SELECT COUNT(Clients1.Client_Number)
FROM dbo.AdHoc_Referrals AS Referrals1 INNER JOIN
dbo.AdHoc_Clients As Clients1 ON Referrals1.Client_Id = Clients1.Client_Id INNER JOIN
dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items1 ON Clients1.Client_Id = Billing_PSI_Line_Items1.Client_Id
WHERE (Referrals1.Client_Application_Date BETWEEN @Start_Date AND @End_Date)
AND (DATEDIFF(yy, Clients1.DOB, GETDATE()) >= 40 AND DATEDIFF(yy, Clients1.DOB, GETDATE()) <= 44)
AND (DATEDIFF(dd, Referrals1.Client_Application_Date, Billing_PSI_Line_Items1.Visit_Date) <= 3)
AND (Referrals.Site_Code = Referrals1.Site_Code)
AND ((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.Visit_Date
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) IS NOT NULL)
AND((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) = Billing_PSI_Line_Items1.PSI_Line_Item_Id)
GROUP BY Referrals1.Site_Code) AS 'TotalWithin3DaysPerAgeGroup'
, Referrals.Site_Code
, Referrals.Site_Code_Description
FROM dbo.AdHoc_Referrals AS Referrals INNER JOIN
dbo.AdHoc_Clients AS Clients ON Referrals.Client_Id = Clients.Client_Id INNER JOIN
dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items ON Clients.Client_Id = Billing_PSI_Line_Items.Client_Id
WHERE (Referrals.Client_Application_Date BETWEEN @Start_Date AND @End_Date)
AND (DATEDIFF(yy, Clients.DOB, GETDATE()) >= 40 AND DATEDIFF(yy, Clients.DOB, GETDATE()) <= 44)
AND ((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.Visit_Date
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) IS NOT NULL)
AND ((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) = Billing_PSI_Line_Items.PSI_Line_Item_Id)
GROUP BY Referrals.Site_Code, Referrals.Site_Code_Description
UNION
SELECT '45to49' AS 'AgeGroup', COUNT(Clients.Client_Number) AS 'AllPerAgeGroup'
,(SELECT COUNT(Clients1.Client_Number)
FROM dbo.AdHoc_Referrals AS Referrals1 INNER JOIN
dbo.AdHoc_Clients As Clients1 ON Referrals1.Client_Id = Clients1.Client_Id INNER JOIN
dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items1 ON Clients1.Client_Id = Billing_PSI_Line_Items1.Client_Id
WHERE (Referrals1.Client_Application_Date BETWEEN @Start_Date AND @End_Date)
AND (DATEDIFF(yy, Clients1.DOB, GETDATE()) >= 45 AND DATEDIFF(yy, Clients1.DOB, GETDATE()) <= 49)
AND (DATEDIFF(dd, Referrals1.Client_Application_Date, Billing_PSI_Line_Items1.Visit_Date) <= 3)
AND (Referrals.Site_Code = Referrals1.Site_Code)
AND ((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.Visit_Date
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) IS NOT NULL)
AND((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) = Billing_PSI_Line_Items1.PSI_Line_Item_Id)
GROUP BY Referrals1.Site_Code) AS 'TotalWithin3DaysPerAgeGroup'
, Referrals.Site_Code
, Referrals.Site_Code_Description
FROM dbo.AdHoc_Referrals AS Referrals INNER JOIN
dbo.AdHoc_Clients AS Clients ON Referrals.Client_Id = Clients.Client_Id INNER JOIN
dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items ON Clients.Client_Id = Billing_PSI_Line_Items.Client_Id
WHERE (Referrals.Client_Application_Date BETWEEN @Start_Date AND @End_Date)
AND (DATEDIFF(yy, Clients.DOB, GETDATE()) >= 45 AND DATEDIFF(yy, Clients.DOB, GETDATE()) <= 49)
AND ((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.Visit_Date
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) IS NOT NULL)
AND ((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) = Billing_PSI_Line_Items.PSI_Line_Item_Id)
GROUP BY Referrals.Site_Code, Referrals.Site_Code_Description
UNION
SELECT '50to54' AS 'AgeGroup', COUNT(Clients.Client_Number) AS 'AllPerAgeGroup'
,(SELECT COUNT(Clients1.Client_Number)
FROM dbo.AdHoc_Referrals AS Referrals1 INNER JOIN
dbo.AdHoc_Clients As Clients1 ON Referrals1.Client_Id = Clients1.Client_Id INNER JOIN
dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items1 ON Clients1.Client_Id = Billing_PSI_Line_Items1.Client_Id
WHERE (Referrals1.Client_Application_Date BETWEEN @Start_Date AND @End_Date)
AND (DATEDIFF(yy, Clients1.DOB, GETDATE()) >= 50 AND DATEDIFF(yy, Clients1.DOB, GETDATE()) <= 54)
AND (DATEDIFF(dd, Referrals1.Client_Application_Date, Billing_PSI_Line_Items1.Visit_Date) <= 3)
AND (Referrals.Site_Code = Referrals1.Site_Code)
AND ((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.Visit_Date
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) IS NOT NULL)
AND((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) = Billing_PSI_Line_Items1.PSI_Line_Item_Id)
GROUP BY Referrals1.Site_Code) AS 'TotalWithin3DaysPerAgeGroup'
, Referrals.Site_Code
, Referrals.Site_Code_Description
FROM dbo.AdHoc_Referrals AS Referrals INNER JOIN
dbo.AdHoc_Clients AS Clients ON Referrals.Client_Id = Clients.Client_Id INNER JOIN
dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items ON Clients.Client_Id = Billing_PSI_Line_Items.Client_Id
WHERE (Referrals.Client_Application_Date BETWEEN @Start_Date AND @End_Date)
AND (DATEDIFF(yy, Clients.DOB, GETDATE()) >= 50 AND DATEDIFF(yy, Clients.DOB, GETDATE()) <= 54)
AND ((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.Visit_Date
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) IS NOT NULL)
AND ((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) = Billing_PSI_Line_Items.PSI_Line_Item_Id)
GROUP BY Referrals.Site_Code, Referrals.Site_Code_Description
UNION
SELECT '55to59' AS 'AgeGroup', COUNT(Clients.Client_Number) AS 'AllPerAgeGroup'
,(SELECT COUNT(Clients1.Client_Number)
FROM dbo.AdHoc_Referrals AS Referrals1 INNER JOIN
dbo.AdHoc_Clients As Clients1 ON Referrals1.Client_Id = Clients1.Client_Id INNER JOIN
dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items1 ON Clients1.Client_Id = Billing_PSI_Line_Items1.Client_Id
WHERE (Referrals1.Client_Application_Date BETWEEN @Start_Date AND @End_Date)
AND (DATEDIFF(yy, Clients1.DOB, GETDATE()) >= 55 AND DATEDIFF(yy, Clients1.DOB, GETDATE()) <= 59)
AND (DATEDIFF(dd, Referrals1.Client_Application_Date, Billing_PSI_Line_Items1.Visit_Date) <= 3)
AND (Referrals.Site_Code = Referrals1.Site_Code)
AND ((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.Visit_Date
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) IS NOT NULL)
AND((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) = Billing_PSI_Line_Items1.PSI_Line_Item_Id)
GROUP BY Referrals1.Site_Code) AS 'TotalWithin3DaysPerAgeGroup'
, Referrals.Site_Code
, Referrals.Site_Code_Description
FROM dbo.AdHoc_Referrals AS Referrals INNER JOIN
dbo.AdHoc_Clients AS Clients ON Referrals.Client_Id = Clients.Client_Id INNER JOIN
dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items ON Clients.Client_Id = Billing_PSI_Line_Items.Client_Id
WHERE (Referrals.Client_Application_Date BETWEEN @Start_Date AND @End_Date)
AND (DATEDIFF(yy, Clients.DOB, GETDATE()) >= 55 AND DATEDIFF(yy, Clients.DOB, GETDATE()) <= 59)
AND ((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.Visit_Date
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) IS NOT NULL)
AND ((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) = Billing_PSI_Line_Items.PSI_Line_Item_Id)
GROUP BY Referrals.Site_Code, Referrals.Site_Code_Description
UNION
SELECT '60to64' AS 'AgeGroup', COUNT(Clients.Client_Number) AS 'AllPerAgeGroup'
,(SELECT COUNT(Clients1.Client_Number)
FROM dbo.AdHoc_Referrals AS Referrals1 INNER JOIN
dbo.AdHoc_Clients As Clients1 ON Referrals1.Client_Id = Clients1.Client_Id INNER JOIN
dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items1 ON Clients1.Client_Id = Billing_PSI_Line_Items1.Client_Id
WHERE (Referrals1.Client_Application_Date BETWEEN @Start_Date AND @End_Date)
AND (DATEDIFF(yy, Clients1.DOB, GETDATE()) >= 60 AND DATEDIFF(yy, Clients1.DOB, GETDATE()) <= 64)
AND (DATEDIFF(dd, Referrals1.Client_Application_Date, Billing_PSI_Line_Items1.Visit_Date) <= 3)
AND (Referrals.Site_Code = Referrals1.Site_Code)
AND ((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.Visit_Date
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) IS NOT NULL)
AND((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) = Billing_PSI_Line_Items1.PSI_Line_Item_Id)
GROUP BY Referrals1.Site_Code) AS 'TotalWithin3DaysPerAgeGroup'
, Referrals.Site_Code
, Referrals.Site_Code_Description
FROM dbo.AdHoc_Referrals AS Referrals INNER JOIN
dbo.AdHoc_Clients AS Clients ON Referrals.Client_Id = Clients.Client_Id INNER JOIN
dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items ON Clients.Client_Id = Billing_PSI_Line_Items.Client_Id
WHERE (Referrals.Client_Application_Date BETWEEN @Start_Date AND @End_Date)
AND (DATEDIFF(yy, Clients.DOB, GETDATE()) >= 60 AND DATEDIFF(yy, Clients.DOB, GETDATE()) <= 64)
AND ((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.Visit_Date
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) IS NOT NULL)
AND ((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) = Billing_PSI_Line_Items.PSI_Line_Item_Id)
GROUP BY Referrals.Site_Code, Referrals.Site_Code_Description
UNION
SELECT '65to69' AS 'AgeGroup', COUNT(Clients.Client_Number) AS 'AllPerAgeGroup'
,(SELECT COUNT(Clients1.Client_Number)
FROM dbo.AdHoc_Referrals AS Referrals1 INNER JOIN
dbo.AdHoc_Clients As Clients1 ON Referrals1.Client_Id = Clients1.Client_Id INNER JOIN
dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items1 ON Clients1.Client_Id = Billing_PSI_Line_Items1.Client_Id
WHERE (Referrals1.Client_Application_Date BETWEEN @Start_Date AND @End_Date)
AND (DATEDIFF(yy, Clients1.DOB, GETDATE()) >= 65 AND DATEDIFF(yy, Clients1.DOB, GETDATE()) <= 69)
AND (DATEDIFF(dd, Referrals1.Client_Application_Date, Billing_PSI_Line_Items1.Visit_Date) <= 3)
AND (Referrals.Site_Code = Referrals1.Site_Code)
AND ((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.Visit_Date
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) IS NOT NULL)
AND((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) = Billing_PSI_Line_Items1.PSI_Line_Item_Id)
GROUP BY Referrals1.Site_Code) AS 'TotalWithin3DaysPerAgeGroup'
, Referrals.Site_Code
, Referrals.Site_Code_Description
FROM dbo.AdHoc_Referrals AS Referrals INNER JOIN
dbo.AdHoc_Clients AS Clients ON Referrals.Client_Id = Clients.Client_Id INNER JOIN
dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items ON Clients.Client_Id = Billing_PSI_Line_Items.Client_Id
WHERE (Referrals.Client_Application_Date BETWEEN @Start_Date AND @End_Date)
AND (DATEDIFF(yy, Clients.DOB, GETDATE()) >= 65 AND DATEDIFF(yy, Clients.DOB, GETDATE()) <= 69)
AND ((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.Visit_Date
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) IS NOT NULL)
AND ((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) = Billing_PSI_Line_Items.PSI_Line_Item_Id)
GROUP BY Referrals.Site_Code, Referrals.Site_Code_Description
UNION
SELECT '70to74' AS 'AgeGroup', COUNT(Clients.Client_Number) AS 'AllPerAgeGroup'
,(SELECT COUNT(Clients1.Client_Number)
FROM dbo.AdHoc_Referrals AS Referrals1 INNER JOIN
dbo.AdHoc_Clients As Clients1 ON Referrals1.Client_Id = Clients1.Client_Id INNER JOIN
dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items1 ON Clients1.Client_Id = Billing_PSI_Line_Items1.Client_Id
WHERE (Referrals1.Client_Application_Date BETWEEN @Start_Date AND @End_Date)
AND (DATEDIFF(yy, Clients1.DOB, GETDATE()) >= 70 AND DATEDIFF(yy, Clients1.DOB, GETDATE()) <= 74)
AND (DATEDIFF(dd, Referrals1.Client_Application_Date, Billing_PSI_Line_Items1.Visit_Date) <= 3)
AND (Referrals.Site_Code = Referrals1.Site_Code)
AND ((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.Visit_Date
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) IS NOT NULL)
AND((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) = Billing_PSI_Line_Items1.PSI_Line_Item_Id)
GROUP BY Referrals1.Site_Code) AS 'TotalWithin3DaysPerAgeGroup'
, Referrals.Site_Code
, Referrals.Site_Code_Description
FROM dbo.AdHoc_Referrals AS Referrals INNER JOIN
dbo.AdHoc_Clients AS Clients ON Referrals.Client_Id = Clients.Client_Id INNER JOIN
dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items ON Clients.Client_Id = Billing_PSI_Line_Items.Client_Id
WHERE (Referrals.Client_Application_Date BETWEEN @Start_Date AND @End_Date)
AND (DATEDIFF(yy, Clients.DOB, GETDATE()) >= 70 AND DATEDIFF(yy, Clients.DOB, GETDATE()) <= 74)
AND ((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.Visit_Date
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) IS NOT NULL)
AND ((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) = Billing_PSI_Line_Items.PSI_Line_Item_Id)
GROUP BY Referrals.Site_Code, Referrals.Site_Code_Description
UNION
SELECT '75to79' AS 'AgeGroup', COUNT(Clients.Client_Number) AS 'AllPerAgeGroup'
,(SELECT COUNT(Clients1.Client_Number)
FROM dbo.AdHoc_Referrals AS Referrals1 INNER JOIN
dbo.AdHoc_Clients As Clients1 ON Referrals1.Client_Id = Clients1.Client_Id INNER JOIN
dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items1 ON Clients1.Client_Id = Billing_PSI_Line_Items1.Client_Id
WHERE (Referrals1.Client_Application_Date BETWEEN @Start_Date AND @End_Date)
AND (DATEDIFF(yy, Clients1.DOB, GETDATE()) >= 75 AND DATEDIFF(yy, Clients1.DOB, GETDATE()) <= 79)
AND (DATEDIFF(dd, Referrals1.Client_Application_Date, Billing_PSI_Line_Items1.Visit_Date) <= 3)
AND (Referrals.Site_Code = Referrals1.Site_Code)
AND ((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.Visit_Date
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) IS NOT NULL)
AND((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) = Billing_PSI_Line_Items1.PSI_Line_Item_Id)
GROUP BY Referrals1.Site_Code) AS 'TotalWithin3DaysPerAgeGroup'
, Referrals.Site_Code
, Referrals.Site_Code_Description
FROM dbo.AdHoc_Referrals AS Referrals INNER JOIN
dbo.AdHoc_Clients AS Clients ON Referrals.Client_Id = Clients.Client_Id INNER JOIN
dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items ON Clients.Client_Id = Billing_PSI_Line_Items.Client_Id
WHERE (Referrals.Client_Application_Date BETWEEN @Start_Date AND @End_Date)
AND (DATEDIFF(yy, Clients.DOB, GETDATE()) >= 75 AND DATEDIFF(yy, Clients.DOB, GETDATE()) <= 79)
AND ((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.Visit_Date
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) IS NOT NULL)
AND ((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) = Billing_PSI_Line_Items.PSI_Line_Item_Id)
GROUP BY Referrals.Site_Code, Referrals.Site_Code_Description
UNION
SELECT '80to84' AS 'AgeGroup', COUNT(Clients.Client_Number) AS 'AllPerAgeGroup'
,(SELECT COUNT(Clients1.Client_Number)
FROM dbo.AdHoc_Referrals AS Referrals1 INNER JOIN
dbo.AdHoc_Clients As Clients1 ON Referrals1.Client_Id = Clients1.Client_Id INNER JOIN
dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items1 ON Clients1.Client_Id = Billing_PSI_Line_Items1.Client_Id
WHERE (Referrals1.Client_Application_Date BETWEEN @Start_Date AND @End_Date)
AND (DATEDIFF(yy, Clients1.DOB, GETDATE()) >= 80 AND DATEDIFF(yy, Clients1.DOB, GETDATE()) <= 84)
AND (DATEDIFF(dd, Referrals1.Client_Application_Date, Billing_PSI_Line_Items1.Visit_Date) <= 3)
AND (Referrals.Site_Code = Referrals1.Site_Code)
AND ((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.Visit_Date
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) IS NOT NULL)
AND((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) = Billing_PSI_Line_Items1.PSI_Line_Item_Id)
GROUP BY Referrals1.Site_Code) AS 'TotalWithin3DaysPerAgeGroup'
, Referrals.Site_Code
, Referrals.Site_Code_Description
FROM dbo.AdHoc_Referrals AS Referrals INNER JOIN
dbo.AdHoc_Clients AS Clients ON Referrals.Client_Id = Clients.Client_Id INNER JOIN
dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items ON Clients.Client_Id = Billing_PSI_Line_Items.Client_Id
WHERE (Referrals.Client_Application_Date BETWEEN @Start_Date AND @End_Date)
AND (DATEDIFF(yy, Clients.DOB, GETDATE()) >= 80 AND DATEDIFF(yy, Clients.DOB, GETDATE()) <= 84)
AND ((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.Visit_Date
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) IS NOT NULL)
AND ((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) = Billing_PSI_Line_Items.PSI_Line_Item_Id)
GROUP BY Referrals.Site_Code, Referrals.Site_Code_Description
UNION
SELECT '85to89' AS 'AgeGroup', COUNT(Clients.Client_Number) AS 'AllPerAgeGroup'
,(SELECT COUNT(Clients1.Client_Number)
FROM dbo.AdHoc_Referrals AS Referrals1 INNER JOIN
dbo.AdHoc_Clients As Clients1 ON Referrals1.Client_Id = Clients1.Client_Id INNER JOIN
dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items1 ON Clients1.Client_Id = Billing_PSI_Line_Items1.Client_Id
WHERE (Referrals1.Client_Application_Date BETWEEN @Start_Date AND @End_Date)
AND (DATEDIFF(yy, Clients1.DOB, GETDATE()) >= 85 AND DATEDIFF(yy, Clients1.DOB, GETDATE()) <= 89)
AND (DATEDIFF(dd, Referrals1.Client_Application_Date, Billing_PSI_Line_Items1.Visit_Date) <= 3)
AND (Referrals.Site_Code = Referrals1.Site_Code)
AND ((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.Visit_Date
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) IS NOT NULL)
AND((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) = Billing_PSI_Line_Items1.PSI_Line_Item_Id)
GROUP BY Referrals1.Site_Code) AS 'TotalWithin3DaysPerAgeGroup'
, Referrals.Site_Code
, Referrals.Site_Code_Description
FROM dbo.AdHoc_Referrals AS Referrals INNER JOIN
dbo.AdHoc_Clients AS Clients ON Referrals.Client_Id = Clients.Client_Id INNER JOIN
dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items ON Clients.Client_Id = Billing_PSI_Line_Items.Client_Id
WHERE (Referrals.Client_Application_Date BETWEEN @Start_Date AND @End_Date)
AND (DATEDIFF(yy, Clients.DOB, GETDATE()) >= 85 AND DATEDIFF(yy, Clients.DOB, GETDATE()) <= 89)
AND ((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.Visit_Date
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) IS NOT NULL)
AND ((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) = Billing_PSI_Line_Items.PSI_Line_Item_Id)
GROUP BY Referrals.Site_Code, Referrals.Site_Code_Description
UNION
SELECT '90to94' AS 'AgeGroup', COUNT(Clients.Client_Number) AS 'AllPerAgeGroup'
,(SELECT COUNT(Clients1.Client_Number)
FROM dbo.AdHoc_Referrals AS Referrals1 INNER JOIN
dbo.AdHoc_Clients As Clients1 ON Referrals1.Client_Id = Clients1.Client_Id INNER JOIN
dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items1 ON Clients1.Client_Id = Billing_PSI_Line_Items1.Client_Id
WHERE (Referrals1.Client_Application_Date BETWEEN @Start_Date AND @End_Date)
AND (DATEDIFF(yy, Clients1.DOB, GETDATE()) >= 90 AND DATEDIFF(yy, Clients1.DOB, GETDATE()) <= 94)
AND (DATEDIFF(dd, Referrals1.Client_Application_Date, Billing_PSI_Line_Items1.Visit_Date) <= 3)
AND (Referrals.Site_Code = Referrals1.Site_Code)
AND ((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.Visit_Date
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) IS NOT NULL)
AND((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) = Billing_PSI_Line_Items1.PSI_Line_Item_Id)
GROUP BY Referrals1.Site_Code) AS 'TotalWithin3DaysPerAgeGroup'
, Referrals.Site_Code
, Referrals.Site_Code_Description
FROM dbo.AdHoc_Referrals AS Referrals INNER JOIN
dbo.AdHoc_Clients AS Clients ON Referrals.Client_Id = Clients.Client_Id INNER JOIN
dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items ON Clients.Client_Id = Billing_PSI_Line_Items.Client_Id
WHERE (Referrals.Client_Application_Date BETWEEN @Start_Date AND @End_Date)
AND (DATEDIFF(yy, Clients.DOB, GETDATE()) >= 90 AND DATEDIFF(yy, Clients.DOB, GETDATE()) <= 94)
AND ((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.Visit_Date
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) IS NOT NULL)
AND ((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) = Billing_PSI_Line_Items.PSI_Line_Item_Id)
GROUP BY Referrals.Site_Code, Referrals.Site_Code_Description
UNION
SELECT '95plus' AS 'AgeGroup', COUNT(Clients.Client_Number) AS 'AllPerAgeGroup'
,(SELECT COUNT(Clients1.Client_Number)
FROM dbo.AdHoc_Referrals AS Referrals1 INNER JOIN
dbo.AdHoc_Clients As Clients1 ON Referrals1.Client_Id = Clients1.Client_Id INNER JOIN
dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items1 ON Clients1.Client_Id = Billing_PSI_Line_Items1.Client_Id
WHERE (Referrals1.Client_Application_Date BETWEEN @Start_Date AND @End_Date)
AND (DATEDIFF(yy, Clients1.DOB, GETDATE()) >= 95)
AND (DATEDIFF(dd, Referrals1.Client_Application_Date, Billing_PSI_Line_Items1.Visit_Date) <= 3)
AND (Referrals.Site_Code = Referrals1.Site_Code)
AND ((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.Visit_Date
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) IS NOT NULL)
AND((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) = Billing_PSI_Line_Items1.PSI_Line_Item_Id)
GROUP BY Referrals1.Site_Code) AS 'TotalWithin3DaysPerAgeGroup'
, Referrals.Site_Code
, Referrals.Site_Code_Description
FROM dbo.AdHoc_Referrals AS Referrals INNER JOIN
dbo.AdHoc_Clients AS Clients ON Referrals.Client_Id = Clients.Client_Id INNER JOIN
dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items ON Clients.Client_Id = Billing_PSI_Line_Items.Client_Id
WHERE (Referrals.Client_Application_Date BETWEEN @Start_Date AND @End_Date)
AND (DATEDIFF(yy, Clients.DOB, GETDATE()) >= 95)
AND ((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.Visit_Date
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) IS NOT NULL)
AND ((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id
FROM dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1 INNER JOIN
dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1 ON
AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id INNER JOIN
dbo.AdHoc_Referrals AS AdHoc_Referrals_2 ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id
WHERE (Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id)
ORDER BY AdHoc_Billing_PSI_Line_Items_1.Visit_Date) = Billing_PSI_Line_Items.PSI_Line_Item_Id)
GROUP BY Referrals.Site_Code, Referrals.Site_Code_Description
OPTION (MAXDOP 5)
Thanks in advanced
Bill

Viewing 15 posts - 1 through 15 (of 43 total)

You must be logged in to reply to this topic. Login to reply

sturner SSC-Insane Points: 22411 More actions · Answer 1

you're kidding me right? For one thing I couldn't even begin to analyze that query without spending a lot of time looking at the database tables and understanding the process and business logic. And that is really just the start.

Good luck but I think you need to get someone on site to analyze the situation and figure out what's really going on there.

The probability of survival is inversely proportional to the angle of arrival.

Bill_CCAC Valued Member Points: 70 More actions · Answer 2

it's not as big as it may appear. it's the ten unions that make it look complicated.

But thanks for you input.

irobertson Hall of Fame Points: 3695 More actions · Answer 3

:w00t:

Hi Bill,

Your problem here is that you're running the same queries over and over and over and over again to get your output. Given the complexity of the query and the lack of sample data/table structures/expected results, it's difficult to do anything other than try to point you in the right direction. Have a try with the below and see how you get on...

The good news is that you should be able to create a single query that returns everything you need.

Instead of a query for each age group, try using a case statement:

case DATEDIFF(yy, Clients1.DOB, GETDATE())

when <18 then '1to18'

when <25 then '19to24'

etc...

Instead of running the subqueries to get criteria values, e.g. "((SELECT TOP (1) AdHoc_Billing_PSI_Line_Items_1.Visit_Date...", try using a variable to hold this value and calculating it before you execute your main query:

declare @criteria_date datetime

select @criteria_date = TOP 1 AdHoc_Billing_PSI_Line_Items_1.Visit_Date...

Do this for any other criteria values that are being looked up, including the getdate() calls.

Regards, Iain

irobertson Hall of Fame Points: 3695 More actions · Answer 4

Bill_CCAC (10/19/2010)
it's not as big as it may appear. it's the ten unions that make it look complicated.

lol 😀

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 5

sturner (10/19/2010)
you're kidding me right? ...

You took the words right out of my mouth!

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

This may help a little bit to decipher the code.

DECLARE @Start_Date As Datetime

DECLARE @End_Date As Datetime

SET @Start_Date = '04/01/2010'

SET @End_Date = '07/01/2010'

SELECT

'1to18' AS 'AgeGroup'

, COUNT(Clients.Client_Number) AS 'AllPerAgeGroup'

, (

SELECT

COUNT(Clients1.Client_Number)

FROM

dbo.AdHoc_Referrals AS Referrals1

INNER JOIN dbo.AdHoc_Clients As Clients1

ON Referrals1.Client_Id = Clients1.Client_Id

INNER JOIN dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items1

ON Clients1.Client_Id = Billing_PSI_Line_Items1.Client_Id

WHERE

( Referrals1.Client_Application_Date BETWEEN @Start_Date

AND @End_Date )

AND ( DATEDIFF(yy, Clients1.DOB, GETDATE()) <= 18 )

AND ( DATEDIFF(dd, Referrals1.Client_Application_Date,

Billing_PSI_Line_Items1.Visit_Date) <= 3 )

AND ( Referrals.Site_Code = Referrals1.Site_Code )

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) IS NOT NULL )

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) = Billing_PSI_Line_Items1.PSI_Line_Item_Id )

GROUP BY

Referrals1.Site_Code

) AS 'TotalWithin3DaysPerAgeGroup'

, Referrals.Site_Code

, Referrals.Site_Code_Description

FROM

dbo.AdHoc_Referrals AS Referrals

INNER JOIN dbo.AdHoc_Clients AS Clients

ON Referrals.Client_Id = Clients.Client_Id

INNER JOIN dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items

ON Clients.Client_Id = Billing_PSI_Line_Items.Client_Id

WHERE

( Referrals.Client_Application_Date BETWEEN @Start_Date

AND @End_Date )

AND ( DATEDIFF(yy, Clients.DOB, GETDATE()) <= 18 )

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) IS NOT NULL )

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) = Billing_PSI_Line_Items.PSI_Line_Item_Id )

GROUP BY

Referrals.Site_Code

, Referrals.Site_Code_Description

UNION

SELECT

'19to24' AS 'AgeGroup'

, COUNT(Clients.Client_Number) AS 'AllPerAgeGroup'

, (

SELECT

COUNT(Clients1.Client_Number)

FROM

dbo.AdHoc_Referrals AS Referrals1

INNER JOIN dbo.AdHoc_Clients As Clients1

ON Referrals1.Client_Id = Clients1.Client_Id

INNER JOIN dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items1

ON Clients1.Client_Id = Billing_PSI_Line_Items1.Client_Id

WHERE

( Referrals1.Client_Application_Date BETWEEN @Start_Date

AND @End_Date )

AND (

DATEDIFF(yy, Clients1.DOB, GETDATE()) >= 19

AND DATEDIFF(yy, Clients1.DOB, GETDATE()) <= 24

)

AND ( DATEDIFF(dd, Referrals1.Client_Application_Date,

Billing_PSI_Line_Items1.Visit_Date) <= 3 )

AND ( Referrals.Site_Code = Referrals1.Site_Code )

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) IS NOT NULL )

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) = Billing_PSI_Line_Items1.PSI_Line_Item_Id )

GROUP BY

Referrals1.Site_Code

) AS 'TotalWithin3DaysPerAgeGroup'

, Referrals.Site_Code

, Referrals.Site_Code_Description

FROM

dbo.AdHoc_Referrals AS Referrals

INNER JOIN dbo.AdHoc_Clients AS Clients

ON Referrals.Client_Id = Clients.Client_Id

INNER JOIN dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items

ON Clients.Client_Id = Billing_PSI_Line_Items.Client_Id

WHERE

( Referrals.Client_Application_Date BETWEEN @Start_Date

AND @End_Date )

AND (

DATEDIFF(yy, Clients.DOB, GETDATE()) >= 19

AND DATEDIFF(yy, Clients.DOB, GETDATE()) <= 24

)

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) IS NOT NULL )

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) = Billing_PSI_Line_Items.PSI_Line_Item_Id )

GROUP BY

Referrals.Site_Code

, Referrals.Site_Code_Description

UNION

SELECT

'25to29' AS 'AgeGroup'

, COUNT(Clients.Client_Number) AS 'AllPerAgeGroup'

, (

SELECT

COUNT(Clients1.Client_Number)

FROM

dbo.AdHoc_Referrals AS Referrals1

INNER JOIN dbo.AdHoc_Clients As Clients1

ON Referrals1.Client_Id = Clients1.Client_Id

INNER JOIN dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items1

ON Clients1.Client_Id = Billing_PSI_Line_Items1.Client_Id

WHERE

( Referrals1.Client_Application_Date BETWEEN @Start_Date

AND @End_Date )

AND (

DATEDIFF(yy, Clients1.DOB, GETDATE()) >= 25

AND DATEDIFF(yy, Clients1.DOB, GETDATE()) <= 29

)

AND ( DATEDIFF(dd, Referrals1.Client_Application_Date,

Billing_PSI_Line_Items1.Visit_Date) <= 3 )

AND ( Referrals.Site_Code = Referrals1.Site_Code )

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) IS NOT NULL )

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) = Billing_PSI_Line_Items1.PSI_Line_Item_Id )

GROUP BY

Referrals1.Site_Code

) AS 'TotalWithin3DaysPerAgeGroup'

, Referrals.Site_Code

, Referrals.Site_Code_Description

FROM

dbo.AdHoc_Referrals AS Referrals

INNER JOIN dbo.AdHoc_Clients AS Clients

ON Referrals.Client_Id = Clients.Client_Id

INNER JOIN dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items

ON Clients.Client_Id = Billing_PSI_Line_Items.Client_Id

WHERE

( Referrals.Client_Application_Date BETWEEN @Start_Date

AND @End_Date )

AND (

DATEDIFF(yy, Clients.DOB, GETDATE()) >= 25

AND DATEDIFF(yy, Clients.DOB, GETDATE()) <= 29

)

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) IS NOT NULL )

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) = Billing_PSI_Line_Items.PSI_Line_Item_Id )

GROUP BY

Referrals.Site_Code

, Referrals.Site_Code_Description

UNION

SELECT

'30to34' AS 'AgeGroup'

, COUNT(Clients.Client_Number) AS 'AllPerAgeGroup'

, (

SELECT

COUNT(Clients1.Client_Number)

FROM

dbo.AdHoc_Referrals AS Referrals1

INNER JOIN dbo.AdHoc_Clients As Clients1

ON Referrals1.Client_Id = Clients1.Client_Id

INNER JOIN dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items1

ON Clients1.Client_Id = Billing_PSI_Line_Items1.Client_Id

WHERE

( Referrals1.Client_Application_Date BETWEEN @Start_Date

AND @End_Date )

AND (

DATEDIFF(yy, Clients1.DOB, GETDATE()) >= 30

AND DATEDIFF(yy, Clients1.DOB, GETDATE()) <= 34

)

AND ( DATEDIFF(dd, Referrals1.Client_Application_Date,

Billing_PSI_Line_Items1.Visit_Date) <= 3 )

AND ( Referrals.Site_Code = Referrals1.Site_Code )

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) IS NOT NULL )

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) = Billing_PSI_Line_Items1.PSI_Line_Item_Id )

GROUP BY

Referrals1.Site_Code

) AS 'TotalWithin3DaysPerAgeGroup'

, Referrals.Site_Code

, Referrals.Site_Code_Description

FROM

dbo.AdHoc_Referrals AS Referrals

INNER JOIN dbo.AdHoc_Clients AS Clients

ON Referrals.Client_Id = Clients.Client_Id

INNER JOIN dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items

ON Clients.Client_Id = Billing_PSI_Line_Items.Client_Id

WHERE

( Referrals.Client_Application_Date BETWEEN @Start_Date

AND @End_Date )

AND (

DATEDIFF(yy, Clients.DOB, GETDATE()) >= 30

AND DATEDIFF(yy, Clients.DOB, GETDATE()) <= 34

)

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) IS NOT NULL )

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) = Billing_PSI_Line_Items.PSI_Line_Item_Id )

GROUP BY

Referrals.Site_Code

, Referrals.Site_Code_Description

UNION

SELECT

'35to39' AS 'AgeGroup'

, COUNT(Clients.Client_Number) AS 'AllPerAgeGroup'

, (

SELECT

COUNT(Clients1.Client_Number)

FROM

dbo.AdHoc_Referrals AS Referrals1

INNER JOIN dbo.AdHoc_Clients As Clients1

ON Referrals1.Client_Id = Clients1.Client_Id

INNER JOIN dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items1

ON Clients1.Client_Id = Billing_PSI_Line_Items1.Client_Id

WHERE

( Referrals1.Client_Application_Date BETWEEN @Start_Date

AND @End_Date )

AND (

DATEDIFF(yy, Clients1.DOB, GETDATE()) >= 35

AND DATEDIFF(yy, Clients1.DOB, GETDATE()) <= 39

)

AND ( DATEDIFF(dd, Referrals1.Client_Application_Date,

Billing_PSI_Line_Items1.Visit_Date) <= 3 )

AND ( Referrals.Site_Code = Referrals1.Site_Code )

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) IS NOT NULL )

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) = Billing_PSI_Line_Items1.PSI_Line_Item_Id )

GROUP BY

Referrals1.Site_Code

) AS 'TotalWithin3DaysPerAgeGroup'

, Referrals.Site_Code

, Referrals.Site_Code_Description

FROM

dbo.AdHoc_Referrals AS Referrals

INNER JOIN dbo.AdHoc_Clients AS Clients

ON Referrals.Client_Id = Clients.Client_Id

INNER JOIN dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items

ON Clients.Client_Id = Billing_PSI_Line_Items.Client_Id

WHERE

( Referrals.Client_Application_Date BETWEEN @Start_Date

AND @End_Date )

AND (

DATEDIFF(yy, Clients.DOB, GETDATE()) >= 35

AND DATEDIFF(yy, Clients.DOB, GETDATE()) <= 39

)

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) IS NOT NULL )

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) = Billing_PSI_Line_Items.PSI_Line_Item_Id )

GROUP BY

Referrals.Site_Code

, Referrals.Site_Code_Description

UNION

SELECT

'40to44' AS 'AgeGroup'

, COUNT(Clients.Client_Number) AS 'AllPerAgeGroup'

, (

SELECT

COUNT(Clients1.Client_Number)

FROM

dbo.AdHoc_Referrals AS Referrals1

INNER JOIN dbo.AdHoc_Clients As Clients1

ON Referrals1.Client_Id = Clients1.Client_Id

INNER JOIN dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items1

ON Clients1.Client_Id = Billing_PSI_Line_Items1.Client_Id

WHERE

( Referrals1.Client_Application_Date BETWEEN @Start_Date

AND @End_Date )

AND (

DATEDIFF(yy, Clients1.DOB, GETDATE()) >= 40

AND DATEDIFF(yy, Clients1.DOB, GETDATE()) <= 44

)

AND ( DATEDIFF(dd, Referrals1.Client_Application_Date,

Billing_PSI_Line_Items1.Visit_Date) <= 3 )

AND ( Referrals.Site_Code = Referrals1.Site_Code )

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) IS NOT NULL )

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) = Billing_PSI_Line_Items1.PSI_Line_Item_Id )

GROUP BY

Referrals1.Site_Code

) AS 'TotalWithin3DaysPerAgeGroup'

, Referrals.Site_Code

, Referrals.Site_Code_Description

FROM

dbo.AdHoc_Referrals AS Referrals

INNER JOIN dbo.AdHoc_Clients AS Clients

ON Referrals.Client_Id = Clients.Client_Id

INNER JOIN dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items

ON Clients.Client_Id = Billing_PSI_Line_Items.Client_Id

WHERE

( Referrals.Client_Application_Date BETWEEN @Start_Date

AND @End_Date )

AND (

DATEDIFF(yy, Clients.DOB, GETDATE()) >= 40

AND DATEDIFF(yy, Clients.DOB, GETDATE()) <= 44

)

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) IS NOT NULL )

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) = Billing_PSI_Line_Items.PSI_Line_Item_Id )

GROUP BY

Referrals.Site_Code

, Referrals.Site_Code_Description

UNION

SELECT

'45to49' AS 'AgeGroup'

, COUNT(Clients.Client_Number) AS 'AllPerAgeGroup'

, (

SELECT

COUNT(Clients1.Client_Number)

FROM

dbo.AdHoc_Referrals AS Referrals1

INNER JOIN dbo.AdHoc_Clients As Clients1

ON Referrals1.Client_Id = Clients1.Client_Id

INNER JOIN dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items1

ON Clients1.Client_Id = Billing_PSI_Line_Items1.Client_Id

WHERE

( Referrals1.Client_Application_Date BETWEEN @Start_Date

AND @End_Date )

AND (

DATEDIFF(yy, Clients1.DOB, GETDATE()) >= 45

AND DATEDIFF(yy, Clients1.DOB, GETDATE()) <= 49

)

AND ( DATEDIFF(dd, Referrals1.Client_Application_Date,

Billing_PSI_Line_Items1.Visit_Date) <= 3 )

AND ( Referrals.Site_Code = Referrals1.Site_Code )

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) IS NOT NULL )

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) = Billing_PSI_Line_Items1.PSI_Line_Item_Id )

GROUP BY

Referrals1.Site_Code

) AS 'TotalWithin3DaysPerAgeGroup'

, Referrals.Site_Code

, Referrals.Site_Code_Description

FROM

dbo.AdHoc_Referrals AS Referrals

INNER JOIN dbo.AdHoc_Clients AS Clients

ON Referrals.Client_Id = Clients.Client_Id

INNER JOIN dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items

ON Clients.Client_Id = Billing_PSI_Line_Items.Client_Id

WHERE

( Referrals.Client_Application_Date BETWEEN @Start_Date

AND @End_Date )

AND (

DATEDIFF(yy, Clients.DOB, GETDATE()) >= 45

AND DATEDIFF(yy, Clients.DOB, GETDATE()) <= 49

)

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) IS NOT NULL )

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) = Billing_PSI_Line_Items.PSI_Line_Item_Id )

GROUP BY

Referrals.Site_Code

, Referrals.Site_Code_Description

UNION

SELECT

'50to54' AS 'AgeGroup'

, COUNT(Clients.Client_Number) AS 'AllPerAgeGroup'

, (

SELECT

COUNT(Clients1.Client_Number)

FROM

dbo.AdHoc_Referrals AS Referrals1

INNER JOIN dbo.AdHoc_Clients As Clients1

ON Referrals1.Client_Id = Clients1.Client_Id

INNER JOIN dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items1

ON Clients1.Client_Id = Billing_PSI_Line_Items1.Client_Id

WHERE

( Referrals1.Client_Application_Date BETWEEN @Start_Date

AND @End_Date )

AND (

DATEDIFF(yy, Clients1.DOB, GETDATE()) >= 50

AND DATEDIFF(yy, Clients1.DOB, GETDATE()) <= 54

)

AND ( DATEDIFF(dd, Referrals1.Client_Application_Date,

Billing_PSI_Line_Items1.Visit_Date) <= 3 )

AND ( Referrals.Site_Code = Referrals1.Site_Code )

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) IS NOT NULL )

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) = Billing_PSI_Line_Items1.PSI_Line_Item_Id )

GROUP BY

Referrals1.Site_Code

) AS 'TotalWithin3DaysPerAgeGroup'

, Referrals.Site_Code

, Referrals.Site_Code_Description

FROM

dbo.AdHoc_Referrals AS Referrals

INNER JOIN dbo.AdHoc_Clients AS Clients

ON Referrals.Client_Id = Clients.Client_Id

INNER JOIN dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items

ON Clients.Client_Id = Billing_PSI_Line_Items.Client_Id

WHERE

( Referrals.Client_Application_Date BETWEEN @Start_Date

AND @End_Date )

AND (

DATEDIFF(yy, Clients.DOB, GETDATE()) >= 50

AND DATEDIFF(yy, Clients.DOB, GETDATE()) <= 54

)

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) IS NOT NULL )

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) = Billing_PSI_Line_Items.PSI_Line_Item_Id )

GROUP BY

Referrals.Site_Code

, Referrals.Site_Code_Description

UNION

SELECT

'55to59' AS 'AgeGroup'

, COUNT(Clients.Client_Number) AS 'AllPerAgeGroup'

, (

SELECT

COUNT(Clients1.Client_Number)

FROM

dbo.AdHoc_Referrals AS Referrals1

INNER JOIN dbo.AdHoc_Clients As Clients1

ON Referrals1.Client_Id = Clients1.Client_Id

INNER JOIN dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items1

ON Clients1.Client_Id = Billing_PSI_Line_Items1.Client_Id

WHERE

( Referrals1.Client_Application_Date BETWEEN @Start_Date

AND @End_Date )

AND (

DATEDIFF(yy, Clients1.DOB, GETDATE()) >= 55

AND DATEDIFF(yy, Clients1.DOB, GETDATE()) <= 59

)

AND ( DATEDIFF(dd, Referrals1.Client_Application_Date,

Billing_PSI_Line_Items1.Visit_Date) <= 3 )

AND ( Referrals.Site_Code = Referrals1.Site_Code )

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) IS NOT NULL )

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) = Billing_PSI_Line_Items1.PSI_Line_Item_Id )

GROUP BY

Referrals1.Site_Code

) AS 'TotalWithin3DaysPerAgeGroup'

, Referrals.Site_Code

, Referrals.Site_Code_Description

FROM

dbo.AdHoc_Referrals AS Referrals

INNER JOIN dbo.AdHoc_Clients AS Clients

ON Referrals.Client_Id = Clients.Client_Id

INNER JOIN dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items

ON Clients.Client_Id = Billing_PSI_Line_Items.Client_Id

WHERE

( Referrals.Client_Application_Date BETWEEN @Start_Date

AND @End_Date )

AND (

DATEDIFF(yy, Clients.DOB, GETDATE()) >= 55

AND DATEDIFF(yy, Clients.DOB, GETDATE()) <= 59

)

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) IS NOT NULL )

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) = Billing_PSI_Line_Items.PSI_Line_Item_Id )

GROUP BY

Referrals.Site_Code

, Referrals.Site_Code_Description

UNION

SELECT

'60to64' AS 'AgeGroup'

, COUNT(Clients.Client_Number) AS 'AllPerAgeGroup'

, (

SELECT

COUNT(Clients1.Client_Number)

FROM

dbo.AdHoc_Referrals AS Referrals1

INNER JOIN dbo.AdHoc_Clients As Clients1

ON Referrals1.Client_Id = Clients1.Client_Id

INNER JOIN dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items1

ON Clients1.Client_Id = Billing_PSI_Line_Items1.Client_Id

WHERE

( Referrals1.Client_Application_Date BETWEEN @Start_Date

AND @End_Date )

AND (

DATEDIFF(yy, Clients1.DOB, GETDATE()) >= 60

AND DATEDIFF(yy, Clients1.DOB, GETDATE()) <= 64

)

AND ( DATEDIFF(dd, Referrals1.Client_Application_Date,

Billing_PSI_Line_Items1.Visit_Date) <= 3 )

AND ( Referrals.Site_Code = Referrals1.Site_Code )

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) IS NOT NULL )

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) = Billing_PSI_Line_Items1.PSI_Line_Item_Id )

GROUP BY

Referrals1.Site_Code

) AS 'TotalWithin3DaysPerAgeGroup'

, Referrals.Site_Code

, Referrals.Site_Code_Description

FROM

dbo.AdHoc_Referrals AS Referrals

INNER JOIN dbo.AdHoc_Clients AS Clients

ON Referrals.Client_Id = Clients.Client_Id

INNER JOIN dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items

ON Clients.Client_Id = Billing_PSI_Line_Items.Client_Id

WHERE

( Referrals.Client_Application_Date BETWEEN @Start_Date

AND @End_Date )

AND (

DATEDIFF(yy, Clients.DOB, GETDATE()) >= 60

AND DATEDIFF(yy, Clients.DOB, GETDATE()) <= 64

)

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) IS NOT NULL )

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) = Billing_PSI_Line_Items.PSI_Line_Item_Id )

GROUP BY

Referrals.Site_Code

, Referrals.Site_Code_Description

UNION

SELECT

'65to69' AS 'AgeGroup'

, COUNT(Clients.Client_Number) AS 'AllPerAgeGroup'

, (

SELECT

COUNT(Clients1.Client_Number)

FROM

dbo.AdHoc_Referrals AS Referrals1

INNER JOIN dbo.AdHoc_Clients As Clients1

ON Referrals1.Client_Id = Clients1.Client_Id

INNER JOIN dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items1

ON Clients1.Client_Id = Billing_PSI_Line_Items1.Client_Id

WHERE

( Referrals1.Client_Application_Date BETWEEN @Start_Date

AND @End_Date )

AND (

DATEDIFF(yy, Clients1.DOB, GETDATE()) >= 65

AND DATEDIFF(yy, Clients1.DOB, GETDATE()) <= 69

)

AND ( DATEDIFF(dd, Referrals1.Client_Application_Date,

Billing_PSI_Line_Items1.Visit_Date) <= 3 )

AND ( Referrals.Site_Code = Referrals1.Site_Code )

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) IS NOT NULL )

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) = Billing_PSI_Line_Items1.PSI_Line_Item_Id )

GROUP BY

Referrals1.Site_Code

) AS 'TotalWithin3DaysPerAgeGroup'

, Referrals.Site_Code

, Referrals.Site_Code_Description

FROM

dbo.AdHoc_Referrals AS Referrals

INNER JOIN dbo.AdHoc_Clients AS Clients

ON Referrals.Client_Id = Clients.Client_Id

INNER JOIN dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items

ON Clients.Client_Id = Billing_PSI_Line_Items.Client_Id

WHERE

( Referrals.Client_Application_Date BETWEEN @Start_Date

AND @End_Date )

AND (

DATEDIFF(yy, Clients.DOB, GETDATE()) >= 65

AND DATEDIFF(yy, Clients.DOB, GETDATE()) <= 69

)

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) IS NOT NULL )

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) = Billing_PSI_Line_Items.PSI_Line_Item_Id )

GROUP BY

Referrals.Site_Code

, Referrals.Site_Code_Description

UNION

SELECT

'70to74' AS 'AgeGroup'

, COUNT(Clients.Client_Number) AS 'AllPerAgeGroup'

, (

SELECT

COUNT(Clients1.Client_Number)

FROM

dbo.AdHoc_Referrals AS Referrals1

INNER JOIN dbo.AdHoc_Clients As Clients1

ON Referrals1.Client_Id = Clients1.Client_Id

INNER JOIN dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items1

ON Clients1.Client_Id = Billing_PSI_Line_Items1.Client_Id

WHERE

( Referrals1.Client_Application_Date BETWEEN @Start_Date

AND @End_Date )

AND (

DATEDIFF(yy, Clients1.DOB, GETDATE()) >= 70

AND DATEDIFF(yy, Clients1.DOB, GETDATE()) <= 74

)

AND ( DATEDIFF(dd, Referrals1.Client_Application_Date,

Billing_PSI_Line_Items1.Visit_Date) <= 3 )

AND ( Referrals.Site_Code = Referrals1.Site_Code )

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) IS NOT NULL )

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) = Billing_PSI_Line_Items1.PSI_Line_Item_Id )

GROUP BY

Referrals1.Site_Code

) AS 'TotalWithin3DaysPerAgeGroup'

, Referrals.Site_Code

, Referrals.Site_Code_Description

FROM

dbo.AdHoc_Referrals AS Referrals

INNER JOIN dbo.AdHoc_Clients AS Clients

ON Referrals.Client_Id = Clients.Client_Id

INNER JOIN dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items

ON Clients.Client_Id = Billing_PSI_Line_Items.Client_Id

WHERE

( Referrals.Client_Application_Date BETWEEN @Start_Date

AND @End_Date )

AND (

DATEDIFF(yy, Clients.DOB, GETDATE()) >= 70

AND DATEDIFF(yy, Clients.DOB, GETDATE()) <= 74

)

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) IS NOT NULL )

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) = Billing_PSI_Line_Items.PSI_Line_Item_Id )

GROUP BY

Referrals.Site_Code

, Referrals.Site_Code_Description

UNION

SELECT

'75to79' AS 'AgeGroup'

, COUNT(Clients.Client_Number) AS 'AllPerAgeGroup'

, (

SELECT

COUNT(Clients1.Client_Number)

FROM

dbo.AdHoc_Referrals AS Referrals1

INNER JOIN dbo.AdHoc_Clients As Clients1

ON Referrals1.Client_Id = Clients1.Client_Id

INNER JOIN dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items1

ON Clients1.Client_Id = Billing_PSI_Line_Items1.Client_Id

WHERE

( Referrals1.Client_Application_Date BETWEEN @Start_Date

AND @End_Date )

AND (

DATEDIFF(yy, Clients1.DOB, GETDATE()) >= 75

AND DATEDIFF(yy, Clients1.DOB, GETDATE()) <= 79

)

AND ( DATEDIFF(dd, Referrals1.Client_Application_Date,

Billing_PSI_Line_Items1.Visit_Date) <= 3 )

AND ( Referrals.Site_Code = Referrals1.Site_Code )

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) IS NOT NULL )

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) = Billing_PSI_Line_Items1.PSI_Line_Item_Id )

GROUP BY

Referrals1.Site_Code

) AS 'TotalWithin3DaysPerAgeGroup'

, Referrals.Site_Code

, Referrals.Site_Code_Description

FROM

dbo.AdHoc_Referrals AS Referrals

INNER JOIN dbo.AdHoc_Clients AS Clients

ON Referrals.Client_Id = Clients.Client_Id

INNER JOIN dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items

ON Clients.Client_Id = Billing_PSI_Line_Items.Client_Id

WHERE

( Referrals.Client_Application_Date BETWEEN @Start_Date

AND @End_Date )

AND (

DATEDIFF(yy, Clients.DOB, GETDATE()) >= 75

AND DATEDIFF(yy, Clients.DOB, GETDATE()) <= 79

)

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) IS NOT NULL )

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) = Billing_PSI_Line_Items.PSI_Line_Item_Id )

GROUP BY

Referrals.Site_Code

, Referrals.Site_Code_Description

UNION

SELECT

'80to84' AS 'AgeGroup'

, COUNT(Clients.Client_Number) AS 'AllPerAgeGroup'

, (

SELECT

COUNT(Clients1.Client_Number)

FROM

dbo.AdHoc_Referrals AS Referrals1

INNER JOIN dbo.AdHoc_Clients As Clients1

ON Referrals1.Client_Id = Clients1.Client_Id

INNER JOIN dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items1

ON Clients1.Client_Id = Billing_PSI_Line_Items1.Client_Id

WHERE

( Referrals1.Client_Application_Date BETWEEN @Start_Date

AND @End_Date )

AND (

DATEDIFF(yy, Clients1.DOB, GETDATE()) >= 80

AND DATEDIFF(yy, Clients1.DOB, GETDATE()) <= 84

)

AND ( DATEDIFF(dd, Referrals1.Client_Application_Date,

Billing_PSI_Line_Items1.Visit_Date) <= 3 )

AND ( Referrals.Site_Code = Referrals1.Site_Code )

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) IS NOT NULL )

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) = Billing_PSI_Line_Items1.PSI_Line_Item_Id )

GROUP BY

Referrals1.Site_Code

) AS 'TotalWithin3DaysPerAgeGroup'

, Referrals.Site_Code

, Referrals.Site_Code_Description

FROM

dbo.AdHoc_Referrals AS Referrals

INNER JOIN dbo.AdHoc_Clients AS Clients

ON Referrals.Client_Id = Clients.Client_Id

INNER JOIN dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items

ON Clients.Client_Id = Billing_PSI_Line_Items.Client_Id

WHERE

( Referrals.Client_Application_Date BETWEEN @Start_Date

AND @End_Date )

AND (

DATEDIFF(yy, Clients.DOB, GETDATE()) >= 80

AND DATEDIFF(yy, Clients.DOB, GETDATE()) <= 84

)

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) IS NOT NULL )

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) = Billing_PSI_Line_Items.PSI_Line_Item_Id )

GROUP BY

Referrals.Site_Code

, Referrals.Site_Code_Description

UNION

SELECT

'85to89' AS 'AgeGroup'

, COUNT(Clients.Client_Number) AS 'AllPerAgeGroup'

, (

SELECT

COUNT(Clients1.Client_Number)

FROM

dbo.AdHoc_Referrals AS Referrals1

INNER JOIN dbo.AdHoc_Clients As Clients1

ON Referrals1.Client_Id = Clients1.Client_Id

INNER JOIN dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items1

ON Clients1.Client_Id = Billing_PSI_Line_Items1.Client_Id

WHERE

( Referrals1.Client_Application_Date BETWEEN @Start_Date

AND @End_Date )

AND (

DATEDIFF(yy, Clients1.DOB, GETDATE()) >= 85

AND DATEDIFF(yy, Clients1.DOB, GETDATE()) <= 89

)

AND ( DATEDIFF(dd, Referrals1.Client_Application_Date,

Billing_PSI_Line_Items1.Visit_Date) <= 3 )

AND ( Referrals.Site_Code = Referrals1.Site_Code )

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) IS NOT NULL )

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) = Billing_PSI_Line_Items1.PSI_Line_Item_Id )

GROUP BY

Referrals1.Site_Code

) AS 'TotalWithin3DaysPerAgeGroup'

, Referrals.Site_Code

, Referrals.Site_Code_Description

FROM

dbo.AdHoc_Referrals AS Referrals

INNER JOIN dbo.AdHoc_Clients AS Clients

ON Referrals.Client_Id = Clients.Client_Id

INNER JOIN dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items

ON Clients.Client_Id = Billing_PSI_Line_Items.Client_Id

WHERE

( Referrals.Client_Application_Date BETWEEN @Start_Date

AND @End_Date )

AND (

DATEDIFF(yy, Clients.DOB, GETDATE()) >= 85

AND DATEDIFF(yy, Clients.DOB, GETDATE()) <= 89

)

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) IS NOT NULL )

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) = Billing_PSI_Line_Items.PSI_Line_Item_Id )

GROUP BY

Referrals.Site_Code

, Referrals.Site_Code_Description

UNION

SELECT

'90to94' AS 'AgeGroup'

, COUNT(Clients.Client_Number) AS 'AllPerAgeGroup'

, (

SELECT

COUNT(Clients1.Client_Number)

FROM

dbo.AdHoc_Referrals AS Referrals1

INNER JOIN dbo.AdHoc_Clients As Clients1

ON Referrals1.Client_Id = Clients1.Client_Id

INNER JOIN dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items1

ON Clients1.Client_Id = Billing_PSI_Line_Items1.Client_Id

WHERE

( Referrals1.Client_Application_Date BETWEEN @Start_Date

AND @End_Date )

AND (

DATEDIFF(yy, Clients1.DOB, GETDATE()) >= 90

AND DATEDIFF(yy, Clients1.DOB, GETDATE()) <= 94

)

AND ( DATEDIFF(dd, Referrals1.Client_Application_Date,

Billing_PSI_Line_Items1.Visit_Date) <= 3 )

AND ( Referrals.Site_Code = Referrals1.Site_Code )

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) IS NOT NULL )

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) = Billing_PSI_Line_Items1.PSI_Line_Item_Id )

GROUP BY

Referrals1.Site_Code

) AS 'TotalWithin3DaysPerAgeGroup'

, Referrals.Site_Code

, Referrals.Site_Code_Description

FROM

dbo.AdHoc_Referrals AS Referrals

INNER JOIN dbo.AdHoc_Clients AS Clients

ON Referrals.Client_Id = Clients.Client_Id

INNER JOIN dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items

ON Clients.Client_Id = Billing_PSI_Line_Items.Client_Id

WHERE

( Referrals.Client_Application_Date BETWEEN @Start_Date

AND @End_Date )

AND (

DATEDIFF(yy, Clients.DOB, GETDATE()) >= 90

AND DATEDIFF(yy, Clients.DOB, GETDATE()) <= 94

)

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) IS NOT NULL )

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) = Billing_PSI_Line_Items.PSI_Line_Item_Id )

GROUP BY

Referrals.Site_Code

, Referrals.Site_Code_Description

UNION

SELECT

'95plus' AS 'AgeGroup'

, COUNT(Clients.Client_Number) AS 'AllPerAgeGroup'

, (

SELECT

COUNT(Clients1.Client_Number)

FROM

dbo.AdHoc_Referrals AS Referrals1

INNER JOIN dbo.AdHoc_Clients As Clients1

ON Referrals1.Client_Id = Clients1.Client_Id

INNER JOIN dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items1

ON Clients1.Client_Id = Billing_PSI_Line_Items1.Client_Id

WHERE

( Referrals1.Client_Application_Date BETWEEN @Start_Date

AND @End_Date )

AND ( DATEDIFF(yy, Clients1.DOB, GETDATE()) >= 95 )

AND ( DATEDIFF(dd, Referrals1.Client_Application_Date,

Billing_PSI_Line_Items1.Visit_Date) <= 3 )

AND ( Referrals.Site_Code = Referrals1.Site_Code )

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) IS NOT NULL )

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) = Billing_PSI_Line_Items1.PSI_Line_Item_Id )

GROUP BY

Referrals1.Site_Code

) AS 'TotalWithin3DaysPerAgeGroup'

, Referrals.Site_Code

, Referrals.Site_Code_Description

FROM

dbo.AdHoc_Referrals AS Referrals

INNER JOIN dbo.AdHoc_Clients AS Clients

ON Referrals.Client_Id = Clients.Client_Id

INNER JOIN dbo.AdHoc_Billing_PSI_Line_Items AS Billing_PSI_Line_Items

ON Clients.Client_Id = Billing_PSI_Line_Items.Client_Id

WHERE

( Referrals.Client_Application_Date BETWEEN @Start_Date

AND @End_Date )

AND ( DATEDIFF(yy, Clients.DOB, GETDATE()) >= 95 )

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) IS NOT NULL )

AND ( (

SELECT TOP ( 1 )

AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id

FROM

dbo.AdHoc_Billing_PSI_Line_Items AS AdHoc_Billing_PSI_Line_Items_1

INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS AdHoc_Billing_PSI_Payment_Adjustments_1

ON AdHoc_Billing_PSI_Line_Items_1.PSI_Line_Item_Id = AdHoc_Billing_PSI_Payment_Adjustments_1.PSI_Line_Item_Id

INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2

ON AdHoc_Billing_PSI_Payment_Adjustments_1.Referral_Id = AdHoc_Referrals_2.Referral_Id

WHERE

( Referrals.Referral_Id = AdHoc_Referrals_2.Referral_Id )

ORDER BY

AdHoc_Billing_PSI_Line_Items_1.Visit_Date

) = Billing_PSI_Line_Items.PSI_Line_Item_Id )

GROUP BY

Referrals.Site_Code

, Referrals.Site_Code_Description

OPTION

( MAXDOP 5 )

Bill_CCAC Valued Member Points: 70 More actions · Answer 7

Alvin Ramard (10/19/2010)
sturner (10/19/2010)
you're kidding me right? ...
You took the words right out of my mouth!

I don't waste my time or yours with jokes on forums. I asked because I want help.

Thanks to SSC-Enthusiastic for the usefully input.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 8

It's basically the same query except for this:

DATEDIFF(yy, Clients1.DOB, GETDATE()) >= 30 AND DATEDIFF(yy, Clients1.DOB, GETDATE()) <= 34

which is buried deep in the middle of the thing.

So, instead of doing a union for each group, do a subquery that groups by DATEDIFF(yy, DOB, GETDATE()) for the information, and then use a SUM( CASE WHEN DOBAge (or whatever you call it) BETWEEN 30 and 34 THEN 1 ELSE 0 END) as your counting mechanism.

Now, beyond that? You're roughly screwed if you don't have access to the structures, indexes, and optimization methodologies you'll need at the data level to deal with this. Send it up to the DBAs in your company for optimization, or get dbo rights.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

As it's been pointed out you can do a single query and add an age group. From there pivot the data in the reporting application.

That'll take out 99% of the work (90% of the queries and 9 very costly unions)

BTW I can assume that union all in your case would also yield the same results without the cost of doing a massive distinct at the end.

Greg Edwards-268690 SSC-Insane Points: 20676 More actions · Answer 10

It also looks like half of the unions might be due to looking for within 3 days?

So have you used Case statements before?

Usually your best answers come when you post some scripts to create tables and sample data, and expected results.

This allows a rather easy way to create a local environment to work with and see how you'd solve it.

Is that something you could do?

You don't have time for jokes, and these are all volunteers.

The quicker they can really understand the problem, the more time they might find to actually help.

Greg E

sturner SSC-Insane Points: 22411 More actions · Answer 11

Thanks for making that point GregE.

Just so the original poster doesn't think I'm a grouch and since others did point a few of the more obvious problems, I will add that I see a lot of this stuff in the where clause:

and (DATEDIFF(yy, Clients1.DOB, GETDATE()) <= 18)

You should avoid datediff (and the use of any function) on sargs in the where clause because any indexes on these dates cannot be used. I know this particular case requires looking at each DOB but I just wanted to point that out.

The probability of survival is inversely proportional to the angle of arrival.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 12

sturner (10/19/2010)
So you don't think I'm a grouch and since other did point a few of the more obvious problems, I will add that I see a lot of this stuff in the where clause:
and (DATEDIFF(yy, Clients1.DOB, GETDATE()) <= 18)
You should avoid this (and the use of any function) on sargs in the where clause because any indexes on these dates cannot be used.

Admittedly I barely scanned the code, but at a guess his selectivity is shot no matter what here for the ranges he's pulling, so he's never going to get it to seek. If he can get this down to 1 scan instead of 10 he'll find a nice speed boost, though.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Brandie Tarvin SSC Guru Points: 172998 More actions · Answer 13

Bill_CCAC (10/19/2010)
Hi All. I'm hoping for ideas on how to make either my query more efficient of a better way of collecting the same result set. This query currently takes the better part of an hour to run which is not at all acceptable but it does return the right results. I'm a report writer, not a DBA and I don't have access to the server to fine tune.

If you don't have access to the servers, how are you going to change the query?

Still, ISTR that having multiple UNION statements like this does tend to kill performance. If these are all the same query with multiple conditions, I'd advise following the Case statement advice. Or, break it down by inserting everything into a temp table, one query at a time. Or use a CTE.

Any of those should improve performance over the monster query you have now.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Bill_CCAC Valued Member Points: 70 More actions · Answer 14

you folks are all fabulous. thank you all.

I had been trying to use the case statement but ran into problems and didn't get the results I was after. I wasn't grouping with the case statement so with the above advise I added that and I now have it back down to one select and no unions. As you pointed out, "The way it should be" 🙂