May 23, 2008 at 3:01 am
Hi,
I need some help to fix this query. Here when i create join, i would like to select only those records where LOS is greater than average of datediff(day, big.admdat, big.disdat).
In short in where clause of left outer join query. i need to specify that where datediff(day, big.admdat, big.disdat) < avg(datediff(day, big.admdat, big.disdat)....now i cant use average function in where clause easily...i need to specify OP1 as well like i did in B....
can some one help me with this please??? here is a query....
SELECT
A.Unitno, A.surname, A.forename1, A.sex, A.DOB, A.Age, A.OP1, A.TCIdate, B.LOS, A.INT_STAY
FROM WLDBF_Test A
LEFT OUTER JOIN (
SELECT
avg(datediff(day, Big.admdat, Big.disdat)) as LOS, Big.OP1, Big.sexFROM
BigIPYear Big
WHERE
Big.epiend > dateadd(yy,-2,getdate())
AND
Big.EP_TYPE = 'L'
GROUP BY
Big.OP1, Big.sex
) B
ON
A.OP1 = B.OP1
AND
A.sex = B.sex
where
A.TCIDates between @startdate AND @enddate
AND
A.INT_STAY <> 'V'
May 30, 2008 at 2:41 am
Please, post DDL and sample data - a script that we can use to reproduce the tables and the data, and also provide a sample of expected results.
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com
May 30, 2008 at 9:50 am
without full details it is hard to suggest a solution, but you should be able to use the HAVING clause to have aggregate functions behave like a where clause. Check BOL for syntax
as suggested post more details for more help..
May 30, 2008 at 10:04 am
You may also try windowing the AVG function, so you have access to your average without the GROUP BY. Performance doesn't seem so great on those with big groups, so you may just need to go for sub-queries instead.
You would help your performance a LOT if you made that datediff() a calculated, persisted column in your table.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply