Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 помогите, плиз  [new]
Артем_sql
Member

Откуда:
Сообщений: 27
Есть сумасшедший запрос:
SELECT     PACKAGE.PACKAGESHORTNAME COLLATE Cyrillic_General_CI_AS AS code, CONVERT(VARCHAR(10), 
                      PACKAGE_PRICE.PACKAGEPRICEFROMDATE, 105) AS Expr1, CONVERT(VARCHAR(10), PACKAGE_PRICE.PACKAGEPRICETODATE, 105) AS Expr2, 
                      PACKAGE_ELEMENT.PACKAGEELEMENTID , 
                      OCCUPANCY_TYPE.OCCUPANCYTYPEID, PACKAGE_PRICE.PACKAGEPRICEAMOUNT, 
                      TourIndexAssignedExtraBedID.PackageElementExtraID AS extra_id, PACKAGE_PRICE_1.PACKAGEPRICEAMOUNT AS extraprice, 
                      Package_Child_Rate.PackageChildRateFromAge, Package_Child_Rate.PackageChildRateToAge, Package_Child_Rate.PackageChildRateAmount, 
                      CURRENCY.CURRENCYSYMBOL
FROM         Package_Child_Rate RIGHT OUTER JOIN
                      PACKAGE INNER JOIN
                      PACKAGE_ELEMENT ON PACKAGE.PACKAGEID = PACKAGE_ELEMENT.PACKAGEID INNER JOIN
                      SERVICE_TYPE_OPTION ON PACKAGE_ELEMENT.SERVICETYPEOPTIONID = SERVICE_TYPE_OPTION.SERVICETYPEOPTIONID INNER JOIN
                      PACKAGE_PRICE ON PACKAGE_ELEMENT.PACKAGEELEMENTID = PACKAGE_PRICE.PACKAGEELEMENTID INNER JOIN
                      CURRENCY ON PACKAGE_PRICE.CURRENCYID = CURRENCY.CURRENCYID INNER JOIN
                      ASSIGNED_OCCUPANCY ON SERVICE_TYPE_OPTION.SERVICETYPEOPTIONID = ASSIGNED_OCCUPANCY.SERVICETYPEOPTIONID INNER JOIN
                      OCCUPANCY_TYPE ON ASSIGNED_OCCUPANCY.OCCUPANCYTYPEID = OCCUPANCY_TYPE.OCCUPANCYTYPEID LEFT OUTER JOIN
                      PACKAGE_PRICE AS PACKAGE_PRICE_1 INNER JOIN
                      TourIndexAssignedExtraBedID ON PACKAGE_PRICE_1.PACKAGEELEMENTID = TourIndexAssignedExtraBedID.PackageElementParentID ON 
                      PACKAGE_PRICE.PACKAGEPRICEFROMDATE = PACKAGE_PRICE_1.PACKAGEPRICEFROMDATE AND 
                      PACKAGE_ELEMENT.PACKAGEELEMENTID = TourIndexAssignedExtraBedID.PackageElementParentID ON 
                      Package_Child_Rate.PackagePriceID = PACKAGE_PRICE.PACKAGEPRICEID
WHERE     (PACKAGE.PACKAGESHORTNAME IN ('USA_DOM14D_13N', 'PUJ11SUOGPC')) AND 
(PACKAGE_PRICE.PACKAGEPRICEINTERNETAVAILABLE = 1) AND 
                      (PACKAGE_PRICE.PACKAGEPRICEVALIDATED = 1) AND (PACKAGE.PACKAGESTATUSID = 1) AND 
                      (PACKAGE_PRICE.PACKAGEPRICETODATE >= CONVERT(DATETIME, '2011-10-26 0:00:00', 102)) AND 
                      (PACKAGE_PRICE.PACKAGEPRICEFROMDATE <= CONVERT(DATETIME, '2011-11-27 0:00:00', 102)) AND
				       (ASSIGNED_OCCUPANCY.OCCUPANCYTYPEID = 7) AND
						(Package_Child_Rate.PackageChildRateFromAge <= 13) AND (Package_Child_Rate.PackageChildRateToAge>=13)AND (PACKAGE_ELEMENT.PACKAGEELEMENTID NOT IN
                          (SELECT     PackageElementExtraID
                            FROM          TourIndexAssignedExtraBedID AS TourIndexAssignedExtraBedID_1)) AND (PACKAGE_PRICE.PACKAGEPRICEVALIDATED = 1)

 
ORDER BY PACKAGE.PACKAGESHORTNAME, PACKAGE_PRICE.PACKAGEPRICEFROMDATE, PACKAGE_PRICE.PACKAGEPRICEAMOUNT


Результат запроса в приложенном файле.

Нужно вывести первую запись по уникальному ключу CODE, Expr1

К сообщению приложен файл. Размер - 74Kb
27 окт 11, 14:29    [11509273]     Ответить | Цитировать Сообщить модератору
 Re: помогите, плиз  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
select
    *
from (
    select
        *
        , rn = row_number() over ( partition by CODE, Expr1 order by <поле, задающее порядок для "Нужно вывести первую запись"> )
    from (
    SELECT     PACKAGE.PACKAGESHORTNAME COLLATE Cyrillic_General_CI_AS AS code, CONVERT(VARCHAR(10), 
                          PACKAGE_PRICE.PACKAGEPRICEFROMDATE, 105) AS Expr1, CONVERT(VARCHAR(10), PACKAGE_PRICE.PACKAGEPRICETODATE, 105) AS Expr2, 
                          PACKAGE_ELEMENT.PACKAGEELEMENTID , 
                          OCCUPANCY_TYPE.OCCUPANCYTYPEID, PACKAGE_PRICE.PACKAGEPRICEAMOUNT, 
                          TourIndexAssignedExtraBedID.PackageElementExtraID AS extra_id, PACKAGE_PRICE_1.PACKAGEPRICEAMOUNT AS extraprice, 
                          Package_Child_Rate.PackageChildRateFromAge, Package_Child_Rate.PackageChildRateToAge, Package_Child_Rate.PackageChildRateAmount, 
                          CURRENCY.CURRENCYSYMBOL
    FROM         Package_Child_Rate RIGHT OUTER JOIN
                          PACKAGE INNER JOIN
                          PACKAGE_ELEMENT ON PACKAGE.PACKAGEID = PACKAGE_ELEMENT.PACKAGEID INNER JOIN
                          SERVICE_TYPE_OPTION ON PACKAGE_ELEMENT.SERVICETYPEOPTIONID = SERVICE_TYPE_OPTION.SERVICETYPEOPTIONID INNER JOIN
                          PACKAGE_PRICE ON PACKAGE_ELEMENT.PACKAGEELEMENTID = PACKAGE_PRICE.PACKAGEELEMENTID INNER JOIN
                          CURRENCY ON PACKAGE_PRICE.CURRENCYID = CURRENCY.CURRENCYID INNER JOIN
                          ASSIGNED_OCCUPANCY ON SERVICE_TYPE_OPTION.SERVICETYPEOPTIONID = ASSIGNED_OCCUPANCY.SERVICETYPEOPTIONID INNER JOIN
                          OCCUPANCY_TYPE ON ASSIGNED_OCCUPANCY.OCCUPANCYTYPEID = OCCUPANCY_TYPE.OCCUPANCYTYPEID LEFT OUTER JOIN
                          PACKAGE_PRICE AS PACKAGE_PRICE_1 INNER JOIN
                          TourIndexAssignedExtraBedID ON PACKAGE_PRICE_1.PACKAGEELEMENTID = TourIndexAssignedExtraBedID.PackageElementParentID ON 
                          PACKAGE_PRICE.PACKAGEPRICEFROMDATE = PACKAGE_PRICE_1.PACKAGEPRICEFROMDATE AND 
                          PACKAGE_ELEMENT.PACKAGEELEMENTID = TourIndexAssignedExtraBedID.PackageElementParentID ON 
                          Package_Child_Rate.PackagePriceID = PACKAGE_PRICE.PACKAGEPRICEID
    WHERE     (PACKAGE.PACKAGESHORTNAME IN ('USA_DOM14D_13N', 'PUJ11SUOGPC')) AND 
    (PACKAGE_PRICE.PACKAGEPRICEINTERNETAVAILABLE = 1) AND 
                          (PACKAGE_PRICE.PACKAGEPRICEVALIDATED = 1) AND (PACKAGE.PACKAGESTATUSID = 1) AND 
                          (PACKAGE_PRICE.PACKAGEPRICETODATE >= CONVERT(DATETIME, '2011-10-26 0:00:00', 102)) AND 
                          (PACKAGE_PRICE.PACKAGEPRICEFROMDATE <= CONVERT(DATETIME, '2011-11-27 0:00:00', 102)) AND
				           (ASSIGNED_OCCUPANCY.OCCUPANCYTYPEID = 7) AND
						    (Package_Child_Rate.PackageChildRateFromAge <= 13) AND (Package_Child_Rate.PackageChildRateToAge>=13)AND (PACKAGE_ELEMENT.PACKAGEELEMENTID NOT IN
                              (SELECT     PackageElementExtraID
                                FROM          TourIndexAssignedExtraBedID AS TourIndexAssignedExtraBedID_1)) AND (PACKAGE_PRICE.PACKAGEPRICEVALIDATED = 1)
    ) a
) a
where
    a.rn = 1
ORDER BY PACKAGE.PACKAGESHORTNAME, PACKAGE_PRICE.PACKAGEPRICEFROMDATE, PACKAGE_PRICE.PACKAGEPRICEAMOUNT
27 окт 11, 14:34    [11509308]     Ответить | Цитировать Сообщить модератору
 Re: помогите, плиз  [new]
Артем_sql
Member

Откуда:
Сообщений: 27
Гавриленко Сергей Алексеевич,
Супер! Огромное спасибо!

но..
запрос пришлось немного изменить, так как выдавало ошибку.

в итоге используется такой запрос:

select
    *
from (
    select
        *
        , rn = row_number() over ( partition by CODE, Expr1 order by price )
    from (
    SELECT     PACKAGE.PACKAGESHORTNAME COLLATE Cyrillic_General_CI_AS AS code, CONVERT(VARCHAR(10), 
                          PACKAGE_PRICE.PACKAGEPRICEFROMDATE, 105) AS Expr1, CONVERT(VARCHAR(10), PACKAGE_PRICE.PACKAGEPRICETODATE, 105) AS Expr2, 
                          PACKAGE_ELEMENT.PACKAGEELEMENTID , 
                          OCCUPANCY_TYPE.OCCUPANCYTYPEID, PACKAGE_PRICE.PACKAGEPRICEAMOUNT as price, 
                          TourIndexAssignedExtraBedID.PackageElementExtraID AS extra_id, PACKAGE_PRICE_1.PACKAGEPRICEAMOUNT AS extraprice, 
                          Package_Child_Rate.PackageChildRateFromAge, Package_Child_Rate.PackageChildRateToAge, Package_Child_Rate.PackageChildRateAmount, 
                          CURRENCY.CURRENCYSYMBOL
    FROM         Package_Child_Rate RIGHT OUTER JOIN
                          PACKAGE INNER JOIN
                          PACKAGE_ELEMENT ON PACKAGE.PACKAGEID = PACKAGE_ELEMENT.PACKAGEID INNER JOIN
                          SERVICE_TYPE_OPTION ON PACKAGE_ELEMENT.SERVICETYPEOPTIONID = SERVICE_TYPE_OPTION.SERVICETYPEOPTIONID INNER JOIN
                          PACKAGE_PRICE ON PACKAGE_ELEMENT.PACKAGEELEMENTID = PACKAGE_PRICE.PACKAGEELEMENTID INNER JOIN
                          CURRENCY ON PACKAGE_PRICE.CURRENCYID = CURRENCY.CURRENCYID INNER JOIN
                          ASSIGNED_OCCUPANCY ON SERVICE_TYPE_OPTION.SERVICETYPEOPTIONID = ASSIGNED_OCCUPANCY.SERVICETYPEOPTIONID INNER JOIN
                          OCCUPANCY_TYPE ON ASSIGNED_OCCUPANCY.OCCUPANCYTYPEID = OCCUPANCY_TYPE.OCCUPANCYTYPEID LEFT OUTER JOIN
                          PACKAGE_PRICE AS PACKAGE_PRICE_1 INNER JOIN
                          TourIndexAssignedExtraBedID ON PACKAGE_PRICE_1.PACKAGEELEMENTID = TourIndexAssignedExtraBedID.PackageElementParentID ON 
                          PACKAGE_PRICE.PACKAGEPRICEFROMDATE = PACKAGE_PRICE_1.PACKAGEPRICEFROMDATE AND 
                          PACKAGE_ELEMENT.PACKAGEELEMENTID = TourIndexAssignedExtraBedID.PackageElementParentID ON 
                          Package_Child_Rate.PackagePriceID = PACKAGE_PRICE.PACKAGEPRICEID
    WHERE     (PACKAGE.PACKAGESHORTNAME IN ('USA_DOM14D_13N')) AND 
    (PACKAGE_PRICE.PACKAGEPRICEINTERNETAVAILABLE = 1) AND 
                          (PACKAGE_PRICE.PACKAGEPRICEVALIDATED = 1) AND (PACKAGE.PACKAGESTATUSID = 1) AND 
                          (PACKAGE_PRICE.PACKAGEPRICETODATE >= CONVERT(DATETIME, '2011-10-26 0:00:00', 102)) AND 
                          (PACKAGE_PRICE.PACKAGEPRICEFROMDATE <= CONVERT(DATETIME, '2011-11-27 0:00:00', 102)) AND
               (ASSIGNED_OCCUPANCY.OCCUPANCYTYPEID = 1) AND
       --   (Package_Child_Rate.PackageChildRateFromAge <= 13) AND (Package_Child_Rate.PackageChildRateToAge>=13) AND 
(PACKAGE_ELEMENT.PACKAGEELEMENTID NOT IN
                              (SELECT     PackageElementExtraID
                                FROM          TourIndexAssignedExtraBedID AS TourIndexAssignedExtraBedID_1)) AND (PACKAGE_PRICE.PACKAGEPRICEVALIDATED = 1)
 
   ) a
) a
where
    a.rn = 1
   ORDER BY code, Expr1

Но при выводе не сортируется по Expr1.

прописываю в строке:
partition by CODE, Expr1 order by price, Expr1

результат тот же..
выводит по датам: 15-11-2011, 19-11-2011, 18-10-2011
27 окт 11, 15:01    [11509571]     Ответить | Цитировать Сообщить модератору
 Re: помогите, плиз  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
partition by CODE, Expr1 order by price, Expr1 - это только для нумерации сортировка.

Чтобы сортировать набор, надо в финальный order by писать нужное выражение.
27 окт 11, 15:06    [11509621]     Ответить | Цитировать Сообщить модератору
 Re: помогите, плиз  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Артем_sql
Но при выводе не сортируется по Expr1.

прописываю в строке:
partition by CODE, Expr1 order by price, Expr1

результат тот же..
выводит по датам: 15-11-2011, 19-11-2011, 18-10-2011
Небось, все CODE различные?
А по Expr1 сортируется только тогда, когда CODE оказались равными
27 окт 11, 15:09    [11509648]     Ответить | Цитировать Сообщить модератору
 Re: помогите, плиз  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
iap
Артем_sql
Но при выводе не сортируется по Expr1.

прописываю в строке:
partition by CODE, Expr1 order by price, Expr1

результат тот же..
выводит по датам: 15-11-2011, 19-11-2011, 18-10-2011
Небось, все CODE различные?
А по Expr1 сортируется только тогда, когда CODE оказались равными
Для rn=1, я имею в виду
27 окт 11, 15:10    [11509657]     Ответить | Цитировать Сообщить модератору
 Re: помогите, плиз  [new]
Артем_sql
Member

Откуда:
Сообщений: 27
Гавриленко Сергей Алексеевич,

я понял в чем дело..

Все дело в
CONVERT(VARCHAR(10), PACKAGE_PRICE.PACKAGEPRICEFROMDATE, 105)
Если убрать convert, то все норм сортирует.

в итоге сделал так:
 select
 code,
    CONVERT(VARCHAR(10), Expr1, 105) as DateFrom, 
    CONVERT(VARCHAR(10), Expr2, 105) as DateTo, 
 PACKAGEELEMENTID
 OCCUPANCYTYPEID,
 price,
 extra_id,
 extraprice,
 PackageChildRateFromAge, 
 PackageChildRateToAge,
 PackageChildRateAmount, 
    CURRENCYSYMBOL 
from (
    select
        *
        , rn = row_number() over ( partition by CODE, Expr1 order by price )
    from (
    SELECT     PACKAGE.PACKAGESHORTNAME COLLATE Cyrillic_General_CI_AS AS code, 
                          PACKAGE_PRICE.PACKAGEPRICEFROMDATE AS Expr1, 
        PACKAGE_PRICE.PACKAGEPRICETODATE AS Expr2, 
                          PACKAGE_ELEMENT.PACKAGEELEMENTID as PACKAGEELEMENTID, 
                          OCCUPANCY_TYPE.OCCUPANCYTYPEID as OCCUPANCYTYPEID,
        PACKAGE_PRICE.PACKAGEPRICEAMOUNT as price, 
                          TourIndexAssignedExtraBedID.PackageElementExtraID AS extra_id, 
        PACKAGE_PRICE_1.PACKAGEPRICEAMOUNT AS extraprice, 
                          Package_Child_Rate.PackageChildRateFromAge as PackageChildRateFromAge, 
        Package_Child_Rate.PackageChildRateToAge as PackageChildRateToAge, 
        Package_Child_Rate.PackageChildRateAmount as PackageChildRateAmount, 
                          CURRENCY.CURRENCYSYMBOL as CURRENCYSYMBOL
    FROM         Package_Child_Rate RIGHT OUTER JOIN
                          PACKAGE INNER JOIN
                          PACKAGE_ELEMENT ON PACKAGE.PACKAGEID = PACKAGE_ELEMENT.PACKAGEID INNER JOIN
                          SERVICE_TYPE_OPTION ON PACKAGE_ELEMENT.SERVICETYPEOPTIONID = SERVICE_TYPE_OPTION.SERVICETYPEOPTIONID INNER JOIN
                          PACKAGE_PRICE ON PACKAGE_ELEMENT.PACKAGEELEMENTID = PACKAGE_PRICE.PACKAGEELEMENTID INNER JOIN
                          CURRENCY ON PACKAGE_PRICE.CURRENCYID = CURRENCY.CURRENCYID INNER JOIN
                          ASSIGNED_OCCUPANCY ON SERVICE_TYPE_OPTION.SERVICETYPEOPTIONID = ASSIGNED_OCCUPANCY.SERVICETYPEOPTIONID INNER JOIN
                          OCCUPANCY_TYPE ON ASSIGNED_OCCUPANCY.OCCUPANCYTYPEID = OCCUPANCY_TYPE.OCCUPANCYTYPEID LEFT OUTER JOIN
                          PACKAGE_PRICE AS PACKAGE_PRICE_1 INNER JOIN
                          TourIndexAssignedExtraBedID ON PACKAGE_PRICE_1.PACKAGEELEMENTID = TourIndexAssignedExtraBedID.PackageElementParentID ON 
                          PACKAGE_PRICE.PACKAGEPRICEFROMDATE = PACKAGE_PRICE_1.PACKAGEPRICEFROMDATE AND 
                          PACKAGE_ELEMENT.PACKAGEELEMENTID = TourIndexAssignedExtraBedID.PackageElementParentID ON 
                          Package_Child_Rate.PackagePriceID = PACKAGE_PRICE.PACKAGEPRICEID
    WHERE     (PACKAGE.PACKAGESHORTNAME IN ('USA_DOM14D_13N')) AND 
    (PACKAGE_PRICE.PACKAGEPRICEINTERNETAVAILABLE = 1) AND 
                          (PACKAGE_PRICE.PACKAGEPRICEVALIDATED = 1) AND (PACKAGE.PACKAGESTATUSID = 1) AND 
                          (PACKAGE_PRICE.PACKAGEPRICETODATE >= CONVERT(DATETIME, '2011-10-26 0:00:00', 102)) AND 
                          (PACKAGE_PRICE.PACKAGEPRICEFROMDATE <= CONVERT(DATETIME, '2011-11-27 0:00:00', 102)) AND
               (ASSIGNED_OCCUPANCY.OCCUPANCYTYPEID = 3) AND
     --   (Package_Child_Rate.PackageChildRateFromAge <= 13) AND (Package_Child_Rate.PackageChildRateToAge>=13) AND 
(PACKAGE_ELEMENT.PACKAGEELEMENTID NOT IN
                              (SELECT     PackageElementExtraID
                                FROM          TourIndexAssignedExtraBedID AS TourIndexAssignedExtraBedID_1)) AND (PACKAGE_PRICE.PACKAGEPRICEVALIDATED = 1)
 
   ) a
) a
where
    a.rn = 1
27 окт 11, 15:32    [11509931]     Ответить | Цитировать Сообщить модератору
 Re: помогите, плиз  [new]
Артем_sql
Member

Откуда:
Сообщений: 27
Артем_sql
 select
 code,
    CONVERT(VARCHAR(10), Expr1, 105) as DateFrom, 
    CONVERT(VARCHAR(10), Expr2, 105) as DateTo, 
 PACKAGEELEMENTID
 OCCUPANCYTYPEID,
 price,
 extra_id,
 extraprice,
 PackageChildRateFromAge, 
 PackageChildRateToAge,
 PackageChildRateAmount, 
    CURRENCYSYMBOL 
from (
    select
        *
        , rn = row_number() over ( partition by CODE, Expr1 order by price )
    from (
    SELECT     PACKAGE.PACKAGESHORTNAME COLLATE Cyrillic_General_CI_AS AS code, 
                          PACKAGE_PRICE.PACKAGEPRICEFROMDATE AS Expr1, 
        PACKAGE_PRICE.PACKAGEPRICETODATE AS Expr2, 
                          PACKAGE_ELEMENT.PACKAGEELEMENTID as PACKAGEELEMENTID, 
                          OCCUPANCY_TYPE.OCCUPANCYTYPEID as OCCUPANCYTYPEID,
        PACKAGE_PRICE.PACKAGEPRICEAMOUNT as price, 
                          TourIndexAssignedExtraBedID.PackageElementExtraID AS extra_id, 
        PACKAGE_PRICE_1.PACKAGEPRICEAMOUNT AS extraprice, 
                          Package_Child_Rate.PackageChildRateFromAge as PackageChildRateFromAge, 
        Package_Child_Rate.PackageChildRateToAge as PackageChildRateToAge, 
        Package_Child_Rate.PackageChildRateAmount as PackageChildRateAmount, 
                          CURRENCY.CURRENCYSYMBOL as CURRENCYSYMBOL
    FROM         Package_Child_Rate RIGHT OUTER JOIN
                          PACKAGE INNER JOIN
                          PACKAGE_ELEMENT ON PACKAGE.PACKAGEID = PACKAGE_ELEMENT.PACKAGEID INNER JOIN
                          SERVICE_TYPE_OPTION ON PACKAGE_ELEMENT.SERVICETYPEOPTIONID = SERVICE_TYPE_OPTION.SERVICETYPEOPTIONID INNER JOIN
                          PACKAGE_PRICE ON PACKAGE_ELEMENT.PACKAGEELEMENTID = PACKAGE_PRICE.PACKAGEELEMENTID INNER JOIN
                          CURRENCY ON PACKAGE_PRICE.CURRENCYID = CURRENCY.CURRENCYID INNER JOIN
                          ASSIGNED_OCCUPANCY ON SERVICE_TYPE_OPTION.SERVICETYPEOPTIONID = ASSIGNED_OCCUPANCY.SERVICETYPEOPTIONID INNER JOIN
                          OCCUPANCY_TYPE ON ASSIGNED_OCCUPANCY.OCCUPANCYTYPEID = OCCUPANCY_TYPE.OCCUPANCYTYPEID LEFT OUTER JOIN
                          PACKAGE_PRICE AS PACKAGE_PRICE_1 INNER JOIN
                          TourIndexAssignedExtraBedID ON PACKAGE_PRICE_1.PACKAGEELEMENTID = TourIndexAssignedExtraBedID.PackageElementParentID ON 
                          PACKAGE_PRICE.PACKAGEPRICEFROMDATE = PACKAGE_PRICE_1.PACKAGEPRICEFROMDATE AND 
                          PACKAGE_ELEMENT.PACKAGEELEMENTID = TourIndexAssignedExtraBedID.PackageElementParentID ON 
                          Package_Child_Rate.PackagePriceID = PACKAGE_PRICE.PACKAGEPRICEID
    WHERE     (PACKAGE.PACKAGESHORTNAME IN ('USA_DOM14D_13N')) AND 
    (PACKAGE_PRICE.PACKAGEPRICEINTERNETAVAILABLE = 1) AND 
                          (PACKAGE_PRICE.PACKAGEPRICEVALIDATED = 1) AND (PACKAGE.PACKAGESTATUSID = 1) AND 
                          (PACKAGE_PRICE.PACKAGEPRICETODATE >= CONVERT(DATETIME, '2011-10-26 0:00:00', 102)) AND 
                          (PACKAGE_PRICE.PACKAGEPRICEFROMDATE <= CONVERT(DATETIME, '2011-11-27 0:00:00', 102)) AND
               (ASSIGNED_OCCUPANCY.OCCUPANCYTYPEID = 3) AND
     --   (Package_Child_Rate.PackageChildRateFromAge <= 13) AND (Package_Child_Rate.PackageChildRateToAge>=13) AND 
(PACKAGE_ELEMENT.PACKAGEELEMENTID NOT IN
                              (SELECT     PackageElementExtraID
                                FROM          TourIndexAssignedExtraBedID AS TourIndexAssignedExtraBedID_1)) AND (PACKAGE_PRICE.PACKAGEPRICEVALIDATED = 1)
 
   ) a
) a
where
    a.rn = 1


ой, внизу строчка потерялась..:
   ORDER BY code, Expr1
27 окт 11, 15:35    [11509964]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить