Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2003
    Posts
    1

    Unanswered: SPROC help needed

    Hi,

    I have two tables as below:

    CableDrums
    -DrumID
    -CableType
    -OriginalLength
    -ArrivalDate

    SubDeliveries
    -DeliveryID
    -DrumID
    -DeliveredLength

    The tables are related via DrumID (PrimaryKey in CableDrums).

    I want to create a SPROC that give me the following info:
    DrumID, CableType, OriginalLength, RemainingLength (=OriginalLength - (the sum of all DeliveredLength for the specific DrumID) as well as last date and remaining days for last SubDelivery (Based on maximum of ArrivaDate + 210 days). I have tried the following but it does not work:

    SELECT CableDrums.DrumNo, CableDrums.CableType, CableDrums.OriginalLength, (CableDrums.Length - SUM(SubDeliveries.DeliveredLength)) AS RemainingLength, CableDrums.ArrivalDate, (DateAdd (day, 210, CableDrums.ArrivalDate)) As LastDate, (Datediff (day, GetDate(), DateAdd (day, 210, CableDrums.ArrivalDate))) as RemainingDays

    FROM CableDrums LEFT OUTER JOIN SubDeliveries ON CableDrums.DrumNo = SubDeliveries.DrumNo

    GROUP BY CableDrums.DrumNo, CableDrums.OriginalLength

    Somehow it seems the SUM-function does not work with the Date-functions since they work separately. But I want the info together since I am feeding a custom ASP.NET control which I can not change.

    Any help appreciated!!

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Any fields in the return set which are not an aggregate functin need to be include in the group by clause. It seems like the optimizer ought to know this by default, but you must include them explicitly like this:

    SELECT CableDrums.DrumNo,
    CableDrums.CableType,
    CableDrums.OriginalLength,
    (CableDrums.Length - SUM(SubDeliveries.DeliveredLength)) AS RemainingLength,
    CableDrums.ArrivalDate,
    (DateAdd (day, 210, CableDrums.ArrivalDate)) As LastDate,
    (Datediff (day, GetDate(), DateAdd (day, 210, CableDrums.ArrivalDate))) as RemainingDays
    FROM CableDrums
    LEFT OUTER JOIN SubDeliveries ON CableDrums.DrumNo = SubDeliveries.DrumNo
    GROUP BY CableDrums.DrumNo,
    CableDrums.CableType,
    CableDrums.OriginalLength,
    CableDrums.ArrivalDate,
    DateAdd (day, 210, CableDrums.ArrivalDate),
    Datediff (day, GetDate(), DateAdd (day, 210, CableDrums.ArrivalDate))

    blindman

Posting Permissions

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