May 23, 2008 at 3:04 am
Hi,
Please have a look at query below.
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.sex FROM
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 23, 2008 at 4:04 am
could you able to provide some Table declaration along with sample datas and expected Results
It will be better to help you
Rajesh
May 23, 2008 at 4:17 am
Hi,
Basically when there is select statement of average for admdat and disdat i need to specify some condition in where clause of that select statmenet.
table is simple but very big so i cant really provide. And OP1 is operation code. disdat, admdat and epiend is all date in proper format. U can ignore where clause of TCI date and INT_STAY.
I wrote query below which should do what i want but it is problem as when i wrote another Left join and wrote select statement, it returns multiple value which is not acceptable in subquery.
i just need to solve that.
SELECT A.Unitno, A.surname, A.forename1, A.sex, A.DOB, A.Age, A.OP1, A.TCIdate, B.LOS, A.INT_STAY
FROM Vijay A
LEFT OUTER JOIN (
SELECT avg(datediff(day, Big.admdat, Big.disdat)) as LOS, Big.OP1, Big.sex
FROM Big Big
LEFT OUTER JOIN (
Select avg(datediff(day, Bi.admdat, Bi.disdat)) as LOS, Bi.OP1, Bi.Sex
FROM Big Bi
where datediff(day, Bi.admdat, Bi.disdat) <
(select avg(datediff(day, BIP.admdat, BIP.disdat)) from Big BIP
where BIP.epiend > dateadd(yy,-3,getdate()) AND BIP.EP_TYPE = 'L'
Group by BIP.OP1, BIP.Sex)
Group by Bi.OP1, Bi.Sex)C
ON
Big.OP1 = C.OP1
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.TCIDate between '20080521' AND '20080530'
AND A.INT_STAY <> 'D'
order by B.LOS
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply