Enrico Enrico - 1 year ago 56
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 Source

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 )