Enrico Enrico - 6 months ago 13
SQL Question

SQL - Compare 2 different ranges of date

The table has these columns:

DATA, CODE and so on..

I need to display two different ranges of date and its code like:

|data|code|data2|code|

My query is:

SELECT DATA,CODE
FROM people
WHERE DATA >= ${data1} AND DATA <= ${data2}
GROUP BY DATA
ORDER BY DATA


What I did was trying to do 2 queries with differents variable but both return always the same range of data.
So I did something like:

SELECT DATA,CODE
FROM people
WHERE DATA >= ${d1} AND DATA <= ${d2}
GROUP BY DATA
ORDER BY DATA


and try to assign 4 differents date in order to get 2 ranges of period. Let's imagine
data1='01-01-2001'
and
data2='31-12-2001'
while
d1='01-01-2002'
and
d2='31-12-2002'
.

When I assigned the dates, both return only the last range.

So instead of getting |2001|code|2002|code| I've got |2002|code|2002|code|

I need for comparison, so I want to compare every day of the year 2001 on the left and with every day of the year 2002 on the right.

MT0 MT0
Answer

Using the bind variables :start1 and :end1 as the bounds for the first range and :start2 and :end2 as the bounds for the second range:

SELECT d1.data AS data1,
       d1.code AS code1,
       d2.data AS data2,
       d2.code AS code2
FROM   (
  SELECT data,
         code,
         ROW_NUMBER() OVER ( ORDER BY data ) AS rn
  FROM   people
  WHERE  data BETWEEN :start1 AND :end1
) d1
FULL OUTER JOIN
(
  SELECT data,
         code,
         ROW_NUMBER() OVER ( ORDER BY data ) AS rn
  FROM   people
  WHERE  data BETWEEN :start2 AND :end2
) d2
ON ( d1.rn = d2.rn )