Calculate durations between dates

Question

Post reply

Calculate durations between dates

tanehome

Ten Centuries

Points: 1167

March 29, 2021 at 9:21 pm

#3863378

hi all,

I need some help as I have a table contains issue & reply dates and need to make query calculate some durations between those dates as shown below:

- elapsed days from issue date

- due date=issue date+14

- late days (count days more than due date) if not replayed

- overdue= (count days more than due date) if replied and reply date after due date

also why the empty date shows '1900-01-01' and how to avoid

appreciate your assistance.

below sample data table

CREATE TABLE [dbo].[dates](
[issuedate] [date] NULL,
[replydate] [date] NULL,

) ON [PRIMARY]

INSERT INTO [dates] (issuedate, replydate)   values ('2021-03-15','');
INSERT INTO [dates] (issuedate, replydate)   values ('2020-12-12','2020-12-20');
INSERT INTO [dates] (issuedate, replydate)   values ('2021-02-01','2021-02-20');
INSERT INTO [dates] (issuedate, replydate)   values ('2020-10-29','2020-12-08');
INSERT INTO [dates] (issuedate, replydate)   values ('2020-11-08','2020-12-08');
INSERT INTO [dates] (issuedate, replydate)   values ('2021-01-08','2021-01-18');
INSERT INTO [dates] (issuedate, replydate)   values ('2020-12-08','');
INSERT INTO [dates] (issuedate, replydate)   values ('2021-02-08','');
INSERT INTO [dates] (issuedate, replydate)   values ('2021-03-26','');
INSERT INTO [dates] (issuedate, replydate)   values ('2021-03-08','2021-03-22');

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101103 More actions · Answer 1

SELECT
    issuedate, replydate,
CASE WHEN replydate IS NULL AND DATEDIFF(DAY, duedate, GETDATE()) > 0 
         THEN DATEDIFF(DAY, duedate, GETDATE()) 
         ELSE NULL /*0*/ END AS [late days],
CASE WHEN replydate IS NOT NULL AND replydate > duedate AND
          DATEDIFF(DAY, duedate, GETDATE()) > 0 
        THEN DATEDIFF(DAY, duedate, GETDATE())  
        ELSE NULL /*0*/ END AS [overdue]
FROM dbo.dates
CROSS APPLY (
    SELECT DATEADD(DAY, 14, issuedate) AS duedate
) AS calc1

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

ratbak Ten Centuries Points: 1379 More actions · Answer 2

Why the empty date shows '1900-01-01' and how to avoid?

Because that's what an empty string casts to (the equivalent of 0).

If you want NULL, use NULL instead of empty string.

What do you want to return when replydate is not yet populated?

Or do you want to just exclude those rows (in which case you would use "where replydate = '' or "where replydate is null")?

- elapsed days from issue date:

DATEDIFF(day,issuedate,replydate)

- due date=issue date+14:

DATEADD(day,14,issuedate)

- late days (count days more than due date) if not replayed: This is just replydate minus due date -- i.e., elapsed days - due days (wrap w/ a case statement if you only want to return this when > 0)

DATEDIFF(DAY,DATEADD(day,14,issuedate),replydate) or DATEDIFF(day,issuedate,replydate) - 14

Note: It appears issuedate should always be populated, so create it as NOT NULL.