Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2011
    Posts
    6

    Unanswered: Microsoft SQL Having clause problem while attempting a Left Join

    Hello all,

    I'm trying to combine a "having clause" to a piece of SQL code I already have written but I'm getting an incorrect syntax error near "Group by". I've attached the piece of code I'm having trouble with. If anyone could let me know what might be wrong I'd really appreciate it!


    Left Join
    (
    SELECT

    m.DayofWeek,
    Count(Distinct [Route #]) As [mLO]

    FROM HISTORY.dbo.tbl_Masterdata

    Where
    Week_Ending >= @Begin_weekend and week_ending <= @End_weekend
    AND Facility = @Facility
    AND Ledgend = @Ledgend
    AND [Route #] IN
    (
    SELECT DISTINCT [Route #]
    FROM TBL_MasterData
    WHERE
    Week_Ending >= @Begin_weekend and week_ending <= @End_weekend
    AND Facility = @Facility
    AND Ledgend = @Ledgend
    --AND Cust_# = @Cust_#
    AND Chain = @Chain
    )) m

    group by DayofWeek
    having sum(Distance) > 500


    ) n

    On n.DayofWeek = h.iDayofWeek
    Order By iDayofWeek

  2. #2
    Join Date
    Sep 2011
    Location
    Greenville, SC USA
    Posts
    34

    RE: Microsoft SQL Having clause problem while attempting a Left Join

    From what you provided, could you build upon the following suggestion?

    Code:
    LEFT JOIN
    (
      SELECT
        m.DayofWeek
       ,Count(Distinct m.[Route #]) As [mLO]
       ,sum(m.Distance) as [Total Distance]
      FROM HISTORY.dbo.TBL_Masterdata m (nolock)
      WHERE
      m.Week_Ending >= @Begin_weekend and m.Week_Ending <= @End_weekend
      AND m.Facility = @Facility
      AND m.Ledgend = @Ledgend
      AND m.[Route #] IN
      (
        SELECT DISTINCT [Route #] 
        FROM dbo.TBL_MasterData (nolock)
        WHERE
        Week_Ending >= @Begin_weekend and Week_Ending <= @End_weekend
        AND Facility = m.Facility
        AND Ledgend = m.Ledgend
        -- AND Cust_# = m.Cust_#
        AND Chain = m.Chain
      )
      GROUP BY m.DayofWeek
      HAVING sum(m.Distance) > 500
    ) n
    ON n.DayofWeek = h.iDayofWeek
    ORDER BY h.iDayofWeek

  3. #3
    Join Date
    Sep 2011
    Posts
    71
    syntax error near "Group by"
    please imwild12 ,Write the complete error message ,so we can detect your error exactly
    Nive?

  4. #4
    Join Date
    Jul 2011
    Posts
    6
    Here is the entire message:

    Server: Msg 156, Level 15, State 1, Line 211
    Incorrect syntax near the keyword 'group'.

    and here is my entire code...thanks everyone!



    declare @Begin_weekend char(50)
    declare @End_weekend char(50)
    declare @Facility char(50)
    declare @Ledgend char(50)
    --declare @Cust_# char(50)
    declare @Chain char(50)

    -- ****SET YOUR PARAMETERS HERE**** --

    set @Begin_weekend = 'we091011'
    set @End_weekend = 'we091011'
    set @Facility = 'California'
    set @Ledgend = 'DELIVERED'
    --set @Cust_# = '12345'
    set @Chain = 'WEST COAST STORES'


    SELECT
    iDayofWeek,
    IsNull(kLoads, 0) As [Total Loads],
    IsNull(kStops, 0) AS [Total Stops],
    IsNull(iStops, 0) AS [Chain Stops],
    IsNull(kVolume, 0) As [Total Cube],
    IsNull(iVolume, 0) As [Chain Cube],
    IsNull(kPieces, 0) As [Total Pieces],
    IsNull(iPieces, 0) As [Chain Pieces],
    IsNull(kPallets, 0) As [Total Pallets],
    IsNull(iPallets, 0) As [Chain Pallets],
    Round(IsNull([kRaw_Miles], 0), 0) AS [Total Miles],

    IsNull([kSPL],0) AS [Total SPL],
    IsNull([iSPL],0) AS [Chain SPL],
    IsNull([kCuPL],0) AS [Total CuPL],
    IsNull([iCuPL],0) AS [Chain CuPL],
    IsNull([kCaPL],0) AS [Total CaPL],
    IsNull([iCaPL],0) AS [Chain CaPL],
    IsNull([kPPL],0) AS [Total PPL],
    IsNull([iPPL],0) AS [Chain PPL],
    Round(IsNull([kMPL],0),0) AS [Total MPL],
    IsNull([mLO],0) AS [One Layover]


    --IsNull(iWeight, 0) As [Chain Weight],
    --IsNull(kWeight, 0) As [Total Weight],
    --IsNull([iTotal_Time], 0) AS [Chain Total Time],
    --IsNull([kTotal_Time], 0) AS [Total Total Time],
    --IsNull([iWPL],0) AS [Chain WPL],
    --IsNull([kWPL],0) AS [Total WPL]

    FROM (Select One As iDayofWeek from (SELECT 1 AS One, 2 AS two, 3 As three, 4 As four, 5 As five, 6 As six, 7 As Seven ) a
    Union All Select Two FROM (SELECT 1 AS One, 2 AS two, 3 As three, 4 As four, 5 As five, 6 As six, 7 As Seven ) b
    Union All Select Three FROM (SELECT 1 AS One, 2 AS two, 3 As three, 4 As four, 5 As five, 6 As six, 7 As Seven ) c
    Union All Select Four FROM (SELECT 1 AS One, 2 AS two, 3 As three, 4 As four, 5 As five, 6 As six, 7 As Seven ) d
    Union All Select Five FROM (SELECT 1 AS One, 2 AS two, 3 As three, 4 As four, 5 As five, 6 As six, 7 As Seven ) e
    Union All Select Six FROM (SELECT 1 AS One, 2 AS two, 3 As three, 4 As four, 5 As five, 6 As six, 7 As Seven ) f
    Union All Select Seven FROM (SELECT 1 AS One, 2 AS two, 3 As three, 4 As four, 5 As five, 6 As six, 7 As Seven ) g) h

    Left Join
    (

    SELECT i.DayofWeek,
    Count(Distinct [Route #])AS iLoads,
    Count(i.Stops) AS iStops,
    Sum(i.Volume) AS iVolume,
    Sum(i.Pieces) AS iPieces,
    Sum(i.Pallets) As iPallets,
    Sum(i.Distance)*1.1 AS [iRaw_Miles],
    Sum(i.Weight) As iWeight,
    Sum(i.TotalMin) AS [iTotal_Time],

    Sum((i.Distance)*1.1)/Count(Distinct [Route #]) AS [iMPL],
    Count(i.Stops)/Count(Distinct [Route #]) AS [iSPL],
    Sum(i.Pallets)/Count(Distinct [Route #]) AS [iPPL],
    Sum(i.Pieces)/Count(Distinct [Route #]) AS [iCaPL],
    Sum(i.Volume)/Count(Distinct [Route #]) AS [iCuPL],
    Sum(i.Weight)/Count(Distinct [Route #]) AS [iWPL]

    FROM (

    SELECT
    Stops,
    Cust_#,
    Chain,
    Distance,
    Weight,
    Pieces,
    [Route #]+[Route Group] As [Route #],
    Volume,
    Pallets,
    [Route Group],

    Right([Route Group], 2) AS DayNum,
    DatePArt(Weekday, Dispatch) AS DayofWeek,
    Dispatch,
    [ReturnDateTime],
    DateDiff(minute, Dispatch, ReturnDateTime) AS TotalMin

    FROM HISTORY.dbo.tbl_Masterdata

    Where
    Week_Ending >= @Begin_weekend and week_ending <= @End_weekend
    AND Facility = @Facility
    AND Ledgend = @Ledgend
    --AND Cust_# = @Cust_#
    AND Chain = @Chain

    ) i

    GROUP BY DayofWeek
    ) j

    On j.DayofWeek = h.iDayofWeek

    Left Join
    (

    SELECT k.DayofWeek,
    Count(Distinct [Route #])AS kLoads,
    Count(k.Stops) AS kStops,
    Sum(k.TotalMin) AS [kTotal_Time],
    Sum(k.Distance)*1.1 AS [kRaw_Miles],
    Sum(k.Weight) As kWeight,
    Sum(k.Volume) AS kVolume,
    Sum(k.Pieces) AS kPieces,
    Sum(k.Pallets) As kPallets,
    Count(k.Stops)/Count(Distinct [Route #]) AS [kSPL],
    Sum(k.Pallets)/Count(Distinct [Route #]) AS [kPPL],
    Sum(k.Pieces)/Count(Distinct [Route #]) AS [kCaPL],
    Sum(k.Volume)/Count(Distinct [Route #]) AS [kCuPL],
    Sum(k.Weight)/Count(Distinct [Route #]) AS [kWPL],
    Sum((k.Distance)*1.1)/Count(Distinct [Route #]) AS [kMPL]

    FROM (

    SELECT
    Stops,
    Cust_#,
    Chain,
    Distance,
    Weight,
    Pieces,
    [Route #]+[Route Group] As [Route #],
    Volume,
    Pallets,
    [Route Group],

    Right([Route Group], 2) AS DayNum,
    DatePArt(Weekday, Dispatch) AS DayofWeek,
    Dispatch,
    [ReturnDateTime],
    DateDiff(minute, Dispatch, ReturnDateTime) AS TotalMin

    FROM HISTORY.dbo.tbl_Masterdata

    Where
    Week_Ending >= @Begin_weekend and week_ending <= @End_weekend
    AND Facility = @Facility
    AND Ledgend = @Ledgend
    AND [Route #] IN
    (
    SELECT DISTINCT [Route #]
    FROM TBL_MasterData
    WHERE
    Week_Ending >= @Begin_weekend and week_ending <= @End_weekend
    AND Facility = @Facility
    AND Ledgend = @Ledgend
    --AND Cust_# = @Cust_#
    AND Chain = @Chain


    )) k

    GROUP BY DayofWeek
    ) l

    On l.DayofWeek = h.iDayofWeek






    --****LAYOVER CALCULATIONS****--

    Left Join
    (

    SELECT
    m.DayofWeek,
    Count(Distinct [Route #]) As [mLO]


    FROM HISTORY.dbo.tbl_Masterdata

    Where
    Week_Ending >= @Begin_weekend and week_ending <= @End_weekend
    AND Facility = @Facility
    AND Ledgend = @Ledgend
    AND [Route #] IN
    (
    SELECT DISTINCT [Route #]
    FROM TBL_MasterData
    WHERE
    Week_Ending >= @Begin_weekend and week_ending <= @End_weekend
    AND Facility = @Facility
    AND Ledgend = @Ledgend
    --AND Cust_# = @Cust_#
    AND Chain = @Chain
    )) m

    group by DayofWeek

    having sum(Distance) > 500
    ) n

    On n.DayofWeek = h.iDayofWeek

    Order By iDayofWeek

  5. #5
    Join Date
    Mar 2009
    Location
    Australia
    Posts
    113
    You should indent your code to make the nested layers more readable.

    From what I've been able to discern I believe you have a superfluous closing bracket at the end of your last left join, before the group by. You're aliasing as m the nested sub-query inside your where clause which you cannot do. From what I gather you should be aliasing the table name in the from clause, i.e. "FROM HISTORY.dbo.tbl_Masterdata m"

    Re-writing to get around these issues your last left join should be:
    Code:
    Left Join
    		(
    
    			SELECT 
    			m.DayofWeek,
    			Count(Distinct [Route #]) As [mLO]
    
    
    			FROM HISTORY.dbo.tbl_Masterdata m
    
    			Where
    			Week_Ending >= @Begin_weekend and week_ending <= @End_weekend
    			AND Facility = @Facility
    			AND Ledgend = @Ledgend
    			AND [Route #] IN 
    			(
    				SELECT DISTINCT [Route #] 
    				FROM TBL_MasterData
    				WHERE
    				Week_Ending >= @Begin_weekend and week_ending <= @End_weekend
    				AND Facility = @Facility
    				AND Ledgend = @Ledgend
    				--AND Cust_# = @Cust_# 
    				AND Chain = @Chain 
    			)
    
    			group by DayofWeek
    
    			having sum(Distance) > 500
    		) n
    
    	On n.DayofWeek = h.iDayofWeek

  6. #6
    Join Date
    Jul 2011
    Posts
    6

    I'm a little confused...

    I see what you're saying but it doesn't exactly make sense...when I run the query by itself I have no problems at all. It's just when I try to left join the results that I'm having an issue. For instance, the following portion works perfectly...it's when I try to add it in where it fails.


    select
    week_ending,
    --chain,
    --[Route #],
    Count(Distinct [Route #]) As [1 Layover]

    from TBL_MasterData

    WHERE
    --Substring(Week_Ending,7,2) = '11'
    Week_Ending = 'WE092411'
    AND Facility IN ('California')
    AND (Ledgend LIKE 'DEL')
    AND [Route #] IN
    (
    SELECT DISTINCT [Route #]
    FROM TBL_MasterData
    WHERE
    --Substring(Week_Ending,7,2) = '11'
    Week_Ending = 'WE092411'
    AND Facility IN ('California')
    AND (Ledgend LIKE 'DEL')
    AND (Chain Like '%Chain%')
    )
    group by
    Week_Ending
    --[Route #],
    --Chain
    having sum(Distance) > 500

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Following the parentheses in the first post, the group by is outside the parentheses for the subquery. SQL Server can not tell where subquery m ends and n begins:
    Code:
    Left Join
    (SELECT m.DayofWeek,
    	Count(Distinct [Route #]) As [mLO]
     FROM HISTORY.dbo.tbl_Masterdata
     Where Week_Ending >= @Begin_weekend 
       and week_ending <= @End_weekend
       AND Facility = @Facility
       AND Ledgend = @Ledgend
       AND [Route #] IN (
    	SELECT DISTINCT [Route #]
    	FROM TBL_MasterData
    	WHERE Week_Ending >= @Begin_weekend 
    	  and week_ending <= @End_weekend
    	  AND Facility = @Facility
    	  AND Ledgend = @Ledgend
    	  --AND Cust_# = @Cust_#
    	  AND Chain = @Chain)
     ) m
    
    group by DayofWeek
    having sum(Distance) > 500
    
    ) n
    
    On n.DayofWeek = h.iDayofWeek
    Order By iDayofWeek
    Last edited by MCrowley; 11-01-11 at 15:38. Reason: Fixed code tag

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •