October 20, 2010 at 8:14 am
irobertson (10/20/2010)
Hi Bill,One thing you might like to try is to pre-calculate the two correlated subqueries that you're using to generate criteria and hold the values in variables.
Also, a comment on readability - using aliases for table names can make things a bit easier to read (and type :-)), e.g.
Ditto on both. You need to think about efficiency. (EDIT: Using short aliases will save you time and effort in typing.)
Someone will correct me if I'm wrong, but I believe that those subqueries in your WHERE clause get re-processed for every row that comes out of your database. If the values of those subqueries will never change, it is much more efficient, less processor intensive, and less of a pain to simply assign the values to variables and then match to the variables at the end of your query.
Example (that you WILL need to fix, since the WHERE clauses reference a table outside of the subqueries and my code below doesn't take that into account):
DECLARE @VisitDate datetime, @PSILineItemID int; --Guessing at this last datatype
SET @VisitDate = (SELECT TOP (1) abpli1.Visit_Date
FROM dbo.AdHoc_Billing_PSI_Line_Items AS abpli1
INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS adj1
ON abpli1.PSI_Line_Item_Id = adj1.PSI_Line_Item_Id
INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2
ON adj1.Referral_Id = AdHoc_Referrals_2.Referral_Id
AND Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id
--moved into ON & out of WHERE
ORDER BY abpli1.Visit_Date)
SET @PSILineItemID = (SELECT TOP (1) abpli1.PSI_Line_Item_Id
FROM dbo.AdHoc_Billing_PSI_Line_Items AS abpli1
INNER JOIN dbo.AdHoc_Billing_PSI_Payment_Adjustments AS adj1
ON abpli1.PSI_Line_Item_Id = adj1.PSI_Line_Item_Id
INNER JOIN dbo.AdHoc_Referrals AS AdHoc_Referrals_2
ON adj1.Referral_Id = AdHoc_Referrals_2.Referral_Id
AND Referrals1.Referral_Id = AdHoc_Referrals_2.Referral_Id
--moved into ON & out of WHERE
ORDER BY abpli1.Visit_Date)
....insert query here....
WHERE ...
AND @VisitDate IS NOT NULL)
AND @PSILineItemID = Billing_PSI_Line_Items1.PSI_Line_Item_Id)
October 20, 2010 at 10:24 am
Here's another idea too... I moved one of the sub-queries to the join clause, computing the MaxVisitDate for all the referral_ids in one fell swoop instead of one at a time, will this work for you? I don't have the data, so I can't test it (I hope all my parens are in the right place). You can move the other subquery too, although how you write it will depend on whether the max visit date always falls in with the max line item ID or not...
SELECT COUNT(Clients1.Client_Number) As Totals
,SUM(CASE WHEN DATEDIFF(yy, Clients1.DOB, @TodaysDate) <= 18 THEN 1 ELSE 0 END) As 'allInAgeGroup0_18'
,SUM(CASE WHEN DATEDIFF(yy, Clients1.DOB, @TodaysDate) >= 19 AND DATEDIFF(yy, Clients1.DOB, @TodaysDate) <= 24 THEN 1 ELSE 0 END) As 'allInAgeGroup19-24'
,SUM(CASE WHEN DATEDIFF(yy, Clients1.DOB, @TodaysDate) >= 25 AND DATEDIFF(yy, Clients1.DOB, @TodaysDate) <= 29 THEN 1 ELSE 0 END) As 'allInAgeGroup25-29'
,SUM(CASE WHEN DATEDIFF(yy, Clients1.DOB, @TodaysDate) >= 30 AND DATEDIFF(yy, Clients1.DOB, @TodaysDate) <= 34 THEN 1 ELSE 0 END) As 'allInAgeGroup30-34'
,SUM(CASE WHEN DATEDIFF(yy, Clients1.DOB, @TodaysDate) >= 35 AND DATEDIFF(yy, Clients1.DOB, @TodaysDate) <= 39 THEN 1 ELSE 0 END) As 'allInAgeGroup35-39'
,SUM(CASE WHEN DATEDIFF(yy, Clients1.DOB, @TodaysDate) >= 40 AND DATEDIFF(yy, Clients1.DOB, @TodaysDate) <= 44 THEN 1 ELSE 0 END) As 'allInAgeGroup40-44'
,SUM(CASE WHEN DATEDIFF(yy, Clients1.DOB, @TodaysDate) >= 45 AND DATEDIFF(yy, Clients1.DOB, @TodaysDate) <= 49 THEN 1 ELSE 0 END) As 'allInAgeGroup45-49'
,SUM(CASE WHEN DATEDIFF(yy, Clients1.DOB, @TodaysDate) >= 50 AND DATEDIFF(yy, Clients1.DOB, @TodaysDate) <= 54 THEN 1 ELSE 0 END) As 'allInAgeGroup50-54'
,SUM(CASE WHEN DATEDIFF(yy, Clients1.DOB, @TodaysDate) >= 55 AND DATEDIFF(yy, Clients1.DOB, @TodaysDate) <= 59 THEN 1 ELSE 0 END) As 'allInAgeGroup55-59'
,SUM(CASE WHEN DATEDIFF(yy, Clients1.DOB, @TodaysDate) >= 60 AND DATEDIFF(yy, Clients1.DOB, @TodaysDate) <= 64 THEN 1 ELSE 0 END) As 'allInAgeGroup60-64'
,SUM(CASE WHEN DATEDIFF(yy, Clients1.DOB, @TodaysDate) >= 65 AND DATEDIFF(yy, Clients1.DOB, @TodaysDate) <= 69 THEN 1 ELSE 0 END) As 'allInAgeGroup65-69'
,SUM(CASE WHEN DATEDIFF(yy, Clients1.DOB, @TodaysDate) >= 70 AND DATEDIFF(yy, Clients1.DOB, @TodaysDate) <= 74 THEN 1 ELSE 0 END) As 'allInAgeGroup70-74'
,SUM(CASE WHEN DATEDIFF(yy, Clients1.DOB, @TodaysDate) >= 75 AND DATEDIFF(yy, Clients1.DOB, @TodaysDate) <= 79 THEN 1 ELSE 0 END) As 'allInAgeGroup75-79'
,SUM(CASE WHEN DATEDIFF(yy, Clients1.DOB, @TodaysDate) >= 80 AND DATEDIFF(yy, Clients1.DOB, @TodaysDate) <= 84 THEN 1 ELSE 0 END) As 'allInAgeGroup80-84'
,SUM(CASE WHEN DATEDIFF(yy, Clients1.DOB, @TodaysDate) >= 85 AND DATEDIFF(yy, Clients1.DOB, @TodaysDate) <= 89 THEN 1 ELSE 0 END) As 'allInAgeGroup85-89'
,SUM(CASE WHEN DATEDIFF(yy, Clients1.DOB, @TodaysDate) >= 90 AND DATEDIFF(yy, Clients1.DOB, @TodaysDate) <= 94 THEN 1 ELSE 0 END) As 'allInAgeGroup90-94'
,SUM(CASE WHEN DATEDIFF(yy, Clients1.DOB, @TodaysDate) >= 95 THEN 1 ELSE 0 END) As 'allInAgeGroup95+'
, Referrals1.Site_Code, Referrals1.Site_Code_Description, DATEDIFF(dd, Referrals1.Client_Application_Date, Billing_PSI_Line_Items1.Visit_Date) As 1stHmVisitTime
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
INNER JOIN (
SELECT AdHoc_Referrals_2.Referral_Id, MAX(AdHoc_Billing_PSI_Line_Items_1.Visit_Date) AS MaxVisitDate
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 (Referrals2.Client_Application_Date BETWEEN @Start_Date AND @End_Date)
GROUP BY AdHoc_Referrals_2.Referral_Id
HAVING MAX(AdHoc_Billing_PSI_Line_Items_1.Visit_Date) IS NOT NULL
) AS WhateverTable
ON Referrals1.Referral_Id = WhateverTable.Referral_Id
WHERE (Referrals1.Client_Application_Date BETWEEN @Start_Date AND @End_Date)
-- AND (DATEDIFF(dd, Referrals1.Client_Application_Date, Billing_PSI_Line_Items1.Visit_Date) <= 3)
-- AND (104/*Referrals.Site_Code*/ = Referrals1.Site_Code)
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)
Thanks,
Chad
October 20, 2010 at 10:41 am
I was thinking along the lines of a view (since it sounds like you have a DBA to talk to) to have the max date availble.
Without the structure, it's a bit hard to tell how the LineItems top 1 fits in for sure.
Maybe it's just me - looking at table structure makes it a lot easier to visualize the relationships than picking it out of the sql.
Greg E
October 21, 2010 at 7:42 am
my final result.
DECLARE @Start_Date As Datetime
DECLARE @End_Date As Datetime
DECLARE @TodaysDate As Datetime
SET @Start_Date = '04/01/2010'
SET @End_Date = '07/01/2010'
SET @TodaysDate = GETDATE()
SELECT COUNT(Clients.Client_Number) As Totals
,SUM(CASE WHEN DATEDIFF(yy, Clients.DOB, @TodaysDate) <= 18 THEN 1 ELSE 0 END) As 'allInAgeGroup0_18'
,SUM(CASE WHEN DATEDIFF(yy, Clients.DOB, @TodaysDate) >= 19 AND DATEDIFF(yy, Clients.DOB, @TodaysDate) <= 24 THEN 1 ELSE 0 END) As 'allInAgeGroup19-24'
,SUM(CASE WHEN DATEDIFF(yy, Clients.DOB, @TodaysDate) >= 25 AND DATEDIFF(yy, Clients.DOB, @TodaysDate) <= 29 THEN 1 ELSE 0 END) As 'allInAgeGroup25-29'
,SUM(CASE WHEN DATEDIFF(yy, Clients.DOB, @TodaysDate) >= 30 AND DATEDIFF(yy, Clients.DOB, @TodaysDate) <= 34 THEN 1 ELSE 0 END) As 'allInAgeGroup30-34'
,SUM(CASE WHEN DATEDIFF(yy, Clients.DOB, @TodaysDate) >= 35 AND DATEDIFF(yy, Clients.DOB, @TodaysDate) <= 39 THEN 1 ELSE 0 END) As 'allInAgeGroup35-39'
,SUM(CASE WHEN DATEDIFF(yy, Clients.DOB, @TodaysDate) >= 40 AND DATEDIFF(yy, Clients.DOB, @TodaysDate) <= 44 THEN 1 ELSE 0 END) As 'allInAgeGroup40-44'
,SUM(CASE WHEN DATEDIFF(yy, Clients.DOB, @TodaysDate) >= 45 AND DATEDIFF(yy, Clients.DOB, @TodaysDate) <= 49 THEN 1 ELSE 0 END) As 'allInAgeGroup45-49'
,SUM(CASE WHEN DATEDIFF(yy, Clients.DOB, @TodaysDate) >= 50 AND DATEDIFF(yy, Clients.DOB, @TodaysDate) <= 54 THEN 1 ELSE 0 END) As 'allInAgeGroup50-54'
,SUM(CASE WHEN DATEDIFF(yy, Clients.DOB, @TodaysDate) >= 55 AND DATEDIFF(yy, Clients.DOB, @TodaysDate) <= 59 THEN 1 ELSE 0 END) As 'allInAgeGroup55-59'
,SUM(CASE WHEN DATEDIFF(yy, Clients.DOB, @TodaysDate) >= 60 AND DATEDIFF(yy, Clients.DOB, @TodaysDate) <= 64 THEN 1 ELSE 0 END) As 'allInAgeGroup60-64'
,SUM(CASE WHEN DATEDIFF(yy, Clients.DOB, @TodaysDate) >= 65 AND DATEDIFF(yy, Clients.DOB, @TodaysDate) <= 69 THEN 1 ELSE 0 END) As 'allInAgeGroup65-69'
,SUM(CASE WHEN DATEDIFF(yy, Clients.DOB, @TodaysDate) >= 70 AND DATEDIFF(yy, Clients.DOB, @TodaysDate) <= 74 THEN 1 ELSE 0 END) As 'allInAgeGroup70-74'
,SUM(CASE WHEN DATEDIFF(yy, Clients.DOB, @TodaysDate) >= 75 AND DATEDIFF(yy, Clients.DOB, @TodaysDate) <= 79 THEN 1 ELSE 0 END) As 'allInAgeGroup75-79'
,SUM(CASE WHEN DATEDIFF(yy, Clients.DOB, @TodaysDate) >= 80 AND DATEDIFF(yy, Clients.DOB, @TodaysDate) <= 84 THEN 1 ELSE 0 END) As 'allInAgeGroup80-84'
,SUM(CASE WHEN DATEDIFF(yy, Clients.DOB, @TodaysDate) >= 85 AND DATEDIFF(yy, Clients.DOB, @TodaysDate) <= 89 THEN 1 ELSE 0 END) As 'allInAgeGroup85-89'
,SUM(CASE WHEN DATEDIFF(yy, Clients.DOB, @TodaysDate) >= 90 AND DATEDIFF(yy, Clients.DOB, @TodaysDate) <= 94 THEN 1 ELSE 0 END) As 'allInAgeGroup90-94'
,SUM(CASE WHEN DATEDIFF(yy, Clients.DOB, @TodaysDate) >= 95 THEN 1 ELSE 0 END) As 'allInAgeGroup95plus'
, Ref.Site_Code, Ref.Site_Code_Description, DATEDIFF(dd, Ref.Client_Application_Date, /*Billing_PSI_Line_Items.Visit_Date*/Bill2.First_Visit) As '1stHmVisitTime'
--, Ref.Referral_Id, Ref.Referral_Start_Date, Bill2.First_Visit
FROM dbo.AdHoc_Referrals AS Ref INNER JOIN
dbo.AdHoc_Clients AS Clients ON Ref.Client_Id = Clients.Client_Id
INNER JOIN dbo.AdHoc_Service_Recipient_Coding AS Code ON Ref.Referral_Id = Code.Referral_Id
LEFT OUTER JOIN
(
SELECT Bill1.Referral_Id, MIN(Bill1.Visit_Date) AS First_Visit
From dbo.AdHoc_Billing_PSI_Line_Items AS Bill1
Group By Bill1.Referral_Id
) AS Bill2 ON Ref.Referral_Id = Bill2.Referral_Id
WHERE ref.Referral_Start_Date >= (@Start_Date)
AND ref.Referral_Start_Date <= (@End_Date)
AND Code.Service_Recipient_Code = '91'
GROUP BY Ref.Site_Code, Ref.Site_Code_Description, DATEDIFF(dd, Ref.Client_Application_Date, Bill2.First_Visit)
It's returning in a nice 5 seconds this am. I don't have an exact number of records but I'm guessing at least a couple hundred thousand.
Now, off to using it in SSRS. 🙂
October 21, 2010 at 7:43 am
BTW, thank you very much to everyone.
October 21, 2010 at 7:57 am
I don't know if it makes sens for your data, but I usually copy the begin and end date filters in the derived tables as well when I can.
This usually gives another good boost by reducing data in the join as well as giving a chance for an index [range] seek in the derived table select.
Even if you cannot use both begin and end date, maybe you can use at least one of them.
October 21, 2010 at 9:43 am
Rather than trying to muddle around with Air Code on a query like that... where the results of the Air Code is going to cause you more headaches than you original planned on...
Could you script us the tables in question?
October 21, 2010 at 9:53 am
Congrats Bill!
Now, since it's all over there is only one question. Did you think that was fun?
If so, welcome to the club, glad to have you. If not... oh well, still glad to have you 😀
Chad
October 21, 2010 at 8:11 pm
chuckneerg (10/21/2010)
Rather than trying to muddle around with Air Code on a query like that... where the results of the Air Code is going to cause you more headaches than you original planned on...Could you script us the tables in question?
Heh... ok... what's "Air Code"?
--Jeff Moden
Change is inevitable... Change for the better is not.
October 22, 2010 at 5:13 am
It's like air guitar... it's pretend code rather than actual table scripts and actual data which gives you the final and tested query.
October 22, 2010 at 6:43 am
It is fun Chad 😀
I'm thinking I shouldn't post the table structures or anything like that because of our privacy and security restrictions. I may have even stepped over the line with the code I posted. 🙁
Sorry.
October 22, 2010 at 8:14 am
Bill,
You can't get good help without posting sample data, DDLs and code. The way to overset the privacy restrictions is to basically scrub your data and code. Make it generic object names and use Mickey Mouse instead of Dalia Foster. Stuff like that. I have to do it every time for the same reason.
You can still post and still get help, even using generic names. But not posting at all guarantees you won't get anything useful out of the forum.
October 22, 2010 at 1:48 pm
Bill_CCAC (10/22/2010)
It is fun Chad 😀I'm thinking I shouldn't post the table structures or anything like that because of our privacy and security restrictions. I may have even stepped over the line with the code I posted. 🙁
Sorry.
I agree. Besides, at this point it would be a bit like closing the door after the horse got out.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 22, 2010 at 6:59 pm
Just to add to the assistance.
Please read Lynn Pettis article on calculating a person's age at http://www.sqlservercentral.com/articles/T-SQL/63351/
The correct calculation is
DATEDIFF(yy,Clients.DOB,@TodaysDate)
- case when @TodaysDate < dateadd(yy,datediff(yy,Clients.DOB,@TodaysDate),Clients.DOB)
then 1 else 0 end
When a calculation is repeated, such as the 32 places where there is an Age Calculation, create a nested query that performs the calculation once.
As a personal preference, because I think it is clearer that "value >= x and value <= Y" should be replace with "Value between X and Y". Once upon a time, the query optimizer could not determine that these are equivalant statments.
Lastly, when you set the value of the variable dates, you are assumming that the connection is using a date format of Month-Day-Year, which may not be a correct assumption. If you use the ISO format of 'YYYYMMDD', then any date format or language settings will not affect the conversion.
DECLARE @Start_Date As Datetime
DECLARE @End_Date As Datetime
DECLARE @TodaysDate As Datetime
SET @Start_Date = '20100401'
SET @End_Date = '20100701'
SET @TodaysDate = GETDATE()
SELECTSite_Code
,Site_Code_Description
,Referral_to_FirstVisit_Days
,COUNT(Clients.Client_Number) As Totals
,SUM(CASE WHEN ClientAgeYears between 00 AND 18 THEN 1 ELSE 0 END) As 'allInAgeGroup0_18'
,SUM(CASE WHEN ClientAgeYears between 19 AND 24 THEN 1 ELSE 0 END) As 'allInAgeGroup19-24' ,SUM(CASE WHEN ClientAgeYears between 25 AND 29 THEN 1 ELSE 0 END) As 'allInAgeGroup25-29'
,SUM(CASE WHEN ClientAgeYears between 30 AND 34 THEN 1 ELSE 0 END) As 'allInAgeGroup30-34'
,SUM(CASE WHEN ClientAgeYears between 35 AND 39 THEN 1 ELSE 0 END) As 'allInAgeGroup35-39'
,SUM(CASE WHEN ClientAgeYears between 40 AND 44 THEN 1 ELSE 0 END) As 'allInAgeGroup40-44'
,SUM(CASE WHEN ClientAgeYears between 45 AND 49 THEN 1 ELSE 0 END) As 'allInAgeGroup45-49'
,SUM(CASE WHEN ClientAgeYears between 50 AND 54 THEN 1 ELSE 0 END) As 'allInAgeGroup50-54'
,SUM(CASE WHEN ClientAgeYears between 55 AND 59 THEN 1 ELSE 0 END) As 'allInAgeGroup55-59'
,SUM(CASE WHEN ClientAgeYears between 60 AND 64 THEN 1 ELSE 0 END) As 'allInAgeGroup60-64'
,SUM(CASE WHEN ClientAgeYears between 65 AND 69 THEN 1 ELSE 0 END) As 'allInAgeGroup65-69'
,SUM(CASE WHEN ClientAgeYears between 70 AND 74 THEN 1 ELSE 0 END) As 'allInAgeGroup70-74'
,SUM(CASE WHEN ClientAgeYears between 75 AND 79 THEN 1 ELSE 0 END) As 'allInAgeGroup75-79'
,SUM(CASE WHEN ClientAgeYears between 80 AND 84 THEN 1 ELSE 0 END) As 'allInAgeGroup80-84'
,SUM(CASE WHEN ClientAgeYears between 85 AND 89 THEN 1 ELSE 0 END) As 'allInAgeGroup85-89'
,SUM(CASE WHEN ClientAgeYears between 90 AND 94 THEN 1 ELSE 0 END) As 'allInAgeGroup90-94'
,SUM(CASE WHEN ClientAgeYears between 95AND 999 THEN 1 ELSE 0 END) As 'allInAgeGroup95plus'
FROM(SELECTClients.Client_Number
,Referrals.Site_Code
,Referrals.Site_Code_Description
,Referrals.Client_Application_Date
,DATEDIFF(yy,Clients.DOB,@TodaysDate)
- case when @TodaysDate < dateadd(yy,datediff(yy,Clients.DOB,@TodaysDate),Clients.DOB) then 1 else 0 end
As ClientAgeYears
,DATEDIFF(dd, Referrals.Client_Application_Date, Bill2.First_Visit)as Referral_to_FirstVisit_Days
FROMdbo.AdHoc_Referralsas Referrals
JOINdbo.AdHoc_ClientsAS Clients
ON Referrals.Client_Id= Clients.Client_Id
JOINdbo.AdHoc_Service_Recipient_Coding AS Code
ON Referrals.Referral_Id= Code.Referral_Id
LEFT OUTER JOIN
(
SELECTBill1.Referral_Id
,MIN(Bill1.Visit_Date) AS First_Visit
Fromdbo.AdHoc_Billing_PSI_Line_Items AS Bill1
Group By Bill1.Referral_Id
) AS Bill2
ON Referrals.Referral_Id = Bill2.Referral_Id
WHEREReferrals.Referral_Start_Date between @Start_Date and @End_Date
ANDCode.Service_Recipient_Code = '91'
) As ClientReferals
GROUP BY Site_Code
, Site_Code_Description
, Referral_to_FirstVisit_Days
SQL = Scarcely Qualifies as a Language
Viewing 14 posts - 31 through 43 (of 43 total)
You must be logged in to reply to this topic. Login to reply