Multiple Date Difference Calculation [closed]

Question

Closed. This question needs details or clarity. It is not currently accepting answers.

Want to improve this question? As written, this question is lacking some of the information it needs to be answered. If the author adds details in comments, consider editing them into the question. Once there's sufficient detail to answer, vote to reopen the question.

Closed last year.

Improve this question

I'm trying to create a table in SQL that shows the difference in hours between each deposit for each user e.g (1st deposit Vs 2nd deposit, 2nd deposit Vs 3rd deposit, 3rd deposit Vs 4th deposit etc...)

here is the original table:

with q1 as (SELECT 
[UserId],
[DepositAttemptDate], 
[AmountSystem]
FROM [dbo].[v_DepositAttempts_Marketing]
WHERE [DepositAttemptDate] >= '2024-01-01' AND [Status] IN ('Completed', 'Complete', 'Confirmed') AND [DepositType]=''
ORDER BY [UserId] ASC, [DepositAttemptDate] DESC)

User	Deposit Date	Value
83815-fa85-466b-aaa7-00029c5	2024-10-30 12:42:58.793	8.698677800974
62ac8-5d7f-464a-993c-00039b1	2024-02-28 20:38:59.883	253.390000000000
62ac8-5d7f-464a-993c-00039b1	2024-02-28 19:09:26.323	253.390000000000
62ac8-5d7f-464a-993c-00039b1	2024-02-28 18:15:13.223	253.390000000000
62ac8-5d7f-464a-993c-00039b1	2024-02-28 17:54:17.340	253.390000000000
62ac8-5d7f-464a-993c-00039b1	2024-02-06 19:43:12.757	115.140000000000
62ac8-5d7f-464a-993c-00039b1	2024-01-30 13:36:56.937	253.390000000000
62ac8-5d7f-464a-993c-00039b1	2024-01-09 14:20:03.440	405.040000000000
17f62-01c7-4a9c-ba28-0003b7f	2024-05-15 18:50:18.350	51.500000000000
17f62-01c7-4a9c-ba28-0003b7f	2024-03-21 22:59:57.217	51.500000000000
17f62-01c7-4a9c-ba28-0003b7f	2024-01-21 13:24:44.393	51.500000000000
17f62-01c7-4a9c-ba28-0003b7f	2024-01-03 20:13:14.167	51.500000000000
76763-384a-46cb-9a08-0004393	2024-09-06 18:51:29.677	103.000000000000
76763-384a-46cb-9a08-0004393	2024-09-05 00:01:44.587	51.500000000000
76763-384a-46cb-9a08-0004393	2024-08-25 03:42:08.290	103.000000000000

Do not tag spam, pick one database program and use that as the tag, drop the rest. — Adrian Klaver
– Adrian Klaver, Commented Oct 30, 2024 at 16:20
I've gone ahead and removed the tag spam here; please edit your question to (re)tag the actual (R)DBMS you are using. Why does tagging multiple RDBMS products make my question unclear? The use of brackets ([]) to delimit identify, and the schema dbo implies SQL Server, but you need to correct your post here. — Thom A
– Thom A ♦, Commented Oct 30, 2024 at 16:21
You also don't ask anything here; you tell us what you need to dol, but not what isn't working about your attempt, or what the difficulty you're having solving the problem is. — Thom A
– Thom A ♦, Commented Oct 30, 2024 at 16:22
Your query isn't complete. You have to select from q1, not just declare it. — jarlh
– jarlh, Commented Oct 30, 2024 at 18:32
Make it easy to assist you, provide a complete minimal reproducible example. — jarlh
– jarlh, Commented Oct 30, 2024 at 18:33

Alan Schofield · Accepted Answer · 2024-10-30 16:59:35Z

0

Assuming this is SQL Server and guessing how accurate you want the results to be (as you didn't specify either), then you could try one of these

DATEDIFF(HOUR, LAG(DepositAttemptDate, 1, DepositAttemptDate) OVER(PARTITION BY UserId ORDER BY DepositAttemptDate), DepositAttemptDate) AS FullHours

or

DATEDIFF(MINUTE, LAG(DepositAttemptDate, 1, DepositAttemptDate) OVER(PARTITION BY UserId ORDER BY DepositAttemptDate), DepositAttemptDate)/60.0 AS DecimalHours

The first will give you full hours, the second will give you a decimal representation, so for example, three hours and 6 minutes would return 3.1

In both examples, we've used LAG to get the previous datetime. If no previous record is found (first record for each UsersId) we've return the current record's DepositAttemptDate so the DATEDIFF will return 0 in these cases.

answered Oct 30, 2024 at 16:59

Alan Schofield

21.9k3 gold badges25 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Multiple Date Difference Calculation [closed]

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related