如何根据两列筛选连续3个月的记录

Question

我在数据库名称[DWSTAGE].INVAUD中有一个表，由于这个表太大，我创建了临时表名称##INV_UD_TRANSACTION_71，它只过滤事务类型71，

我的目标是根据inumbr、itrloc过滤连续3个月的记录。

下面的我的部分脚本

#温度表

SELECT INUMBR,ITRLOC,ITRDAT
INTO #INV_UD_TRANSACTION_71
FROM [DWSTAGE].INVAUD
WHERE ITRTYP = '71'

连续3个月部分查询

SELECT DISTINCT * FROM
(SELECT  E1.INUMBR
,E1.ITRLOC
,E1.ITRDAT
FROM #INV_UD_TRANSACTION_71  E1
JOIN #INV_UD_TRANSACTION_71  E2
ON E2.INUMBR = E1.INUMBR
AND MONTH(E2.ITRDAT)  = MONTH(E1.ITRDAT) + 1
JOIN #INV_UD_TRANSACTION_71  E3
ON E3.INUMBR = E2.INUMBR
AND MONTH(E3.ITRDAT) = MONTH(E2.ITRDAT)  + 1
UNION ALL
SELECT  E2.INUMBR
,E2.ITRLOC
,E2.ITRDAT
FROM #INV_UD_TRANSACTION_71  E1
JOIN #INV_UD_TRANSACTION_71  E2
ON E2.INUMBR = E1.INUMBR
AND MONTH(E2.ITRDAT)  = MONTH(E1.ITRDAT) + 1
JOIN #INV_UD_TRANSACTION_71  E3
ON E3.INUMBR = E2.INUMBR
AND MONTH(E3.ITRDAT) = MONTH(E2.ITRDAT)  + 1
UNION ALL
SELECT   E3.INUMBR
,E3.ITRLOC
,E3.ITRDAT
FROM #INV_UD_TRANSACTION_71  E1
JOIN #INV_UD_TRANSACTION_71  E2
ON E2.INUMBR = E1.INUMBR
AND MONTH(E2.ITRDAT)  = MONTH(E1.ITRDAT) + 1
JOIN #INV_UD_TRANSACTION_71  E3
ON E3.INUMBR = E2.INUMBR
AND MONTH(E3.ITRDAT) = MONTH(E2.ITRDAT)  + 1
) A
ORDER BY INUMBR,ITRLOC

查询结果为

INUMBR  ITRLOC  ITRDAT
40  13001   210823
40  14002   211115
40  15008   210419
40  15010   210416
40  15012   211115
43  11004   210129
43  12004   210909
43  12004   181018
43  12004   210129
43  12004   210701
43  12004   220404
43  13003   220117
43  13003   210329
43  14001   210301
43  14006   220214
43  14006   210617
43  14006   201009
43  14006   210909
43  14006   220110
43  14006   220505
......................

我的预期结果样本

INUMBR  ITRLOC  ITRDAT
92    12002     210105
92    12002     210210
92    12002     210311
92    12003     210405
107   12009     190104
107   12009     190210
107   12009     190329
1187  13001     220506
1187  13001     220611
1187  13001     220713
1187  13001     220817
1187  13001     220920

Answer 1

您可以使用日期而不是整数格式来处理此问题。

首先，通过以下语法将ITRDAT转换为日期格式

select convert (date,  Stuff(Stuff('210823',5,0,'.'),3,0,'.'), 4)

表创建：

CREATE TABLE #INV_UD_TRANSACTION_71(INUMBR  int,ITRLOC  int,ITRDAT varchar(20))
GO
insert into #INV_UD_TRANSACTION_71
select 40 ,13001,'210823' union
select 40 ,13001,'150923' union
select 40 ,13001,'200723' union
select 41 ,13002,'210617' union
select 41 ,13002,'151017' union
select 41 ,13002,'110417' 

请尝试以下查询以比较连续月份之间的数据。

SELECT * 
FROM(
SELECT  E1.INUMBR
,E1.ITRLOC
,E1.ITRDAT 
FROM #INV_UD_TRANSACTION_71  E1
JOIN #INV_UD_TRANSACTION_71  E2
ON E2.INUMBR = E1.INUMBR 
AND E2.ITRLOC = E1.ITRLOC
JOIN #INV_UD_TRANSACTION_71  E3
ON E3.INUMBR = E1.INUMBR 
AND E3.ITRLOC = E1.ITRLOC
AND DATEADD(DAY,1, EOMONTH(convert (date,  Stuff(Stuff(e1.ITRDAT,5,0,'.'),3,0,'.'), 4),-1)) = DATEADD(DAY,1, EOMONTH(convert (date,  Stuff(Stuff(e2.ITRDAT,5,0,'.'),3,0,'.'), 4),0))
AND DATEADD(DAY,1, EOMONTH(convert (date,  Stuff(Stuff(e1.ITRDAT,5,0,'.'),3,0,'.'), 4),-1)) = DATEADD(DAY,1, EOMONTH(convert (date,  Stuff(Stuff(e3.ITRDAT,5,0,'.'),3,0,'.'), 4),1))
) A

它是如何工作的

查询1：将每个ITRDAT转换为月初日期

DATEADD(DAY,1, EOMONTH(convert (date,  Stuff(Stuff(e1.ITRDAT,5,0,'.'),3,0,'.'), 4),-1))

查询2：将上月日期转换为本月开始日期

DATEADD(DAY,1, EOMONTH(convert (date,  Stuff(Stuff(e2.ITRDAT,5,0,'.'),3,0,'.'), 4),0))

查询3：将下月日期转换为当前月份的开始日期

DATEADD(DAY,1, EOMONTH(convert (date,  Stuff(Stuff(e3.ITRDAT,5,0,'.'),3,0,'.'), 4),1))

一旦将上个月和下个月的日期转换为本月的第一个日期，我就会对这些日期进行相等的比较。