Help creating a SELECT statement for "today"

Hello,

I am attempting to build a MS SQL query that will return data from
"today"; today being current day 8:00AM-10:00PM today. My goal is to
return the data from a table that is written to throughout the day, the
query will provide the current grade of service in our call center.

I am having difficulty defining my where clause:

- I can accomplish my goal my statically defining my 'date between' as
the actual date and time (not ideal)

- I can accomplish the second part of my date using CURRENT_TIMESTAMP;
but I am unable to define the starting point

Here is where I am thus far:

/* We are going to count the total calls into each queue from start of
business today (8:00AM) to now */

select COUNT(Result) as "Total Sales Calls Offered" from
dbo.QueueEncounter
where Direction='0'
and
QueueID='1631'
and
/* This is where I get lost */
Time between DATEPART(day, GETDATE()) and DATEPART(day, GETDATE())

Clearly the last line returns zero as there are no calls between the
same date range. How can I add to that line, or write this to work?

Any thoughts?

Thanks for the help.

-ChrisOn 25 Jan 2006 10:03:53 -0800, Chris wrote:

>Hello,
>I am attempting to build a MS SQL query that will return data from
>"today"; today being current day 8:00AM-10:00PM today. My goal is to
>return the data from a table that is written to throughout the day, the
>query will provide the current grade of service in our call center.
>I am having difficulty defining my where clause:
>- I can accomplish my goal my statically defining my 'date between' as
>the actual date and time (not ideal)
>- I can accomplish the second part of my date using CURRENT_TIMESTAMP;
>but I am unable to define the starting point
>Here is where I am thus far:
>/* We are going to count the total calls into each queue from start of
>business today (8:00AM) to now */
>select COUNT(Result) as "Total Sales Calls Offered" from
>dbo.QueueEncounter
>where Direction='0'
>and
>QueueID='1631'
>and
>/* This is where I get lost */
>Time between DATEPART(day, GETDATE()) and DATEPART(day, GETDATE())
>Clearly the last line returns zero as there are no calls between the
>same date range. How can I add to that line, or write this to work?
>Any thoughts?
>Thanks for the help.
>-Chris

Hi Chris,

You say you want rows for today, 8:00AM-10:00PM. Does this imply that
the table also contains rows outside the 8:00AM-10:00PM time frame that
you don't want to include?

AND Time BETWEEN DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP),
'8:00AM')
AND DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP),
'10:00PM')

Note that this will include a row with time exactly equal to 10 PM, but
exclude a row with time 3 milliseconds after 10PM.

If you want all rows for the whole day (0:00 - 24:00), use
AND Time >= DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 0)
AND Time < DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 1)

--
Hugo Kornelis, SQL Server MVP|||Awesome Hugo, thanks so much for the help - My query is now nearly
complete; with one last problem....

declare @.today datetime,
@.tomorrow datetime,
@.offered smallint,
@.answeredin120 smallint,
@.GOS smallint

set @.today = convert(char(8), GETDATE ( ), 112)
set @.tomorrow = @.today + 1

-- Find total calls offered
set @.offered = (select COUNT(Result) from dbo.QueueEncounter
where Direction='0' and QueueID='1438' and Time >= @.today and Time <
@.tomorrow)

-- Find total calls answered in 120 seconds
set @.answeredin120 =(select COUNT(Result) from dbo.QueueEncounter
where Direction='0' and QueueID='1438' and Time >= @.today and Time <
@.tomorrow and WaitTime <= 120)

-- Divide the total calls offered by the total calls answered in X
multiplied by 100 to find current GOS ??

set @.GOS = (@.offered)/(@.answeredin120)*100

select @.GOS

The problem is my GOS is being returned as 100 when it is really apprx
77%.
Where did I go wrong?

-Thanks!|||On 25 Jan 2006 14:12:48 -0800, Chris wrote:

(snip)
>-- Divide the total calls offered by the total calls answered in X
>multiplied by 100 to find current GOS ??
>set @.GOS = (@.offered)/(@.answeredin120)*100
>select @.GOS
>The problem is my GOS is being returned as 100 when it is really apprx
>77%.
>Where did I go wrong?

Hi Chris,

Integer division: divide two integers, the result is integer too.

SELECT 1/3
SELECT 1.0/3
SELECT 1/3.0
SELECT 1.0/3.0

The above show that forcing at least one operand to non-integer suffices
to get a result with fraction. In your case, one possible way would be

SET @.GOS = CAST(@.offered AS decimal(10,2)) / @.answeredin120 * 100

Or even

SET @.GOS = 100.0 * @.offerec / @.answeredin120

--
Hugo Kornelis, SQL Server MVP|||Hugo, Again many thanks... I will try this at the office tomorrow.

Cheers.