Wanted: Set based solution to evil problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Wanted: Set based solution to evil problem

Author

Topic

JamesT
Yak Posting Veteran

97 Posts

Posted - 2002-03-28 : 11:50:38

Names and such are changed to protect the innocent.

Here's the setup:

Let's say we have an organization that allows people to purchase products and receive commissions from said purchases but only if they are purchasing products each month. After a specific number of months where they don't purchase products they are removed from the organization. Different groups are removed after different intervals of inactivity. For example: the blue-haired people are removed after they don't purchase for three months, the purple-haired people after five months, etc.

TABLES AND DATA (It doesn't really look like this but gainful employment is good and the confidentiality statement I signed has teeth).

CREATE TABLE PERSON_TYPE (PERSON_TYPE_ID INT, PERSON_DESC VARCHAR(20), PERSON_INACTIVE_MONTHS TINYINT)
INSERT INTO PERSON_TYPE (PERSON_TYPE_ID, PERSON_DESC, PERSON_INACTIVE_MONTHS) VALUES(1,'BLUE HAIR',3)
INSERT INTO PERSON_TYPE (PERSON_TYPE_ID, PERSON_DESC, PERSON_INACTIVE_MONTHS) VALUES(2,'PURPLE HAIR',5)
INSERT INTO PERSON_TYPE (PERSON_TYPE_ID, PERSON_DESC, PERSON_INACTIVE_MONTHS) VALUES(3,'BROWN HAIR',2)
CREATE TABLE PERSON (PERSON_ID INT, PERSON_TYPE_FK INT)
INSERT INTO PERSON (PERSON_ID, PERSON_TYPE_FK) VALUES (1,1)
INSERT INTO PERSON (PERSON_ID, PERSON_TYPE_FK) VALUES (2,3)
INSERT INTO PERSON (PERSON_ID, PERSON_TYPE_FK) VALUES (3,2)
CREATE TABLE PERIOD (PERIOD_ID INT, PERIOD_DATE DATETIME)
INSERT INTO PERIOD (PERIOD_ID, PERIOD_DATE) VALUES (1,'1/31/02')
INSERT INTO PERIOD (PERIOD_ID, PERIOD_DATE) VALUES (2,'2/28/02')
INSERT INTO PERIOD (PERIOD_ID, PERIOD_DATE) VALUES (3,'3/31/02')
INSERT INTO PERIOD (PERIOD_ID, PERIOD_DATE) VALUES (4,'4/30/02')
INSERT INTO PERIOD (PERIOD_ID, PERIOD_DATE) VALUES (5,'5/31/02')
INSERT INTO PERIOD (PERIOD_ID, PERIOD_DATE) VALUES (6,'6/30/02')
INSERT INTO PERIOD (PERIOD_ID, PERIOD_DATE) VALUES (7,'7/31/02')
INSERT INTO PERIOD (PERIOD_ID, PERIOD_DATE) VALUES (8,'8/31/02')
CREATE TABLE ACTIVITY (PERSON_FK INT, PURCHASE_PERIOD_FK INT, PURCHASE_AMT INT)
INSERT INTO ACTIVITY (PERSON_FK, PURCHASE_PERIOD_FK, PURCHASE_AMT) VALUES (1,1,5)
INSERT INTO ACTIVITY (PERSON_FK, PURCHASE_PERIOD_FK, PURCHASE_AMT) VALUES (1,2,7)
INSERT INTO ACTIVITY (PERSON_FK, PURCHASE_PERIOD_FK, PURCHASE_AMT) VALUES (1,3,3)
INSERT INTO ACTIVITY (PERSON_FK, PURCHASE_PERIOD_FK, PURCHASE_AMT) VALUES (1,4,4)
INSERT INTO ACTIVITY (PERSON_FK, PURCHASE_PERIOD_FK, PURCHASE_AMT) VALUES (1,5,0)
INSERT INTO ACTIVITY (PERSON_FK, PURCHASE_PERIOD_FK, PURCHASE_AMT) VALUES (1,6,0)
INSERT INTO ACTIVITY (PERSON_FK, PURCHASE_PERIOD_FK, PURCHASE_AMT) VALUES (1,7,0)
INSERT INTO ACTIVITY (PERSON_FK, PURCHASE_PERIOD_FK, PURCHASE_AMT) VALUES (2,1,5)
INSERT INTO ACTIVITY (PERSON_FK, PURCHASE_PERIOD_FK, PURCHASE_AMT) VALUES (2,2,7)
INSERT INTO ACTIVITY (PERSON_FK, PURCHASE_PERIOD_FK, PURCHASE_AMT) VALUES (2,3,3)
INSERT INTO ACTIVITY (PERSON_FK, PURCHASE_PERIOD_FK, PURCHASE_AMT) VALUES (2,4,5)
INSERT INTO ACTIVITY (PERSON_FK, PURCHASE_PERIOD_FK, PURCHASE_AMT) VALUES (2,5,2)
INSERT INTO ACTIVITY (PERSON_FK, PURCHASE_PERIOD_FK, PURCHASE_AMT) VALUES (2,6,3)
INSERT INTO ACTIVITY (PERSON_FK, PURCHASE_PERIOD_FK, PURCHASE_AMT) VALUES (2,7,0)
INSERT INTO ACTIVITY (PERSON_FK, PURCHASE_PERIOD_FK, PURCHASE_AMT) VALUES (3,1,1)
INSERT INTO ACTIVITY (PERSON_FK, PURCHASE_PERIOD_FK, PURCHASE_AMT) VALUES (3,2,2)
INSERT INTO ACTIVITY (PERSON_FK, PURCHASE_PERIOD_FK, PURCHASE_AMT) VALUES (3,3,0)
INSERT INTO ACTIVITY (PERSON_FK, PURCHASE_PERIOD_FK, PURCHASE_AMT) VALUES (3,4,0)
INSERT INTO ACTIVITY (PERSON_FK, PURCHASE_PERIOD_FK, PURCHASE_AMT) VALUES (3,5,0)
INSERT INTO ACTIVITY (PERSON_FK, PURCHASE_PERIOD_FK, PURCHASE_AMT) VALUES (3,6,0)
INSERT INTO ACTIVITY (PERSON_FK, PURCHASE_PERIOD_FK, PURCHASE_AMT) VALUES (3,7,0)

So, here's the rub. How do I write a select statement to determine who should be "removed" based on their months of inactivity? I don't want to have to write different statements for different groups. different = heavy maintenance = bad. table based = low maintenance = good.

Just to clarify: From the above data, Person 1 and Person 3 would be removed as their Person Type record indicates they are inactive for 3 and 5 months respectively.

Any thoughts are appreciated and thanks in advance for consideration.

James

Edited by - JamesT on 03/28/2002 12:11:40

dsdeming

479 Posts

Posted - 2002-03-28 : 13:27:32

I just whipped this together, but I think it may give you what you need.

DECLARE @iToday int
SELECT @iToday = MAX( PERIOD_ID ) FROM PERIOD WHERE PERIOD_DATE < '8/1/2002' --getdate()

SELECT P.PERSON_ID
FROM PERSON P
JOIN PERSON_TYPE T ON P.PERSON_TYPE_FK = T.PERSON_TYPE_ID
LEFT OUTER JOIN ( SELECT PERSON_FK, PURCHASE_PERIOD_FK = MAX( PURCHASE_PERIOD_FK ) FROM ACTIVITY WHERE PURCHASE_AMT > 0 GROUP BY PERSON_FK ) A
ON P.PERSON_ID = A.PERSON_FK
LEFT OUTER JOIN PERIOD PD ON PD.PERIOD_ID = A.PURCHASE_PERIOD_FK
WHERE a.PURCHASE_PERIOD_FK + t.PERSON_INACTIVE_MONTHS <= @iToday

JamesT
Yak Posting Veteran

97 Posts

Posted - 2002-03-28 : 13:36:04

Excellent. Thank you very much. I appreciate the assistance.

damcalcan
Starting Member

11 Posts

Posted - 2002-03-31 : 10:50:43

another solution using datediff function
you should replace '2002-09-30' date (used for testing purpose) with GETDATE() function

SELECT person_id
FROM PERSON PRS
JOIN ACTIVITY A
ON PRS.person_id = A.person_fk
JOIN PERIOD P
ON P.period_id= A.purchase_period_fk
JOIN PERSON_TYPE T
ON T.person_type_id = PRS.person_type_fk
GROUP BY person_id ,person_inactive_months
HAVING DATEDIFF (mm,MAX(period_date) ,'2002-09-30') < T.person_inactive_months

calin

Edited by - damcalcan on 03/31/2002 11:14:58

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources