Hi Experts,
I got stuck in a state where i am not sure how to proceed. Any help is really appreciated. Hope i am explaining this in a meaningful way....
I provided the input and output in table variable and would likt to know if there is any easy way to achieve it.
1.)I need only the customers who visited in the span of 30 days.
2.)If a customer visited 3 or more times in the span of 45 days or more but if each visit is within 30 days range, then i need all 3 or more visits in the output.
3.) I want only if code1 of 1st record and code1 of 2nd record OR code2 of first record and code2 of second record are same with in that 30 days span.
declare @input table (customerID int,code1 varchar(32), code2 varchar(32), ArrivalDate DATETIME)
-- scenario 1
INSERT INTO @input (customerID, code1, code2, ArrivalDate) VALUES (1,'yes','yes','2012-01-01')
INSERT INTO @input (customerID, code1, code2, ArrivalDate) VALUES (1,'yes','no','2012-01-05')
INSERT INTO @input (customerID, code1, code2, ArrivalDate) VALUES (1,'no','yes','2012-01-29')
INSERT INTO @input (customerID, code1, code2, ArrivalDate) VALUES (1,'yes','yes','2013-03-02')
-- scenario 2.
INSERT INTO @input (customerID, code1, code2, ArrivalDate) VALUES (11,'yes','yes','2013-01-01')
INSERT INTO @input (customerID, code1, code2, ArrivalDate) VALUES (11,'no','no','2013-01-05')
INSERT INTO @input (customerID, code1, code2, ArrivalDate) VALUES (11,'yes','yes','2013-01-29')
INSERT INTO @input (customerID, code1, code2, ArrivalDate) VALUES (11,'yes','yes','2014-03-02')
-- scenario 3. There should be no output because either code1 or code2 is same.
INSERT INTO @input (customerID, code1, code2, ArrivalDate) VALUES (2,'yes','yes','2013-01-01')
INSERT INTO @input (customerID, code1, code2, ArrivalDate) VALUES (2,'no','no','2013-01-05')
SELECT * FROM @input
declare @Output table (customerID int,code1 varchar(32), code2 varchar(32), ArrivalDate DATETIME,Active BIT)
-- scenario 1 OUtput
INSERT INTO @Output (customerID, code1, code2, ArrivalDate,Active) VALUES (1,'yes','yes','2012-01-01',1)
INSERT INTO @Output (customerID, code1, code2, ArrivalDate,Active) VALUES (1,'yes','no','2012-01-05',0)
INSERT INTO @Output (customerID, code1, code2, ArrivalDate,Active) VALUES (1,'no','yes','2012-01-29',0)
-- scenario 2 OUtput
INSERT INTO @Output (customerID, code1, code2, ArrivalDate,Active) VALUES (11,'yes','yes','2013-01-01',1)
INSERT INTO @Output (customerID, code1, code2, ArrivalDate,Active) VALUES (11,'yes','yes','2013-01-29',0)
SELECT * FROM @Output