How Can I Filter Records that Have 3 Consecutive Months base on two Columns-CodePudding

I have a table in the database name [DWSTAGE].INVAUD, Since this table is too Huge I create temp table name ##INV_UD_TRANSACTION_71 which filter only transaction type 71,

My goal was to Filter records which having 3 consecutive months based on inumbr,itrloc.

my Partial script below

#temp table

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

Partial Query for 3 consecutive months

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

And Query Result was

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
......................

My expected result sample

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

CodePudding user response：

you can handle this with the date rather than having the in integer format.

Firstly, Convert the ITRDAT to date format by following syntax

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

Table creation:

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'

Try below query to compare the data between consecutive months.

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

How does it work

Query 1: converts every ITRDAT to start of the month date

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

Query 2: Converts the previous Month date to Current months start date

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

Query 3: Converts the Next Month date to Current months start date

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

Once Converted previous month and Next month date to current month first date, I am doing equal comparison for the dates.