Results 1 to 8 of 8

Thread: Help with Query

  1. #1
    Join Date
    Jan 2009
    Posts
    5

    Post Unanswered: Help with Query

    I have a query that returns records:
    Code:
    SELECT     CAST(detail_record.ticket_number AS int) AS TicketNumber, cmDelvTo.customer_number,  
                  cmBillTo.customer_number, CAST(detail_record.pickup_dt AS datetime) AS PickupDate, 
                  CAST(detail_record.deliver_dt AS datetime) AS DeliverDate,detail_record.division AS Division,
                  detail_record.pickup_weight AS PickupPounds, detail_record.ddp_weight AS LoadPounds,
                  detail_record.hauler_number AS HaulerNumber
    FROM       detail_record INNER JOIN
                  customer_master AS cmBillTo ON detail_record.customer_bill_to = cmBillTo.customer_number INNER JOIN
                  customer_master AS cmDelvTo ON detail_record.customer_delv_to = cmDelvTo.customer_number
    WHERE     (detail_record.pickup_date BETWEEN @start_date AND @end_date) AND (detail_record.customer_delv_to = @customer_number) OR
                  (detail_record.pickup_date BETWEEN @start_date AND @end_date) AND (detail_record.customer_bill_to = @customer_number)
    ORDER BY detail_record.pickup_dt
    and returns this record
    Click image for larger version. 

Name:	QueryLevelOne.JPG 
Views:	22 
Size:	151.1 KB 
ID:	9286
    Then I rewrote the query to this to get rid of the repeating Divisions:
    Code:
    SELECT     CAST(detail_record.ticket_number AS int) AS TicketNumber, cmDelvTo.customer_number AS DeliverToNumber, 
                  cmBillTo.customer_number AS BillToNumber, CAST(detail_record.pickup_dt AS datetime) AS PickupDate, 
                  CAST(detail_record.deliver_dt AS datetime) AS DeliverDate,detail_record.division AS Division,
                  SUM(detail_record.pickup_weight) AS PickupPounds,SUM(DISTINCT detail_record.ddp_weight) AS LoadPounds,
                  detail_record.hauler_number AS HaulerNumber
    FROM       detail_record INNER JOIN
                  customer_master AS cmBillTo ON detail_record.customer_bill_to = cmBillTo.customer_number INNER JOIN
                  customer_master AS cmDelvTo ON detail_record.customer_delv_to = cmDelvTo.customer_number
    WHERE     (detail_record.pickup_date BETWEEN @start_date AND @end_date) AND (detail_record.customer_delv_to = @customer_number) OR
                  (detail_record.pickup_date BETWEEN @start_date AND @end_date) AND (detail_record.customer_bill_to = @customer_number)
    GROUP BY cmBillTo.customer_number, cmDelvTo.customer_number, detail_record.pickup_dt, detail_record.ticket_number, 
                  detail_record.division, detail_record.deliver_dt, detail_record.hauler_number
    ORDER BY detail_record.pickup_dt
    And that returned this data:
    Click image for larger version. 

Name:	QueryLevelTwo.JPG 
Views:	22 
Size:	101.9 KB 
ID:	9287
    Now I need to gone step further and SUM the LoadPounds by PickupDate:

    example
    PickupDate PickupPounds LoadPounds
    2009-01-18 44590 64522
    2009-01-18 1157 64522
    2009-01-18 18775 64522

    LoadPounds 6452

    2009-01-31 12241 17749
    2009-01-31 5508 17749
    2009-01-31 24768 43076
    2009-01-31 18308 43076

    LoadPounds 60825


    Is there a way to do this that as a beginner I will understand

  2. #2
    Join Date
    Jan 2009
    Posts
    5
    Is no one interested in helping me figure this out?

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Well I'll try.

    Based on your sample data you want to sum each DISTINCT laodpounds value per date correct? There are no other criteria, or variations on this?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Jan 2009
    Posts
    5
    That is the way I was looking to solve this when I posted the question, this is a query for a report and all the fieids are correct except the LoadPounds. I was trying to create a tempory table and if that was possible I would use TicketNumber and the new SUM for LoadPounds.

    I am going to work on it today and was going to try to use alter table and try to add a column and put the new SUM of LoadPounds there.

    I appreciate the help, I am learning all of this on my first programming job and the SQL Server queries I am learning as I go. I have always used access and only basic queries before, nothing advanced at all.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jan 2009
    Posts
    5
    I wasn't aware that the 2 forums were connected, my apologies. Still doesn't mean I do not require help.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    They aren't connected as such, we just post links when we see cross posting. Otherwise one might waste a lot of effort on something that has already been tried\ discarded.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jan 2009
    Posts
    5
    It's not tried, discarded or resolved yet

Posting Permissions

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