Calculate many durations between two dates

Transact-SQL
1

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');

2 
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
3

The reason for the '1900-01-01' dates is the '' being loaded into the table; SQL Server treats a blank date as 19000101. Use NULL instead.

4

thank you for your reply
I think something wrong in my table as the calculation does not work properly for overdue and late always null
Capture

5

I checked for a NULL date. Since you have '19000101', not NULL, as the date, you'll need to do this:

SELECT
    issuedate, replydate,
	CASE WHEN NULLIF(replydate, '1900-01-01') IS NULL AND
              DATEDIFF(DAY, duedate, GETDATE()) > 0 
         THEN DATEDIFF(DAY, duedate, GETDATE()) 
         ELSE NULL /*0*/ END AS [late days],
	CASE WHEN 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

Btw, you should use one method or the other: allow NULL or NOT NULL and 1900-01-01. You don't want two different ways to specify the same data condition.

6

thanks a lot for your reply and clarification
i've tried to make some modification to add "elapsed" and to calculate overdue from replydate...am I right?
and can we combined both overdue and late in one column...
SELECT
issuedate, DATEADD(DAY, 14, issuedate) AS duedate,NULLIF(replydate, '1900-01-01') replydate ,
CASE WHEN NULLIF(replydate, '1900-01-01') IS NULL
THEN DATEDIFF(DAY, issuedate, GETDATE())
ELSE DATEDIFF(DAY, issuedate, replydate) END AS [elapsed],
CASE WHEN NULLIF(replydate, '1900-01-01') IS NULL AND
DATEDIFF(DAY, duedate, GETDATE()) > 0
THEN DATEDIFF(DAY, duedate, GETDATE())
ELSE NULL /0/ END AS [late days],
CASE WHEN replydate > duedate AND
DATEDIFF(DAY, duedate, replydate) > 0
THEN DATEDIFF(DAY, duedate, replydate)
ELSE NULL /0/ END AS [overdue]

FROM dbo.dates
CROSS APPLY (
SELECT DATEADD(DAY, 14, issuedate) AS duedate
) AS calc1

7 
SELECT
    issuedate, DATEADD(DAY, 14, issuedate) AS duedate, NULLIF(replydate, '1900-01-01') replydate,
	CASE WHEN NULLIF(replydate, '1900-01-01') IS NULL
         THEN DATEDIFF(DAY, issuedate, GETDATE())
         ELSE DATEDIFF(DAY, issuedate, replydate) END AS [elapsed],
	CASE WHEN NULLIF(replydate, '1900-01-01') IS NULL AND DATEDIFF(DAY, duedate, GETDATE()) > 0
         THEN DATEDIFF(DAY, duedate, GETDATE())
         WHEN replydate > duedate AND DATEDIFF(DAY, duedate, replydate) > 0
         THEN DATEDIFF(DAY, duedate, replydate)
         ELSE NULL END AS [late_or_overdue]
FROM dbo.dates
CROSS APPLY (
    SELECT DATEADD(DAY, 14, issuedate) AS duedate
) AS calc1
2 Likes
8

thanks again, it works perfect.