SQL Server 2005/2008 sum(qty) results multiplied on join

I'm left joining 2 tables: T1 has a list of all items ordered and T2 has a list of all items shipped. I'm looking for a result set that will show the qty of all items ordered from T1 and the corresponding shipped qty from T2.

When I check T1 for the date/order/item/qty info for one order, it looks like this:

DATE                  |ORDER    |ITEM  |DESCRIPTION|QTY|CUSTOMER
01/01/2011 12:00:00 AM|123456789|123456|shoes      |1  |JANE
01/01/2011 12:00:00 AM|123456789|234567|shirt      |2  |TIM
01/01/2011 12:00:00 AM|123456789|345678|pants      |4  |JOE
01/01/2011 12:00:00 AM|123456789|123456|shoes      |9  |BOB

T2 looks like this:

ORDER    |ITEM  |QTYSHIPPED|SHIPPED
123456789|123456|1         |01/10/2011 12:00:00 PM
123456789|234567|2         |01/10/2011 12:00:00 PM
123456789|345678|4         |01/10/2011 12:00:00 PM
123456789|123456|9         |01/10/2011 12:00:00 PM

My query is as follows:

select convert(varchar,a.date,101) as orderdate, a.order, a.item, a.description, sum(a.qty) as qty_ordered, convert(varchar,b.shipped,101) as shippeddate sum(b.qtyshipped) as qtyshipped
from T1 a --T1 is table with all items ordered
left join shipped T2 --T2 contains order #, qty shipped and shipped date
on a.order = b.order
group by convert(varchar,a.date,101), a.order, a.item, a.description, b.shipped

The results look like this:

orderdate|order|item|description|qty_ordered|shippeddate|qtyshipped
01/01/2011|123456789|123456|20|01/10/2011|20
01/01/2011|123456789|234567|4|01/10/2011|20
01/01/2011|123456789|345678|8|01/10/2011|20

The results I would like to see would b开发者_StackOverflow社区e as below:

orderdate|order|item|description|qty_ordered|shippeddate|qtyshipped
01/01/2011|123456789|123456|10|01/10/2011|20
01/01/2011|123456789|234567|2|01/10/2011|20
01/01/2011|123456789|345678|4|01/10/2011|20

Any info and help would be appreciated!

I think you need to also join on item:

on a.order = b.order, a.item = b.item

WITH OrderDays AS (
  SELECT *, OrderDate = DATEADD(day, DATEDIFF(day, 0, DATE), 0)
  FROM T1
),
ShippingDays AS (
  SELECT *, ShippingDate = DATEADD(day, DATEDIFF(day, 0, SHIPPED), 0)
  FROM T2
),
OrdersGrouped AS (
  SELECT
    OrderDate,
    [ORDER],
    ITEM,
    DESCRIPTION,
    Qty = SUM(QTY)
  FROM OrderDays
  GROUP BY OrderDate, [ORDER], ITEM, DESCRIPTION
),
ShippingsGrouped AS (
  SELECT
    ShippingDate,
    [ORDER],
    ITEM,
    QtyShipped = SUM(QTYHSHIPPED)
  FROM OrderDays
  GROUP BY ShippingDate, [ORDER], ITEM
)
SELECT
  o.*,
  s.ShippingDate,
  s.QtyShipped
FROM OrdersGrouped o
  LEFT JOIN ShippingsGrouped s ON o.[ORDER] = s.[ORDER] AND o.ITEM = s.ITEM

Same item shippings that happened on different days will be grouped (and displayed) separately. If you want them grouped together, just modify the ShippingGrouped CTE above like this:

…
ShippingsGrouped AS (
  SELECT
    ShippingDate = MAX(ShippingDate),
    [ORDER],
    ITEM,
    QtyShipped = SUM(QTYHSHIPPED)
  FROM OrderDays
  GROUP BY [ORDER], ITEM
)
…

The ShippingDate column will contain the corresponding item's last shipping date.