Take the 2-minute tour ×

I'm using SQL Server I need to know the number of days that a patient was receiving a treatment. The problem is that I can only get a startDate but not an endDate When I run a query and order it by StartDate I get something like this:

2012-10-11 22:00:00.000
2012-10-11 23:10:31.000
2012-10-12 00:28:31.000
2012-10-12 01:39:01.000
2012-10-12 02:09:01.000
2012-10-12 03:39:01.000
2012-10-12 04:38:50.000
2012-10-20 06:00:00.000
2012-10-20 08:06:05.000
2012-10-20 10:21:55.000
2012-10-21 14:13:01.000
2012-10-21 15:13:01.000

The answer I should get is 4 days (Days 11, 12, 20 and 21) The treatment stopped on 2012-10-12 and a new treatment started on 2012-10-20 How can I sum up the days the patient was getting the treatment despite not having an endDate?

Thank you,


share|improve this question
The first thing you need to do is determine your criteria for continuous treatment. Until you can come up with an empirical rule that can determine what constitutes a new treatment in your return set, you won't get anywhere. For instance, what if you had a date of 2012-10-12 16:00. That's over 12 hours after the last entry before it, but does it mean a new treatment, or just a much larger gap than the norm. Ideally, you need another column that truly indicates what treatment the records corresponds to. –  Data Masseur Oct 23 '12 at 19:57

4 Answers 4

up vote 3 down vote accepted
SELECT COUNT(StartDate) AS treatmentDays
FROM ...
GROUP BY CAST(StartDate as date)

basically, convert the date/time values to just dates, group on that date value, then count how many there are. The grouping will collapse the repeated dates into a single one, so you should get 4 as the answer given your sample data.

share|improve this answer
Yes, that worked! I did: SELECT COUNT (StartDate) AS TREATMENTDAYS FROM( DATEADD(dd,0,DATEDIFF(dd,0,StartDate])) StartDate WHERE... )AS MyTable GroupBy StartDate Thanks! –  loperam Oct 23 '12 at 20:11

try this

You need to get DISTINCT date count

) A
share|improve this answer
This doesn't seem right. Why is GETDATE() in there? The current date is irrelevant. –  voithos Oct 23 '12 at 20:09
@voithos - my bad. thanks for noticing :) –  Parag Meshram Oct 23 '12 at 20:11

What you seem to be looking for are sequences of consecutive days. Assuming you are using SQL Server 2005 or later, you can take the following approach. For each patient, extract only the days in the sequence. Then enumerate the days. The difference between these will be constant for a "treatment".

Here is an example:

select patientId, seqStartDate, count(*) as NumDays, sum(NumOnDay) as NumRecs,
       dateadd(day, count(*) - 1, seqStartDate) as SeqEndDate
from (select dp.*,
             row_number() over (partition by patientId order by StartDate) as seqnum,
                     - row_number() over (partition by patientId order by StartDate)
                     ) as seqStartDate
      from (select cast(StartDate as date) as thedate, patientId, count(*) as NumOnDay
            from table
            group by cast(StartDate as date), patientId
           ) dp
     ) dp
group by patientId, seqStartDate
order by 1, 2

Actually, this syntax also uses cast(<val> as date) which is the SQL Server 2008 syntax for removing the time component. There are alternative methods, if you are using SQL Server 2005.

share|improve this answer

If you just want to get the total number of unique days that exist in StartDate, you can use this:

SELECT COUNT(IndividualDays)
    (SELECT DISTINCT CONVERT(VARCHAR(10), StartDate, 111) AS IndividualDays
     FROM TreatmentTable) AS A

All this does is convert the datetime values into just their date parts, and drops duplicates. After that is done, the number of rows returned is the same as the number of unique days, so it just gets a count.

share|improve this answer

Your Answer


