Dynamically Calculate DateDiff for all rows in Date column MS SQL Server

Question

I am trying to calculate the difference in days between dates for consecutive rows in a single column called Date_col. I then want to put those differences into a new column called Expected_Results.

I have the following code so far:

DECLARE @datedifferences DATETIME

SET @datedifferences = (SELECT DATEDIFF(DAY, MIN(Date_col), MAX(Date_col)) FROM Schema.Table)

SELECT DISTINCT TOP 100 Date_col
FROM Database.Schema.Table
WHERE Date_col = @datedifferences
ORDER BY Date_col

The below is what I'd like to have returned:

Date_col    Expected_Results
1/1/2018    --
2/2/2018    31
3/3/2018    31
4/4/2018    31
5/5/2018    31
6/6/2018    31
7/7/2018    31
8/8/2018    31

However, the query runs successfully but nothing is returned. I suspect this is because I'm missing some kind of loop to iterate over the rows. How can I incorporate a while loop into query to successfully iterate over the rows such that the query prints the expected results?

@datedifferences` is declared as `DATETIME`, but is assigned to the return value of the `DATEDIFF` function, which [returns an integer](https://learn.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sql?view=sql-server-2017). — Zack, Oct 18 '18 at 17:50
[`Lag`](https://docs.microsoft.com/en-us/sql/t-sql/functions/lag-transact-sql?view=sql-server-2017) is the (modern) way to access data from a prior row within a `select`. That's assuming you have an undisclosed table with a column containing dates and want to display the dates in order along with the number of days between dates in consecutive rows. Your question lacks a question, among other details. Not sure what the "day sum differences" are about. Please read [this](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) for some tips on improving your question. — HABO, Oct 19 '18 at 03:42
duplicate of: https://stackoverflow.com/questions/5728602/sql-datediff-find-datediff-between-rows — Tab Alleman, Oct 19 '18 at 13:30

Tab Alleman · Answer 1 · 2018-10-18T18:26:53.277

1

Are you thinking that this is all one statement? Because it's not. You have 3 separate statements:

DECLARE @datedifferences datetime;

SET @datedifferences = DATEDIFF(day, min(Date_col), max(Date_col));

SELECT DISTINCT TOP 100 Date_col
FROM Database.Schema.Table
WHERE Date_col = @datedifferences
ORDER BY Date_col;

The middle statement (SET...) has no access to the FROM clause of the SELECT statement below it, so it has no knowledge of the Date_col you are referencing in it. Therefore Date_col is an invalid column name in the middle statement.

edited Oct 18 '18 at 18:26

answered Oct 18 '18 at 17:46

Tab Alleman

31,483
7
36
52

. . . You need `@datedifferences` as `INT` type instead of `datetime`. – Yogesh Sharma Oct 18 '18 at 18:02
@YogeshSharma that's also true, but it's not the reason for the error message in the question. – Tab Alleman Oct 18 '18 at 18:24
@TabAlleman The OP finally say what he want to do exactly in a comment under my answer. – Ilyes Oct 18 '18 at 18:39
@TabAlleman, I have added the FROM clause to the SET statement, and while now the query runs, it does not return any values. Can you help? – thedatasleuth Oct 18 '18 at 20:43

Ilyes · Answer 2 · 2018-10-18T18:30:45.377

0

That because the SET part, where hte Date_Col column is not known/accessible, you need to specify from where like

DECLARE @datedifferences datetime;
SET @datedifferences = (SELECT DATEDIFF(day, min(Date_col), max(Date_col)) FROM Database.Schema.Table);
SELECT DISTINCT TOP 100 Date_col
FROM Database.Schema.Table
WHERE Date_col = @datedifferences
ORDER BY Date_col;

Now, the DATEDIFF() function return INT datatype, and your @datedifferences declared as DATETIME datatype. This for the first, so you can't use it to compare in the WHERE clause cause I don't think you want to compare DATETIME with INT, which mean even you change the datatype of your variable to INT this part of your query won't work

WHERE Date_col = @datedifferences

Now, according to your code I think you want to select the top 100 where Date_Col between the MAX() and MIN() dates from your table, so your code maybe looks like

DECLARE @MaxDate DATETIME = (SELECT MAX(Date_Col) FROM Database.Schema.Table);
DECLARE @MinDate DATEIME = (SELECT MIN(Date_Col) FROM Database.Schema.Table);

SELECT DISTINCT TOP 100 Date_col
FROM Database.Schema.Table
WHERE Date_col BETWEEN @MaxDate AND @MinDate
ORDER BY Date_col;

Update:

I just want to calculate the date_diffs for the first 100 rows in the date_col

SELECT DATEDIFF(Day, MIN(Date_Col), MAX(Date_Col)) As Def
FROM
(
    SELECT TOP 100 Date_Col
    FROM Database.Schema.Table
    ORDER BY Date_Col
) T;

edited Oct 18 '18 at 18:30

answered Oct 18 '18 at 17:57

Ilyes

14,640
4
29
55

. . . You need `@datedifferences` as `INT` type instead of `datetime`. – Yogesh Sharma Oct 18 '18 at 17:59
@YogeshSharma Yeah, my answer still has other updates, ty BTW – Ilyes Oct 18 '18 at 18:00
when I substitute datetime for int I get this error: Operand type clash: date is incompatible with int – thedatasleuth Oct 18 '18 at 18:02
and how do I then insert the result of the @datedifferences into a new column? – thedatasleuth Oct 18 '18 at 18:03
The query runs successfully but doesn't return anything – thedatasleuth Oct 18 '18 at 18:12
@YogeshSharma Finally, the OP says what he want to do really :) – Ilyes Oct 18 '18 at 18:32
You misunderstood what I said. This answer is not what I'm trying to do. I'm not looking to calculate one date_diff, I want to calculate the date_diffs for all the rows dynamically. – thedatasleuth Oct 18 '18 at 18:39
@thedatasleuth Well, a DDL, Sample data and expected results would be helpful. – Ilyes Oct 18 '18 at 18:45
@Sami, can you please remove your downvote so my question can have more visibility and can be answered? – thedatasleuth Oct 18 '18 at 21:37

Dynamically Calculate DateDiff for all rows in Date column MS SQL Server

2 Answers2