[[gluglu]]

Gracias a todos por vuestros comentarios, pero sobre todo a Caro.

Justo ahora había encontrado ahora esa misma solución en otro sitio en Internet.

Lo he probabo ya y funciona !


Para el que quiere ver mi consulta completa, tal y como quedó :

Código SQL [-]
Select Q_SELA.OUT_DATE, Q_SELA.INTERNALNO, Q_SELA.WEEKEND,
       Q_SELA.CATEGORYNO, Q_SELA.SUBCATEGORYNO, Q_SELA.VIRTUALNO,
       Q_SELA.APPLYON, Q_SELA.REAL_ALLOTMENT_CLOSE, Q_SELA.QUANTITY
from
  (Select OUT_DATE, INTERNALNO, WEEKEND,
          CATEGORYNO, SUBCATEGORYNO, VIRTUALNO,
          1 as APPLYON, REAL_ALLOTMENT_CLOSE, QUANTITY
   from
     (Select Count(*) as NUM_HITS, OUT_DATE, INTERNALNO, WEEKEND,
             CATEGORYNO, SUBCATEGORYNO, VIRTUALNO,
             APPLYON, REAL_ALLOTMENT_CLOSE, QUANTITY
      from
        (Select AID.OUT_DATE,
                AOL.INTERNALNO, AOL.WEEKEND,
                AOL.CATEGORYNO, AOL.SUBCATEGORYNO, Coalesce(AOL.VIRTUALNO, 0) as VIRTUALNO,
                AOL.APPLYON, AOL.REAL_ALLOTMENT_CLOSE, AOL.QUANTITY
         from AVAIL_ONLINE AOL
         left join AUTO_INC_DATE('03-24-2012', 3) AID
           on 1 = 1
         left join SEASONS_DATES SD
           on SD.CODENAME = AOL.SEASONCODE
           and AID.OUT_DATE between SD.DATEFROM and SD.DATETO
         left join AVAIL_ONLINE_CONDITIONS AOC
           on AOC.INTERNALNO = AOL.INTERNALNO
         left join AVAIL_CALC_TEMP ACT
           on ACT.RANDOMNO    = 1
           and ACT.CATEGORYNO    = AOC.CATEGORYNO
           and ACT.SUBCATEGORYNO = Coalesce(AOC.SUBCATEGORYNO, 0)
           and ACT.INTV_AVAIL    = Coalesce(AOC.AVAILABILITYGRP, 0)
           and ACT.DATE_AVAIL    = AID.OUT_DATE
         left join FIXEDVALUES FX
           on FX.BUILDINGNO = 2
           and AID.OUT_DATE between FX.DATEFROM and FX.DATETO
         where AOL.BUILDINGNO  = 2
         and AOL.CATEGORYNO    = 1
         and ((AOL.DATEFROM   <= '03-26-2012'
               and AOL.DATETO >= '03-24-2012')
              or (AOL.SEASONCODE in
                  (Select Distinct(CODENAME) from SEASONS_DATES SD
                   where SD.DATEFROM <= '03-26-2012'
                   and SD.DATETO     >= '03-24-2012'
                  and SD.BUILDINGNO  = 2)))
         and ((AID.OUT_DATE between AOL.DATEFROM and AOL.DATETO) or
              (AID.OUT_DATE between SD.DATEFROM and SD.DATETO))
         and ((Q_ANTIRELEASE is Null) or
              (TYPE_Q_ANTIRELEASE = 1 and AID.OUT_DATE >= Current_Date + Q_ANTIRELEASE) or
              (TYPE_Q_ANTIRELEASE = 2 and AID.OUT_DATE >= Current_Date + (Q_ANTIRELEASE*7)))
         and ((Q_RELEASE is Null) or
              (TYPE_Q_RELEASE = 1 and AID.OUT_DATE <= Current_Date + Q_RELEASE) or
              (TYPE_Q_RELEASE = 2 and AID.OUT_DATE <= Current_Date + (Q_RELEASE*7)))
         and ((AOC.LESS_MORE_THAN = 1 and ACT.NUM_OBJECTS - ACT.NUM_LOCKED - ACT.NUM_BOOKINGS <= AOC.QUANTITY) or
              (AOC.LESS_MORE_THAN = 2 and ACT.NUM_OBJECTS - ACT.NUM_LOCKED - ACT.NUM_BOOKINGS >= AOC.QUANTITY))
         and (AOL.WEEKEND = 0 or (AOL.WEEKEND = 1 and Position(Cast(Extract(WeekDay from AID.OUT_DATE-1)+1 as CHAR) in FX.WEEKEND_DAYS) <> 0)))
      group by OUT_DATE, INTERNALNO, WEEKEND, CATEGORYNO, SUBCATEGORYNO,
               VIRTUALNO, APPLYON, REAL_ALLOTMENT_CLOSE, QUANTITY) Q_SEL
      where NUM_HITS = (Select Count(*) from AVAIL_ONLINE_CONDITIONS AOC2
                        where AOC2.INTERNALNO = Q_SEL.INTERNALNO)
      and Position('1' in Q_SEL.APPLYON) <> 0
   union
   Select OUT_DATE, INTERNALNO, WEEKEND,
          CATEGORYNO, SUBCATEGORYNO, VIRTUALNO,
          2 as APPLYON, REAL_ALLOTMENT_CLOSE, QUANTITY
   from
     (Select Count(*) as NUM_HITS, OUT_DATE, INTERNALNO, WEEKEND,
             CATEGORYNO, SUBCATEGORYNO, VIRTUALNO,
             APPLYON, REAL_ALLOTMENT_CLOSE, QUANTITY
      from
        (Select AID.OUT_DATE,
                AOL.INTERNALNO, AOL.WEEKEND,
                AOL.CATEGORYNO, AOL.SUBCATEGORYNO, Coalesce(AOL.VIRTUALNO, 0) as VIRTUALNO,
                AOL.APPLYON, AOL.REAL_ALLOTMENT_CLOSE, AOL.QUANTITY
         from AVAIL_ONLINE AOL
         left join AUTO_INC_DATE('03-24-2012', 3) AID
           on 1 = 1
         left join SEASONS_DATES SD
           on SD.CODENAME = AOL.SEASONCODE
           and AID.OUT_DATE between SD.DATEFROM and SD.DATETO
         left join AVAIL_ONLINE_CONDITIONS AOC
           on AOC.INTERNALNO = AOL.INTERNALNO
         left join AVAIL_CALC_TEMP ACT
           on ACT.RANDOMNO    = 1
           and ACT.CATEGORYNO    = AOC.CATEGORYNO
           and ACT.SUBCATEGORYNO = Coalesce(AOC.SUBCATEGORYNO, 0)
           and ACT.INTV_AVAIL    = Coalesce(AOC.AVAILABILITYGRP, 0)
           and ACT.DATE_AVAIL    = AID.OUT_DATE
         left join FIXEDVALUES FX
           on FX.BUILDINGNO = 2
           and AID.OUT_DATE between FX.DATEFROM and FX.DATETO
         where AOL.BUILDINGNO  = 2
         and AOL.CATEGORYNO    = 1
         and ((AOL.DATEFROM   <= '03-26-2012'
               and AOL.DATETO >= '03-24-2012')
              or (AOL.SEASONCODE in
                  (Select Distinct(CODENAME) from SEASONS_DATES SD
                   where SD.DATEFROM <= '03-26-2012'
                   and SD.DATETO     >= '03-24-2012'
                   and SD.BUILDINGNO  = 2)))
         and ((AID.OUT_DATE between AOL.DATEFROM and AOL.DATETO) or
              (AID.OUT_DATE between SD.DATEFROM and SD.DATETO))
         and ((Q_ANTIRELEASE is Null) or
              (TYPE_Q_ANTIRELEASE = 1 and AID.OUT_DATE >= Current_Date + Q_ANTIRELEASE) or
              (TYPE_Q_ANTIRELEASE = 2 and AID.OUT_DATE >= Current_Date + (Q_ANTIRELEASE*7)))
         and ((Q_RELEASE is Null) or
              (TYPE_Q_RELEASE = 1 and AID.OUT_DATE <= Current_Date + Q_RELEASE) or
              (TYPE_Q_RELEASE = 2 and AID.OUT_DATE <= Current_Date + (Q_RELEASE*7)))
         and ((AOC.LESS_MORE_THAN = 1 and ACT.NUM_OBJECTS - ACT.NUM_LOCKED - ACT.NUM_BOOKINGS <= AOC.QUANTITY) or
              (AOC.LESS_MORE_THAN = 2 and ACT.NUM_OBJECTS - ACT.NUM_LOCKED - ACT.NUM_BOOKINGS >= AOC.QUANTITY))
         and (AOL.WEEKEND = 0 or (AOL.WEEKEND = 1 and Position(Cast(Extract(WeekDay from AID.OUT_DATE-1)+1 as CHAR) in FX.WEEKEND_DAYS) <> 0)))
      group by OUT_DATE, INTERNALNO, WEEKEND, CATEGORYNO, SUBCATEGORYNO,
               VIRTUALNO, APPLYON, REAL_ALLOTMENT_CLOSE, QUANTITY) Q_SEL
      where NUM_HITS = (Select Count(*) from AVAIL_ONLINE_CONDITIONS AOC2
                        where AOC2.INTERNALNO = Q_SEL.INTERNALNO)
      and Position('2' in Q_SEL.APPLYON) <> 0) Q_SELA
left join
  (Select OUT_DATE, CATEGORYNO, SUBCATEGORYNO, VIRTUALNO, APPLYON,
          Max(WEEKEND) as WEEKEND
   from
     (Select OUT_DATE, INTERNALNO, WEEKEND,
             CATEGORYNO, SUBCATEGORYNO, VIRTUALNO,
             1 as APPLYON, REAL_ALLOTMENT_CLOSE, QUANTITY
      from
        (Select Count(*) as NUM_HITS, OUT_DATE, INTERNALNO, WEEKEND,
                CATEGORYNO, SUBCATEGORYNO, VIRTUALNO,
                APPLYON, REAL_ALLOTMENT_CLOSE, QUANTITY
         from
           (Select AID.OUT_DATE,
                   AOL.INTERNALNO, AOL.WEEKEND,
                   AOL.CATEGORYNO, AOL.SUBCATEGORYNO, Coalesce(AOL.VIRTUALNO, 0) as VIRTUALNO,
                   AOL.APPLYON, AOL.REAL_ALLOTMENT_CLOSE, AOL.QUANTITY
            from AVAIL_ONLINE AOL
            left join AUTO_INC_DATE('03-24-2012', 3) AID
              on 1 = 1
            left join SEASONS_DATES SD
              on SD.CODENAME = AOL.SEASONCODE
              and AID.OUT_DATE between SD.DATEFROM and SD.DATETO
            left join AVAIL_ONLINE_CONDITIONS AOC
              on AOC.INTERNALNO = AOL.INTERNALNO
            left join AVAIL_CALC_TEMP ACT
              on ACT.RANDOMNO    = 1
              and ACT.CATEGORYNO    = AOC.CATEGORYNO
              and ACT.SUBCATEGORYNO = Coalesce(AOC.SUBCATEGORYNO, 0)
              and ACT.INTV_AVAIL    = Coalesce(AOC.AVAILABILITYGRP, 0)
              and ACT.DATE_AVAIL    = AID.OUT_DATE
            left join FIXEDVALUES FX
              on FX.BUILDINGNO = 2
              and AID.OUT_DATE between FX.DATEFROM and FX.DATETO
            where AOL.BUILDINGNO  = 2
            and AOL.CATEGORYNO    = 1
            and ((AOL.DATEFROM   <= '03-26-2012'
                  and AOL.DATETO >= '03-24-2012')
                 or (AOL.SEASONCODE in
                     (Select Distinct(CODENAME) from SEASONS_DATES SD
                      where SD.DATEFROM <= '03-26-2012'
                      and SD.DATETO     >= '03-24-2012'
                      and SD.BUILDINGNO  = 2)))
            and ((AID.OUT_DATE between AOL.DATEFROM and AOL.DATETO) or
                 (AID.OUT_DATE between SD.DATEFROM and SD.DATETO))
            and ((Q_ANTIRELEASE is Null) or
                 (TYPE_Q_ANTIRELEASE = 1 and AID.OUT_DATE >= Current_Date + Q_ANTIRELEASE) or
                 (TYPE_Q_ANTIRELEASE = 2 and AID.OUT_DATE >= Current_Date + (Q_ANTIRELEASE*7)))
            and ((Q_RELEASE is Null) or
                 (TYPE_Q_RELEASE = 1 and AID.OUT_DATE <= Current_Date + Q_RELEASE) or
                 (TYPE_Q_RELEASE = 2 and AID.OUT_DATE <= Current_Date + (Q_RELEASE*7)))
            and ((AOC.LESS_MORE_THAN = 1 and ACT.NUM_OBJECTS - ACT.NUM_LOCKED - ACT.NUM_BOOKINGS <= AOC.QUANTITY) or
                 (AOC.LESS_MORE_THAN = 2 and ACT.NUM_OBJECTS - ACT.NUM_LOCKED - ACT.NUM_BOOKINGS >= AOC.QUANTITY))
            and (AOL.WEEKEND = 0 or (AOL.WEEKEND = 1 and Position(Cast(Extract(WeekDay from AID.OUT_DATE-1)+1 as CHAR) in FX.WEEKEND_DAYS) <> 0)))
         group by OUT_DATE, INTERNALNO, WEEKEND, CATEGORYNO, SUBCATEGORYNO,
                  VIRTUALNO, APPLYON, REAL_ALLOTMENT_CLOSE, QUANTITY) Q_SEL
         where NUM_HITS = (Select Count(*) from AVAIL_ONLINE_CONDITIONS AOC2
                           where AOC2.INTERNALNO = Q_SEL.INTERNALNO)
         and Position('1' in Q_SEL.APPLYON) <> 0
      union
      Select OUT_DATE, INTERNALNO, WEEKEND,
             CATEGORYNO, SUBCATEGORYNO, VIRTUALNO,
             2 as APPLYON, REAL_ALLOTMENT_CLOSE, QUANTITY
      from
        (Select Count(*) as NUM_HITS, OUT_DATE, INTERNALNO, WEEKEND,
                CATEGORYNO, SUBCATEGORYNO, VIRTUALNO,
                APPLYON, REAL_ALLOTMENT_CLOSE, QUANTITY
         from
           (Select AID.OUT_DATE,
                   AOL.INTERNALNO, AOL.WEEKEND,
                   AOL.CATEGORYNO, AOL.SUBCATEGORYNO, Coalesce(AOL.VIRTUALNO, 0) as VIRTUALNO,
                   AOL.APPLYON, AOL.REAL_ALLOTMENT_CLOSE, AOL.QUANTITY
            from AVAIL_ONLINE AOL
            left join AUTO_INC_DATE('03-24-2012', 3) AID
              on 1 = 1
            left join SEASONS_DATES SD
              on SD.CODENAME = AOL.SEASONCODE
              and AID.OUT_DATE between SD.DATEFROM and SD.DATETO
            left join AVAIL_ONLINE_CONDITIONS AOC
              on AOC.INTERNALNO = AOL.INTERNALNO
            left join AVAIL_CALC_TEMP ACT
              on ACT.RANDOMNO    = 1
              and ACT.CATEGORYNO    = AOC.CATEGORYNO
              and ACT.SUBCATEGORYNO = Coalesce(AOC.SUBCATEGORYNO, 0)
              and ACT.INTV_AVAIL    = Coalesce(AOC.AVAILABILITYGRP, 0)
              and ACT.DATE_AVAIL    = AID.OUT_DATE
            left join FIXEDVALUES FX
              on FX.BUILDINGNO = 2
              and AID.OUT_DATE between FX.DATEFROM and FX.DATETO
            where AOL.BUILDINGNO  = 2
            and AOL.CATEGORYNO    = 1
            and ((AOL.DATEFROM   <= '03-26-2012'
                  and AOL.DATETO >= '03-24-2012')
                 or (AOL.SEASONCODE in
                     (Select Distinct(CODENAME) from SEASONS_DATES SD
                      where SD.DATEFROM <= '03-26-2012'
                      and SD.DATETO     >= '03-24-2012'
                      and SD.BUILDINGNO  = 2)))
            and ((AID.OUT_DATE between AOL.DATEFROM and AOL.DATETO) or
                 (AID.OUT_DATE between SD.DATEFROM and SD.DATETO))
            and ((Q_ANTIRELEASE is Null) or
                 (TYPE_Q_ANTIRELEASE = 1 and AID.OUT_DATE >= Current_Date + Q_ANTIRELEASE) or
                 (TYPE_Q_ANTIRELEASE = 2 and AID.OUT_DATE >= Current_Date + (Q_ANTIRELEASE*7)))
            and ((Q_RELEASE is Null) or
                 (TYPE_Q_RELEASE = 1 and AID.OUT_DATE <= Current_Date + Q_RELEASE) or
                 (TYPE_Q_RELEASE = 2 and AID.OUT_DATE <= Current_Date + (Q_RELEASE*7)))
            and ((AOC.LESS_MORE_THAN = 1 and ACT.NUM_OBJECTS - ACT.NUM_LOCKED - ACT.NUM_BOOKINGS <= AOC.QUANTITY) or
                 (AOC.LESS_MORE_THAN = 2 and ACT.NUM_OBJECTS - ACT.NUM_LOCKED - ACT.NUM_BOOKINGS >= AOC.QUANTITY))
            and (AOL.WEEKEND = 0 or (AOL.WEEKEND = 1 and Position(Cast(Extract(WeekDay from AID.OUT_DATE-1)+1 as CHAR) in FX.WEEKEND_DAYS) <> 0)))
         group by OUT_DATE, INTERNALNO, WEEKEND, CATEGORYNO, SUBCATEGORYNO,
                  VIRTUALNO, APPLYON, REAL_ALLOTMENT_CLOSE, QUANTITY) Q_SEL
         where NUM_HITS = (Select Count(*) from AVAIL_ONLINE_CONDITIONS AOC2
                           where AOC2.INTERNALNO = Q_SEL.INTERNALNO)
         and Position('2' in Q_SEL.APPLYON) <> 0)
   Group by OUT_DATE, CATEGORYNO, SUBCATEGORYNO, VIRTUALNO, APPLYON) Q_SELB
on Q_SELA.OUT_DATE       = Q_SELB.OUT_DATE
and Q_SELA.CATEGORYNO    = Q_SELB.CATEGORYNO
and Q_SELA.SUBCATEGORYNO = Q_SELB.SUBCATEGORYNO
and Q_SELA.VIRTUALNO     = Q_SELB.VIRTUALNO
and Q_SELA.APPLYON       = Q_SELB.APPLYON
and Q_SELA.WEEKEND       = Q_SELB.WEEKEND
where Q_SELA.OUT_DATE    = Q_SELB.OUT_DATE
and Q_SELA.CATEGORYNO    = Q_SELB.CATEGORYNO
and Q_SELA.SUBCATEGORYNO = Q_SELB.SUBCATEGORYNO
and Q_SELA.VIRTUALNO     = Q_SELB.VIRTUALNO
and Q_SELA.APPLYON       = Q_SELB.APPLYON
and Q_SELA.WEEKEND       = Q_SELB.WEEKEND