Post reply

SQL QUERY problem

  • May 20, 2005 at 7:56 am

    #88512

    Hi I am quite new to sql and have a problem with this query

    Select DateTime as DATE, CallTypeID as skillgrp,CallsOfferedHalf AS offered 

    From dbo.t_Call_Type_Half_Hour  where (CallTypeID=5002) AND (CONVERT(CHAR(11),DateTime) LIKE CONVERT(CHAR(11),GETDATE()))

    The above works, however i need to add up all the numbers returned in the offered colum.

    Any idea how i can do this?

     

     

  • May 20, 2005 at 7:58 am

    #560200

    Is this what you need?

    Select DateTime as DATE, CallTypeID as skillgrp,SUM(CallsOfferedHalf) AS offered

    From dbo.t_Call_Type_Half_Hour where (CallTypeID=5002) AND (CONVERT(CHAR(11),DateTime) LIKE CONVERT(CHAR(11),GETDATE()))

    GROUP BY DateTime, CallTypeID

  • May 21, 2005 at 1:00 pm

    #560392

    Assuming your "DateTime" column is a data type of datetime then using a function such as (CONVERT(CHAR(11),DateTime) will not allow the optimizer to use any index that may be on that colulmn.

    So for better performance put an index on the column "DateTime" and use something like this that removes the time portion from Getdate().

    ... where (CallTypeID=5002)

    AND DateTime >= Dateadd(d, datediff(d, 0, Getdate()), 0)

    AND DateTime < Dateadd(d, datediff(d, 0, Getdate()), 1) ...

    For a full explanation and a good article on working with sql server dates see:

    http://www.sql-server-performance.com/fk_datetime.asp

     

     

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply