grouping problem

21 Jul 2006 3:51 PM

Phill

I am trying to return a result set in a cross-tab format. My problem is that
my date field has the time, thus returning more than one record for date
instead of summing it. How can I fix this? This is my query:
    SELECT dbo.tblDrivers.First_Name + ' ' + dbo.tblDrivers.Last_Name AS Driver,
           dbo.tblDrivers.Dispatch_Number,

           CAST(DATEPART(DAY,dbo.tblOrders.DeliveryDateTime) AS VARCHAR) + '-' +
DATENAME(MONTH,dbo.tblOrders.DeliveryDateTime),
           DATENAME(dw,dbo.tblOrders.DeliveryDateTime),

           SUM(CASE WHEN
DATENAME(dw,dbo.tblOrders.DeliveryDateTime)='Thursday' THEN 1 END),

           SUM(CASE WHEN
DATENAME(dw,dbo.tblOrders.DeliveryDateTime)='Thursday' THEN Amount END)

           --COUNT(dbo.tblOrders.ID) AS Deliveries,
           --SUM(dbo.tblOrders.Amount) AS Money

    FROM dbo.tblOrders INNER JOIN
          dbo.tblDrivers ON dbo.tblOrders.Driver_ID = dbo.tblDrivers.Driver_ID

    GROUP BY dbo.tblDrivers.First_Name,
            dbo.tblDrivers.Last_Name,
            dbo.tblDrivers.Dispatch_Number,
            dbo.tblOrders.DeliveryDateTime

    HAVING dbo.tblOrders.DeliveryDateTime IS NOT NULL

21 Jul 2006 3:57 PM

Tracy McKibben

Phill wrote:
Show quote

> I am trying to return a result set in a cross-tab format. My problem is that
> my date field has the time, thus returning more than one record for date
> instead of summing it. How can I fix this? This is my query:
>     SELECT dbo.tblDrivers.First_Name + ' ' + dbo.tblDrivers.Last_Name AS Driver,
>            dbo.tblDrivers.Dispatch_Number,
>
>            CAST(DATEPART(DAY,dbo.tblOrders.DeliveryDateTime) AS VARCHAR) + '-' +
> DATENAME(MONTH,dbo.tblOrders.DeliveryDateTime),
>            DATENAME(dw,dbo.tblOrders.DeliveryDateTime),
>
>            SUM(CASE WHEN
> DATENAME(dw,dbo.tblOrders.DeliveryDateTime)='Thursday' THEN 1 END),
>
>            SUM(CASE WHEN
> DATENAME(dw,dbo.tblOrders.DeliveryDateTime)='Thursday' THEN Amount END)
>
>            --COUNT(dbo.tblOrders.ID) AS Deliveries,
>            --SUM(dbo.tblOrders.Amount) AS Money
>
>     FROM dbo.tblOrders INNER JOIN
>           dbo.tblDrivers ON dbo.tblOrders.Driver_ID = dbo.tblDrivers.Driver_ID
>
>     GROUP BY dbo.tblDrivers.First_Name,
>             dbo.tblDrivers.Last_Name,
>             dbo.tblDrivers.Dispatch_Number,
>             dbo.tblOrders.DeliveryDateTime
>
>     HAVING dbo.tblOrders.DeliveryDateTime IS NOT NULL

Look here for one method of dropping the time from a date/time value:
http://realsqlguy.com/twiki/bin/view/RealSQLGuy/TipsForWorkingWithDateTimeValues

--
Tracy McKibben
MCDBA
http://www.realsqlguy.com

21 Jul 2006 6:27 PM

Omnibuzz

try using this in group by instead of the datetime field and check..

convert(varchar(10),dbo.tblOrders.DeliveryDateTime ,101)

Hope this helps.

--
-Omnibuzz (The SQL GC)

http://omnibuzz-sql.blogspot.com/