Butterfly2
asked on
Date sort repeating its self in intstead of doing total count.
I have a query in which I results for how many patients a doctor saw on a given day.
My result set should be
Provid admDate Pat count
029025 2013-03-01 7
instead I get
Provid admDate Pat count
029025 2013-03-01 7
029025 2013-03-01 8
see my code below
Select distinct prov_cd,
cast(ch.pt_id_start_dtime as date)as admDate,
count(ch.episode_no) as PatCount
From smsmir.cen_hist as ch
Inner Join smsmir.mir_care_prov_hist cp
on ch.src_sys_id = cp.src_sys_id
and ch.orgz_cd = cp.orgz_cd
and ch.pt_id = cp.pt_id
and ch.episode_no = cp.episode_no
and ch.vst_id = cp.vst_id
and ch.vst_no = cp.vst_no
and ch.pt_id_start_dtime = cp.pt_id_start_dtime
and cp.prov_key <> ''
and cp.seq_no >= 0
where prov_cd= '029025'
and cast(ch.pt_id_start_dtime as date) = '2013-03-01' --and '20130430'
--or convert(varchar(10), ch.pt_id_start_dtime, 112) = '20130701' --and '20130831')
and cp.prov_rel_to_pt = '13'
Group by prov_cd,
ch.pt_id_start_dtime
Can somebody please help me out with this. Thanks.
My result set should be
Provid admDate Pat count
029025 2013-03-01 7
instead I get
Provid admDate Pat count
029025 2013-03-01 7
029025 2013-03-01 8
see my code below
Select distinct prov_cd,
cast(ch.pt_id_start_dtime as date)as admDate,
count(ch.episode_no) as PatCount
From smsmir.cen_hist as ch
Inner Join smsmir.mir_care_prov_hist cp
on ch.src_sys_id = cp.src_sys_id
and ch.orgz_cd = cp.orgz_cd
and ch.pt_id = cp.pt_id
and ch.episode_no = cp.episode_no
and ch.vst_id = cp.vst_id
and ch.vst_no = cp.vst_no
and ch.pt_id_start_dtime = cp.pt_id_start_dtime
and cp.prov_key <> ''
and cp.seq_no >= 0
where prov_cd= '029025'
and cast(ch.pt_id_start_dtime as date) = '2013-03-01' --and '20130430'
--or convert(varchar(10), ch.pt_id_start_dtime, 112) = '20130701' --and '20130831')
and cp.prov_rel_to_pt = '13'
Group by prov_cd,
ch.pt_id_start_dtime
Can somebody please help me out with this. Thanks.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
so this did solve the first part of my problem, even though I am still getting some records duplicating when I put in a date range. The above example was only one day. Also I need to find the records where the doctor was the attending physcian( cp.prov_rel_to_pt = '13')
but I also need to find the records where she was the admitting physican(cp.prov_rel_to_pt
Here is the code in its entirety, but its not correct:
but I also need to find the records where she was the admitting physican(cp.prov_rel_to_pt
Here is the code in its entirety, but its not correct:
Select prov_cd,
cast(ch.pt_id_start_dtime as date)as admDate,
count(distinct ch.episode_no) as PatCount
-- 'attending' as RelType
From smsmir.cen_hist as ch
Inner Join smsmir.mir_care_prov_hist cp
on ch.src_sys_id = cp.src_sys_id
and ch.orgz_cd = cp.orgz_cd
and ch.pt_id = cp.pt_id
and ch.episode_no = cp.episode_no
and ch.vst_id = cp.vst_id
and ch.vst_no = cp.vst_no
and ch.pt_id_start_dtime = cp.pt_id_start_dtime
and cp.prov_key <> ''
and cp.seq_no >= 0
where prov_cd= '029025'
and (cast(ch.pt_id_start_dtime as date) between '2013-03-01' and '2013-04-30'
or cast(ch.pt_id_start_dtime as date) between '2013-07-01' and '2013-08-31')
and cp.prov_rel_to_pt = '13'
Group by prov_cd,
cast(ch.pt_id_start_dtime as date)
----order by pt_id_start_date desc
union
Select prov_cd,
cast(ch.pt_id_start_dtime as date)as admDate,
count(distinct ch.episode_no) as PatCount
From smsmir.cen_hist as ch
Inner Join smsmir.mir_care_prov_hist cp
on ch.src_sys_id = cp.src_sys_id
and ch.orgz_cd = cp.orgz_cd
and ch.pt_id = cp.pt_id
and ch.episode_no = cp.episode_no
and ch.vst_id = cp.vst_id
and ch.vst_no = cp.vst_no
and ch.pt_id_start_dtime = cp.pt_id_start_dtime
and cp.prov_key <> ''
and cp.seq_no >= 0
where prov_cd= '029025'
and (cast(ch.pt_id_start_dtime as date) between '2013-03-01' and '2013-04-30'
or cast(ch.pt_id_start_dtime as date) between '2013-07-01' and '2013-08-31')
and cp.prov_rel_to_pt = '14'
Group by prov_cd,
cast(ch.pt_id_start_dtime as date)Provid admDate PatCnt
029025 2013-03-01 5
029025 2013-03-02 2
029025 2013-03-03 3
029025 2013-03-03 4
029025 2013-03-04 5
029025 2013-03-05 6
029025 2013-03-06 4
029025 2013-03-07 3
029025 2013-03-08 1
029025 2013-03-09 3
029025 2013-03-10 4
029025 2013-03-11 5
029025 2013-03-12 4
029025 2013-03-13 8
029025 2013-03-14 6
029025 2013-03-15 6
029025 2013-03-16 3
029025 2013-03-17 6
029025 2013-03-18 7
029025 2013-03-18 8
029025 2013-03-19 6
029025 2013-03-19 7
029025 2013-03-20 5
029025 2013-03-21 4
029025 2013-03-22 6
029025 2013-03-23 5
029025 2013-03-24 5
029025 2013-03-25 7
029025 2013-03-26 6
029025 2013-03-27 6
029025 2013-03-28 7
029025 2013-03-29 8
029025 2013-03-30 6
029025 2013-03-31 1
029025 2013-04-01 6
029025 2013-04-01 8
029025 2013-04-02 1
029025 2013-04-03 5
029025 2013-04-04 5
029025 2013-04-05 4
029025 2013-04-06 3
029025 2013-04-07 5
029025 2013-04-08 4
029025 2013-04-09 4
029025 2013-04-10 3
029025 2013-04-11 2
029025 2013-04-12 4
029025 2013-04-13 5
029025 2013-04-14 4
029025 2013-04-15 4
029025 2013-04-16 1
029025 2013-04-16 2
029025 2013-04-17 5
029025 2013-04-17 6
029025 2013-04-18 5
029025 2013-04-18 6
029025 2013-04-19 5
029025 2013-04-20 7
029025 2013-04-21 1
029025 2013-04-22 9
029025 2013-04-23 5
029025 2013-04-24 2
029025 2013-04-25 11
029025 2013-04-25 12
029025 2013-04-26 6
029025 2013-04-27 1
029025 2013-04-28 6
029025 2013-04-29 2
029025 2013-04-30 4
029025 2013-04-30 5
029025 2013-07-01 7
029025 2013-07-02 4
029025 2013-07-03 2
029025 2013-07-04 5
029025 2013-07-05 5
029025 2013-07-06 3
029025 2013-07-07 3
029025 2013-07-08 4
029025 2013-07-09 7
029025 2013-07-10 6
029025 2013-07-11 4
029025 2013-07-12 4
029025 2013-07-13 3
029025 2013-07-14 5
029025 2013-07-15 8
029025 2013-07-16 3
029025 2013-07-17 2
029025 2013-07-18 2
029025 2013-07-19 4
029025 2013-07-20 7
029025 2013-07-21 4
029025 2013-07-22 4
029025 2013-07-23 4
029025 2013-07-23 5
029025 2013-07-24 3
029025 2013-07-25 7
029025 2013-07-26 7
029025 2013-07-27 8
029025 2013-07-28 5
029025 2013-07-29 5
029025 2013-07-30 1
029025 2013-07-31 5
029025 2013-08-01 2
029025 2013-08-02 6
029025 2013-08-03 4
029025 2013-08-04 2
029025 2013-08-04 3
029025 2013-08-05 3
029025 2013-08-06 1
029025 2013-08-07 6
029025 2013-08-08 5
029025 2013-08-09 4
029025 2013-08-10 7
029025 2013-08-11 1
029025 2013-08-11 2
029025 2013-08-12 4
029025 2013-08-13 5
029025 2013-08-15 4
029025 2013-08-16 3
029025 2013-08-17 4
029025 2013-08-18 6
029025 2013-08-19 6
029025 2013-08-19 7
029025 2013-08-20 3
029025 2013-08-20 4
029025 2013-08-21 4
029025 2013-08-22 5
029025 2013-08-22 7
029025 2013-08-23 10
029025 2013-08-24 1
029025 2013-08-25 4
029025 2013-08-26 7
029025 2013-08-27 5
029025 2013-08-28 6
029025 2013-08-29 3
029025 2013-08-30 4
>so this did solve the first part of my problem,
Okay, so we've answered your original question.
>even though I am still getting some records duplicating when I put in a date range.
I'd verify that all of the JOIN columns between the two tables are correct, as if one is missing this could result in duplicate rows returned. You're on your own on this one.
>Also I need to find the records where the doctor was the attending physcian( cp.prov_rel_to_pt = '13') but I also need to find the records where she was the admitting physican(cp.prov_rel_to_pt
The above doesn't make sense, as if doctor id's are the same then it should be something like ... = 13 and ... <> 13, and perhaps different columns.
Okay, so we've answered your original question.
>even though I am still getting some records duplicating when I put in a date range.
I'd verify that all of the JOIN columns between the two tables are correct, as if one is missing this could result in duplicate rows returned. You're on your own on this one.
>Also I need to find the records where the doctor was the attending physcian( cp.prov_rel_to_pt = '13') but I also need to find the records where she was the admitting physican(cp.prov_rel_to_pt
The above doesn't make sense, as if doctor id's are the same then it should be something like ... = 13 and ... <> 13, and perhaps different columns.
UNION!
This is a "row operator" just like "distinct"
that means if the count is different, then the row is different.
{+edit}
in fact, union could be a mini-disaster here
if the counts from the upper and lower happen to be the same, then one row will simply disappear, and the count is therefore half of what it should be.
This is a "row operator" just like "distinct"
that means if the count is different, then the row is different.
{+edit}
in fact, union could be a mini-disaster here
if the counts from the upper and lower happen to be the same, then one row will simply disappear, and the count is therefore half of what it should be.
ASKER
the first solution did work, I am not sure how to clarify. with the <> to 13, that wont work for me because I dont want consults or referrals. If the doctor was the attending doctor they have a code of 13, if it ws they were the admitting then they get a code of 14. In most cases it is the same, if it is the same you get 2 records in the mir_care_prov_hist for the same patient, the only diff is the rel code,but I only need one record(attending only, not admitting). Now in same cases if the doctor just admitted the patient, you will only get 1 record in the table for the the patient and the doctor on that paticular day. so basically what I am asking, is how do I grab the patients for the doctor who admitted a patient, without being the attending physcian.
You HAVE to get rid of the UNION... the results are misleading
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Thanks for the split. Good luck with your project. -Jim
a.
include all the non-aggregated columns under the group by,
as you use them under the select (but without any aliases)
SELECT
prov_cd,
cast(ch.pt_id_start_dtime as date) as admDate,
...
GROUP BY
prov_cd,
cast(ch.pt_id_start_dtime as date)
b.
SELECT DISTINCT --<< distinct is 100% redundant when using group by
.....
GROUP BY
....
No points please