先取出特定區間，再逐筆判斷是否為90天內的首次紀錄

mssql

wu2960 2022-05-11 10:17:28 ‧ 501 瀏覽

分享至

--#table 原始資料
CREATE TABLE #table
(
	[date] datetime,
	[user] nvarchar(10),
	[qty] int
)

INSERT INTO #table ([date],[user],[qty])
VALUES                      --是否為90天內首次?
                            --3~6  --6~7 
('2021/10/1', 'Allen', 10),
('2022/1/25', 'Allen', 20),
('2022/3/10', 'Allen', 30), --X
('2022/5/20', 'Allen', 20), --X    
('2022/6/7', 'Allen', 20),  --X    --X

('2021/6/1', 'Ben', 40),     
('2022/4/1', 'Ben', 20),    --O 
('2022/4/10', 'Ben', 10),   --X
('2022/5/3', 'Ben', 10),    --X    
('2022/6/7', 'Ben', 40),    --X    --X

('2021/12/25', 'Cathy', 30),
('2022/1/9', 'Cathy', 10), 
('2022/5/17', 'Cathy', 30), --O    
('2022/5/22', 'Cathy', 10), --X   

('2021/12/19', 'Dave', 40), 
('2022/3/24', 'Dave', 20),  --O
('2022/6/30', 'Dave', 30),  --O    --O
('2022/7/8', 'Dave', 20),          --X
('2022/8/24', 'Dave', 30),

('2022/1/8', 'Eric', 20),
('2022/3/3', 'Eric', 10),   --X    
('2022/6/11', 'Eric', 40),  --O    --O
('2022/6/18', 'Eric', 30),  --X    --X
('2022/7/26', 'Eric', 30),
('2022/8/2', 'Eric', 30)

SELECT * FROM #table 

--#resultA 希望呈現的結果(日期在3~6月)
CREATE TABLE #resultA
(
	[date] datetime,
	[user] nvarchar(10),
	[qty] int,
	[1stTimeIn90Days] char(1)
)
INSERT INTO #resultA ([date],[user],[qty],[1stTimeIn90Days])
VALUES
('2022/3/10', 'Allen', 30, 'X'), 
('2022/5/20', 'Allen', 20, 'X'), 
('2022/6/7', 'Allen', 20, 'X'), 

('2022/4/1', 'Ben', 20, 'O'),  
('2022/4/10', 'Ben', 10, 'X'), 
('2022/5/3', 'Ben', 10, 'X'), 
('2022/6/7', 'Ben', 40, 'X'), 

('2022/5/17', 'Cathy', 30, 'O'), 
('2022/5/22', 'Cathy', 10, 'X'), 

('2022/3/24', 'Dave', 20, 'O'), 
('2022/6/30', 'Dave', 30, 'O'), 

('2022/3/3', 'Eric', 10, 'X'),  
('2022/6/11', 'Eric', 40, 'O'), 
('2022/6/18', 'Eric', 30, 'X') 

SELECT * FROM #resultA
ORDER BY [user], [date]

--#resultB 希望呈現的結果(日期在6~7月)
CREATE TABLE #resultB
(
	[date] datetime,
	[user] nvarchar(10),
	[qty] int,
	[1stTimeIn90Days] char(1)
)
INSERT INTO #resultB ([date],[user],[qty],[1stTimeIn90Days])
VALUES
('2022/6/7', 'Allen', 20, 'X'), 
('2022/6/7', 'Ben', 40, 'X'), 
('2022/6/30', 'Dave', 30, 'O'),
('2022/7/8', 'Dave', 20, 'X'), 
('2022/6/11', 'Eric', 40, 'O'),
('2022/6/18', 'Eric', 30, 'X') 

SELECT * FROM #resultB
ORDER BY [user], [date]

我想要找出2022/3~6月的紀錄，
並判斷同一個user往前推90天內，
是否為第一筆紀錄
(如結果#resultA:
Allen: 2022/3/10，前一筆是2022/1/25，不是90天內的首次記錄
Ben: 2022/4/1，前一筆是2021/6/1，是90天內的首次記錄
Ben: 2022/4/10，前一筆是2022/4/1，不是90天內的首次記錄
)

如果改成2022/6~7月的紀錄，結果會是#resultB

請問這樣的邏輯下，SQL語法該如何去寫呢?謝謝。

問題已解
補上我最後的語法

--#result 結果(日期在3~6月)
SELECT *
		, CASE WHEN (SELECT TOP(1) DATEDIFF(DAY, TB2.[date], TB1.[date])
					 FROM #table AS TB2 
					 WHERE TB1.[user] = TB2.[user] 
						AND TB1.[date] <> TB2.[date] 
						AND DATEDIFF(DAY, TB2.[date], TB1.[date]) BETWEEN 0 AND 90
					 ORDER BY [date]) IS NULL THEN 'O' ELSE 'X' END AS [1stTimeIn90Days]
FROM #table AS TB1
WHERE [date] >= '2022/3/1' AND [date] <= '2022/6/30'
ORDER BY [user], [date]

天黑 iT邦研究生 5 級 ‧ 2022-05-11 11:08:51 檢舉

換個想法，取得使用者指定區間最後一筆資料存暫存表，暫存表增加欄位最後日期前推90天日期，再去撈符合暫存表內區間的資料，沒有資料就是第一筆

wu2960 iT邦新手 3 級 ‧ 2022-05-11 14:06:23 檢舉

好像不能自問自答，最後我的語法補充在原文

obarisk iT邦研究生 2 級 ‧ 2022-05-12 14:30:31 檢舉

用 ROW_NUMBER() OVER (PARITION BY user ORDER BY date)

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

1 個回答

w4560000

iT邦研究生 5 級 ‧ 2022-05-11 11:42:36

最佳解答

SQL SERVER

3~6月

SELECT 
	[date],
	[user],
	qty,
	CASE 
		WHEN diff > 90 THEN 'O'
		ELSE 'X'
	END AS '1stTimeIn90Days'
FROM
(
	SELECT t.*,
		   DATEDIFF(DAY,
					LAG([date]) over (partition by [user] order by [date]),
					[date]
				   ) AS diff
	FROM  #table t 
) AS TT
WHERE [date] BETWEEN '2022-03-01' AND '2022-06-30 23:59:59'
ORDER BY [user], [date]

6~7月
#resultB是否有漏一筆需要確認一下
2022-07-26 00:00:00.000 Eric 30 X

6~7月

SELECT 
	[date],
	[user],
	qty,
	CASE 
		WHEN diff > 90 THEN 'O'
		ELSE 'X'
	END AS '1stTimeIn90Days'
FROM
(
	SELECT t.*,
		   DATEDIFF(DAY,
					LAG([date]) over (partition by [user] order by [date]),
					[date]
				   ) AS diff
	FROM  #table t 
) AS TT
WHERE [date] BETWEEN '2022-06-01' AND '2022-07-31 23:59:59'
ORDER BY [user], [date]