Error while getting values between dates

Question

I am trying to get a chart date vs number of patients .I am trying to make a chart using MS Chart in asp.net where I get date on x axis and number of patients on y axis. I am accepting from date and to date as input. I want number of patients between these 2 dates on each date between from and to date. I am trying something as follows

select 
convert(varchar,creation_Date,105) as 'creation_Date',
count(Pat_ID)
FROM Patient_Ref_master
where         
 (CONVERT(VARCHAR(10),Patient_Ref_master.creation_Date,111)  BETWEEN '2013/07/23' AND '2013/07/25')

group by Pat_ID

but giving me an error of

Msg 8120, Level 16, State 1, Line 1
Column 'Patient_Ref_master.creation_Date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Where I am going wrong.? Please suggest. Thanks

Why, why, why are you converting to a string

Aaron Bertrand
– Aaron Bertrand

2013-07-25 19:03:05 +00:00
Commented Jul 25, 2013 at 19:03 — Aaron Bertrand
– Aaron Bertrand, Commented Jul 25, 2013 at 19:03

McAden · Accepted Answer · 2013-07-25 18:53:52Z

3

Group by creation_Date instead of Pat_ID

answered Jul 25, 2013 at 18:53

McAden

14k5 gold badges39 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sonam · Accepted Answer · 2013-07-25 18:51:55Z

1

You should also have Creation_Date in the group By clause. Following is the correct query:

    select 
convert(varchar,creation_Date,105) as 'creation_Date',
count(Pat_ID)
FROM Patient_Ref_master
where         
 (CONVERT(VARCHAR(10),Patient_Ref_master.creation_Date,111)  BETWEEN '2013/07/23' AND '2013/07/25')

group by creation_Date

answered Jul 25, 2013 at 18:51

Sonam

3,4861 gold badge14 silver badges25 bronze badges

2 Comments

Ankur Over a year ago

too good Sonam...It worked. Can you please tell me where I was going wrong.?

Sonam Over a year ago

Instead of Pat_Id, you should have Creation_Date in the group by clause.

Gordon Linoff · Accepted Answer · 2013-07-25 18:55:48Z

1

You want to count patients on a date, so the date needs to be in the group by clause. However, you need to be sure it really is a date and not a datetime for what you are doing. The following uses your preferred format:

select convert(varchar,creation_Date, 105) as 'creation_Date',
       count(Pat_ID) as Numpateitns
FROM Patient_Ref_master
where CONVERT(VARCHAR(10),Patient_Ref_master.creation_Date, 111)  BETWEEN '2013/07/23' AND '2013/07/25'
group by convert(varchar,creation_Date, 105)

answered Jul 25, 2013 at 18:55

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

Bill Gregg · Accepted Answer · 2013-07-25 18:55:50Z

1

You wouldn't group by Pat_ID, you want to group by creation_Date, or your expression that is creation_Date. Also, I wouldn't convert your dates to varchars in your where clause. Just keep them as date times.

select 
convert(varchar,creation_Date,105) as 'creation_Date',
count(Pat_ID)
FROM Patient_Ref_master
where Patient_Ref_master.creation_Date >= convert(datetime,'2013/07/23') 
AND Patient_Ref_master.creation_Date < dateadd(day,1,convert(datetime,'2013/07/25'))
group by convert(varchar,creation_Date,105)

answered Jul 25, 2013 at 18:55

Bill Gregg

7,1572 gold badges24 silver badges40 bronze badges

Collectives™ on Stack Overflow

Error while getting values between dates

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related