Prev: Selecting largest for each group
Next: Insert Trigger on a BulkCopy
From: qjlee on 2 Jun 2010 13:38
I have a table called membership which contains memberid, effdat and expdat.
When a member extend the membership on time, the next effective date would
start right after previous expiration date (the next day), thus there is no
break in membership. However, if a membership does not extend on time, the
next effective date would not be right next day to the previous expiration
date, thus causing a break between previous expiration date and next
effective date.

Is there any way that I can merge all expiration date and effective date
that are not break and leaving only the expiration date and effective dates
that are break.

Thanks,
From: --CELKO-- on 2 Jun 2010 19:36
>> I have a table called membership .. <<

"A problem well stated is a problem half solved." -- Charles F.
Kettering

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. If you know how, follow ISO-11179 data element naming
conventions and formatting rules. Temporal data should use ISO-8601
formats. Code should be in Standard SQL as much as possible and not
local dialect.

Sample data is also a good idea, along with clear specifications. It
is very hard to debug code when you do not let us see it. If you want
to learn how to ask a question on a Newsgroup, look at:
http://www.catb.org/~esr/faqs/smart-questions.html

>> which contains memberid, effdat and expdat. <<

Is this what you might have posted?

CREATE TABLE Membership
(member_id INTEGER NOT NULL,
eff_date DATE NOT NULL,
PRIMARY KEY (member_id, eff_date),
expire_date DATE NOT NULL,
CHECK (eff_date < expire_date)
);

>> When a member extend the membership on time, the next effective date would start right after previous expiration date (the next day), thus there is no break in membership.  However, if a membership does not extend on time, the next effective date would not be right next day to the previous expiration date, thus causing a break between previous expiration date and next effective date.

>> Is there any way that I can merge all expiration date and effective date that are not break and leaving only the expiration date and effective dates that are break.<<

Here is a skeleton, untested:

MERGE INTO Membership
USING InputList
ON Membership.member_id = InputList.member_id
WHEN NOT MATCHED
THEN INSERT InputList.*
WHEN MATCHED
AND EXISTS (SELECT *
FROM Membership AS M1
WHERE M1.expire_date = InputList.eff_date - INTERVAL '1'
DAY
THEN UPDATE
SET Membership.expire_date = InputList.expire_date
ELSE INSERT InputList.*
;
 | 
Pages: 1
Prev: Selecting largest for each group
Next: Insert Trigger on a BulkCopy