"Mark Moss" <markm***@adelphia.net> wrote in message
news:uIGEIFtqFHA.3720@TK2MSFTNGP14.phx.gbl...
> Gentlemen and Ladies
>
>        Below is the complete query that I have convert over from HQL to
> SQL
> and I am getting one last error which is
>
>        Server: Msg 170, Level 15, State 1 Line116
>
>        Line 52: Incorrect Syntax Near ')'
>
>        I am running lines 65 thru 115 when I get this error -- HELP -- I
> am
> going blind looking for the error
>
> Mark
>
> {========================================================}
>
>
> Drop View T0;
>
> Create View T0 as
>
> Select StoreID,
>       VendorNumber,
>       ItemNumber,
>       PLUNumber
>
> From   Item
>
> Where  StoreID = 151995
>  and  DepartmentCode = (1)
>
> /* -------------------------------------------------------------------------
> -- */
>
> Drop View T1A;
>
> Create View T1A as
>
> Select T1A.StoreID,
>       T1A.VendorNumber,
>       T1A.ItemNumber,
>       T1A.PLUNumber,
>       Min(LandedCost)                             AS  MinLandedCost,
>       Min(FeeOperating + FeeService + FeeFreight) AS  LowFees,
>       Max(LandedCost)                             AS  MaxLandedCost,
>       Max(FeeOperating + FeeService + FeeFreight) AS  HighFees,
>       Max(IDate)                                  AS  LastInvoiceDate,
>       Sum(PurchaseQty)                            AS  TotalPurchaseQty,
>       Sum(PurchaseQty * CasePack * SRP)           AS  ExtendedRetail,
>       Count(*)                                    AS  NumOfInv
>
> From Invoice as T1A, T0
>
> Where    T1A.StoreID      = T0.StoreID
>  and    T1A.VendorNumber = T0.VendorNumber
>  and    T1A.ItemNumber   = T0.ItemNumber
>  and    T1A.PLUNumber    = T0.PLUNumber
>
> Group By T1A.StoreID,
>         T1A.VendorNumber,
>         T1A.ItemNumber,
>         T1A.PLUNumber
>
> /* -------------------------------------------------------------------------
> -- */
>
> Drop View T1;
>
> Create View T1 as
>
> Select *,
>       ( MaxLandedCost - MinLandedCost)                        AS
> DiffCentsCost,
>       ( HighFees      - LowFees)                              AS
> DiffCentsFees,
>       ((MaxLandedCost - MinLandedCost) / ISNULL(NULLIF(MinLandedCost,
> 0.00), 1.00 )) * 100 AS DiffPercentCost,
>       ((HighFees      - LowFees)       / ISNULL(NULLIF(LowFees,
> 0.00), 1.00 )) * 100 AS DiffPercentFees
>
> From T1A
>
> /* -------------------------------------------------------------------------
> -- */
>
> Drop View T2;
>
> Create View T2 as
>
> Select   StoreID,
>         VendorNumber,
>         ItemNumber,
>         PLUNumber,
>         Brand,
>         [Description],
>         BaseCasePack,
>         SizeQuantity,
>         AvgWeeklyMovement,
>         InventoryTurns,
>         ( ( CurrentMargin / ( CASE ( CurrentMargin ) WHEN 100 THEN 1 ELSE
> ( 100 - CurrentMargin ) END ) ) * InventoryTurns ) AS ROII
>
> From (
>        Select  T2.StoreID,
>          T2.VendorNumber,
>          T2.ItemNumber,
>          T2.PLUNumber,
>          I3.Brand,
>          I3.[Description],
>          I3.BaseCasePack,
>          I3.SizeQuantity,
>          T2.AvgWeeklyMovement,
>          ( ( T2.AvgWeeklyMovement * 52 ) / ( CASE ( I3.BaseCasePack ) WHEN
> 0 THEN 1 ELSE I3.BaseCasePack END ) ) AS InventoryTurns,
>         I3.CurrentMargin,
>          I3.CurrentPrice
>
>        from    Item AS I3, (
>    Select  StoreID,
>             VendorNumber,
>             ItemNumber,
>             PLUNumber,
>             ( sum( POSMovement ) / ( ( DATEDIFF( day, min(TDate),
> max(TDate) ) + 1 ) / 7 ) ) AS AvgWeeklyMovement
>
>    From    Movement
>
>    Where   StoreID  = 151995
>      and   TDate   >= DATEADD( day, - ( 3 * 365 ), CurDate )  --  '- ( 7 *
> 26 )'
>      and   TDate   <= CurDate
>
>     Group By StoreID,
>            VendorNumber,
>            ItemNumber,
>            PLUNumber
>                            ) AS T2
> '--------- ERROR IS ON THIS LINE ----------'
>
> Where    I3.StoreID      = T2.StoreID
>  and    I3.VendorNumber = T2.VendorNumber
>  and    I3.ItemNum      = T2.ItemNumber
>  and    I3.PLUNum       = T2.PLUNumber
>     )
>
> /* -------------------------------------------------------------------------
> -- */
>
> Select   T1.StoreID,
>         T1.VendorNumber,
>         T1.ItemNumber,
>         T1.PLUNumber,
>         T2.Brand,
>         T2.[Description],
>         T2.BaseCasePack,
>         T2.SizeQuantity,
>         T2.AvgWeeklyMovement,
>         T2.InventoryTurns,
>         T2.ROII,
>         T1.MinLandedCost,
>         T1.LowFees,
>         T1.MaxLandedCost,
>         T1.HighFees,
>         T1.LastInvoiceDate,
>         T1.TotalPurchaseQty,
>         T1.ExtendedRetail,
>         T1.NumOfInv,
>         T1.DiffCentsCost,
>         T1.DiffCentsFees,
>         T1.DiffPercentCost,
>         T1.DiffPercentFees
>
> From T1 T1, T2 T2
>
> Where    T2.StoreID      = T1.StoreID
>  and    T2.VendorNumber = T1.VendorNumber
>  and    T2.ItemNumber   = T1.ItemNumber
>  and    T2.PLUNumber    = T1.PLUNumber
>
> Order By T1.DiffPercentCost;
>
>