June 15, 2017 at 8:01 am
Hi
I have a project table that has a column called TargetDate. This is the date the project was scheduled to be completed by.
there is a column called CompletedDate - this is the actual date the project was completed
is there a way by datediff or a crossmatch to find closest match... if any project CompltedDate is +1 day / -1 day of the TargetDate
please help
June 15, 2017 at 8:10 am
WITH DateDiffs AS (
SELECT *, DENSE_RANK() OVER ORDER BY(ABS(DATEDIFF(day,TargetDate,CompletedDate))) AS RowNo
FROM Mytable
)
SELECT * FROM DateDiffs
WHERE RowNo = 1
John
June 15, 2017 at 8:15 am
joanna.seldon - Thursday, June 15, 2017 8:01 AMHi
I have a project table that has a column called TargetDate. This is the date the project was scheduled to be completed by.there is a column called CompletedDate - this is the actual date the project was completed
is there a way by datediff or a crossmatch to find closest match... if any project CompltedDate is +1 day / -1 day of the TargetDate
please help
Is this an accurate representation of your problem and the solution expected? If it's not, please share sample data and expected results in the same format I used (using DDL and Insert statement).
CREATE TABLE #Project(
projectId int,
TargetDate date,
CompletedDate date
);
INSERT INTO #Project
VALUES
( 1, '20170603', '20170601'),
( 2, '20170603', '20170602'),
( 3, '20170603', '20170603'),
( 4, '20170603', '20170604'),
( 5, '20170603', '20170605');
SELECT *
FROM #Project
WHERE CompletedDate >= DATEADD(dd, -1, TargetDate)
AND CompletedDate <= DATEADD(dd, 1, TargetDate);
DROP TABLE #Project;
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply