Am wondering if this is possible. In a table I'll
call 'Cases' I have Case_Start, Case_End, Case_Active
fields. Case_Start and Case_End are date fields
indicating when the case started and when the case ended.
Case_Start will always be a date since we can't have a
record unless we have this information. Case_End will
be null if the case is open and the ending date is not
known, it will be a date if we know the case ending
date. Case_Active is a bit field. I would like to make
it a computed field. I would like it to = 1 if the
Case_End date is not null and todays date is between the
Case_Start and Case_End date. I would like it to = 1 if
the Case_End field is null. I would like it to = 0 if
Case_End is not null and todays date is greater than the
Case_End date. Is this possible with a computed field
and how? If not, is there a better way to accomplish
what I'm trying to do? Thanks in advance.Hi Mark,
Use the following for the computed column:
Case_active AS CASE WHEN Case_start > GETDATE() OR Case_end < GETDATE() THEN
0 ELSE 1 END
I.e. a case is not active if the startdate is after today or the enddate is
before today, in all other cases, including an unknown enddate, the case is
active.
--
Jacco Schalkwijk
SQL Server MVP
"Mark" <anonymous@.discussions.microsoft.com> wrote in message
news:132a01c3fbaf$e2a700f0$a501280a@.phx.gbl...
> Am wondering if this is possible. In a table I'll
> call 'Cases' I have Case_Start, Case_End, Case_Active
> fields. Case_Start and Case_End are date fields
> indicating when the case started and when the case ended.
> Case_Start will always be a date since we can't have a
> record unless we have this information. Case_End will
> be null if the case is open and the ending date is not
> known, it will be a date if we know the case ending
> date. Case_Active is a bit field. I would like to make
> it a computed field. I would like it to = 1 if the
> Case_End date is not null and todays date is between the
> Case_Start and Case_End date. I would like it to = 1 if
> the Case_End field is null. I would like it to = 0 if
> Case_End is not null and todays date is greater than the
> Case_End date. Is this possible with a computed field
> and how? If not, is there a better way to accomplish
> what I'm trying to do? Thanks in advance.
Showing posts with label call. Show all posts
Showing posts with label call. Show all posts
Thursday, March 22, 2012
Tuesday, March 20, 2012
Composite vs Single Primary Key
Our DBA has chosen to use an autonumber column as the primary key for a linking table consisting of 2 other IDs, I'll call them x and y. Another developer is concerned that since our application only keeps track of IDs x and y, not the autonumber ID, that searches will be less efficient because if x and y were a composite key they would be indexed. Is this correct? If so, couldn't you just create an index for these columns to make it equally efficient?
I believe that to eliminate all composite keys is a requirement for second normal form (correct me if I'm wrong), but does assigning an autonumber primary key to a linking table made up of solely 2 IDs have any benefits?
First of all, it's entirely possible to have the primary key (in the relational database sense, the one you can link tables with) different from the CLUSTERING key (the key for the main index of the table), e.g.:
Create Table T(p int primary key nonclustered, x int, y int);
Create Clustered Index T_i on T(x);
Also, you can always create secondary nonclustered indexes to improve efficiency, please look up indexing strategies articles (e.g. Books On-Line article http://msdn2.microsoft.com/en-us/library/ms189271.aspx), although I think for a linking table a single clustered index should be fine.
Now, for your particular issue I can't think of any benefit to an autonumber primary key except that if a lot of INSERT operations occur, and if the two other ID's are not naturally increasing (on average), then the autonumbering clustering key would keep the index from being fragmented and INSERT's would be quicker since you always insert at the end. Having said that, my gut feel is you shouldn't do it and you should keep the composite key. The composite key (if it's a primary key not just a clustereing key) also would enforce that no duplicate combinations would be inserted into the linking table.
|||2nf just states that all data in a table must rely on the entire key.
that doesnt mean the key cant be composite.
if this table resolves a many to many relationship (Which sounds to be the case) then it is correctly designed for 2nd (even 3nf)
Thursday, March 8, 2012
Complex(?) SQL Query
I need a complex query, but I will try to keep this short. I have four table
s
(call then Main, Building, Location and Unit). Main is the main table and ha
s
several fields. In Main, I have two fields that can reference Unit, and one
field that references Building. Unit also references Building, and Building
references Location
.
In other words, Units belong to a Building and a Building belongs to a
Location. If I have a Unit in Main, I have to go to Unit to get the Building
,
then Building to get the Building related info and the Location.
Table Main has a BuildingCode field and two UnitCode (UCa and UCb) fields.
If there is a UCa value, there should not be a BuildingCode and vice versa.
There may or may not be a value for UCb.
My query needs to return all related information in a single recordset (i.e.
Building.BuildingName & Location.LocationName based on a join from
BuildingCode in Main, and Unit.UnitName, Building.BuildingName and
Location.LocationName based on a join on UnitCode in Main (remember two
differnt places where UnitCode is used in Main and they can be different). I
f
this makes sense to anyone and they can help, I would be eternally grateful,
and if you are ever in the Kansas City are I will buy you dinner.
tia,
Dougdoug,
not sure if i understand completely, it would be much easier for all if you
posted some DDL and sample data.
do your tables look something like this?
create table locations (
location_code char(2) not null primary key,
location_name varchar(10) not null
)
go
create table buildings (
building_code char(2) not null primary key,
location_code char(2) not null foreign key references
locations(location_code),
building_name varchar(10) not null
)
go
create table units (
unit_code char(2) not null primary key,
building_code char(2) not null foreign key references
buildings(building_code),
unit_name varchar(10) not null
)
go
create table main (
main_code char(2) not null primary key,
unit_code_a char(2) null foreign key references units(unit_code),
unit_code_b char(2) null foreign key references units(unit_code),
building_code char(2) null foreign key references buildings(building_code),
check (case when unit_code_a is null then 0 else 1 end + case when
building_code is null then 0 else 1 end = 1)
)
go
and some sample data:
insert locations(location_code, location_name)
select 'L1', 'location 1'
union all
select 'L2', 'location 2'
insert buildings(building_code, location_code, building_name)
select 'B1', 'L1', 'building 1'
union all
select 'B2', 'L2', 'building 2'
insert units(unit_code, building_code, unit_name)
select 'U1', 'B1', 'unit 1'
insert main(main_code, unit_code_a, unit_code_b, building_code)
select 'M1', 'U1', null, null
union all
select 'M2', null, null, 'B2'
(it is unclear what is unit_code_b used for.. could you please explain?)
then we could do it like this:
select coalesce(b1.building_name,b2.building_name) as building_name,
coalesce(l1.location_name,l2.location_name) as location_name,
u.unit_name
from main m
left outer join units u on m.unit_code_a=u.unit_code and m.building_code is
null
left outer join buildings b1 on u.building_code=b1.building_code
left outer join locations l1 on b1.location_code=l1.location_code
left outer join buildings b2 on m.building_code=b2.building_code and
m.unit_code_a is null
left outer join locations l2 on b2.location_code=l2.location_code
dean
"Doug" <dbb211-hd204@.yahoo.com> wrote in message
news:08311DB6-A81A-4F73-924D-9A8F8143B125@.microsoft.com...
>I need a complex query, but I will try to keep this short. I have four
>tables
> (call then Main, Building, Location and Unit). Main is the main table and
> has
> several fields. In Main, I have two fields that can reference Unit, and
> one
> field that references Building. Unit also references Building, and
> Building
> references Location
> .
> In other words, Units belong to a Building and a Building belongs to a
> Location. If I have a Unit in Main, I have to go to Unit to get the
> Building,
> then Building to get the Building related info and the Location.
> Table Main has a BuildingCode field and two UnitCode (UCa and UCb) fields.
> If there is a UCa value, there should not be a BuildingCode and vice
> versa.
> There may or may not be a value for UCb.
> My query needs to return all related information in a single recordset
> (i.e.
> Building.BuildingName & Location.LocationName based on a join from
> BuildingCode in Main, and Unit.UnitName, Building.BuildingName and
> Location.LocationName based on a join on UnitCode in Main (remember two
> differnt places where UnitCode is used in Main and they can be different).
> If
> this makes sense to anyone and they can help, I would be eternally
> grateful,
> and if you are ever in the Kansas City are I will buy you dinner.
> tia,
> Doug|||Dean and others. Please see [url]http://www.cedar.cr
.cc/images/relationship.jpg[/url
]
for a relationship diagram of the tables in question. A brief description of
why the relationships are as they are.
As you can probably tell, this is an inventory (of computers). A building
may or may not have units designated within it. If there are units in the
building, the unit field will be populated, otherwise the building field wil
l
be populated (in tblCIMComputers). The second Unit field is to allow
(usually) a person in another location to log into they system as though
their computer was located in a specific Unit (our login security system als
o
uses this table). As such, there may or may not be a value in this second
Unit field.
The reason for this type of layout is because Units can move from one
building to another. If such a move is made, one record in one table is
changed (i.e. BuildingCode in tblUnits) and all is well.
The constructs of the tables are very straight forward. Only field
definitions, primary and foreign keys. No other type of constraints. The
purpose of the query I am trying to build is to bring back all pertinent dat
a
to provide the person accessing it with a complete overview of all data.
"Dean" wrote:
> doug,
> not sure if i understand completely, it would be much easier for all if yo
u
> posted some DDL and sample data.
> do your tables look something like this?
> create table locations (
> location_code char(2) not null primary key,
> location_name varchar(10) not null
> )
> go
> create table buildings (
> building_code char(2) not null primary key,
> location_code char(2) not null foreign key references
> locations(location_code),
> building_name varchar(10) not null
> )
> go
> create table units (
> unit_code char(2) not null primary key,
> building_code char(2) not null foreign key references
> buildings(building_code),
> unit_name varchar(10) not null
> )
> go
> create table main (
> main_code char(2) not null primary key,
> unit_code_a char(2) null foreign key references units(unit_code),
> unit_code_b char(2) null foreign key references units(unit_code),
> building_code char(2) null foreign key references buildings(building_code)
,
> check (case when unit_code_a is null then 0 else 1 end + case when
> building_code is null then 0 else 1 end = 1)
> )
> go
>
> and some sample data:
> insert locations(location_code, location_name)
> select 'L1', 'location 1'
> union all
> select 'L2', 'location 2'
> insert buildings(building_code, location_code, building_name)
> select 'B1', 'L1', 'building 1'
> union all
> select 'B2', 'L2', 'building 2'
> insert units(unit_code, building_code, unit_name)
> select 'U1', 'B1', 'unit 1'
> insert main(main_code, unit_code_a, unit_code_b, building_code)
> select 'M1', 'U1', null, null
> union all
> select 'M2', null, null, 'B2'
> (it is unclear what is unit_code_b used for.. could you please explain?)
>
> then we could do it like this:
> select coalesce(b1.building_name,b2.building_name) as building_name,
> coalesce(l1.location_name,l2.location_name) as location_name,
> u.unit_name
> from main m
> left outer join units u on m.unit_code_a=u.unit_code and m.building_code i
s
> null
> left outer join buildings b1 on u.building_code=b1.building_code
> left outer join locations l1 on b1.location_code=l1.location_code
> left outer join buildings b2 on m.building_code=b2.building_code and
> m.unit_code_a is null
> left outer join locations l2 on b2.location_code=l2.location_code
>
> dean
> "Doug" <dbb211-hd204@.yahoo.com> wrote in message
> news:08311DB6-A81A-4F73-924D-9A8F8143B125@.microsoft.com...
>
>|||btw, I am using SQL Server 2000.
Doug|||FIrst, I am not trying to be sarcastic, just giving some friendly advice to
you and other posters that post questions without the necessary code to
allow us to help you quickly.
The diagram is nice but, If we are to help you write a query and test it to
make sure it does what you want, we need to have the tables created on our
database with the right structure and have sameple data in them.
This can happen in 2 ways:
1. We read your diagram and create the tables, relationships, etc from them
and then enter some sample data
2. You can provide us with the SQL script to accomplish this and save us a
lot of time
Which do you think will get you more help quicker? :-)
"Doug" <dbb211-hd204@.yahoo.com> wrote in message
news:96AB7DF5-EE89-41B8-8E93-224EDE8C0564@.microsoft.com...
> Dean and others. Please see
> [url]http://www.cedar.cr.cc/images/relationship.jpg[/url]
> for a relationship diagram of the tables in question. A brief description
> of
> why the relationships are as they are.
> As you can probably tell, this is an inventory (of computers). A building
> may or may not have units designated within it. If there are units in the
> building, the unit field will be populated, otherwise the building field
> will
> be populated (in tblCIMComputers). The second Unit field is to allow
> (usually) a person in another location to log into they system as though
> their computer was located in a specific Unit (our login security system
> also
> uses this table). As such, there may or may not be a value in this second
> Unit field.
> The reason for this type of layout is because Units can move from one
> building to another. If such a move is made, one record in one table is
> changed (i.e. BuildingCode in tblUnits) and all is well.
> The constructs of the tables are very straight forward. Only field
> definitions, primary and foreign keys. No other type of constraints. The
> purpose of the query I am trying to build is to bring back all pertinent
> data
> to provide the person accessing it with a complete overview of all data.
> "Dean" wrote:
>
s
(call then Main, Building, Location and Unit). Main is the main table and ha
s
several fields. In Main, I have two fields that can reference Unit, and one
field that references Building. Unit also references Building, and Building
references Location
.
In other words, Units belong to a Building and a Building belongs to a
Location. If I have a Unit in Main, I have to go to Unit to get the Building
,
then Building to get the Building related info and the Location.
Table Main has a BuildingCode field and two UnitCode (UCa and UCb) fields.
If there is a UCa value, there should not be a BuildingCode and vice versa.
There may or may not be a value for UCb.
My query needs to return all related information in a single recordset (i.e.
Building.BuildingName & Location.LocationName based on a join from
BuildingCode in Main, and Unit.UnitName, Building.BuildingName and
Location.LocationName based on a join on UnitCode in Main (remember two
differnt places where UnitCode is used in Main and they can be different). I
f
this makes sense to anyone and they can help, I would be eternally grateful,
and if you are ever in the Kansas City are I will buy you dinner.
tia,
Dougdoug,
not sure if i understand completely, it would be much easier for all if you
posted some DDL and sample data.
do your tables look something like this?
create table locations (
location_code char(2) not null primary key,
location_name varchar(10) not null
)
go
create table buildings (
building_code char(2) not null primary key,
location_code char(2) not null foreign key references
locations(location_code),
building_name varchar(10) not null
)
go
create table units (
unit_code char(2) not null primary key,
building_code char(2) not null foreign key references
buildings(building_code),
unit_name varchar(10) not null
)
go
create table main (
main_code char(2) not null primary key,
unit_code_a char(2) null foreign key references units(unit_code),
unit_code_b char(2) null foreign key references units(unit_code),
building_code char(2) null foreign key references buildings(building_code),
check (case when unit_code_a is null then 0 else 1 end + case when
building_code is null then 0 else 1 end = 1)
)
go
and some sample data:
insert locations(location_code, location_name)
select 'L1', 'location 1'
union all
select 'L2', 'location 2'
insert buildings(building_code, location_code, building_name)
select 'B1', 'L1', 'building 1'
union all
select 'B2', 'L2', 'building 2'
insert units(unit_code, building_code, unit_name)
select 'U1', 'B1', 'unit 1'
insert main(main_code, unit_code_a, unit_code_b, building_code)
select 'M1', 'U1', null, null
union all
select 'M2', null, null, 'B2'
(it is unclear what is unit_code_b used for.. could you please explain?)
then we could do it like this:
select coalesce(b1.building_name,b2.building_name) as building_name,
coalesce(l1.location_name,l2.location_name) as location_name,
u.unit_name
from main m
left outer join units u on m.unit_code_a=u.unit_code and m.building_code is
null
left outer join buildings b1 on u.building_code=b1.building_code
left outer join locations l1 on b1.location_code=l1.location_code
left outer join buildings b2 on m.building_code=b2.building_code and
m.unit_code_a is null
left outer join locations l2 on b2.location_code=l2.location_code
dean
"Doug" <dbb211-hd204@.yahoo.com> wrote in message
news:08311DB6-A81A-4F73-924D-9A8F8143B125@.microsoft.com...
>I need a complex query, but I will try to keep this short. I have four
>tables
> (call then Main, Building, Location and Unit). Main is the main table and
> has
> several fields. In Main, I have two fields that can reference Unit, and
> one
> field that references Building. Unit also references Building, and
> Building
> references Location
> .
> In other words, Units belong to a Building and a Building belongs to a
> Location. If I have a Unit in Main, I have to go to Unit to get the
> Building,
> then Building to get the Building related info and the Location.
> Table Main has a BuildingCode field and two UnitCode (UCa and UCb) fields.
> If there is a UCa value, there should not be a BuildingCode and vice
> versa.
> There may or may not be a value for UCb.
> My query needs to return all related information in a single recordset
> (i.e.
> Building.BuildingName & Location.LocationName based on a join from
> BuildingCode in Main, and Unit.UnitName, Building.BuildingName and
> Location.LocationName based on a join on UnitCode in Main (remember two
> differnt places where UnitCode is used in Main and they can be different).
> If
> this makes sense to anyone and they can help, I would be eternally
> grateful,
> and if you are ever in the Kansas City are I will buy you dinner.
> tia,
> Doug|||Dean and others. Please see [url]http://www.cedar.cr
.cc/images/relationship.jpg[/url]
for a relationship diagram of the tables in question. A brief description of
why the relationships are as they are.
As you can probably tell, this is an inventory (of computers). A building
may or may not have units designated within it. If there are units in the
building, the unit field will be populated, otherwise the building field wil
l
be populated (in tblCIMComputers). The second Unit field is to allow
(usually) a person in another location to log into they system as though
their computer was located in a specific Unit (our login security system als
o
uses this table). As such, there may or may not be a value in this second
Unit field.
The reason for this type of layout is because Units can move from one
building to another. If such a move is made, one record in one table is
changed (i.e. BuildingCode in tblUnits) and all is well.
The constructs of the tables are very straight forward. Only field
definitions, primary and foreign keys. No other type of constraints. The
purpose of the query I am trying to build is to bring back all pertinent dat
a
to provide the person accessing it with a complete overview of all data.
"Dean" wrote:
> doug,
> not sure if i understand completely, it would be much easier for all if yo
u
> posted some DDL and sample data.
> do your tables look something like this?
> create table locations (
> location_code char(2) not null primary key,
> location_name varchar(10) not null
> )
> go
> create table buildings (
> building_code char(2) not null primary key,
> location_code char(2) not null foreign key references
> locations(location_code),
> building_name varchar(10) not null
> )
> go
> create table units (
> unit_code char(2) not null primary key,
> building_code char(2) not null foreign key references
> buildings(building_code),
> unit_name varchar(10) not null
> )
> go
> create table main (
> main_code char(2) not null primary key,
> unit_code_a char(2) null foreign key references units(unit_code),
> unit_code_b char(2) null foreign key references units(unit_code),
> building_code char(2) null foreign key references buildings(building_code)
,
> check (case when unit_code_a is null then 0 else 1 end + case when
> building_code is null then 0 else 1 end = 1)
> )
> go
>
> and some sample data:
> insert locations(location_code, location_name)
> select 'L1', 'location 1'
> union all
> select 'L2', 'location 2'
> insert buildings(building_code, location_code, building_name)
> select 'B1', 'L1', 'building 1'
> union all
> select 'B2', 'L2', 'building 2'
> insert units(unit_code, building_code, unit_name)
> select 'U1', 'B1', 'unit 1'
> insert main(main_code, unit_code_a, unit_code_b, building_code)
> select 'M1', 'U1', null, null
> union all
> select 'M2', null, null, 'B2'
> (it is unclear what is unit_code_b used for.. could you please explain?)
>
> then we could do it like this:
> select coalesce(b1.building_name,b2.building_name) as building_name,
> coalesce(l1.location_name,l2.location_name) as location_name,
> u.unit_name
> from main m
> left outer join units u on m.unit_code_a=u.unit_code and m.building_code i
s
> null
> left outer join buildings b1 on u.building_code=b1.building_code
> left outer join locations l1 on b1.location_code=l1.location_code
> left outer join buildings b2 on m.building_code=b2.building_code and
> m.unit_code_a is null
> left outer join locations l2 on b2.location_code=l2.location_code
>
> dean
> "Doug" <dbb211-hd204@.yahoo.com> wrote in message
> news:08311DB6-A81A-4F73-924D-9A8F8143B125@.microsoft.com...
>
>|||btw, I am using SQL Server 2000.
Doug|||FIrst, I am not trying to be sarcastic, just giving some friendly advice to
you and other posters that post questions without the necessary code to
allow us to help you quickly.
The diagram is nice but, If we are to help you write a query and test it to
make sure it does what you want, we need to have the tables created on our
database with the right structure and have sameple data in them.
This can happen in 2 ways:
1. We read your diagram and create the tables, relationships, etc from them
and then enter some sample data
2. You can provide us with the SQL script to accomplish this and save us a
lot of time
Which do you think will get you more help quicker? :-)
"Doug" <dbb211-hd204@.yahoo.com> wrote in message
news:96AB7DF5-EE89-41B8-8E93-224EDE8C0564@.microsoft.com...
> Dean and others. Please see
> [url]http://www.cedar.cr
.cc/images/relationship.jpg[/url]> for a relationship diagram of the tables in question. A brief description
> of
> why the relationships are as they are.
> As you can probably tell, this is an inventory (of computers). A building
> may or may not have units designated within it. If there are units in the
> building, the unit field will be populated, otherwise the building field
> will
> be populated (in tblCIMComputers). The second Unit field is to allow
> (usually) a person in another location to log into they system as though
> their computer was located in a specific Unit (our login security system
> also
> uses this table). As such, there may or may not be a value in this second
> Unit field.
> The reason for this type of layout is because Units can move from one
> building to another. If such a move is made, one record in one table is
> changed (i.e. BuildingCode in tblUnits) and all is well.
> The constructs of the tables are very straight forward. Only field
> definitions, primary and foreign keys. No other type of constraints. The
> purpose of the query I am trying to build is to bring back all pertinent
> data
> to provide the person accessing it with a complete overview of all data.
> "Dean" wrote:
>
Saturday, February 25, 2012
Complex Query
Hi Guys
Hope you can help with this - it's certainly got me scratching my head.
I'm Querying a Call Centre Database
I have a Table of Call Data with a Start Time & End Time of the Call.
(CallID,Started,Ended)
10942086 2007-04-01 00:01:09.000 2007-04-01 00:11:31.000
1003855355 2007-04-01 00:01:24.000 2007-04-01 00:01:24.000
10942071 2007-04-01 00:01:25.000 2007-04-01 00:02:43.000
10942271 2007-04-01 00:02:57.000 2007-04-01 00:05:01.000
10942283 2007-04-01 00:05:54.000 2007-04-01 00:06:50.000
10942079 2007-04-01 00:07:15.000 2007-04-01 00:07:46.000
10942287 2007-04-01 00:07:30.000 2007-04-01 00:08:12.000
10942289 2007-04-01 00:07:49.000 2007-04-01 00:08:33.000
I'm trying to produce Stats that tell me how many Calls were live in any one given minute.
Ultimately I will be producing a Line Graph of No of Calls Connected grouped by Minute.
I've gone as far as creating a temp table with every minute in a month with the following query maybe to join to but not sure if this will help me.
WHILE(@.cnt <= 43200)
BEGIN
SELECT @.MaxDate =DATEADD(mi,1,MAX(DTBlock))FROM AprilMinutes
INSERTINTO AprilMinutes VALUES(@.MaxDate,NULL)
SET @.cnt = @.Cnt +1
END
Which produces a nifty little table with
01/04/2007 00:09:00
01/04/2007 00:10:00
01/04/2007 00:11:00
01/04/2007 00:12:00
01/04/2007 00:13:00
01/04/2007 00:14:00
01/04/2007 00:15:00
01/04/2007 00:16:00
If one individual Call Spans 2 minutes I'll count it as 1 in the first minute & 1 in the second minute.
Overall I'm trying analyze how many telephone lines we need
Any Help much, much appreciated
Thanks
GWselect AprilMinutes.datetimecolumn
, count(CallData.StartTime) as active_calls
from AprilMinutes
left outer
join CallData
on AprilMinutes.datetimecolumn
between CallData.StartTime
and CallData.EndTime
group
by AprilMinutes.datetimecolumn|||Tehe
Thanks r937 - Though art a veritable GURU of SQL'ness
My meager Server is crunching the TOP 100 as we speak (creak, grind)
However :eek:
What will happen if a call arrives at eg. 00:05:10 and Ends at 00:05:55
methinks it will not get counted ?
please tell me i'm wrong|||please tell me i'm wrongnope, thou art correct
here's an idea (too busy to test it meself): in the query round the start time down to the nearest previous minute, and round the end time up to the next minute, then instead of BETWEEN, use >= and <|||Whooooaa
Close but no Bannana's
The suggestion produces
(DateTimeBlock,CountOf)
2007-04-01 00:00:00.000 2
2007-04-01 00:01:00.000 4
2007-04-01 00:02:00.000 5
2007-04-01 00:03:00.000 3
2007-04-01 00:04:00.000 7
2007-04-01 00:05:00.000 8
2007-04-01 00:06:00.000 8
2007-04-01 00:07:00.000 8
2007-04-01 00:08:00.000 8
2007-04-01 00:09:00.000 7
From
(CallID,Start,End,FormatedStart,FormatedEnd)
10942086 2007-04-01 00:01:09.000 2007-04-01 00:11:31.000 2007-04-01 00:01:00.000 2007-04-01 00:12:00.000
10038553 2007-04-01 00:01:24.000 2007-04-01 00:01:24.000 2007-04-01 00:01:00.000 2007-04-01 00:02:00.000
10942071 2007-04-01 00:01:25.000 2007-04-01 00:02:43.000 2007-04-01 00:01:00.000 2007-04-01 00:03:00.000
10942271 2007-04-01 00:02:57.000 2007-04-01 00:05:01.000 2007-04-01 00:02:00.000 2007-04-01 00:05:00.000
10942283 2007-04-01 00:05:54.000 2007-04-01 00:06:50.000 2007-04-01 00:05:00.000 2007-04-01 00:07:00.000
10942079 2007-04-01 00:07:15.000 2007-04-01 00:07:46.000 2007-04-01 00:07:00.000 2007-04-01 00:08:00.000
10942287 2007-04-01 00:07:30.000 2007-04-01 00:08:12.000 2007-04-01 00:07:00.000 2007-04-01 00:09:00.000
10942289 2007-04-01 00:07:49.000 2007-04-01 00:08:33.000 2007-04-01 00:07:00.000 2007-04-01 00:09:00.000
10942090 2007-04-01 00:09:52.000 2007-04-01 00:13:40.000 2007-04-01 00:09:00.000 2007-04-01 00:14:00.000
10942094 2007-04-01 00:15:18.000 2007-04-01 00:15:45.000 2007-04-01 00:15:00.000 2007-04-01 00:16:00.000
10942306 2007-04-01 00:15:37.000 2007-04-01 00:16:26.000 2007-04-01 00:15:00.000 2007-04-01 00:17:00.000
10942104 2007-04-01 00:17:51.000 2007-04-01 00:18:29.000 2007-04-01 00:17:00.000 2007-04-01 00:19:00.000
I Estimate it should produce2007-04-01 00:00:00.000 0
2007-04-01 00:01:00.000 3
2007-04-01 00:02:00.000 3
2007-04-01 00:03:00.000 2
2007-04-01 00:04:00.000 2
2007-04-01 00:05:00.000 3
Do you think it will help if I post some code to create Test Tables n Data
I had hoped maybe someone familiar with a Call Centre / telephone type operation would have done this kind of thing.
I'm not sure why your suggestion does'nt work - I'll continue studying it.
:confused:
Thanks|||Oh NO !!
Everyone seems to have given up :eek: :eek: :eek: :eek:
Surely there's a SQL God out there who could make a constructive suggestion.
Come on Chaps OR I'll break out the CURSORS - tehe.
Only Joking.
SELECT CASE WHEN @.GoodSugestions >= 1
THEN
'Thankyou'
ELSE
'Struggle On'
END
Thanks to r937, you've brought me very close.
GW|||can you show the query you developed based on my suggestion in post #4|||Here's the RAW code exactly as used
select top 10 AprilMinutes.DTBlock
,count(callActionSummary.NotiTime)as active_calls
from AprilMinutes
left outer
join callActionSummary
on AprilMinutes.DTBlock >=LEFT(dbo.FormatDate(NotiTime,'yyyy-mm-dd hh:mm:ss.Ms'),16)+':00.000'
and AprilMinutes.DTBlock <DATEADD(mi,1,LEFT(dbo.FormatDate(DATEADD(ss,ssopco nn,NotiTime),'yyyy-mm-dd hh:mm:ss.Ms'),16)+':00')
group
by AprilMinutes.DTBlock
orderby AprilMinutes.DTBlock
Do you think possibly the Date manipulation / dbo.FormatDate is affecting it ?
ssopconn is Int (operator connected seconds)
NotiTime is Datetime (Time Call arrived)
dbo.FormatDate returns nVarChar (custom UDF) hopefuly implicit conversion to date ??
GW|||Do you think possibly the Date manipulation / dbo.FormatDate is affecting it ?it's a possibility ;)
actually, now that i think about it, you would only have to round the start time down to the current minute, to have DTBlock >= pick it up
then if ssopconn doesn't extend the time into the next minute, the call will still be counted in that minute
anyhow, there is a much better way to round down (would've mentioned it yesterday but i was too busy to look it up)
SELECT DATEADD(<datepart>, DATEDIFF(<datepart>, <refdatetime>, <datetime>), <refdatetime>)
which simply adds back the same number of whole dateparts to the reference datetime as there are between the reference datetime and the given datetime
the excess just disappears ;)
example:
SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0) returns midnight of first day of current month (try it :))
reference date of 0 equals the sql server zero datetime, whatever that may be, jan 1 1900 or whatever
it doesn't actually matter what the exact zero datetime is, because it subtracts back out ;)
expression for your situation:
DATEADD(mi, DATEDIFF(mi, '2000-01-01', NotiTime), '2000-01-01')
reference date is chosen closer to expected values, although zero still works
this expression rounds NotiTime down to current minute and is a lot more efficient than string manipulation
:)|||select a.datetimecolumn, count(b.StartTime)
from AprilMinutes a left join CallData b
on a.datetimecolumn between convert(varchar(17),b.StartTime ,113)
and b.EndTime --dateadd(ms,59997, convert(varchar(17),b.EndTime,113) )
group by a.datetimecolumn|||Sorry Chaps
Not had time to test these yet but will do tomorrow
Someone decided to change the CodePages on the Production SyBase servers from 850 to 1252 last night & hoped it would'nt have much of an effect - :shocked: - lol
I'll keep you posted re results of your suggestions
thanks
GW|||expression for your situation:
DATEADD(mi, DATEDIFF(mi, '2000-01-01', NotiTime), '2000-01-01')
is a lot more efficient than string manipulation
Revised queryselect a.DTBlock, count(b.StartTime)
from AprilMinutes a
left join CallData b
on a.DTBlock between dateadd(mi,datediff(mi,0,b.StartTime),0)
and b.EndTime
group by a.DTBlock|||WOW :D :D :D
r937 & pdreyer - Though art Veritable GODS of SQL ness
Worked a Treat & triple fast too.
r937 had it with the Left Outer and pdreyer swooped in with the
between dateadd(mi,datediff(mi,0,b.StartTime),0) and b.EndTime
I'm humbled in your presence - Grovel, Snivel
Many Thanks to the both of you - tis muchly appreciated
Grant|||Just to Round things Off
This is what I did with the Code
ALTERPROCEDURE LineUtilisation @.FromDate DateTime, @.ToDate DateTime
AS
SETNOCOUNTON
DECLARE @.TimeBlock TABLE(DTBlock DateTime)
DECLARE @.CallData TABLE(Call_Def Int,StartTime DateTime,EndTime DateTime)
DECLARE @.MinReq Int
INSERTINTO @.TimeBlock SELECT @.FromDate
SET @.MinReq =DATEDIFF(mi,@.FromDate,@.ToDate)
WHILE(@.MinReq > 1)
BEGIN
INSERTINTO @.TimeBlock
SELECTDATEADD(mi,1,MAX(DTBlock))FROM @.TimeBlock
SET @.MinReq = @.MinReq - 1
END
INSERTINTO @.CallData
SELECT
Call_Def,
NotiTime StartTime,
DATEADD(ss,SSTotConn,NotiTime) EndTime
FROM CallActionSummary
WHERE NotiTime between @.FromDate AND @.ToDate
ANDRTRIM(LTRIM(CallCode ))NOTIN('','B','F','4','Q','H','O','P','16')-- Background
AND ssOpConn > 0
AND ReasonRef <> 5
ORDERBY 2
SELECT a.DTBlock,COUNT(b.StartTime) CallQty
FROM @.TimeBlock a
LEFTJOIN @.CallData b
ON a.DTBlock BETWEENDATEADD(mi,DATEDIFF(mi,0,b.StartTime),0)
AND b.EndTime
GROUPBY a.DTBlock
ORDERBY 1
Will Probably end up tidying it up - poss with some indexing but it works fast enough at the Mo.
PS Mars Bar for anyone who can tell me how to LOSE THE WHILE LOOP !!
GW|||Will Probably end up tidying it up some indentation wouldn't hurt, man ;)|||I don't think you need the @.CallData table and
SELECTDATEADD(mi,1,MAX(DTBlock))FROM @.TimeBlockEvery time you select max you scan the table and the more rows you add the more there is to scan
Rather do this
select @.seq=0, @.MinReq =DATEDIFF(mi,@.FromDate,@.ToDate)
WHILE(@.seq<@.MinReq)
BEGIN
INSERT INTO @.TimeBlock SELECT DATEADD(mi,@.seq,@.FromDate)
SET @.seq = @.seq + 1
END
You could avoid the while loop if you had a numbers table e.g.
select DTBlock=dateadd(mi,numbers.seqno-1,@.FromDate)
from numbers
where seqno<=DATEDIFF(mi,@.FromDate,@.ToDate)+1
You can generate a numbers table by selecting from a large table
e.g.
select top 50000 seq=identity(int,1,1) into numbers from some_big_table
and you can handle all in a single query if you join to the numbers table|||r937 - 4 Sure - looks fine in my Query Editor but everything (including spaces) is stripped off when pasting between #Code# blocks for some reason ?.
Glad it keeps the Pretty Colours though.
Yup pdreyer - using the @.seq variable is gonna make a lot of sense - not sure about a numbers table though as I prefer not to create ancilliary/metadata tables unless I absolutely have to.
I'll have a think & post back optimized Code
Thanks Chaps
GW|||Had some time to play and test what I said before.
The left join can be a killer and it seems you need an interim table
I generated this test data
create table CallActionSummary (Call_Def int,NotiTime datetime,SSTotConn int
, CallCode char(10), ssOpConn int, ReasonRef int)
insert into CallActionSummary select
10942086 ,'2007-04-01 00:01:09.000',622 ,'ok',2,1 union all select
10038553 ,'2007-04-01 00:01:24.000',0 ,'ok',2,1 union all select
10942071 ,'2007-04-01 00:01:25.000',78 ,'ok',2,1 union all select
10942271 ,'2007-04-01 00:02:57.000',124 ,'ok',2,1 union all select
10942283 ,'2007-04-01 00:05:54.000',56 ,'ok',2,1 union all select
10942079 ,'2007-04-01 00:07:15.000',31 ,'ok',2,1 union all select
10942287 ,'2007-04-01 00:07:30.000',42 ,'ok',2,1 union all select
10942289 ,'2007-04-01 00:07:49.000',44 ,'ok',2,1 union all select
10942090 ,'2007-04-01 00:09:52.000',228 ,'ok',2,1 union all select
10942094 ,'2007-04-01 00:15:18.000',27 ,'ok',2,1 union all select
10942306 ,'2007-04-01 00:15:37.000',49 ,'ok',2,1 union all select
10942104 ,'2007-04-01 00:17:51.000',38 ,'ok',2,1 union all select
1 ,'2007-04-01 02:17:51.000',38 ,'ok',2,1 --union all select
insert into CallActionSummary select Call_Def,dateadd(hh,1,NotiTime),SSTotConn ,'ok',2,10 from CallActionSummary
insert into CallActionSummary select Call_Def,dateadd(hh,5,NotiTime),SSTotConn ,'ok',2,10 from CallActionSummary
insert into CallActionSummary select Call_Def,dateadd(hh,12,NotiTime),SSTotConn ,'ok',3,1 from CallActionSummary
insert into CallActionSummary select Call_Def,dateadd(dd,1,NotiTime),SSTotConn ,'ok',3,10 from CallActionSummary
insert into CallActionSummary select Call_Def,dateadd(dd,2,NotiTime),SSTotConn ,'ok',3,10 from CallActionSummary
insert into CallActionSummary select Call_Def,dateadd(dd,4,NotiTime),SSTotConn ,'ok',4,10 from CallActionSummary
insert into CallActionSummary select Call_Def,dateadd(dd,8,NotiTime),SSTotConn ,'ok',4,10 from CallActionSummary
insert into CallActionSummary select Call_Def,dateadd(dd,16,NotiTime),SSTotConn ,'ok',4,1 from CallActionSummary
insert into CallActionSummary select Call_Def,dateadd(mi,30,NotiTime),SSTotConn ,'ok',5,1 from CallActionSummary
insert into CallActionSummary select Call_Def,dateadd(mm,-2,NotiTime),SSTotConn ,'ok',5,1 from CallActionSummary
insert into CallActionSummary select Call_Def,NotiTime,SSTotConn,'16',5,1 from CallActionSummary
union all select Call_Def,NotiTime,SSTotConn,'not ok',0,1 from CallActionSummary
union all select Call_Def,NotiTime,SSTotConn,'not ok',10,5 from CallActionSummary
insert into CallActionSummary select Call_Def,dateadd(mm,-4,NotiTime),SSTotConn ,'ok',5,1 from CallActionSummary
insert into CallActionSummary select Call_Def,dateadd(yy,-1,NotiTime),SSTotConn ,'ok',5,1 from CallActionSummary
insert into CallActionSummary select Call_Def,dateadd(yy,-4,NotiTime),SSTotConn ,'ok',5,1 from CallActionSummary
create index ix1 on CallActionSummary (NotiTime)
and then created this numbers table (You might want to include the generation of a temporary #numbers table in your proc instead)
select top 43200 seq=identity(int,1,1) into numbers
from CallActionSummary
create unique clustered index ix1 on numbers(seq)
Table sizes during test:
name rows reserved data index_size unused
------ ---- ------ ------ ------ ---
CallActionSummary 425984 28408 KB 19040 KB 9328 KB 40 KB
numbers 43200 576 KB 560 KB 16 KB 0 KB
Tried this query (1 day):
declare @.FromDate DateTime, @.ToDate DateTime
select @.FromDate='2007-04-01 00:00:00', @.ToDate='2007-04-01 23:59:59'
select dateadd(mi,seq-1,@.FromDate) DTBlock ,count(NotiTime) CallQty
into #t3 -- used to check runtime without passing data to client
from numbers t1
left join CallActionSummary t2
on t1.seq between datediff(mi,@.FromDate,NotiTime)+1
and (datediff(ss,@.FromDate,NotiTime)+SSTotConn)/60+1
and NotiTime between @.FromDate and @.ToDate
AND RTRIM(LTRIM -- rtrim,ltrim really needed?
(CallCode )) NOT IN ('','B','F','4','Q','H','O','P','16')-- Background
AND ssOpConn > 0
AND ReasonRef <> 5
where t1.seq between 1 and datediff(mi,@.FromDate,@.ToDate)+1
group by t1.seq
order by 1
drop table #t3 -- used to check runtime without passing data to client
(1440 row(s) affected)
Table 'Worktable'. Scan count 621, logical reads 831, physical reads 0, read-ahead reads 0.
Table 'CallActionSummary'. Scan count 2, logical reads 4760, physical reads 0, read-ahead reads 0.
Table 'numbers'. Scan count 2, logical reads 5, physical reads 0, read-ahead reads 0.
Table 'Worktable'. Scan count 817, logical reads 1027, physical reads 0, read-ahead reads 0.
Table '#t3__<snip>'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0.
SQL Server Execution Times: CPU time = 765 ms, elapsed time = 557 ms.
Then 10 days and expected everything times 10
but the time was much worse
and logical reads on 'Worktable' was way up (what's with the worktable?)
...
select @.FromDate='2007-04-01 00:00:00', @.ToDate='2007-04-10 23:59:59'
...
(14400 row(s) affected)
Table 'Worktable'. Scan count 6935, logical reads 43697, physical reads 0, read-ahead reads 0.
Table 'CallActionSummary'. Scan count 2, logical reads 4760, physical reads 0, read-ahead reads 0.
Table 'numbers'. Scan count 2, logical reads 25, physical reads 0, read-ahead reads 0.
Table 'Worktable'. Scan count 7463, logical reads 46865, physical reads 0, read-ahead reads 0.
Table '#t3__<snip>'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0.
SQL Server Execution Times: CPU time = 37672 ms, elapsed time = 19758 ms.
And I killed the test on 30 days after waiting for more than 2 minutes
It seems you need an interim table to solve this
Changed query to:
declare @.FromDate DateTime, @.ToDate DateTime
select @.FromDate='2007-04-01 00:00:00', @.ToDate='2007-04-01 23:59:59'
select dateadd(mi,a.seq-1,@.FromDate) DTBlock, case when b.CallQty is null then 0 else b.CallQty end CallQty
into #t3 -- used to check runtime without passing data to client
from numbers a left join (
select seq, count(*) CallQty --count(NotiTime) CallQty
from numbers t1
join CallActionSummary t2 --with (index (ix1))
on t1.seq between datediff(mi,@.FromDate,NotiTime)+1
and (datediff(ss,@.FromDate,NotiTime)+SSTotConn)/60+1
and NotiTime between @.FromDate and @.ToDate
AND RTRIM(LTRIM -- rtrim,ltrim really needed?
(CallCode )) NOT IN ('','B','F','4','Q','H','O','P','16')-- Background
AND ssOpConn > 0
AND ReasonRef <> 5
where t1.seq between 1 and datediff(mi,@.FromDate,@.ToDate)+1
group by t1.seq
) b on a.seq=b.seq
where a.seq between 1 and datediff(mi,@.FromDate,@.ToDate)+1
order by 1
drop table #t3 -- used to check runtime without passing data to client
(1440 row(s) affected)
Table 'numbers'. Scan count 210, logical reads 451, physical reads 0, read-ahead reads 0.
Table 'CallActionSummary'. Scan count 2, logical reads 2380, physical reads 0, read-ahead reads 0.
Table 't3__<snip>'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0.
SQL Server Execution Times: CPU time = 187 ms, elapsed time = 250 ms.
No more 'Worktable', and the run for 30 days is no longer a problem
...
select @.FromDate='2007-04-01 00:00:00', @.ToDate='2007-04-30 23:59:59'
...
(43200 row(s) affected)
Table 'numbers'. Scan count 6242, logical reads 13015, physical reads 0, read-ahead reads 0.
Table 'CallActionSummary'. Scan count 2, logical reads 2380, physical reads 0, read-ahead reads 0.
Table '#t3__<snip>'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0.
SQL Server Execution Times: CPU time = 719 ms, elapsed time = 752 ms.
Hope this help you to find a suitable solution for your problem|||You don't need a table with a row for every minute in your date range. As you said, for 30 days, you would need 43200 minutes (rows). You actually only need as many rows as the length of the longest call. If the longest call is 26 minutes, your derived table only needs 26 rows. And since the datatype is integer, the performance and resource usage will be no problem.
Thanks to r937, I learned how handy an "integers" table can be (as pdreyer suggested with the numbers table).
Or you could generate the table variable as your example showed. The changes I made were to declare the datetimes as smalldatetime, since you only need precision to the minute. The field in the @.TimeBlock variable is of type int. (Should have changed the variable name to match). Changed @.MinReq to @.MaxReq since it's getting its value from the Max() function. I also wasn't sure about the structure of your "CallActionSummary" table, or the WHERE NOT IN clause (background).
You'll notice that instead of showing all minutes, this method will only show the minutes during which at least one call was taking place.
ALTER PROCEDURE LineUtilisation @.FromDate smalldatetime, @.ToDate smalldatetime
AS
SET NOCOUNT ON
DECLARE @.TimeBlock TABLE (DTBlock int)
DECLARE @.CallData TABLE(Call_Def Int,StartTime smalldatetime,EndTime smalldatetime)
DECLARE @.MaxReq Int
SET @.MaxReq = (SELECT MAX(DATEDIFF(minute, StartTime, EndTime)) FROM CallData)
INSERT INTO @.TimeBlock
SELECT @.MaxReq
WHILE (@.MaxReq > 0)
BEGIN
SET @.MaxReq = @.MaxReq - 1
INSERT INTO @.TimeBlock
SELECT @.MaxReq
END
/*
Wasn't too sure about the structure of your "CallActionSummary" table
so instead referenced the "CallData" table you used earlier in your post
*/
INSERT INTO @.CallData
SELECT
CallId,StartTime,EndTime
FROM CallData
WHERE StartTime Between @.FromDate AND @.ToDate /* if you want to filter on a date range */
SELECT DATEADD(minute, a.DTBlock, b.StartTime) AS Minutes, COUNT(DATEADD(minute, a.DTBlock, b.StartTime)) AS CallQty
FROM @.TimeBlock a INNER JOIN @.CallData b
ON a.DTBlock BETWEEN 0 AND DATEDIFF(minute, b.StartTime, b.EndTime)
GROUP BY DATEADD(minute, a.DTBlock, b.StartTime)
ORDER BY Minutes|||Thanks to r937, I learned how handy an "integers" table can be (as pdreyer suggested with the numbers table).thank you :cool:|||Overall I'm trying analyze how many telephone lines we need
If this is your overall goal, then the solution is very, very simple.
This query will tell you the maximum number of concurrent phonecalls per month
select year(starttime),month(starttime),max(cnt) from
(select dv.starttime, (select count(*) from calllog l where l.starttime<=dv.starttime and l.endtime>dv.starttime) as cnt
from (select distinct starttime from calllog) dv) t
group by year(starttime),month(starttime)
You can group the starttime column to fit your need. Group by day if you want to.
This query is based on samples from the book "Inside Microsoft SQL Server 2005 T-SQL PROGRAMMING" by guru Itzik Ben-Gan|||Thanks kaffenils
But the Query as suggested
setdateformat ymd
selectDATEPART(hour,NotiTime),max(cnt)
from
(select dv.NotiTime,(selectcount(*)
from CallActionSummary l
where l.NotiTime<=dv.NotiTime andDATEADD(ss,l.SSTotConn,l.NotiTime)>dv.NotiTime
)as cnt
from(selectdistinct NotiTime from CallActionSummary
) dv
) t
where NotiTime BETWEEN'2007-05-13'AND'2007-05-14'
groupbyDATEPART(hour,NotiTime)
Took just over one Hour to run a single day sooo..... I'm gonna get my head round the other suggestions and then post back.
Thanks Guys for all your hard work - your truly going above and beyond the call on this one.
GW|||Create an index on NotiTime. That would hopefully improve performance.
Hope you can help with this - it's certainly got me scratching my head.
I'm Querying a Call Centre Database
I have a Table of Call Data with a Start Time & End Time of the Call.
(CallID,Started,Ended)
10942086 2007-04-01 00:01:09.000 2007-04-01 00:11:31.000
1003855355 2007-04-01 00:01:24.000 2007-04-01 00:01:24.000
10942071 2007-04-01 00:01:25.000 2007-04-01 00:02:43.000
10942271 2007-04-01 00:02:57.000 2007-04-01 00:05:01.000
10942283 2007-04-01 00:05:54.000 2007-04-01 00:06:50.000
10942079 2007-04-01 00:07:15.000 2007-04-01 00:07:46.000
10942287 2007-04-01 00:07:30.000 2007-04-01 00:08:12.000
10942289 2007-04-01 00:07:49.000 2007-04-01 00:08:33.000
I'm trying to produce Stats that tell me how many Calls were live in any one given minute.
Ultimately I will be producing a Line Graph of No of Calls Connected grouped by Minute.
I've gone as far as creating a temp table with every minute in a month with the following query maybe to join to but not sure if this will help me.
WHILE(@.cnt <= 43200)
BEGIN
SELECT @.MaxDate =DATEADD(mi,1,MAX(DTBlock))FROM AprilMinutes
INSERTINTO AprilMinutes VALUES(@.MaxDate,NULL)
SET @.cnt = @.Cnt +1
END
Which produces a nifty little table with
01/04/2007 00:09:00
01/04/2007 00:10:00
01/04/2007 00:11:00
01/04/2007 00:12:00
01/04/2007 00:13:00
01/04/2007 00:14:00
01/04/2007 00:15:00
01/04/2007 00:16:00
If one individual Call Spans 2 minutes I'll count it as 1 in the first minute & 1 in the second minute.
Overall I'm trying analyze how many telephone lines we need
Any Help much, much appreciated
Thanks
GWselect AprilMinutes.datetimecolumn
, count(CallData.StartTime) as active_calls
from AprilMinutes
left outer
join CallData
on AprilMinutes.datetimecolumn
between CallData.StartTime
and CallData.EndTime
group
by AprilMinutes.datetimecolumn|||Tehe
Thanks r937 - Though art a veritable GURU of SQL'ness
My meager Server is crunching the TOP 100 as we speak (creak, grind)
However :eek:
What will happen if a call arrives at eg. 00:05:10 and Ends at 00:05:55
methinks it will not get counted ?
please tell me i'm wrong|||please tell me i'm wrongnope, thou art correct
here's an idea (too busy to test it meself): in the query round the start time down to the nearest previous minute, and round the end time up to the next minute, then instead of BETWEEN, use >= and <|||Whooooaa
Close but no Bannana's
The suggestion produces
(DateTimeBlock,CountOf)
2007-04-01 00:00:00.000 2
2007-04-01 00:01:00.000 4
2007-04-01 00:02:00.000 5
2007-04-01 00:03:00.000 3
2007-04-01 00:04:00.000 7
2007-04-01 00:05:00.000 8
2007-04-01 00:06:00.000 8
2007-04-01 00:07:00.000 8
2007-04-01 00:08:00.000 8
2007-04-01 00:09:00.000 7
From
(CallID,Start,End,FormatedStart,FormatedEnd)
10942086 2007-04-01 00:01:09.000 2007-04-01 00:11:31.000 2007-04-01 00:01:00.000 2007-04-01 00:12:00.000
10038553 2007-04-01 00:01:24.000 2007-04-01 00:01:24.000 2007-04-01 00:01:00.000 2007-04-01 00:02:00.000
10942071 2007-04-01 00:01:25.000 2007-04-01 00:02:43.000 2007-04-01 00:01:00.000 2007-04-01 00:03:00.000
10942271 2007-04-01 00:02:57.000 2007-04-01 00:05:01.000 2007-04-01 00:02:00.000 2007-04-01 00:05:00.000
10942283 2007-04-01 00:05:54.000 2007-04-01 00:06:50.000 2007-04-01 00:05:00.000 2007-04-01 00:07:00.000
10942079 2007-04-01 00:07:15.000 2007-04-01 00:07:46.000 2007-04-01 00:07:00.000 2007-04-01 00:08:00.000
10942287 2007-04-01 00:07:30.000 2007-04-01 00:08:12.000 2007-04-01 00:07:00.000 2007-04-01 00:09:00.000
10942289 2007-04-01 00:07:49.000 2007-04-01 00:08:33.000 2007-04-01 00:07:00.000 2007-04-01 00:09:00.000
10942090 2007-04-01 00:09:52.000 2007-04-01 00:13:40.000 2007-04-01 00:09:00.000 2007-04-01 00:14:00.000
10942094 2007-04-01 00:15:18.000 2007-04-01 00:15:45.000 2007-04-01 00:15:00.000 2007-04-01 00:16:00.000
10942306 2007-04-01 00:15:37.000 2007-04-01 00:16:26.000 2007-04-01 00:15:00.000 2007-04-01 00:17:00.000
10942104 2007-04-01 00:17:51.000 2007-04-01 00:18:29.000 2007-04-01 00:17:00.000 2007-04-01 00:19:00.000
I Estimate it should produce2007-04-01 00:00:00.000 0
2007-04-01 00:01:00.000 3
2007-04-01 00:02:00.000 3
2007-04-01 00:03:00.000 2
2007-04-01 00:04:00.000 2
2007-04-01 00:05:00.000 3
Do you think it will help if I post some code to create Test Tables n Data
I had hoped maybe someone familiar with a Call Centre / telephone type operation would have done this kind of thing.
I'm not sure why your suggestion does'nt work - I'll continue studying it.
:confused:
Thanks|||Oh NO !!
Everyone seems to have given up :eek: :eek: :eek: :eek:
Surely there's a SQL God out there who could make a constructive suggestion.
Come on Chaps OR I'll break out the CURSORS - tehe.
Only Joking.
SELECT CASE WHEN @.GoodSugestions >= 1
THEN
'Thankyou'
ELSE
'Struggle On'
END
Thanks to r937, you've brought me very close.
GW|||can you show the query you developed based on my suggestion in post #4|||Here's the RAW code exactly as used
select top 10 AprilMinutes.DTBlock
,count(callActionSummary.NotiTime)as active_calls
from AprilMinutes
left outer
join callActionSummary
on AprilMinutes.DTBlock >=LEFT(dbo.FormatDate(NotiTime,'yyyy-mm-dd hh:mm:ss.Ms'),16)+':00.000'
and AprilMinutes.DTBlock <DATEADD(mi,1,LEFT(dbo.FormatDate(DATEADD(ss,ssopco nn,NotiTime),'yyyy-mm-dd hh:mm:ss.Ms'),16)+':00')
group
by AprilMinutes.DTBlock
orderby AprilMinutes.DTBlock
Do you think possibly the Date manipulation / dbo.FormatDate is affecting it ?
ssopconn is Int (operator connected seconds)
NotiTime is Datetime (Time Call arrived)
dbo.FormatDate returns nVarChar (custom UDF) hopefuly implicit conversion to date ??
GW|||Do you think possibly the Date manipulation / dbo.FormatDate is affecting it ?it's a possibility ;)
actually, now that i think about it, you would only have to round the start time down to the current minute, to have DTBlock >= pick it up
then if ssopconn doesn't extend the time into the next minute, the call will still be counted in that minute
anyhow, there is a much better way to round down (would've mentioned it yesterday but i was too busy to look it up)
SELECT DATEADD(<datepart>, DATEDIFF(<datepart>, <refdatetime>, <datetime>), <refdatetime>)
which simply adds back the same number of whole dateparts to the reference datetime as there are between the reference datetime and the given datetime
the excess just disappears ;)
example:
SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0) returns midnight of first day of current month (try it :))
reference date of 0 equals the sql server zero datetime, whatever that may be, jan 1 1900 or whatever
it doesn't actually matter what the exact zero datetime is, because it subtracts back out ;)
expression for your situation:
DATEADD(mi, DATEDIFF(mi, '2000-01-01', NotiTime), '2000-01-01')
reference date is chosen closer to expected values, although zero still works
this expression rounds NotiTime down to current minute and is a lot more efficient than string manipulation
:)|||select a.datetimecolumn, count(b.StartTime)
from AprilMinutes a left join CallData b
on a.datetimecolumn between convert(varchar(17),b.StartTime ,113)
and b.EndTime --dateadd(ms,59997, convert(varchar(17),b.EndTime,113) )
group by a.datetimecolumn|||Sorry Chaps
Not had time to test these yet but will do tomorrow
Someone decided to change the CodePages on the Production SyBase servers from 850 to 1252 last night & hoped it would'nt have much of an effect - :shocked: - lol
I'll keep you posted re results of your suggestions
thanks
GW|||expression for your situation:
DATEADD(mi, DATEDIFF(mi, '2000-01-01', NotiTime), '2000-01-01')
is a lot more efficient than string manipulation
Revised queryselect a.DTBlock, count(b.StartTime)
from AprilMinutes a
left join CallData b
on a.DTBlock between dateadd(mi,datediff(mi,0,b.StartTime),0)
and b.EndTime
group by a.DTBlock|||WOW :D :D :D
r937 & pdreyer - Though art Veritable GODS of SQL ness
Worked a Treat & triple fast too.
r937 had it with the Left Outer and pdreyer swooped in with the
between dateadd(mi,datediff(mi,0,b.StartTime),0) and b.EndTime
I'm humbled in your presence - Grovel, Snivel
Many Thanks to the both of you - tis muchly appreciated
Grant|||Just to Round things Off
This is what I did with the Code
ALTERPROCEDURE LineUtilisation @.FromDate DateTime, @.ToDate DateTime
AS
SETNOCOUNTON
DECLARE @.TimeBlock TABLE(DTBlock DateTime)
DECLARE @.CallData TABLE(Call_Def Int,StartTime DateTime,EndTime DateTime)
DECLARE @.MinReq Int
INSERTINTO @.TimeBlock SELECT @.FromDate
SET @.MinReq =DATEDIFF(mi,@.FromDate,@.ToDate)
WHILE(@.MinReq > 1)
BEGIN
INSERTINTO @.TimeBlock
SELECTDATEADD(mi,1,MAX(DTBlock))FROM @.TimeBlock
SET @.MinReq = @.MinReq - 1
END
INSERTINTO @.CallData
SELECT
Call_Def,
NotiTime StartTime,
DATEADD(ss,SSTotConn,NotiTime) EndTime
FROM CallActionSummary
WHERE NotiTime between @.FromDate AND @.ToDate
ANDRTRIM(LTRIM(CallCode ))NOTIN('','B','F','4','Q','H','O','P','16')-- Background
AND ssOpConn > 0
AND ReasonRef <> 5
ORDERBY 2
SELECT a.DTBlock,COUNT(b.StartTime) CallQty
FROM @.TimeBlock a
LEFTJOIN @.CallData b
ON a.DTBlock BETWEENDATEADD(mi,DATEDIFF(mi,0,b.StartTime),0)
AND b.EndTime
GROUPBY a.DTBlock
ORDERBY 1
Will Probably end up tidying it up - poss with some indexing but it works fast enough at the Mo.
PS Mars Bar for anyone who can tell me how to LOSE THE WHILE LOOP !!
GW|||Will Probably end up tidying it up some indentation wouldn't hurt, man ;)|||I don't think you need the @.CallData table and
SELECTDATEADD(mi,1,MAX(DTBlock))FROM @.TimeBlockEvery time you select max you scan the table and the more rows you add the more there is to scan
Rather do this
select @.seq=0, @.MinReq =DATEDIFF(mi,@.FromDate,@.ToDate)
WHILE(@.seq<@.MinReq)
BEGIN
INSERT INTO @.TimeBlock SELECT DATEADD(mi,@.seq,@.FromDate)
SET @.seq = @.seq + 1
END
You could avoid the while loop if you had a numbers table e.g.
select DTBlock=dateadd(mi,numbers.seqno-1,@.FromDate)
from numbers
where seqno<=DATEDIFF(mi,@.FromDate,@.ToDate)+1
You can generate a numbers table by selecting from a large table
e.g.
select top 50000 seq=identity(int,1,1) into numbers from some_big_table
and you can handle all in a single query if you join to the numbers table|||r937 - 4 Sure - looks fine in my Query Editor but everything (including spaces) is stripped off when pasting between #Code# blocks for some reason ?.
Glad it keeps the Pretty Colours though.
Yup pdreyer - using the @.seq variable is gonna make a lot of sense - not sure about a numbers table though as I prefer not to create ancilliary/metadata tables unless I absolutely have to.
I'll have a think & post back optimized Code
Thanks Chaps
GW|||Had some time to play and test what I said before.
The left join can be a killer and it seems you need an interim table
I generated this test data
create table CallActionSummary (Call_Def int,NotiTime datetime,SSTotConn int
, CallCode char(10), ssOpConn int, ReasonRef int)
insert into CallActionSummary select
10942086 ,'2007-04-01 00:01:09.000',622 ,'ok',2,1 union all select
10038553 ,'2007-04-01 00:01:24.000',0 ,'ok',2,1 union all select
10942071 ,'2007-04-01 00:01:25.000',78 ,'ok',2,1 union all select
10942271 ,'2007-04-01 00:02:57.000',124 ,'ok',2,1 union all select
10942283 ,'2007-04-01 00:05:54.000',56 ,'ok',2,1 union all select
10942079 ,'2007-04-01 00:07:15.000',31 ,'ok',2,1 union all select
10942287 ,'2007-04-01 00:07:30.000',42 ,'ok',2,1 union all select
10942289 ,'2007-04-01 00:07:49.000',44 ,'ok',2,1 union all select
10942090 ,'2007-04-01 00:09:52.000',228 ,'ok',2,1 union all select
10942094 ,'2007-04-01 00:15:18.000',27 ,'ok',2,1 union all select
10942306 ,'2007-04-01 00:15:37.000',49 ,'ok',2,1 union all select
10942104 ,'2007-04-01 00:17:51.000',38 ,'ok',2,1 union all select
1 ,'2007-04-01 02:17:51.000',38 ,'ok',2,1 --union all select
insert into CallActionSummary select Call_Def,dateadd(hh,1,NotiTime),SSTotConn ,'ok',2,10 from CallActionSummary
insert into CallActionSummary select Call_Def,dateadd(hh,5,NotiTime),SSTotConn ,'ok',2,10 from CallActionSummary
insert into CallActionSummary select Call_Def,dateadd(hh,12,NotiTime),SSTotConn ,'ok',3,1 from CallActionSummary
insert into CallActionSummary select Call_Def,dateadd(dd,1,NotiTime),SSTotConn ,'ok',3,10 from CallActionSummary
insert into CallActionSummary select Call_Def,dateadd(dd,2,NotiTime),SSTotConn ,'ok',3,10 from CallActionSummary
insert into CallActionSummary select Call_Def,dateadd(dd,4,NotiTime),SSTotConn ,'ok',4,10 from CallActionSummary
insert into CallActionSummary select Call_Def,dateadd(dd,8,NotiTime),SSTotConn ,'ok',4,10 from CallActionSummary
insert into CallActionSummary select Call_Def,dateadd(dd,16,NotiTime),SSTotConn ,'ok',4,1 from CallActionSummary
insert into CallActionSummary select Call_Def,dateadd(mi,30,NotiTime),SSTotConn ,'ok',5,1 from CallActionSummary
insert into CallActionSummary select Call_Def,dateadd(mm,-2,NotiTime),SSTotConn ,'ok',5,1 from CallActionSummary
insert into CallActionSummary select Call_Def,NotiTime,SSTotConn,'16',5,1 from CallActionSummary
union all select Call_Def,NotiTime,SSTotConn,'not ok',0,1 from CallActionSummary
union all select Call_Def,NotiTime,SSTotConn,'not ok',10,5 from CallActionSummary
insert into CallActionSummary select Call_Def,dateadd(mm,-4,NotiTime),SSTotConn ,'ok',5,1 from CallActionSummary
insert into CallActionSummary select Call_Def,dateadd(yy,-1,NotiTime),SSTotConn ,'ok',5,1 from CallActionSummary
insert into CallActionSummary select Call_Def,dateadd(yy,-4,NotiTime),SSTotConn ,'ok',5,1 from CallActionSummary
create index ix1 on CallActionSummary (NotiTime)
and then created this numbers table (You might want to include the generation of a temporary #numbers table in your proc instead)
select top 43200 seq=identity(int,1,1) into numbers
from CallActionSummary
create unique clustered index ix1 on numbers(seq)
Table sizes during test:
name rows reserved data index_size unused
------ ---- ------ ------ ------ ---
CallActionSummary 425984 28408 KB 19040 KB 9328 KB 40 KB
numbers 43200 576 KB 560 KB 16 KB 0 KB
Tried this query (1 day):
declare @.FromDate DateTime, @.ToDate DateTime
select @.FromDate='2007-04-01 00:00:00', @.ToDate='2007-04-01 23:59:59'
select dateadd(mi,seq-1,@.FromDate) DTBlock ,count(NotiTime) CallQty
into #t3 -- used to check runtime without passing data to client
from numbers t1
left join CallActionSummary t2
on t1.seq between datediff(mi,@.FromDate,NotiTime)+1
and (datediff(ss,@.FromDate,NotiTime)+SSTotConn)/60+1
and NotiTime between @.FromDate and @.ToDate
AND RTRIM(LTRIM -- rtrim,ltrim really needed?
(CallCode )) NOT IN ('','B','F','4','Q','H','O','P','16')-- Background
AND ssOpConn > 0
AND ReasonRef <> 5
where t1.seq between 1 and datediff(mi,@.FromDate,@.ToDate)+1
group by t1.seq
order by 1
drop table #t3 -- used to check runtime without passing data to client
(1440 row(s) affected)
Table 'Worktable'. Scan count 621, logical reads 831, physical reads 0, read-ahead reads 0.
Table 'CallActionSummary'. Scan count 2, logical reads 4760, physical reads 0, read-ahead reads 0.
Table 'numbers'. Scan count 2, logical reads 5, physical reads 0, read-ahead reads 0.
Table 'Worktable'. Scan count 817, logical reads 1027, physical reads 0, read-ahead reads 0.
Table '#t3__<snip>'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0.
SQL Server Execution Times: CPU time = 765 ms, elapsed time = 557 ms.
Then 10 days and expected everything times 10
but the time was much worse
and logical reads on 'Worktable' was way up (what's with the worktable?)
...
select @.FromDate='2007-04-01 00:00:00', @.ToDate='2007-04-10 23:59:59'
...
(14400 row(s) affected)
Table 'Worktable'. Scan count 6935, logical reads 43697, physical reads 0, read-ahead reads 0.
Table 'CallActionSummary'. Scan count 2, logical reads 4760, physical reads 0, read-ahead reads 0.
Table 'numbers'. Scan count 2, logical reads 25, physical reads 0, read-ahead reads 0.
Table 'Worktable'. Scan count 7463, logical reads 46865, physical reads 0, read-ahead reads 0.
Table '#t3__<snip>'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0.
SQL Server Execution Times: CPU time = 37672 ms, elapsed time = 19758 ms.
And I killed the test on 30 days after waiting for more than 2 minutes
It seems you need an interim table to solve this
Changed query to:
declare @.FromDate DateTime, @.ToDate DateTime
select @.FromDate='2007-04-01 00:00:00', @.ToDate='2007-04-01 23:59:59'
select dateadd(mi,a.seq-1,@.FromDate) DTBlock, case when b.CallQty is null then 0 else b.CallQty end CallQty
into #t3 -- used to check runtime without passing data to client
from numbers a left join (
select seq, count(*) CallQty --count(NotiTime) CallQty
from numbers t1
join CallActionSummary t2 --with (index (ix1))
on t1.seq between datediff(mi,@.FromDate,NotiTime)+1
and (datediff(ss,@.FromDate,NotiTime)+SSTotConn)/60+1
and NotiTime between @.FromDate and @.ToDate
AND RTRIM(LTRIM -- rtrim,ltrim really needed?
(CallCode )) NOT IN ('','B','F','4','Q','H','O','P','16')-- Background
AND ssOpConn > 0
AND ReasonRef <> 5
where t1.seq between 1 and datediff(mi,@.FromDate,@.ToDate)+1
group by t1.seq
) b on a.seq=b.seq
where a.seq between 1 and datediff(mi,@.FromDate,@.ToDate)+1
order by 1
drop table #t3 -- used to check runtime without passing data to client
(1440 row(s) affected)
Table 'numbers'. Scan count 210, logical reads 451, physical reads 0, read-ahead reads 0.
Table 'CallActionSummary'. Scan count 2, logical reads 2380, physical reads 0, read-ahead reads 0.
Table 't3__<snip>'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0.
SQL Server Execution Times: CPU time = 187 ms, elapsed time = 250 ms.
No more 'Worktable', and the run for 30 days is no longer a problem
...
select @.FromDate='2007-04-01 00:00:00', @.ToDate='2007-04-30 23:59:59'
...
(43200 row(s) affected)
Table 'numbers'. Scan count 6242, logical reads 13015, physical reads 0, read-ahead reads 0.
Table 'CallActionSummary'. Scan count 2, logical reads 2380, physical reads 0, read-ahead reads 0.
Table '#t3__<snip>'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0.
SQL Server Execution Times: CPU time = 719 ms, elapsed time = 752 ms.
Hope this help you to find a suitable solution for your problem|||You don't need a table with a row for every minute in your date range. As you said, for 30 days, you would need 43200 minutes (rows). You actually only need as many rows as the length of the longest call. If the longest call is 26 minutes, your derived table only needs 26 rows. And since the datatype is integer, the performance and resource usage will be no problem.
Thanks to r937, I learned how handy an "integers" table can be (as pdreyer suggested with the numbers table).
Or you could generate the table variable as your example showed. The changes I made were to declare the datetimes as smalldatetime, since you only need precision to the minute. The field in the @.TimeBlock variable is of type int. (Should have changed the variable name to match). Changed @.MinReq to @.MaxReq since it's getting its value from the Max() function. I also wasn't sure about the structure of your "CallActionSummary" table, or the WHERE NOT IN clause (background).
You'll notice that instead of showing all minutes, this method will only show the minutes during which at least one call was taking place.
ALTER PROCEDURE LineUtilisation @.FromDate smalldatetime, @.ToDate smalldatetime
AS
SET NOCOUNT ON
DECLARE @.TimeBlock TABLE (DTBlock int)
DECLARE @.CallData TABLE(Call_Def Int,StartTime smalldatetime,EndTime smalldatetime)
DECLARE @.MaxReq Int
SET @.MaxReq = (SELECT MAX(DATEDIFF(minute, StartTime, EndTime)) FROM CallData)
INSERT INTO @.TimeBlock
SELECT @.MaxReq
WHILE (@.MaxReq > 0)
BEGIN
SET @.MaxReq = @.MaxReq - 1
INSERT INTO @.TimeBlock
SELECT @.MaxReq
END
/*
Wasn't too sure about the structure of your "CallActionSummary" table
so instead referenced the "CallData" table you used earlier in your post
*/
INSERT INTO @.CallData
SELECT
CallId,StartTime,EndTime
FROM CallData
WHERE StartTime Between @.FromDate AND @.ToDate /* if you want to filter on a date range */
SELECT DATEADD(minute, a.DTBlock, b.StartTime) AS Minutes, COUNT(DATEADD(minute, a.DTBlock, b.StartTime)) AS CallQty
FROM @.TimeBlock a INNER JOIN @.CallData b
ON a.DTBlock BETWEEN 0 AND DATEDIFF(minute, b.StartTime, b.EndTime)
GROUP BY DATEADD(minute, a.DTBlock, b.StartTime)
ORDER BY Minutes|||Thanks to r937, I learned how handy an "integers" table can be (as pdreyer suggested with the numbers table).thank you :cool:|||Overall I'm trying analyze how many telephone lines we need
If this is your overall goal, then the solution is very, very simple.
This query will tell you the maximum number of concurrent phonecalls per month
select year(starttime),month(starttime),max(cnt) from
(select dv.starttime, (select count(*) from calllog l where l.starttime<=dv.starttime and l.endtime>dv.starttime) as cnt
from (select distinct starttime from calllog) dv) t
group by year(starttime),month(starttime)
You can group the starttime column to fit your need. Group by day if you want to.
This query is based on samples from the book "Inside Microsoft SQL Server 2005 T-SQL PROGRAMMING" by guru Itzik Ben-Gan|||Thanks kaffenils
But the Query as suggested
setdateformat ymd
selectDATEPART(hour,NotiTime),max(cnt)
from
(select dv.NotiTime,(selectcount(*)
from CallActionSummary l
where l.NotiTime<=dv.NotiTime andDATEADD(ss,l.SSTotConn,l.NotiTime)>dv.NotiTime
)as cnt
from(selectdistinct NotiTime from CallActionSummary
) dv
) t
where NotiTime BETWEEN'2007-05-13'AND'2007-05-14'
groupbyDATEPART(hour,NotiTime)
Took just over one Hour to run a single day sooo..... I'm gonna get my head round the other suggestions and then post back.
Thanks Guys for all your hard work - your truly going above and beyond the call on this one.
GW|||Create an index on NotiTime. That would hopefully improve performance.
Sunday, February 19, 2012
Completed(?) and urgent Triggers and Transactions question
I use the VB.NET transaction to update an sql server 2000 database. I call a number of stored procedures within this transaction.
The stored procedures will update tables. These tables use triggers..
My question is, when does the trigger get called? Is it after the each stored procedure, or is it after the whole transaction?
Jag
the trigger will execute when
- new records is being inserted into the table
- records get update
- records get deleted
from the table
|||In my opinion triggers fire as soon as the database updated, and transaction update your table as soon as a query fires. Having triggers do too much is a typical mistake. Triggers should be
left to handle only simple tasks.
cheers
|||
How would you define too much?
At the moment, one of my trigger calls a stored procedure that is doing about 4 selects on a table (one with an MAX()), and maybe a 2 updates.
The stored procedure should be optimised, so it should case too much problems later (?)
EDIT: I am expecting the number of selects to increase to about 10, and the number of updates to 3. The selects will be small selects that mostly pull out ids.
Jag
Hi,
The trigger will be fired immediately after the the update was executed. And the transaction will continue after trigger gets executed.
HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!
|||What I was afraid of was that the triggers would not get fired during the transaction, and would be fired after the commit. But after a little testing, I found this was not true.
I am still trying to find out how much I can put into a trigger before a performance becomes an issue. I am not expecting heavy usage - I'm not even expecting moderate usage on the site. Maybe about 10 inputs/edits a day, that's it.
I have written a stored procedure and the trigger calls this if needed. The stored procedure will have upto 20 select statements to pull out values from the database, and then a couple of updates. Is this too much? I don't see it being too much as I am not expecting too much input to be going on.
Jag
|||Just another quick question.
Same example - there is a transaction with a number of updates/inserts. The tables these apply to have triggers on then. The triggers are fired as soon as the update/insert happens, and not at the end of the transaction.
My question is, if the transaction fails and the changes are reversed, will the changes made by the triggers also be reversed. Common sense says that it should happen, but I need to ensure that it does.
Thanks in advance
Jag
Subscribe to:
Posts (Atom)