SELECT Count(1) AS Expr1
FROM (select distinct PatientsID, iif(datediff('d', End, nz(next_service,End))>30, 1, 0) as MoreThan30 from (
SELECT P1.PatientsID, P1.EffectiveFrom, P1.End, (select min(EffectiveFrom) from TmpPatBillingCodes as P2 where P2.PatientsID=P1.PatientsID and P2.EffectiveFrom > P1.End) AS next_service
FROM TmpPatBillingCodes AS P1 INNER JOIN Patients3 ON P1.PatientsID = Patients3.ID
WHERE (((Patients3.FacilityID)<>6488) AND ((P1.EffectiveFrom)<#1/1/2014#) AND ((P1.End)>=#1/1/2013#))
) as x
) AS y;
Can someone help me with this?
SELECT PatientType,Count(1) AS Expr1
FROM ( select distinct PatientsID, iif(datediff('d', End, nz(next_service,End))>30, 1, 0) as MoreThan30, PatientType
from (
SELECT P1.PatientsID, P1.EffectiveFrom, P1.End,
(select min(EffectiveFrom)
from TmpPatBillingCodes as P2
where P2.PatientsID=P1.PatientsID
and P2.EffectiveFrom > P1.End) AS next_service,
P3.PatientType
FROM TmpPatBillingCodes AS P1
INNER JOIN Patients3 P3 ON P1.PatientsID = P3.ID
WHERE (((Patients3.FacilityID)<>6488) AND ((P1.EffectiveFrom)<#1/1/2014#) AND ((P1.End)>=#1/1/2013#))
) as x
) AS y;
GROUP BY PatientType;
ASKER
ASKER
SELECT Count(PatientsID) AS Counts
, PatientsID
FROM (select distinct
PatientsID
, PatientType
, iif(datediff('d', End, nz(next_service,End))>30, 1, 0) as MoreThan30
from (
SELECT P1.PatientsID
, P3.PatientType
, P1.EffectiveFrom
, P1.End
, (select min(EffectiveFrom)
from TmpPatBillingCodes as P2
where P2.PatientsID=P1.PatientsID
and P2.EffectiveFrom > P1.End) AS next_service
FROM TmpPatBillingCodes AS P1
INNER JOIN Patients3 P3
ON P1.PatientsID = P3.ID
WHERE (((Patients3.FacilityID)<>6488)
AND ((P1.EffectiveFrom)<#1/1/2014#)
AND ((P1.End)>=#1/1/2013#))
) as x
) AS y
group by PatientsID;
ASKER
ASKER
ASKER
ASKER
select distinct
PatientsID
, PatientType
, iif(datediff('d', End, nz(next_service,End))>30, 1, 0) as MoreThan30
from (
SELECT P1.PatientsID
, P3.PatientType
, P1.EffectiveFrom
, P1.End
, (select min(EffectiveFrom)
from TmpPatBillingCodes as P2
where P2.PatientsID=P1.PatientsID
and P2.EffectiveFrom > P1.End) AS next_service
FROM TmpPatBillingCodes AS P1
INNER JOIN Patients3 P3
ON P1.PatientsID = P3.ID
WHERE (((P3.FacilityID)<>6488)
AND ((P1.EffectiveFrom)<#1/1/2014#)
AND ((P1.End)>=#1/1/2013#))
) as x
ASKER
select distinct
, PatientsID
, iif(datediff('d', End, nz(next_service,End))>30, 1, 0) as MoreThan30
from (SELECT P1.PatientsID
, P1.EffectiveFrom
, P1.End
, (select min(EffectiveFrom)
from TmpPatBillingCodes as P2
where P2.PatientsID=P1.PatientsID
and P2.EffectiveFrom > P1.End) AS next_service
FROM TmpPatBillingCodes AS P1
INNER JOIN Patients3 P3
ON P1.PatientsID = P3.ID
WHERE (((Patients3.FacilityID)<>6488)
AND ((P1.EffectiveFrom)<#1/1/2014#)
AND ((P1.End)>=#1/1/2013#))
) as x
ASKER
SELECT Count(1) AS Expr1
FROM (
) as x
) AS y;
To get the same thing.
ASKER
ASKER
ASKER
AND ((P1.EffectiveFrom)<#1/1/2014#) AND ((P1.End)>=#1/1/2013#))
according to this #6509 should not be included.ASKER
Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.
TRUSTED BY
Open in new window
Took the liberty of changing your formatting a bit to make it more readable.