SQL DateDiff without enddate

Question

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:

StartDate
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,

Loperam

Answer 1

SELECT COUNT(StartDate) AS treatmentDays
FROM ...
WHERE ...
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.

Answer 2

try this

You need to get DISTINCT date count

SELECT A.PATIENT_ID, COUNT(DISTINCT A.DATE)
FROM
(
    SELECT PATIENT_ID, CONVERT(VARCHAR(10), StartDate, 101) AS [DATE] --CONVERTS TO [MM/DD/YYYY] FORMAT
    FROM MY_TABLE
) A
GROUP BY A.PATIENT_ID