very basic t-sql.. please help..

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

very basic t-sql.. please help..

Author

Topic

decem
Starting Member

21 Posts

Posted - 2004-04-22 : 13:22:37

i have three columns: day, hour, #

when the hour = 0100, i want what's stored as #

when the hour = 0200, i want ((what's stored as # @ 0200) - (what's stored as # @ 0100))

when the hour = 0300, i want ((what's stored as # @ 0300) - (what's stored as # @ 0200))

etc through 2400

how do i do this?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-22 : 14:44:24

Why do you want to do this?

And what does this mean:

i want what's stored as #

Show us your data.

Tara

decem
Starting Member

21 Posts

Posted - 2004-04-22 : 14:59:29

thanks for offering to help tara.. here's what i have:

CREATE view view_hourly_helpdesk_volume_testing as

select top 10000
workgroup,
date,
case
when datepart (dw, date) = '1' then 'Sunday'
when datepart (dw, date) = '2' then 'Monday'
when datepart (dw, date) = '3' then 'Tuesday'
when datepart (dw, date) = '4' then 'Wednesday'
when datepart (dw, date) = '5' then 'Thursday'
when datepart (dw, date) = '6' then 'Friday'
WHEN datepart (dw, date) = '7' then 'Saturday'
end as 'Day',
time,
calls_received

from view_workgroup
where (workgroup = '4000')
order by workgroup, date, datepart (dw, date), time

the problem is that the calls_received is stored as a cumulative total for the day, so it returns this result:

WG DATE DAY HOUR # CALLS RECEIVED
4000 2003-10-31 00:00:00.000 Friday 2100 135
4000 2003-10-31 00:00:00.000 Friday 2200 137
4000 2003-10-31 00:00:00.000 Friday 2300 138
4000 2003-10-31 00:00:00.000 Friday 2400 140
4000 2003-11-01 00:00:00.000 Saturday 0100 4
4000 2003-11-01 00:00:00.000 Saturday 0200 4
4000 2003-11-01 00:00:00.000 Saturday 0300 5

however, i need a column that shows the number of calls received for just that hour timeframe, not the cumulative total. so i wanted to subtract the #CALLS RECEIVED for that hour by the #CALLS RECEIVED the previous hour into a new column.

# @ 0200 - # @ 0100 = calls received this hour

to go a step further.. after doing this i'll then want to count the number of instances that each day (monday), hour (0100) occurs in a selected date range, and divide the sum of the #CALLS_RECEIVED for those instances by that number of instances

DAY, hour, #
m, 1, 2
m, 1, 3
m, 1, 2

= 7/3 for "average number of calls at 1am on mondays for specified date range"

hopkihc
Starting Member

8 Posts

Posted - 2004-04-22 : 16:01:42

Hi decem. Wow... well, on the one hand, it sounds like the schema is broken, because calls_received should really be cumulative_calls_received, and as such, it can't answer the questions you need to ask it.

But that's just me copping out of an answer.

Sooo...

Off the top of my head, it seems to me you can join back on the same table... something like...

SELECT (CASE
WHEN a.time = 0100 THEN a.calls_received
ELSE a.calls_received - b.calls_received
END) AS calls_for_the_day
FROM a.view_workgroup
LEFT OUTER JOIN b.view_workgroup
ON a.date = b.date
AND a.time = (b.time + 100)

Is that right? Again, this is off the top of my head with no testing of my response (at the risk of looking like an idiot).

It assumes that the date column has no time component (time is always 12:00 am)... which to me also indicates a schema problem, since the time column can EASILY be part of the date column. Then you could do a similar comparison based on one column (using a datediff to compare the number of hours elapsed between times).

Hope this helps or at least points you in the right direction.

John Hopkins
Augusta, GA

decem
Starting Member

21 Posts

Posted - 2004-04-22 : 16:31:05

Thanks John. i haven't tried it yet, but i thought i'd give some more info real quick.

this whole thing is in regards to pulling data from an Altigen PBX system so as to create reports regarding help desk analyst/worgroup trends (right now i'm mainly concerned with workgroup data). there are two main databases, each with three main tables, that come with the altigen system. each db really has only one main table.

in the calldb database is the rtmcall table, which records all call data, assigning a distinct .sessionid for each call. this table had localstarttime column that recorded tim in (i think) varchar data type as YYYYmmDDhhMMss. i created a view_rtmcall that has all this data plus converted this column to a datetime format. this table also has a workgroup column from which i could isolate the workgroups concerned.

i've created a view off of this table like this:

CREATE view view_hourlycallcount as
select top 1000000
wgnum as [workgroup],
sessionid as [sessionid],
localstarttime as [date],
datepart(yyyy,localstarttime) as [year],
datepart(mm,localstarttime) as [month],
datepart(dd,localstarttime) as day_of_month,
datepart(hh,localstarttime) as [hour],
datepart(dw,localstarttime) as day_of_week

from view_rtmcall
where direction = 2
group by datepart(yyyy,localstarttime),datepart(mm,localstarttime), datepart(dd,localstarttime),datepart(hh,localstarttime),datepart(dw,localstarttime), localstarttime, wgnum, sessionid
order by 1, 2, 3, 4, 5

which returns this:
workgroup, sessionid, date, year, month, day, hour, day of week
4000, 1064342659, 9/24/2003 9:20:08 AM, 2003, 9, 24, 9, 4

i then thought i'd make a view that would count (sessionid) where the number of instances where the dw and hh are the same within a date range (i.e. monday at 9am would probably come up 4 times in january) and then divide it by that same number of instances..

something like: sessionid,
date,
year,
month,
day_of_month,
hour,
case
when day_of_week = 1 then 'Sunday'
when day_of_week = 2 then 'Monday'
when day_of_week = 3 then 'Tuesday'
when day_of_week = 4 then 'Wednesday'
when day_of_week = 5 then 'Thursday'
when day_of_week = 6 then 'Friday'
when day_of_week = 7 then 'Saturday'
end as [day_of_week],

case
when ((day_of_week = 2) AND (hour = 9)) then (sum (hourly_call_count) / count (date))
end as Average#CallsPerHour

but i can't figure out the right coding for that either..

so then i went to the rtmdb database, which has a workgroup table with only a date column (also in varchar format and was only as YYYYmmDD) and a time column (varchar format with times as 60, 120, 180, - 1440). so i created a view_workgroup which put added a Date column in datetime format and just changed the time to 0100-2400 for readability. and yes.. the calls received columns (actually a column that i created out of four columns (calls to vm, calls abandoned, etc) which were also cumulative up to 2400)).. and yup.. packaged to show up as cumulative totals..

so crud.. that's pretty much where i'm at.. oh yeah.. if i appear to be a novice with this, i am. i was pretty much just thrown into this position a few days ago and told to generate reports. i took classes and certified in sql, but that was 3 years ago and i haven't touched it since.

if you, or anyone else, has any suggestions on how to use the rtmcall table or anything else at all, i'd greatly appreciate it (my job kinda depends on it i fear).

thanks again

mike

hopkihc
Starting Member

8 Posts

Posted - 2004-04-23 : 11:23:28

Hi. Sorry to hear you're thrown into this situation.

You do want to base your query off rtmcall... and I'd double-check the localstartdatetime column. I'm thinking it MUST be a datetime datatype if you're using DATEPART against it. DATEPART takes a date as its second argument, and if the field is a varchar, and stored in that format, it wouldn't implicity cast to a date in SQL Server (at least I'm pretty sure it wouldn't... just tried and it didn't).

So, going forward, I'm going to assume it's a datetime column.

To get the number of calls per hour per workgroup, you'd do

SELECT wgnum,
(CAST(CONVERT(char(8),@p_Date,112) AS DATETIME)) as calldate,
count(*)
FROM rtmcall
GROUP BY wgnum,(CAST(CONVERT(char(8),@p_Date,112) AS DATETIME))

That CAST(CONVERT... business is ONE way of truncating the time portion off of a datetime column.

So, that should get you the answer you want.

BTW, I'd leave out TOP, unless you want to restrict the number of rows coming back for some reason... I recommend using a date range in your where clause, not a number of rows.

Also, how are you displaying the final report? If it's something like Access, Excel, or an ASP page, I wouldn't bother adding columns to your query that parse out the date bits. Your report columns can simply format the calldate field to only show year, month, day, etc.

If you're using pure SQL output, then you can put this group by query in a view or subquery, and select off of that... a la...

SELECT t.wgnum
,t.calldate
,datepart(yyyy,t.calldate) year
,datepart(mm,t.calldate) month
,t.callcount
FROM (
SELECT wgnum,
(CAST(CONVERT(char(8),@p_Date,112) AS DATETIME)) as calldate,
count(*) callcount
FROM rtmcall
GROUP BY wgnum,(CAST(CONVERT(char(8),@p_Date,112) AS DATETIME))
) t

Hope this helps and that you keep your job as a result :-)

John Hopkins
Augusta, GA

hopkihc
Starting Member

8 Posts

Posted - 2004-04-23 : 11:27:04

oops, I screwed up my query. It gives the number of calls per _day_.

To do the number of calls per _hour_, you need to truncate the minutes and seconds, not the whole time portion. (Let's just blame that programming lapse on low blood sugar.)

For now, I leave that as an exercise to the reader. I gotta go eat. :)

John Hopkins
Augusta, GA

hopkihc
Starting Member

8 Posts

Posted - 2004-04-23 : 11:32:16

Sorry, one more observation. No need for a SELECT CASE to translate days of the week into a day name. Use DATENAME(dw,datefield).

John Hopkins
Augusta, GA

hopkihc
Starting Member

8 Posts

Posted - 2004-04-23 : 11:45:41

Alright, well, my lunch date hasn't shown up yet, so I wrote a function to truncate minutes and seconds.

CREATE FUNCTION dbo.udfTruncateMinutes(@d SMALLDATETIME)
RETURNS SMALLDATETIME
AS
/* making the input parameter a SMALLDATETIME already truncates seconds,
so now we can subtract the number of minutes from the
passed time @d from @d itself, thus given us the date + hour of day */
BEGIN
RETURN (DATEADD(mi,-DATEPART(mi,@d),@d))
END

This can be done a number of ways, but this was my off-the-cuff solution.

Anyway, your query would then be:

SELECT wgnum,
dbo.udfTruncateMinutes(localstartdatetime) as calldate,
count(*)
FROM rtmcall
GROUP BY wgnum,dbo.udfTruncateMinutes(localstartdatetime)

And shoot... I just realized that my query example had a parameter (@p_date) instead of localstartdatetime. Sorry for the confusion. I copy-pasted from a function and didn't replace my variable name with your column name.

John Hopkins
Augusta, GA

decem
Starting Member

21 Posts

Posted - 2004-04-23 : 11:57:44

hey john. thanks again for your help. i've yet to try this on the rtmcall table.. i just wanted to go ahead and throw you an update on what i've done so far..

first created a view off the view_workgroup using your join suggestion to get the number of calls that hour (instead of cumulative):

create view view_HD_hourlycallcount as
select top 10000
a.date,
a.time,
case
when datepart (dw, a.date) = '1' then 'Sunday'
when datepart (dw, a.date) = '2' then 'Monday'
when datepart (dw, a.date) = '3' then 'Tuesday'
when datepart (dw, a.date) = '4' then 'Wednesday'
when datepart (dw, a.date) = '5' then 'Thursday'
when datepart (dw, a.date) = '6' then 'Friday'
WHEN datepart (dw, a.date) = '7' then 'Saturday'
end as 'Day',
a.calls_received,

CASE
WHEN a.time = 0100 THEN a.calls_received
ELSE a.calls_received - b.calls_received
END
AS hourly_call_count

FROM view_workgroup as a

LEFT OUTER JOIN view_workgroup as b
ON
a.nodeid = b.nodeid AND
a.localdate = b.localdate and
a.workgroup = b.workgroup and
a.date = b.date AND
a.time = (b.time + 100)

where a.workgroup = 4000
order by a.date, a.time, a.day

and then referenced this in a stored procedure to average the number of calls for each day and hour:

CREATE Proc Proc_HD_AverageHourlyCallCount
@StartDate varchar (15),
@EndDate varchar (15)
as
select
day as [Day],
time as [Time],
((sum (hourly_call_count)) / (count (day)) ) as [Average # of Calls]
from view_HD_hourlycallcount
where date >= @StartDate AND date <= @EndDate
group by day, time
order by day, time
GO

works like a charm.. i'll refine them more and add more versatility to them later.. but they'll work for now.

thanks for the suggestion on the DATEPART.. wish i'd know that before typing all that crap out..

as for the rtmcall.. the table packaged with the altigen db doesn't have any time stamp.. it has a starttime and endtime column.. but it's in unix time and is in INT format.. then localstarttime and endtime were in VARCHAR format as YYYYmmDDhhMMss so i converted it to datetime in the view.

thanks again for all your help John.

to add more to my situation.. in being thrown into this.. i was also sort of told "you're getting paid as mcdba.. time to earn your pay or find a diff job..".. ha ha.. so, having generated the two reports that they wanted immediately.. i have at least saved my job for another week i think. thanks again

mike

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources