SQL DateDiff without enddate

Question

I am using SQL Server. I need to know the number of days during which the patient received treatment. The problem is that I can only get startDate, but not endDate. When I run the request and order it using 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 in 2012-10-12, and the new treatment started in 2012-10-20. How can I summarize the days when the patient received treatment despite lack of endDate?

Thanks,

Loperam

Answer 1

 SELECT COUNT(StartDate) AS treatmentDays FROM ... WHERE ... GROUP BY CAST(StartDate as date) 

basically, convert date / time values ​​only to dates, group by date value, and then calculate how many are left. Grouping will crash duplicate dates into one, so you should get 4 as the answer provided by your sample data.

Answer 2

try it

You need to get DISTINCT number of days

 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 

Answer 3

What you seem to be looking for are sequences of consecutive days. Assuming you are using SQL Server 2005 or later, you can use the following approach. For each patient, retrieve only days in a sequence. Then list the days. The difference between them will be constant for the "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, dateadd(day, - row_number() over (partition by patientId order by StartDate) 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 syntax of SQL Server 2008 to remove the time component. Alternative methods exist if you are using SQL Server 2005.

Answer 4

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

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

All this means that datetime values ​​are converted only to their part of the date and discard duplicates. After that, the number of returned rows will be the same as the number of unique days, so it just gets the bill.