Różnica daty między kolejnymi wierszami
Mam tabelę o następującej strukturze
ID Account Number Date
1 1001 10/9/2011 (dd/mm/yyyy)
2 2001 1/9/2011 (dd/mm/yyyy)
3 2001 3/9/2011 (dd/mm/yyyy)
4 1001 12/9/2011 (dd/mm/yyyy)
5 3001 18/9/2011 (dd/mm/yyyy)
6 1001 20/9/2011 (dd/mm/yyyy)
Zasadniczo chciałbym mieć zapytanie dostępu, które oblicza różnicę dat dla kolejnych rekordów, ale dla tego samego numeru konta Oczekiwany wynik będzie !!
1001 10/9/2011 - 12/9/2011 2 days
1001 12/9/2011 - 20/9/2011 8 days
1001 20/9/2011 NA
Zasadniczo to, co chciałbym zrobić, to mieć zapytanie dostępu, które oblicza różnicę dat dla kolejnych rekordów, ale dla tego samego numeru konta, w powyższym przykładzie byłoby 1001. (daty nie muszą być pokazywane w wynik)
Używam access 2003.
26
Author: Antonio Pérez, 2012-04-03
5 answers
SELECT T1.ID,
T1.AccountNumber,
T1.Date,
MIN(T2.Date) AS Date2,
DATEDIFF("D", T1.Date, MIN(T2.Date)) AS DaysDiff
FROM YourTable T1
LEFT JOIN YourTable T2
ON T1.AccountNumber = T2.Accountnumber
AND T2.Date > T1.Date
GROUP BY T1.ID, T1.AccountNumber, T1.Date;
Lub
SELECT ID,
AccountNumber,
Date,
NextDate,
DATEDIFF("D", Date, NextDate)
FROM ( SELECT ID,
AccountNumber,
Date,
( SELECT MIN(Date)
FROM YourTable T2
WHERE T2.Accountnumber = T1.AccountNumber
AND T2.Date > T1.Date
) AS NextDate
FROM YourTable T1
) AS T
42
Author: GarethD,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-03-28 10:53:40
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-03-28 10:53:40
Możesz dodać Wyciąg WHERE dla numeru konta, jeśli jest to wymagane. Twoja tabela nazywa się t4
SELECT
t4.ID,
t4.AccountNumber,
t4.AcDate,
(SELECT TOP 1 AcDate
FROM t4 b
WHERE b.AccountNumber=t4.AccountNumber And b.AcDate>t4.AcDate
ORDER BY AcDate DESC, ID) AS NextDate,
[NextDate]-[AcDate] AS Diff
FROM t4
ORDER BY t4.AcDate;
3
Author: Fionnuala,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-04-03 14:16:30
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-04-03 14:16:30
Spróbuj tego:
select [Account Number], DATEDIFF(DD, min(date), max(date)) as dif
from your_table
group by [Account Number]
0
Author: Diego,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-04-03 14:46:48
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-04-03 14:46:48
Odpowiedź Garethda zadziałała dla mnie idealnie.
FYI: jeśli potrzebujesz klauzuli ORDER BY, użyj jej na końcu zapytania SELECT w katalogu głównym.
SELECT ConsignorID,
DateRequired StartDate,
NextDate,
DATEDIFF("D", DateRequired, NextDate)
FROM ( SELECT ConsignorID,
DateRequired,
(SELECT MIN(DateRequired)
FROM "TABLENAME" T2
WHERE T2.DateRequired > T1.DateRequired
) AS NextDate
FROM "TABLENAME" T1
) AS T
ORDER by T. DateRequired ASC
0
Author: Jimmy,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-06-23 10:21:39
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-06-23 10:21:39
SELECT ID,
AccountNumber,
Date,
NextDate,
DATEDIFF("D", Date, NextDate)
FROM ( SELECT ID,
AccountNumber,
Date,
( SELECT MIN(Date)
FROM YourTable T2
WHERE T2.Accountnumber = T1.AccountNumber
AND T2.Date > T1.Date
) AS NextDate
FROM YourTable T1
) AS T
-1
Author: school student,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-08-08 16:14:41
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-08-08 16:14:41