Help creating a SELECT statement for "today"

[Chris]

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_TIMESTA MP;
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.QueueEncoun ter
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

[Hugo Kornelis]

On 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_TIMESTA MP;
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.QueueEncou nter
where Direction='0'
and
QueueID='163 1'
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_TIMESTA MP),
'8:00AM')
AND DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTA MP),
'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_TIMESTA MP), 0)
AND Time < DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTA MP), 1)

--
Hugo Kornelis, SQL Server MVP

[Chris]

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.QueueEncoun ter
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.QueueEncoun ter
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!

[Hugo Kornelis]

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

[Chris]

Hugo, Again many thanks... I will try this at the office tomorrow.

Cheers.