CODEWITHSUNDEEP
sqlsql-server
aparna rai
aparna rai

Reputation: 833

Count of Date in SQL Server

I have a table (SQL Server)

      Date     |    Time          |  Id      |  Patient
   -------------------------------------------------------
    2018-06-19 | 18:02:49.0000000 | 1800257  |  Patient1
    2018-06-19 | 18:04:24.0000000 | 1800258  |  Patient2
    2018-06-19 | 18:35:35.0000000 | 1800259  |  Patient3

How to get Count of Same Date with in Same Query

I have to try this 

select COUNT(con.AppDate) as Appointment,con.AppDate as Date 
from Consultation as con 
inner join DoctorMaster as doc on con.DrCode=doc.id 
where con.DrCode='2'      
GROUP BY CAST(AppDate AS DATE),con.AppDate

Use this i am getting only count of date but i want one more column that is time which is use for scheduling

Expected Output:

 CountOfDate  |     Date     |     Time     |  Patient
----------------------------------------------------------
    3         |   2018-06-19 |   18:02:49   |  Patient1
    3         |   2018-06-19 |   19:10:10   |  Patient1
    3         |   2018-06-19 |   19:02:50   |  Patient1

How to show Count of date each row ?

Upvotes: 2

Views: 83

Answers (3)

D-Shih
D-Shih

Reputation: 46249

From your question and sample data, I guess you need to select CAST(con.AppDate AS DATE) instead of con.AppDate.

select COUNT(con.AppDate) as Appointment,CAST(con.AppDate AS DATE) as Date 
from  Consultation as con 
inner join DoctorMaster as doc on con.DrCode=doc.id   
where con.DrCode='2'

EDIT

I saw you edit your question

You can try to use COUNT with windows function, and CAST(t.Time AS time(0)) let time format to hh:mm:ss

SELECT  COUNT(*) OVER (PARTITION BY con.AppDate ORDER BY con.AppDate) CountOfDate,
        con.AppDate,
        CAST(con.AppTime AS time(0)) time,
        con.Patient
from Consultation as con inner join DoctorMaster as doc on con.DrCode=doc.id   
where con.DrCode='2'

[Results]:

| CountOfDate |    AppDate |     time |  Patient |
|-------------|------------|----------|----------|
|           3 | 2018-06-19 | 18:02:49 | Patient1 |
|           3 | 2018-06-19 | 18:04:24 | Patient2 |
|           3 | 2018-06-19 | 18:35:35 | Patient3 |

sqlfiddle:http://sqlfiddle.com/#!18/2feac/1

Upvotes: 1

Squirrel
Squirrel

Reputation: 24803

you can use window function COUNT() OVER () 

select COUNT(con.AppDate) OVER (PARTITION BY con.DrCode, con.AppDate) as Appointment,
       con.AppDate as Date,
       con.AppTime as Time 
from   Consultation as con 
       inner join DoctorMaster as doc 
       on con.DrCode = doc.id   
where  con.DrCode = '2'

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

As per sample data you seems want:

select *, count(*) over (partition by date) as CountOfDate, 
          min(Patient) over (partition by date) as Patient
from table t;

Upvotes: 3

Related Questions