Sunday, September 12, 2010
Data Migration Architecture
This article outline the detail end to end data migration technical design. Below diagram illustrate at a high level all the different components involved.
Thursday, March 25, 2010
Oracle SQL: Finding Overlapping Dates
When the object validation error reported,
"An account cannot have multiple auto-pay options effective at the same time"
To find out the overlapping dates, following query became handy. This can be generalized for any situation where overlapping periods of data needs to be identified.
SELECT *
FROM (SELECT acct_id,
start_dt,
end_dt,
CASE
WHEN NOT (start_dt > Lead(end_dt)
OVER(PARTITION BY acct_id ORDER BY start_dt, end_dt)
OR end_dt < Lead(start_dt)
OVER(PARTITION BY acct_id ORDER BY start_dt, end_dt))
THEN 'Yes'
ELSE 'No'
END AS overlap
FROM ci_acct_apay)
WHERE overlap = 'Yes'
"An account cannot have multiple auto-pay options effective at the same time"
To find out the overlapping dates, following query became handy. This can be generalized for any situation where overlapping periods of data needs to be identified.
SELECT *
FROM (SELECT acct_id,
start_dt,
end_dt,
CASE
WHEN NOT (start_dt > Lead(end_dt)
OVER(PARTITION BY acct_id ORDER BY start_dt, end_dt)
OR end_dt < Lead(start_dt)
OVER(PARTITION BY acct_id ORDER BY start_dt, end_dt))
THEN 'Yes'
ELSE 'No'
END AS overlap
FROM ci_acct_apay)
WHERE overlap = 'Yes'
Subscribe to:
Posts (Atom)
