Apply window function logic on an interval

Question

I have the following table:

 +----------+----------+--------------+
| Customer |  Month   | Had Meeting? |
+----------+----------+--------------+
| John     | Jan-15   | N            |
| John     | Feb-15   | Y            |
| John     | March-15 | Y            |
| John     | April-15 | N            |
| John     | May-15   | N            |
| John     | June-15  | N            |
| John     | July-15  | N            |
| John     | Aug-15   | N            |
| John     | Sep-15   | N            |
| John     | Oct-15   | N            |
| John     | Nov-15   | Y            |
| John     | Dec-15   | N            |
+----------+----------+--------------+

1. I wish to apply a logic which looks at a customer and checks whether he had any meetings in a rolling 3-month period. This period refers to a dynamic interval in which the meeting could have occured. i.e. - in May 15' record I wish to look at the period between February-July.

2. If there was more then 1 meeting, i wish to return the earliest meeting.

i.e., in In January 15 I want to look at the inerval between January and March, which captures 2 meetings, I want to return back the earlier of the two (February).

In February I want to look at the interval between January and April (2 months preceding and 2 months following) - and again, It should return February's meeting.

In March I wish to look at January - May (this in fact is a full time iterval of 2 months preceiding and 2 month following which I need to capture) - and again, return February.

The final outcome which I wish to capture from this dataset is February and November's meetings. I think I need something like:

row_number() over(partition by Customer order by Sale_made, Month Rows between 2 preceding and 2 following)

However row_number cannot be applied as a window function.

I'm using TD v 13.0.

Appreciate your help.

Asaf.

Answer 1

Seems like you want to return a row if it had a meeting and the previous row with Had Meeting? is at least three months ago.

SELECT *
FROM tab
WHERE "Had Meeting?" = 'Y' -- no need for rows without meeting
QUALIFY 
   COALESCE(MAX("Month") -- previous row's date (i.e. LAG syntax)
            OVER (PARTITION BY Customer 
                  ORDER BY "Month"
                  ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)
            ,DATE '0001-01-01') -- otherwise 1st row will return NULL
   < ADD_MONTHS("Month", -3)  -- three month before the current row's date

Answer 2

Give this a try, I had to convert the dates so right now they are varchar

create table #table(
Customer varchar(50),
[Month] varchar(50),
[Had Meeting] varchar(1)
)

INSERT INTO #table (Customer, [Month], [Had Meeting])
  VALUES 
  ('John', 'Jan-15', 'N'),
  ('John', 'Feb-15', 'Y'),
  ('John', 'March-15', 'Y'),
  ('John', 'April-15', 'N'),
  ('John', 'May-15', 'N'),
  ('John', 'June-15 ', 'N'),
  ('John', 'July-15 ', 'N'),
  ('John', ' Aug-15', 'N'),
  ('John', ' Sep-15', 'N'),
  ('John', ' Oct-15', 'N'),
  ('John', 'Nov-15', 'Y'),
  ('John', 'Dec-15', 'N')

DECLARE @searchdate datetime = '2015-05-01 00:00:00.000'

SELECT TOP 1
  Customer,
  MIN([Month]) AS [Month],
  [Had Meeting]
FROM #table
WHERE DATEPART(YEAR, @searchdate) = DATEPART(YEAR, DATEADD(YEAR, -1, GETDATE()))
AND [Had Meeting] = 'Y'
AND CONVERT(datetime, '1-' + [Month], 104) >= DATEADD(MONTH, -2, @searchdate)
AND DATEPART(MONTH, CONVERT(datetime, '1-' + [Month], 104)) <= (DATEPART(MONTH, @searchdate) + 2)
AND Customer = 'John'
GROUP BY Customer,
         [Month],
         [Had Meeting]