November 17, 2011 at 12:19 pm
Hello,
I was given the task to write a query to produce both Discharge Information and Incompletes. Below is the query:
SELECT
--cv.ClientDisplayName, cd.DocumentName, cv.DischargeDtm
dateadd(month,datediff(month,0,cv.dischargedtm),0)as Discharged,
ISNULL(count(cd.IsIncomplete),0) #incomplete
FROM CV3ClientVisit cv (nolock)
LEFT OUTER JOIN CV3ClientDocument cd (nolock) ON cv.GUID = cd.ClientVisitGUID
WHERE cv.TypeCode = 'inpatient'
and cd.documentname like '%patient profile'
and cd.isincomplete = 1
AND NOT EXISTS
(select *
from CV3ClientVisit cv1 (nolock)
left outer join CV3ClientDocument cd (nolock)
on cd.ClientVisitGUID = cv1.guid
where cd.documentname like '%patient profile'
and cd.isincomplete = 0
and cv.GUID = cv1.guid )
and cd.iscanceled = 0
and cv.visitstatus = 'dsc'
and cv.dischargedisposition not in ('expired','left ama')
and cv.admitdtm <= getdate()-1 /*used to eliminate patients admitted on current date. Nurses have 24 hours
post admission to complete the profiles */
and cv.currentlocation not like 'loa%' -- excludes the test unit
and cv.currentlocation not like 'outpatient%'
and dateadd(month,datediff(month,0,cv.dischargedtm),0) >= (select dateadd(mm,datediff(mm,0,@thisdate)-12,0))
GROUP BY dateadd(month,datediff(month,0,cv.dischargedtm),0)
Below is the result set
Discharged # of Complete
2010-11-01 00:00:00.000 7
2010-12-01 00:00:00.000 15
2011-01-01 00:00:00.000 11
2011-02-01 00:00:00.000 1
2011-03-01 00:00:00.000 1
2011-06-01 00:00:00.000 3
2011-07-01 00:00:00.000 3
2011-08-01 00:00:00.000 2
2011-09-01 00:00:00.000 2
2011-10-01 00:00:00.000 1
2011-11-01 00:00:00.000 7
My question is this, how can I produce results for April and May of 2011, even if the count is 0?
Thank you for your help!
November 17, 2011 at 12:24 pm
Your LEFT JOIN table has columns specified in the WHERE clause checking for specific values, thereby eliminating the not matched rows.
and cd.documentname like '%patient profile'
and cd.isincomplete = 1
If you place these clauses in the LEFT JOIN, you will include the not matched rows in the final result. Therefore, you should get the zero as expected.
November 17, 2011 at 12:34 pm
John Michael Robertson (11/17/2011)
Your LEFT JOIN table has columns specified in the WHERE clause checking for specific values, thereby eliminating the not matched rows.and cd.documentname like '%patient profile'
and cd.isincomplete = 1
If you place these clauses in the LEFT JOIN, you will include the not matched rows in the final result. Therefore, you should get the zero as expected.
Thanks John!
I removed the columns within the WHERE clause and moved them to the LEFT JOIN but I'm still receiving the same result set. Below is a copy of the modified code:
declare @thisdate datetime;
set @thisdate = getdate();
SELECT
--cv.ClientDisplayName, cd.DocumentName, cv.DischargeDtm
dateadd(month,datediff(month,0,cv.dischargedtm),0)as Discharged,
ISNULL(count(cd.IsIncomplete),0) #incomplete
FROM CV3ClientVisit cv (nolock)
LEFT OUTER JOIN CV3ClientDocument cd (nolock)
ON cv.GUID = cd.ClientVisitGUID
and cd.documentname like '%patient profile'
and cd.isincomplete = 1
WHERE cv.TypeCode = 'inpatient'
AND NOT EXISTS
(select *
from CV3ClientVisit cv1 (nolock)
left outer join CV3ClientDocument cd (nolock)
on cd.ClientVisitGUID = cv1.guid
where cd.documentname like '%patient profile'
and cd.isincomplete = 0
and cv.GUID = cv1.guid )
and cd.iscanceled = 0
and cv.visitstatus = 'dsc'
and cv.dischargedisposition not in ('expired','left ama')
and cv.admitdtm <= getdate()-1 /*used to eliminate patients admitted on current date. Nurses have 24 hours
post admission to complete the profiles */
and cv.currentlocation not like 'loa%' -- excludes the test unit
and cv.currentlocation not like 'outpatient%'
and dateadd(month,datediff(month,0,cv.dischargedtm),0) >= (select dateadd(mm,datediff(mm,0,@thisdate)-12,0))
GROUP BY dateadd(month,datediff(month,0,cv.dischargedtm),0)
November 17, 2011 at 12:47 pm
What exactly is the NOT EXISTS trying to accomplish? It appears the code is almost duplicated, even to the left join issue described before. I would suggest also using a different column alias in the NOT EXISTS for CV3ClientDocument, such as cd1.
November 17, 2011 at 12:59 pm
I reviewed the code a little closer this time. I went ahead and made a correction to the NOT EXISTS to utilize the LEFT JOIN by placing the referenced columns within the LEFT JOIN clause. Also, found another instance of the primary LEFT JOIN referenced in the where clause "and cd.iscanceled = 0". I moved that as well. Please try the code provided to see if it works for you. If not, we can try something else.
declare @thisdate datetime;
set @thisdate = getdate();
SELECT
--cv.ClientDisplayName, cd.DocumentName, cv.DischargeDtm
dateadd(month,datediff(month,0,cv.dischargedtm),0)as Discharged,
ISNULL(count(cd.IsIncomplete),0) #incomplete
FROM CV3ClientVisit cv (nolock)
LEFT OUTER JOIN CV3ClientDocument cd (nolock)
ON cv.GUID = cd.ClientVisitGUID
and cd.documentname like '%patient profile'
and cd.isincomplete = 1
and cd.iscanceled = 0
WHERE cv.TypeCode = 'inpatient'
AND NOT EXISTS (
select *
from CV3ClientVisit cv1 (nolock)
left outer join CV3ClientDocument cd (nolock)
on cd.ClientVisitGUID = cv1.guid
and cd.isincomplete = 0
and cd.documentname like '%patient profile'
where cv.GUID = cv1.guid
)
and cv.visitstatus = 'dsc'
and cv.dischargedisposition not in ('expired','left ama')
and cv.admitdtm <= getdate()-1 /*used to eliminate patients admitted on current date. Nurses have 24 hours
post admission to complete the profiles */
and cv.currentlocation not like 'loa%' -- excludes the test unit
and cv.currentlocation not like 'outpatient%'
and dateadd(month,datediff(month,0,cv.dischargedtm),0) >= (select dateadd(mm,datediff(mm,0,@thisdate)-12,0))
GROUP BY dateadd(month,datediff(month,0,cv.dischargedtm),0)
November 17, 2011 at 1:00 pm
After some tweaking with the subquery and limiting one of the criterias within the WHERE clause, I was able to get it to work!
Thank you John!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply