CODEWITHSUNDEEP
sql-servert-sql
thedatasleuth
thedatasleuth

Reputation: 549

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

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?

Upvotes: 0

Views: 1879

Answers (2)

Ilyes
Ilyes

Reputation: 14928

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;

Upvotes: 0

Tab Alleman
Tab Alleman

Reputation: 31785

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.

Upvotes: 1

Related Questions