SQL DateDiff without enddate

2018-06-23 03:59:18

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

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.

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

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,
             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 SQL Server 2008 syntax for removing the time component. There are alternative methods, if you are using SQL Server 2005.

链接地址: http://www.djcxy.com/p/64960.html

上一篇: .Net SerialPort在可用字节数超过0.5秒后才会读取字节

下一篇: 没有结束日期的SQL DateDiff