Re: Need help with SQL Statement
From: Adam Machanic (amachanic_at_hotmail._removetoemail_.com)
Date: 12/14/04
- Next message: ae: "syntax?"
- Previous message: Kent Prokopy: "Re: Need help with SQL Statement"
- In reply to: Kent Prokopy: "Re: Need help with SQL Statement"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 14 Dec 2004 11:45:51 -0500
Okay, I think this should do it for you. You should create the Intervals
table ahead of time and keep it around -- you can populate it with whatever
range you feel is appropriate for your situation. Tell me if you need
clarification on any of this:
use tempdb
go
create table intervals (interval smalldatetime primary key)
go
--create temp table of numbers
select top 200000 identity(int, 0, 1) as num
into #numbers
from pubs..authors a,
pubs..authors b,
pubs..authors c,
pubs..authors d
--set 'initial' date
declare @startdate smalldatetime
set @startdate = '20030101'
--populate intervals table
insert intervals
select dateadd(mi, num * 30, @startdate)
from #numbers
where dateadd(mi, num * 30, @startdate) >= '20030101'
and dateadd(mi, num * 30, @startdate) < '20100101'
go
CREATE TABLE [dbo].[Logins] (
[ORIG_DTS] [datetime] NOT NULL ,
[TERM_DTS] [datetime] NOT NULL ,
[AGENT_ID] [int] NOT NULL
) ON [PRIMARY]
GO
insert logins
values
('20040101 12:21:02', '20040101 12:37:05', 123)
insert logins
values
('20040101 12:38:12', '20040101 12:42:15', 123)
insert logins
values
('20040101 2:15:45', '20040101 4:20:20', 234)
GO
select logins.agent_id,
intervals.interval as intervalStart,
SUM(case when orig_dts between intervals.interval and dateadd(mi, 30,
intervals.interval)
or term_dts between intervals.interval and dateadd(mi, 30,
intervals.interval)
or intervals.interval between orig_dts and term_dts then
--started in this interval
case when orig_dts >= intervals.interval then
--ends in this interval
case when term_dts < dateadd(mi, 30, intervals.interval) then
datediff(mi, orig_dts, term_dts)
--ends in a later interval
else
datediff(mi, orig_dts, dateadd(mi, 30, intervals.interval))
end
--started in previous interval
when orig_dts < intervals.interval then
--ends in this interval
case when term_dts < dateadd(mi, 30, intervals.interval) then
datediff(mi, intervals.interval, term_dts)
--ends in a later interval
else
datediff(mi, intervals.interval, dateadd(mi, 30, intervals.interval))
end
else 0
end
else 0
end)
from logins
cross join intervals
where intervals.interval >= '20040101' and intervals.interval < '20040102'
group by logins.agent_id,
intervals.interval
order by logins.agent_id,
intervals.interval
GO
drop table logins
drop table intervals
drop table #numbers
GO
-- Adam Machanic SQL Server MVP http://www.sqljunkies.com/weblog/amachanic -- "Kent Prokopy" <KentProkopy@discussions.microsoft.com> wrote in message news:1FE7E139-C62B-4331-ADF6-5235EB252A1C@microsoft.com... > The data is stored as DateTime. Three columns: > > DateTime_IN, DateTime_Out, AgentID > 12/13/2004 12:05:00 PM, 12/13/2004 12:37:00 PM, 123 > > I would like to see every interval if this is posable. > > "Adam Machanic" wrote: > > > Can you post DDL for your table? What datatype is TIME_IN / TIME_OUT? And > > what does the data look like if, for instance, TIME_IN is 11:58:00 PM and > > the call lasted for 20 minutes? > > > > I recommend that you store times and dates together and eliminate the "date" > > column. > > > > Also, in this query are you hoping to see every 30 minute interval during > > the day (including those with no activity), or just those with activity? > > > > -- > > Adam Machanic > > SQL Server MVP > > http://www.sqljunkies.com/weblog/amachanic > > -- > > > > > > "Kent Prokopy" <Kent Prokopy@discussions.microsoft.com> wrote in message > > news:F8090154-E0BC-4703-B98B-3D1AFBC62681@microsoft.com... > > > I have a table that stores when an agent logs on and off a phone. The data > > > looks like this. > > > > > > DATE, AGENT_ID, TIME_IN, TIME_OUT > > > 1/1/04, 123, 12:05:00 PM, 12:37:00 PM > > > > > > How can I write an sql statement that will show me the following: > > > > > > DATE, INTERVAL, AGENT_ID, DURATION > > > 1/1/04, 12:00:00 PM, 123, 25 > > > 1/1/04, 12:30:00 PM, 123, 07 > > > > > >
- Next message: ae: "syntax?"
- Previous message: Kent Prokopy: "Re: Need help with SQL Statement"
- In reply to: Kent Prokopy: "Re: Need help with SQL Statement"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
