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.
Butterfly2Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>SELECT cast(ch.pt_id_start_dtime as date)as admDate,
>GROUP BY ... ch.pt_Id_start_Dtime

Assuming this column is a datetime, if there is a time component to this data then you'll need to include the CAST as date in the GROUP BY clause.  Otherwise, it may be displaying only the day, but grouping by the day and time.
0
PortletPaulfreelancerCommented:
not only that..... but to reinforce it

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
0
Butterfly2Author Commented:
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 = '14)only, in other words, she was only the admitting physcian without being the attending physican.

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)

Open in new window

Here are my reults:

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

Open in new window

0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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 = '14) only, in other words, she was only the admitting physcian without being the attending physican.

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.
0
PortletPaulfreelancerCommented:
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.
0
Butterfly2Author Commented:
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.
0
PortletPaulfreelancerCommented:
You HAVE to get rid of the UNION... the results are misleading
0
PortletPaulfreelancerCommented:
try this, see if it helps identify the way forward:
SELECT
        prov_cd
      , CAST(ch.pt_id_start_dtime AS date) AS admDate
      , COUNT(DISTINCT CASE
                WHEN cp.prov_rel_to_pt = '13' THEN ch.episode_no
                END)                       AS PatCount_13
      , COUNT(DISTINCT CASE
                WHEN cp.prov_rel_to_pt = '14' THEN ch.episode_no
                END)                       AS PatCount_14
      , 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 (
             (ch.pt_id_start_dtime >= '2013-03-01' AND ch.pt_id_start_dtime < '2013-05-01') /* < the next day! */
            OR 
             (ch.pt_id_start_dtime >= '2013-07-01' AND ch.pt_id_start_dtime < '2013-09-01')  /* < the next day! */
            )
        AND cp.prov_rel_to_pt IN ('13', '14')
GROUP BY prov_cd
       , CAST(ch.pt_id_start_dtime AS date)
;

Open in new window

Please also pay attention to the way I filtered by date ranges.
"BETWEEN" is actually your enemy with date ranges.
the way you had it forces a 2 function calls on every row, this reduces efficiency.
All you have to get your head around is that you ask for data that is less then (the next day).

for more on this topic please see: "Beware of Between"

{+2 edits}
apologizes

The way you had your where clause structured may also have been wrong
one must be very careful with OR's
I think you need to wrap that second OR date range as you now see above.

AND (
           ( date range 1) OR (date range 2)
        )
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the split.  Good luck with your project.  -Jim
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.