One more question

[Delphin]

I have another dealing with this... realizing my SQL is not what I thought

I have a datetime field: start
1/11/2005 11:52
1/11/2005 14:45
total seconds: SEC
10
20
Agent: agent
Ben

I need to create a stored procedure based on a single date that will return to me the sum of the calls on a specific day and also the sum of the calls in a specific month.

I am having issues
select sum(Sec), Count(start) from calls where agent = 'Ben' and date

how do I do a like for the date and for the month to date based on a date like '1/11/05'?


Billy Ballard

For in the past, Lies the future.

 

[donutman]

To match a specific date:

where @ReportDate=dateadd(dd,datediff(dd,0,StartDate),0)

To match a month depends on what you mean by the month. Do you want the last 30-days or do you want any date in January or only January dates up until 1/11/05?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[jhall01]

in your where statement you will want to do:

WHERE
convert(varchar(10),date_col,101) = '01/11/2005'

This will give you all calls for that date.

as for month:

month(date_col) = month('01/11/2005')

 

[EdwinGene]

To match day use the following:


Create Procedure MyProc @in_CheckDate DateTime, @in_Agent VarChar(10)

as

Select
Sum(Sec),
Count(Start)
From
Calls
Where
Agent = @in_Agent
and
Convert(VarChar(10), Start, 111) = Convert(VarChar(10), @in_CheckDate, 111)



To change the check to month instead of day, simply use the "VarChar(7)" in the Convert() functions.

 

[EdwinGene]

A generic stored procedure might look like this:


Create Procedure MyProc @in_ChkDate DateTime, @in_Agent VarChar(10), @in_Period VarChar(5)

as

Declare @Len VarChar(2), @Command VarChar(1000), @CharChkDate VarChar(10)

Set @CharChkDate = LTrim(RTrim(Convert(VarChar(10), @in_ChkDate, 111)))

If @in_Period = 'day'
Set @Len = '10'
Else if @in_Period = 'month'
Set @Len = '7'


Set @Command =
'Select ' +
'Sum(Sec), ' +
'Count(Start) ' +
'From ' +
'Calls ' +
'Where ' +
'Agent = ''' + LTrim(RTrim(@in_Agent)) + ''' ' +
'and ' +
'Convert(VarChar(' + @Len + '), Start, 111) = Convert(VarChar(' + @Len + '), ''' + @CharChkDate + ''', 111)'

Execute(@Command)

 

[Delphin]

TY everyone.... I am over half there, now it is combining these queries to one line item. Default to 0 if the person did not work on a specific day, but did work during the month.

Billy Ballard

For in the past, Lies the future.

 

[Delphin]

I have these working:

SELECT party1name, sum(loc) as totSec, count(loc) as totcalls from ACD_2 where month(start) = month('01/11/2005') and CallDirectionCode in ('O', 'I', 'R')group by party1name

SELECT party1name, sum(loc) as totSec, count(loc) as totcalls from ACD_2 where convert(varchar(10),start,101) = '01/11/2005' and CallDirectionCode in ('O', 'I', 'R')group by party1name


COmbining these into 1 line item with defaults of 0 if the agent did not work on a specific day, but did on a specific month.

1/11/05
Call/day Calltime/day Calls/month calltime/month
Ben 3 120 49 10000
Mary 0 0 91 20000

and so on

Billy Ballard

For in the past, Lies the future.

 

[EdwinGene]

Slap me silly - I put some redundant code into the stored proc.

Here is the trimmed down version:



Create Procedure MyProc @in_ChkDate DateTime, @in_Agent VarChar(10), @in_Period VarChar(5)

as

Declare @Len VarChar(2), @Command VarChar(1000), @CharChkDate VarChar(10)

If @in_Period = 'day'
Begin
Set @Len = '10'
Set @CharChkDate = LTrim(RTrim(Convert(VarChar(10), @in_ChkDate, 111)))
End
Else If @in_Period = 'month'
Begin
Set @Len = '7'
Set @CharChkDate = LTrim(RTrim(Convert(VarChar(7), @in_ChkDate, 111)))
End

Set @Command =
'Select ' +
'Sum(Sec), ' +
'Count(Start) ' +
'From ' +
'Calls ' +
'Where ' +
'Agent = ''' + LTrim(RTrim(@in_Agent)) + ''' ' +
'and ' +
'Convert(VarChar(' + @Len + '), Start, 111) = ''' + @CharChkDate + ''''

Execute(@Command)

 

[EdwinGene]

Delphin,

Convert() style of 111 returns the date in format yyyy/mm/dd. If you use this style, to change from day to month you only have to change the length of the data type in the Convert() function from 7 to 10. 7 returns 'yyyy/mm' and 10 returns 'yyyy/mm/dd'. It makes for easier maintenance.

 

[Delphin]

TY for the help. Still need to get this into 1 line item.. The client wants all of as one line with the agent name only displayed once.

Billy Ballard

For in the past, Lies the future.