I am using SQL Server.

select DISTINCT caseNumber, dateStarted,dateStopped from patientView where dateStarted !='' and dateStopped != '';

We get the following output,

I want to calculate the duration for each caseNumber. For eg. caseNumber 1 has 2 rows and hence total duration would be 18days.

CodePudding user response：

I would suggest using the group by keyword to group redundant case numbers and take the min for the startdates and max for stopdates. You can do something like:

SELECT caseNumber, max(dateStopped)-min(dateStarted)
from patientView
where dateStarted != '' and dateStopped != ''
GROUP BY caseNumber;

CodePudding user response：

It is not clear whether you want the sum of the durations for individual patientView records or the duration from the earliest start to the latest end. It is also not clear whether the stop date is inclusive or exclusive. Is 2022-01-01 to 2022-01-04 considered 3 days or 4 days?

Here is code that shows 4 different calculations:

DECLARE @patientView  TABLE (CaseNumber INT, dateStarted DATETIME, dateStopped DATETIME)
INSERT @patientView
VALUES
    (1,  '2022-01-01 ', '2022-01-04'),
    (1,  '2022-01-05 ', '2022-01-19'),
    (2,  '2022-01-03 ', '2022-01-10'),
    (4,  '2022-01-05 ', '2022-01-11'),
    (4,  '2022-01-13 ', '2022-01-14'),
    (4,  '2022-01-21 ', '2022-01-23'),
    (5,  '2022-01-15 ', '2022-01-16'),
    (5,  '2022-01-17 ', '2022-01-24'),
    (5,  '2022-01-24 ', '2022-01-26'),
    (8,  '2022-01-17 ', '2022-01-20'),
    (8,  '2022-01-21 ', '2022-01-28'),
    (11, '2022-01-18 ', '2022-01-25'),
    (11, '2022-01-26 ', '2022-01-27')
    
SELECT
    CaseNumber,
    SumDaysExclusive = SUM(DATEDIFF(day, dateStarted, dateStopped)),
    SumDaysInclusive = SUM(DATEDIFF(day, dateStarted, dateStopped)   1),
    RangeDaysExclusive = DATEDIFF(day, MIN(dateStarted), MAX(dateStopped)),
    RangeDaysInclusive = DATEDIFF(day, MIN(dateStarted), MAX(dateStopped))   1  
FROM @patientView
GROUP BY CaseNumber
ORDER BY CaseNumber

Results:

db<>fiddle

The test data above uses DATETIME types. (DATE would also work.) If you have dates stored as character data (not a good practice), you may need to add CAST or CONVERT statements.