sql : How to count two rows with the same DATE and TIME as 1 row

Question

ID       |         Date      |    Status      |      Physician ID

44397479   10/13/2009 17:40     NOSHOW
44397480   10/13/2009 16:40     CHECKED OUT            108 
44397481   10/13/2009 14:32     CHECKED OUT            108
44397482   10/14/2009 10:44     RESCHEDULED            108
44397483    8/26/2009 12:35     RESCHEDULED            108
44397484   10/13/2009 15:26     CHECKED OUT            108
56084463   10/10/2009 10:00     PENDING                108
56287658   10/14/2009 10:44     PENDING                108
54728032   10/13/2009 15:56     CHECKED OUT            108

This is a sample from a table contains millions of records.

I need to write a query that will count any two rows that:

1. Contain the same Time (only time, not date)
2. The status of one of them is "RESCHEDULED" and the other is "PENDING"

will count the two rows as one row only (in the SUM function) for each Physician.

I tried to write it but I couldn't - can you help me please?

Answer 1

This should help you in the right direction. (Full example). Now you should be able to group by what you require, and sum the Dups field.

DECLARE @Table TABLE(
        ID INT,
        [Date] DATETIME,
        [Status] VARCHAR(100),
        [Physician ID] INT
)

INSERT INTO @Table SELECT 44397479,'10/13/2009 17:40','NOSHOW' , NULL
INSERT INTO @Table SELECT 44397480,'10/13/2009 16:40','CHECKED OUT' ,108  
INSERT INTO @Table SELECT 44397481,'10/13/2009 14:32','CHECKED OUT' ,108 
INSERT INTO @Table SELECT 44397482,'10/14/2009 10:44','RESCHEDULED' ,108 
INSERT INTO @Table SELECT 44397483,'8/26/2009 12:35','RESCHEDULED' ,108 
INSERT INTO @Table SELECT 44397484,'10/13/2009 15:26','CHECKED OUT' ,108 
INSERT INTO @Table SELECT 56084463,'10/10/2009 10:00','PENDING' ,108 
INSERT INTO @Table SELECT 56287658,'10/14/2009 10:44','PENDING' ,108 
INSERT INTO @Table SELECT 54728032,'10/13/2009 15:56','CHECKED OUT' ,108 


SELECT  *,
        CASE 
            WHEN    t.[Status] = 'PENDING' 
                AND EXISTS(
                        SELECT  * 
                        FROM    @Table 
                        WHERE   DATEPART(hh, [Date]) = DATEPART(hh, t.[Date]) 
                        AND     DATEPART(MI, [Date]) = DATEPART(MI, t.[Date]) 
                        AND     ID <> t.ID) 
                        AND     [Physician ID] = t.[Physician ID]
                        AND     Status = 'RESCHEDULED'
                THEN 0 
            ELSE 1 
        END Dups
FROM    @Table t

Answer 2

select t1.[Physician ID], count(*) as Count
from @Table t1
inner join @Table t2 on (
    t1.date - cast(t1.date as int) = t2.date - cast(t2.date as int)
    and t1.Status = 'RESCHEDULED' and t2.Status = 'PENDING'
    and t1.[Physician ID] = t2.[Physician ID]
)
group by t1.[Physician ID]