CODEWITHSUNDEEP
sqldb2
user504674
user504674

Reputation:

Is this feasible with plain SQL? Or is a procedure required?

(This is in DB2 version 9.5)

Say I have a table with four columns:

C1: [EmployeeId]
C2: [StartDate] yyyy-mm-dd format
C3: [EndDate]   yyyy-mm-dd format
C4: [Age]

The combination [EmployeeId + StartDate] forms the primary key.
Now consider the following sample rows:

1     2010-01-16     2010-04-16     29
2     2010-02-16     2010-03-16     33
3     2010-05-16     2010-05-16     31

There is a guarantee that all DAYS will be the 16th day, across all dates, across all rows. No other way about it.

I'm trying to write a query which will do the following:

For any row where the starting month is less than the ending month (like row 1), replicate the row for each month in between, including the last row. So after running the query, the new rows for row 1 will be:

1     2010-01-16     2010-04-16     29
1     2010-02-16     2010-04-16     29
1     2010-03-16     2010-04-16     29
1     2010-04-16     2010-04-16     29

For rows where the starting month and the ending month are the same, do nothing. For rows where the the ending month is the month right after the starting month, one new row will be inserted (see the last row above to understand why).

Questions:

  1. Is this possible in plain SQL? Or is procedures required?
  2. If yes, then how? (hints, code, anything)

Thanks for reading guys. The problem statement is complex, I hope I did a fair job to explain it. Any ambiguity or inconsistency: please point out and I'll edit it asap.

Upvotes: 0

Views: 110

Answers (2)

onedaywhen
onedaywhen

Reputation: 57083

FWIW a set of attribute- and tuple constraints would simplify to some degree:

CHECK (EXTRACT(DAY FROM StartDate) = 16),
CHECK (EXTRACT(DAY FROM EndDate) = 16),
CHECK (StartDate < EndDate)

Upvotes: 0

user330315
user330315

Reputation:

I think this should do it:

SELECT e.employee_id, 
       date(extract(year from startdate)||'-'||mm.month_nr||'-'||extract(day from startdate)) as stardate,
       first_value(e.enddate) over (partition by employee_id order by e.enddate) as enddate
FROM (
  VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)
) mm (month_nr)
JOIN emp_test e
     ON mm.month_nr = extract(month from e.startdate)
ORDER BY e.employee_id, 
         e.startdate, 
         e.enddate

This of course assumes that the datatype for startdate and enddate is DATE.
And this will only work if all dates are within one single year.

I only tested this with DB2 9.7 but I think it should work with 9.5 just as well.

I think there is a solution for the year-cross problem:

WITH yrs (year_month) AS
(
  SELECT DATE(year_nr||'-'||month_nr||'-16')
  FROM (
   VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)
  ) mon (month_nr)
  CROSS JOIN (
    VALUES (2007), (2008), (2009), (2010), (2011), (2012), (2013)
  ) yr (year_nr)
) 
SELECT e.employee_id, 
       yrs.year_month as startdate,
       first_value(e.enddate) over (partition by employee_id order by e.enddate) as enddate
FROM emp_test e
  JOIN yrs ON yrs.year_month between e.startdate and e.enddate

As you can see the years are "hardcoded" into the CTE (common table expression). The easiest solution would be to generate a "month/year" table holding all possible values - basically the data that is generated by the CTE.

Upvotes: 1

Related Questions