Hi
one more question:
I have to calculate the difference between two dates in years, month and day.
The result should then be used in the following calculation:
CASE WHEN(y_diff >= 31) THEN 200
WHEN(m_diff > 0 AND y_diff = 30) THEN 200
WHEN(d_diff > 0 AND m_diff = 0 AND y_diff = 30) THEN 200
WHEN(d_diff = 0 AND m_diff = 0 AND y_diff = 30) THEN 190
WHEN(y_diff >= 21 and y_diff < 30) THEN 190
WHEN(m_diff > 0 AND y_diff = 20) THEN 190
WHEN(d_diff > 0 AND m_diff = 0 AND y_diff = 20) THEN 190
WHEN(d_diff = 0 AND m_diff = 0 AND y_diff = 20) THEN 180
WHEN(y_diff >= 16 and y_diff < 20) THEN 180
WHEN(m_diff > 0 AND y_diff = 15) THEN 180
WHEN(d_diff > 0 AND m_diff = 0 AND y_diff = 15) THEN 180
WHEN(d_diff = 0 AND m_diff = 0 AND y_diff = 15) THEN 170
WHEN(y_diff >= 11 and y_diff < 15) THEN 170
WHEN(m_diff > 0 AND y_diff = 10) THEN 170
WHEN(d_diff > 0 AND m_diff = 0 AND y_diff = 10) THEN 170
WHEN(d_diff = 0 AND m_diff = 0 AND y_diff = 10) THEN 160
WHEN(y_diff >= 8 and y_diff < 10) THEN 160
WHEN(m_diff > 0 AND y_diff = 7) THEN 160
WHEN(d_diff > 0 AND m_diff = 0 AND y_diff = 7) THEN 160
WHEN(d_diff = 0 AND m_diff = 0 AND y_diff = 7) THEN 150
WHEN(y_diff = 6) THEN 150
WHEN(m_diff > 0 AND y_diff = 5) THEN 150
WHEN(d_diff > 0 AND m_diff = 0 AND y_diff = 5) THEN 150
WHEN(d_diff = 0 AND m_diff = 0 AND y_diff = 5) THEN 140
WHEN(m_diff > 0 AND y_diff = 4) THEN 140
WHEN(d_diff > 0 AND m_diff = 0 AND y_diff = 4) THEN 140
WHEN(d_diff = 0 AND m_diff = 0 AND y_diff = 4) THEN 130
WHEN(m_diff > 0 AND y_diff = 3) THEN 130
WHEN(d_diff > 0 AND m_diff = 0 AND y_diff = 3) THEN 130
WHEN(d_diff = 0 AND m_diff = 0 AND y_diff = 3) THEN 120
WHEN(m_diff > 0 AND y_diff = 2) THEN 120
WHEN(d_diff > 0 AND m_diff = 0 AND y_diff = 2) THEN 120
WHEN(d_diff = 0 AND m_diff = 0 AND y_diff = 2) THEN 110
WHEN(m_diff > 0 AND y_diff = 1) THEN 110
WHEN(d_diff > 0 AND m_diff = 0 AND y_diff = 1) THEN 110
WHEN(d_diff = 0 AND m_diff = 0 AND y_diff = 1) THEN 100
WHEN(m_diff > 9 and m_diff < 12 AND y_diff = 0) THEN 100
WHEN(d_diff > 0 AND m_diff = 9 AND y_diff = 0) THEN 100
WHEN(d_diff = 0 AND m_diff = 9 AND y_diff = 0) THEN 90
WHEN(m_diff > 6 and m_diff < 9 AND y_diff = 0) THEN 90
WHEN(d_diff > 0 AND m_diff = 6 AND y_diff = 0) THEN 90
WHEN(d_diff = 0 AND m_diff = 6 AND y_diff = 0) THEN 80
WHEN(d_diff > 0 AND m_diff = 5 AND y_diff = 0) THEN 80
WHEN(d_diff = 0 AND m_diff = 5 AND y_diff = 0) THEN 70
WHEN(d_diff > 0 AND m_diff = 4 AND y_diff = 0) THEN 70
WHEN(d_diff = 0 AND m_diff = 4 AND y_diff = 0) THEN 60
WHEN(d_diff > 0 AND m_diff = 3 AND y_diff = 0) THEN 60
WHEN(d_diff = 0 AND m_diff = 3 AND y_diff = 0) THEN 50
WHEN(d_diff > 0 AND m_diff = 2 AND y_diff = 0) THEN 50
WHEN(d_diff = 0 AND m_diff = 2 AND y_diff = 0) THEN 40
WHEN(d_diff > 0 AND m_diff = 1 AND y_diff = 0) THEN 40
WHEN(d_diff = 0 AND m_diff = 1 AND y_diff = 0) THEN 30
WHEN(d_diff > 14 AND m_diff = 0 AND y_diff = 0) THEN 30
WHEN(d_diff > 7 and d_diff <= 14 AND m_diff = 0 AND y_diff = 0) THEN 20
WHEN(d_diff > 1 and d_diff <= 7 AND m_diff = 0 AND y_diff = 0) THEN 10
WHEN(d_diff > 1 and d_diff <= 7 AND m_diff = 0 AND y_diff = 0) THEN 10
WHEN(d_diff <= 1 AND m_diff = 0 AND y_diff = 0) THEN 0
where y_diff is difference in years, m_diff is differnce in month etc.
The stuff is time critcal. We should do 1 million rows about 0.5 seconds on one core.
I understand that the case statement should be restructured to nested hierarchical case.
I am working on it an post it once I am ready.
Although the case statement can be optimized, it is still quick enough by itself.
The point is that the input is two dates and I have to calculate y_diff, m_diff and d_diff.
I have been using months_between, but that is far too slow.
Is there any better alternative?
Test with at least a million rows where you can assue positive numbers.