CODEWITHSUNDEEP
sqlsql-server
user2522217
user2522217

Reputation: 355

How can I create a Date Range from a single column?

I am trying to query a database that has these params:

Transaction Date, User Email Address

The resultant table I want is these params:

Email Address, dateDiff

For dateDiff, I want to find the difference between the earliest transaction date and latest transaction date.

How would I go about doing this?

My code (clearly not correct)

SELECT [Email Address], DATEDIFF(day, [Transaction Date],[Transaction Date]) AS 'dateDiff'
FROM [DB].[TABLE]
ORDER BY [dateDiff] Desc

Upvotes: 0

Views: 86

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270763

You need an aggregation to get the minimum and maximum values for each email, before doing datediff():

SELECT [Email Address],
       DATEDIFF(day, min([Transaction Date]), max([Transaction Date])) AS 'dateDiff'
FROM [DB].[TABLE]
GROUP BY [Email Address]
order by 2 desc;

Upvotes: 4

Related Questions