Hi,
What is the execution precedence if there exist more than one update, delete
or insert triggers
I mean if there are more than one type of trigger such as more than one
update trigger.
Also, Can we specify a precedence for them?
and last If we call an update statement in the same update trigger and for
the same table that has this trigger, Is the trigger considers itself, I
mean is it recurs or not, or is it disables itself for that execution period
?.
Thanks in advance...
Emre GuldoganThere is no guarantee as to what order they'll fire in.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Emre Guldogan" <ask me please...> wrote in message
news:e$KbhKMJFHA.656@.TK2MSFTNGP14.phx.gbl...
> Hi,
> What is the execution precedence if there exist more than one update,
delete
> or insert triggers
> I mean if there are more than one type of trigger such as more than one
> update trigger.
> Also, Can we specify a precedence for them?
> and last If we call an update statement in the same update trigger and for
> the same table that has this trigger, Is the trigger considers itself, I
> mean is it recurs or not, or is it disables itself for that execution
period
> ?.
> Thanks in advance...
> Emre Guldogan
>|||You can use sp_settriggerorder to define the first and last trigger to
execute. For the rest the order is undefined. If it matters to you then
put all the logic in one trigger.
David Portas
SQL Server MVP
--|||On Wed, 9 Mar 2005 18:11:48 +0200, "Emre Guldogan" <ask me please...>
wrote:
>What is the execution precedence if there exist more than one update, delet
e
>or insert triggers
>I mean if there are more than one type of trigger such as more than one
>update trigger.
>Also, Can we specify a precedence for them?
Hi Emre,
Already addressed by David and Adam.
>and last If we call an update statement in the same update trigger and for
>the same table that has this trigger, Is the trigger considers itself, I
>mean is it recurs or not, or is it disables itself for that execution perio
d
>?.
There are two options governing this:
1. Recursive triggers. If this is off, a trigger can't fire itself. So
if an update trigger on table AA performans an UPDATE AA command, that
specific trigger is not called. This disables only direct recursion: if
the update trigger on AA performs a DELETE FROM BB statement, and the
delete trigger on BB performs UPDATE AA, the trigger will be called
recursively.
The recursive triggers option is a database setting: it will affect all
triggers in the current database, but it won't affect other databases on
the same server. You use ALTER DATABASE to change it.
2. Nested triggers. If this is off, statements executed from a trigger
don't fire any trigger at all.
The nested triggers option is a server setting: it will affect all
triggers in all databases on the server. You use sp_configure to change
it.
Other options to prevent recursive execution of triggers for specific
triggers only are:
* TRIGGER_NESTLEVEL() - check this to find how many nested trigger
executions there currently are.
* TRGIGER_NESTLEVEL(object_ID('trigger_nam
e')) - check this to find how
many times the specified trigger is currently nested.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Showing posts with label overlapping. Show all posts
Showing posts with label overlapping. Show all posts
Saturday, February 25, 2012
Overlapping times
Hi All,
I have a table which logs calls called calls There are 3 main columns
user_id, starttime,endtime
I would like to run a query (a self-join i guess) which lists all
overlapping calls for a particular user_id.
In theory it should not be possible for a user to make 2 or more
simulataneous calls.
Any help would be greatly appreciated.
SergioYOu should only compare the start time with the data you have to see wheter
it overlaps
Select UserID from YourTable t1
inner join Yourtable t2
on userid = userid
Where t1.Starttime between t2.starttime and t2.endtime
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Serghios" <Serghios.Florides@.elthion.com> schrieb im Newsbeitrag
news:ORD1hXdZFHA.3648@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> I have a table which logs calls called calls There are 3 main columns
> user_id, starttime,endtime
> I would like to run a query (a self-join i guess) which lists all
> overlapping calls for a particular user_id.
> In theory it should not be possible for a user to make 2 or more
> simulataneous calls.
> Any help would be greatly appreciated.
> Sergio
>
>
>|||Sergio, try,
select c1.userid, c1.starttime as s1, c1.endtime as e1,
c2.starttime as s2, c2.endtime as e2
from calls as c1
join calls as c2
on c1.key_col <> c2.key_col
and c1.userid = c2.userid
and c1.starttime <= c2.endtime
and c1.endtime >= c2.starttime
-- not tested
BG, SQL Server MVP
www.SolidQualityLearning.com
"Serghios" <Serghios.Florides@.elthion.com> wrote in message
news:ORD1hXdZFHA.3648@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> I have a table which logs calls called calls There are 3 main columns
> user_id, starttime,endtime
> I would like to run a query (a self-join i guess) which lists all
> overlapping calls for a particular user_id.
> In theory it should not be possible for a user to make 2 or more
> simulataneous calls.
> Any help would be greatly appreciated.
> Sergio
>
>
>|||Unfortunately this query returns all rows in my table
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:ON1yhjdZFHA.1152@.tk2msftngp13.phx.gbl...
> YOu should only compare the start time with the data you have to see
> wheter it overlaps
> Select UserID from YourTable t1
> inner join Yourtable t2
> on userid = userid
> Where t1.Starttime between t2.starttime and t2.endtime
>
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Serghios" <Serghios.Florides@.elthion.com> schrieb im Newsbeitrag
> news:ORD1hXdZFHA.3648@.TK2MSFTNGP14.phx.gbl...
>|||That's because a period always overlaps with itself. You can eliminate the
self match by filtering t1.key <> t2.key, assuming you have a single column
key in the table. If the key is composite, use:
on t1.key1 <> t2.key1 or t1.key2 <> t2.key2, etc.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Serghios" <Serghios.Florides@.elthion.com> wrote in message
news:ODHsQrdZFHA.3712@.TK2MSFTNGP09.phx.gbl...
> Unfortunately this query returns all rows in my table
>
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:ON1yhjdZFHA.1152@.tk2msftngp13.phx.gbl...
>|||This can be more complicated that it looks on the surface. An overlap can ha
ppen
in one of two ways:
1. The start date of one range is between the start and end date of another
range.
2. The end date of one range is between the start and end date of another ra
nge.
It gets a little more complicated if end dates can be null.
Assuming no nulls, you would have something like:
Select F1..Fn
From Table As T1
Join Table As T2
On T1.PK <> T2.PK
And T1.User_Id = T2.User_Id
And (
(T1.Start >= T2.Start
And (T1.Start <= T2.End Or T1.End <= T2.End))
Or (T2.Start >= T1.Start
And (T2.Start <= T1.End Or T2.End <= T1.End))
)
If the dates can be null, you'll have to decide how that should be interpret
ed.
For example, if a null end date means that there is no end, then you would w
ant
to replace the end date with an arbitrarily large date (e.g. 9999-12-31).
Thomas
"Serghios" <Serghios.Florides@.elthion.com> wrote in message
news:ORD1hXdZFHA.3648@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> I have a table which logs calls called calls There are 3 main columns
> user_id, starttime,endtime
> I would like to run a query (a self-join i guess) which lists all overlapp
ing
> calls for a particular user_id.
> In theory it should not be possible for a user to make 2 or more simulatan
eous
> calls.
> Any help would be greatly appreciated.
> Sergio
>
>
>|||Think about this in reverse: Two events do not overlap if one ends
before the other begins, or if it begins after the end of other one.
Write that and then negate it.
SELECT E1.*
FROM Events AS E1,
Events AS E2
WHERE E1.event_key <> E2.event_key
AND E1.user_id = E2.user_id
AND NOT (E1.start_time > E2.end_time
OR E1.end_time < E2.end_time)
Or you can use DeMorgan's law on the last predicate:
AND (E1.start_time <= E2.end_time
AND E1.end_time >= E2.end_time)
There is also an OVERLAPS predicate in SQL-92 for this purpose,but it
is a bit tricky.|||Thomas,
Itzik's solution is simpler and equivalent. In words, it may be
easier to think of in terms of two lives. As long as people do
not die before they are born (something you would implement
as a constraint in the data model), the lives of X and Y
overlap if and only if X died after Y was born and Y died after
X was born. It seems too simple to work, but it works. Your
comment about NULL end dates is important. Here's one
solution to handle NULL meaning "not ended yet"
...
and coalesce(X.End,'99991231') > Y.Start
and coalesce(Y.End,'99991231') > X.Start
Steve Kass
Drew University
Thomas Coleman wrote:
>This can be more complicated that it looks on the surface. An overlap can h
appen
>in one of two ways:
>1. The start date of one range is between the start and end date of another
>range.
>2. The end date of one range is between the start and end date of another r
ange.
>It gets a little more complicated if end dates can be null.
>Assuming no nulls, you would have something like:
>
>Select F1..Fn
>From Table As T1
> Join Table As T2
> On T1.PK <> T2.PK
> And T1.User_Id = T2.User_Id
> And (
> (T1.Start >= T2.Start
> And (T1.Start <= T2.End Or T1.End <= T2.End))
> Or (T2.Start >= T1.Start
> And (T2.Start <= T1.End Or T2.End <= T1.End))
> )
>If the dates can be null, you'll have to decide how that should be interpre
ted.
>For example, if a null end date means that there is no end, then you would
want
>to replace the end date with an arbitrarily large date (e.g. 9999-12-31).
>
>Thomas
>
>"Serghios" <Serghios.Florides@.elthion.com> wrote in message
>news:ORD1hXdZFHA.3648@.TK2MSFTNGP14.phx.gbl...
>
>
>|||On the contrary, the do not produce the same results.
Create Table Dates
(
Id Int Primary Key
, StartDate DateTime
, EndDate DateTime
)
Insert Dates(Id, StartDate, EndDate) Values(1, '20050101', '20050131')
Insert Dates(Id, StartDate, EndDate) Values(2, '20050115', '20050131')
--Original post
Select *
From Dates As D1
Join Dates As D2
On D1.Id <> D2.Id
Where D1.StartDate Between D2.StartDate And D2.EndDate
--My overlaps version
Select *
From Dates As D1
Join Dates As D2
On D1.Id <> D2.Id
Where (D1.StartDate >= D2.StartDate
And (D1.StartDate <= D2.EndDate Or D1.EndDate <= D2.EndDate))
Or (D2.StartDate >= D1.StartDate
And (D2.StartDate <= D1.EndDate Or D2.EndDate <= D1.EndDate))
--equivalent to Joe's version
Select *
From Dates As D1
Join Dates As D2
On D1.Id <> D2.Id
Where Not (D1.StartDate > D2.EndDate Or D1.EndDate < D2.EndDate)
--equivalent to Joe's version using DeMorgan's law
Select *
From Dates As D1
Join Dates As D2
On D1.Id <> D2.Id
Where D1.StartDate <= D2.EndDate And D1.EndDate >= D2.EndDate
Notice that in the first select statement only Item#2 shows as having an ove
rlap
whereas both values show in the second select statement. The problem with on
ly
looking at the start date on a given row is that it does not account for the
End
Date of that row overlapping with an existing range. Rather, it only account
s
for the overlapped range.
I actually modified my results from the Ovelaps specification in the SQL92 s
pec.
Joe's simplified version is actually cleaner and just as accurate. Thanks Jo
e.
Think I'll use that.
Thomas
"Steve Kass" <skass@.drew.edu> wrote in message
news:uzAJechZFHA.3960@.TK2MSFTNGP10.phx.gbl...
> Thomas,
> Itzik's solution is simpler and equivalent. In words, it may be
> easier to think of in terms of two lives. As long as people do
> not die before they are born (something you would implement
> as a constraint in the data model), the lives of X and Y
> overlap if and only if X died after Y was born and Y died after
> X was born. It seems too simple to work, but it works. Your
> comment about NULL end dates is important. Here's one
> solution to handle NULL meaning "not ended yet"
> ...
> and coalesce(X.End,'99991231') > Y.Start
> and coalesce(Y.End,'99991231') > X.Start
> Steve Kass
> Drew University
>
> Thomas Coleman wrote:
>|||The only downside to your approach Joe is when one range is contained within
another. Granted, we are now getting into the semantics of what it really me
ans
to "overlap". However, if you wanted "overlap or contained within", your
algorithm would not work.
For example, using the structure below imagine we have:
Insert Dates(Id, StartDate, EndDate) Values(1, '20050101', '20050131')
Insert Dates(Id, StartDate, EndDate) Values(2, '20050115', '20050130')
This will show that the first item overlaps the second, but not the reverse.
That can easily be fixed by adding a clause that checks for containment like
so:
Select *
From Dates As D1
Join Dates As D2
On D1.Id <> D2.Id
Where D1.StartDate <= D2.EndDate And D1.EndDate >= D2.EndDate
Or (D1.StartDate >= D2.StartDate And D1.EndDate <= D2.EndDate)
Thomas
"Thomas Coleman" <replyingroup@.anywhere.com> wrote in message
news:%23tTYa3hZFHA.3876@.TK2MSFTNGP12.phx.gbl...
> On the contrary, the do not produce the same results.
> Create Table Dates
> (
> Id Int Primary Key
> , StartDate DateTime
> , EndDate DateTime
> )
> Insert Dates(Id, StartDate, EndDate) Values(1, '20050101', '20050131')
> Insert Dates(Id, StartDate, EndDate) Values(2, '20050115', '20050131')
> --Original post
> Select *
> From Dates As D1
> Join Dates As D2
> On D1.Id <> D2.Id
> Where D1.StartDate Between D2.StartDate And D2.EndDate
> --My overlaps version
> Select *
> From Dates As D1
> Join Dates As D2
> On D1.Id <> D2.Id
> Where (D1.StartDate >= D2.StartDate
> And (D1.StartDate <= D2.EndDate Or D1.EndDate <= D2.EndDate))
> Or (D2.StartDate >= D1.StartDate
> And (D2.StartDate <= D1.EndDate Or D2.EndDate <= D1.EndDate))
> --equivalent to Joe's version
> Select *
> From Dates As D1
> Join Dates As D2
> On D1.Id <> D2.Id
> Where Not (D1.StartDate > D2.EndDate Or D1.EndDate < D2.EndDate)
> --equivalent to Joe's version using DeMorgan's law
> Select *
> From Dates As D1
> Join Dates As D2
> On D1.Id <> D2.Id
> Where D1.StartDate <= D2.EndDate And D1.EndDate >= D2.EndDate
>
> Notice that in the first select statement only Item#2 shows as having an
> overlap whereas both values show in the second select statement. The probl
em
> with only looking at the start date on a given row is that it does not acc
ount
> for the End Date of that row overlapping with an existing range. Rather, i
t
> only accounts for the overlapped range.
> I actually modified my results from the Ovelaps specification in the SQL92
> spec. Joe's simplified version is actually cleaner and just as accurate.
> Thanks Joe. Think I'll use that.
>
> Thomas
>
> "Steve Kass" <skass@.drew.edu> wrote in message
> news:uzAJechZFHA.3960@.TK2MSFTNGP10.phx.gbl...
>
I have a table which logs calls called calls There are 3 main columns
user_id, starttime,endtime
I would like to run a query (a self-join i guess) which lists all
overlapping calls for a particular user_id.
In theory it should not be possible for a user to make 2 or more
simulataneous calls.
Any help would be greatly appreciated.
SergioYOu should only compare the start time with the data you have to see wheter
it overlaps
Select UserID from YourTable t1
inner join Yourtable t2
on userid = userid
Where t1.Starttime between t2.starttime and t2.endtime
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Serghios" <Serghios.Florides@.elthion.com> schrieb im Newsbeitrag
news:ORD1hXdZFHA.3648@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> I have a table which logs calls called calls There are 3 main columns
> user_id, starttime,endtime
> I would like to run a query (a self-join i guess) which lists all
> overlapping calls for a particular user_id.
> In theory it should not be possible for a user to make 2 or more
> simulataneous calls.
> Any help would be greatly appreciated.
> Sergio
>
>
>|||Sergio, try,
select c1.userid, c1.starttime as s1, c1.endtime as e1,
c2.starttime as s2, c2.endtime as e2
from calls as c1
join calls as c2
on c1.key_col <> c2.key_col
and c1.userid = c2.userid
and c1.starttime <= c2.endtime
and c1.endtime >= c2.starttime
-- not tested
BG, SQL Server MVP
www.SolidQualityLearning.com
"Serghios" <Serghios.Florides@.elthion.com> wrote in message
news:ORD1hXdZFHA.3648@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> I have a table which logs calls called calls There are 3 main columns
> user_id, starttime,endtime
> I would like to run a query (a self-join i guess) which lists all
> overlapping calls for a particular user_id.
> In theory it should not be possible for a user to make 2 or more
> simulataneous calls.
> Any help would be greatly appreciated.
> Sergio
>
>
>|||Unfortunately this query returns all rows in my table
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:ON1yhjdZFHA.1152@.tk2msftngp13.phx.gbl...
> YOu should only compare the start time with the data you have to see
> wheter it overlaps
> Select UserID from YourTable t1
> inner join Yourtable t2
> on userid = userid
> Where t1.Starttime between t2.starttime and t2.endtime
>
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Serghios" <Serghios.Florides@.elthion.com> schrieb im Newsbeitrag
> news:ORD1hXdZFHA.3648@.TK2MSFTNGP14.phx.gbl...
>|||That's because a period always overlaps with itself. You can eliminate the
self match by filtering t1.key <> t2.key, assuming you have a single column
key in the table. If the key is composite, use:
on t1.key1 <> t2.key1 or t1.key2 <> t2.key2, etc.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Serghios" <Serghios.Florides@.elthion.com> wrote in message
news:ODHsQrdZFHA.3712@.TK2MSFTNGP09.phx.gbl...
> Unfortunately this query returns all rows in my table
>
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:ON1yhjdZFHA.1152@.tk2msftngp13.phx.gbl...
>|||This can be more complicated that it looks on the surface. An overlap can ha
ppen
in one of two ways:
1. The start date of one range is between the start and end date of another
range.
2. The end date of one range is between the start and end date of another ra
nge.
It gets a little more complicated if end dates can be null.
Assuming no nulls, you would have something like:
Select F1..Fn
From Table As T1
Join Table As T2
On T1.PK <> T2.PK
And T1.User_Id = T2.User_Id
And (
(T1.Start >= T2.Start
And (T1.Start <= T2.End Or T1.End <= T2.End))
Or (T2.Start >= T1.Start
And (T2.Start <= T1.End Or T2.End <= T1.End))
)
If the dates can be null, you'll have to decide how that should be interpret
ed.
For example, if a null end date means that there is no end, then you would w
ant
to replace the end date with an arbitrarily large date (e.g. 9999-12-31).
Thomas
"Serghios" <Serghios.Florides@.elthion.com> wrote in message
news:ORD1hXdZFHA.3648@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> I have a table which logs calls called calls There are 3 main columns
> user_id, starttime,endtime
> I would like to run a query (a self-join i guess) which lists all overlapp
ing
> calls for a particular user_id.
> In theory it should not be possible for a user to make 2 or more simulatan
eous
> calls.
> Any help would be greatly appreciated.
> Sergio
>
>
>|||Think about this in reverse: Two events do not overlap if one ends
before the other begins, or if it begins after the end of other one.
Write that and then negate it.
SELECT E1.*
FROM Events AS E1,
Events AS E2
WHERE E1.event_key <> E2.event_key
AND E1.user_id = E2.user_id
AND NOT (E1.start_time > E2.end_time
OR E1.end_time < E2.end_time)
Or you can use DeMorgan's law on the last predicate:
AND (E1.start_time <= E2.end_time
AND E1.end_time >= E2.end_time)
There is also an OVERLAPS predicate in SQL-92 for this purpose,but it
is a bit tricky.|||Thomas,
Itzik's solution is simpler and equivalent. In words, it may be
easier to think of in terms of two lives. As long as people do
not die before they are born (something you would implement
as a constraint in the data model), the lives of X and Y
overlap if and only if X died after Y was born and Y died after
X was born. It seems too simple to work, but it works. Your
comment about NULL end dates is important. Here's one
solution to handle NULL meaning "not ended yet"
...
and coalesce(X.End,'99991231') > Y.Start
and coalesce(Y.End,'99991231') > X.Start
Steve Kass
Drew University
Thomas Coleman wrote:
>This can be more complicated that it looks on the surface. An overlap can h
appen
>in one of two ways:
>1. The start date of one range is between the start and end date of another
>range.
>2. The end date of one range is between the start and end date of another r
ange.
>It gets a little more complicated if end dates can be null.
>Assuming no nulls, you would have something like:
>
>Select F1..Fn
>From Table As T1
> Join Table As T2
> On T1.PK <> T2.PK
> And T1.User_Id = T2.User_Id
> And (
> (T1.Start >= T2.Start
> And (T1.Start <= T2.End Or T1.End <= T2.End))
> Or (T2.Start >= T1.Start
> And (T2.Start <= T1.End Or T2.End <= T1.End))
> )
>If the dates can be null, you'll have to decide how that should be interpre
ted.
>For example, if a null end date means that there is no end, then you would
want
>to replace the end date with an arbitrarily large date (e.g. 9999-12-31).
>
>Thomas
>
>"Serghios" <Serghios.Florides@.elthion.com> wrote in message
>news:ORD1hXdZFHA.3648@.TK2MSFTNGP14.phx.gbl...
>
>
>|||On the contrary, the do not produce the same results.
Create Table Dates
(
Id Int Primary Key
, StartDate DateTime
, EndDate DateTime
)
Insert Dates(Id, StartDate, EndDate) Values(1, '20050101', '20050131')
Insert Dates(Id, StartDate, EndDate) Values(2, '20050115', '20050131')
--Original post
Select *
From Dates As D1
Join Dates As D2
On D1.Id <> D2.Id
Where D1.StartDate Between D2.StartDate And D2.EndDate
--My overlaps version
Select *
From Dates As D1
Join Dates As D2
On D1.Id <> D2.Id
Where (D1.StartDate >= D2.StartDate
And (D1.StartDate <= D2.EndDate Or D1.EndDate <= D2.EndDate))
Or (D2.StartDate >= D1.StartDate
And (D2.StartDate <= D1.EndDate Or D2.EndDate <= D1.EndDate))
--equivalent to Joe's version
Select *
From Dates As D1
Join Dates As D2
On D1.Id <> D2.Id
Where Not (D1.StartDate > D2.EndDate Or D1.EndDate < D2.EndDate)
--equivalent to Joe's version using DeMorgan's law
Select *
From Dates As D1
Join Dates As D2
On D1.Id <> D2.Id
Where D1.StartDate <= D2.EndDate And D1.EndDate >= D2.EndDate
Notice that in the first select statement only Item#2 shows as having an ove
rlap
whereas both values show in the second select statement. The problem with on
ly
looking at the start date on a given row is that it does not account for the
End
Date of that row overlapping with an existing range. Rather, it only account
s
for the overlapped range.
I actually modified my results from the Ovelaps specification in the SQL92 s
pec.
Joe's simplified version is actually cleaner and just as accurate. Thanks Jo
e.
Think I'll use that.
Thomas
"Steve Kass" <skass@.drew.edu> wrote in message
news:uzAJechZFHA.3960@.TK2MSFTNGP10.phx.gbl...
> Thomas,
> Itzik's solution is simpler and equivalent. In words, it may be
> easier to think of in terms of two lives. As long as people do
> not die before they are born (something you would implement
> as a constraint in the data model), the lives of X and Y
> overlap if and only if X died after Y was born and Y died after
> X was born. It seems too simple to work, but it works. Your
> comment about NULL end dates is important. Here's one
> solution to handle NULL meaning "not ended yet"
> ...
> and coalesce(X.End,'99991231') > Y.Start
> and coalesce(Y.End,'99991231') > X.Start
> Steve Kass
> Drew University
>
> Thomas Coleman wrote:
>|||The only downside to your approach Joe is when one range is contained within
another. Granted, we are now getting into the semantics of what it really me
ans
to "overlap". However, if you wanted "overlap or contained within", your
algorithm would not work.
For example, using the structure below imagine we have:
Insert Dates(Id, StartDate, EndDate) Values(1, '20050101', '20050131')
Insert Dates(Id, StartDate, EndDate) Values(2, '20050115', '20050130')
This will show that the first item overlaps the second, but not the reverse.
That can easily be fixed by adding a clause that checks for containment like
so:
Select *
From Dates As D1
Join Dates As D2
On D1.Id <> D2.Id
Where D1.StartDate <= D2.EndDate And D1.EndDate >= D2.EndDate
Or (D1.StartDate >= D2.StartDate And D1.EndDate <= D2.EndDate)
Thomas
"Thomas Coleman" <replyingroup@.anywhere.com> wrote in message
news:%23tTYa3hZFHA.3876@.TK2MSFTNGP12.phx.gbl...
> On the contrary, the do not produce the same results.
> Create Table Dates
> (
> Id Int Primary Key
> , StartDate DateTime
> , EndDate DateTime
> )
> Insert Dates(Id, StartDate, EndDate) Values(1, '20050101', '20050131')
> Insert Dates(Id, StartDate, EndDate) Values(2, '20050115', '20050131')
> --Original post
> Select *
> From Dates As D1
> Join Dates As D2
> On D1.Id <> D2.Id
> Where D1.StartDate Between D2.StartDate And D2.EndDate
> --My overlaps version
> Select *
> From Dates As D1
> Join Dates As D2
> On D1.Id <> D2.Id
> Where (D1.StartDate >= D2.StartDate
> And (D1.StartDate <= D2.EndDate Or D1.EndDate <= D2.EndDate))
> Or (D2.StartDate >= D1.StartDate
> And (D2.StartDate <= D1.EndDate Or D2.EndDate <= D1.EndDate))
> --equivalent to Joe's version
> Select *
> From Dates As D1
> Join Dates As D2
> On D1.Id <> D2.Id
> Where Not (D1.StartDate > D2.EndDate Or D1.EndDate < D2.EndDate)
> --equivalent to Joe's version using DeMorgan's law
> Select *
> From Dates As D1
> Join Dates As D2
> On D1.Id <> D2.Id
> Where D1.StartDate <= D2.EndDate And D1.EndDate >= D2.EndDate
>
> Notice that in the first select statement only Item#2 shows as having an
> overlap whereas both values show in the second select statement. The probl
em
> with only looking at the start date on a given row is that it does not acc
ount
> for the End Date of that row overlapping with an existing range. Rather, i
t
> only accounts for the overlapped range.
> I actually modified my results from the Ovelaps specification in the SQL92
> spec. Joe's simplified version is actually cleaner and just as accurate.
> Thanks Joe. Think I'll use that.
>
> Thomas
>
> "Steve Kass" <skass@.drew.edu> wrote in message
> news:uzAJechZFHA.3960@.TK2MSFTNGP10.phx.gbl...
>
Overlapping Sets
I have the following table structure
CREATE TABLE [dbo].[QDisc](
[Id] [int] NOT NULL,
[MinVal] [int] NOT NULL,
[MaxVal] [int] NOT NULL,
[PerVal] [int] NOT NULL,
CONSTRAINT [PK_QDisc] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
I need to be able to select unique overlapping sets of data based on the minval and maxval.
Simple Example
minval maxval
5 15
16 25
10 20
query would produce
minval maxval
5 10
11 15
16 20
21 25
More Complex example
minval maxval
5 15
16 25
10 20
7 7
1 100
query would produce
minval maxval
1 5
6 6
7 7
8 10
11 15
16 20
21 25
26 100
Extra points if anyone can do this without SP and cursor but I'd be satisfied if someone supplied it that way.Extra points for doing your homework assignment for you?|||Extra points if anyone can do this without SP and cursor but I'd be satisfied if someone supplied it that way.Gosh - generous and gracious. How super.|||I'll give myself extra credit
ALTER PROCEDURE [dbo].[usp_Select_Disc]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
select minval, maxval into #QtyRange from qdisc
declare @.minval int
declare @.maxval int
declare @.xminval int
declare @.xmaxval int
declare @.pmaxval int
declare @.pmaxva2 int
declare @.loopflg bit
set @.loopflg = 1
while @.loopflg = 1
begin
set @.loopflg = 0
DECLARE curSel CURSOR FOR select minval, maxval from #QtyRange order by minval
open curSel
fetch next from curSel into @.minval, @.maxval
WHILE (@.@.FETCH_STATUS <> -1)
begin
set @.xminval = null
set @.xmaxval = null
select top(1) @.xminval = minval, @.xmaxval = maxval from #QtyRange where minval > @.minval or maxval > @.maxval order by minval
if @.maxval > @.xminval
begin
set @.pmaxval = @.maxval
if @.minval = @.xminval
begin
set @.pmaxva2 = @.xmaxval
update #QtyRange set maxval = @.xminval where minval = @.minval and maxval = @.maxval
update #QtyRange set minval = @.xminval + 1, maxval = @.pmaxval where minval = @.xminval and maxval = @.xmaxval
insert into #QtyRange values (@.pmaxval + 1, @.pmaxva2)
set @.loopflg = 1
break
end
update #QtyRange set maxval = @.xminval - 1 where minval = @.minval and maxval = @.maxval
if @.xmaxval > @.pmaxval
begin
set @.pmaxva2 = @.xmaxval
update #QtyRange set maxval = @.pmaxval where minval = @.xminval and maxval = @.xmaxval
insert into #QtyRange values (@.pmaxval + 1, @.pmaxva2)
set @.loopflg = 1
break
end
if @.xmaxval < @.pmaxval
insert into #QtyRange values (@.xmaxval + 1, @.pmaxval)
set @.loopflg = 1
end
if @.maxval = @.xminval
begin
set @.pmaxval = @.maxval
if @.minval = @.maxval
begin
update #QtyRange set minval = @.pmaxval + 1 where minval = @.xminval and maxval = @.xmaxval
set @.loopflg = 1
break
end
else
begin
update #QtyRange set maxval = @.pmaxval - 1 where minval = @.minval and maxval = @.maxval
if @.xminval <> @.xmaxval
begin
insert into #QtyRange values (@.pmaxval, @.pmaxval)
update #QtyRange set minval = @.pmaxval + 1 where minval = @.xminval and maxval = @.xmaxval
set @.loopflg = 1
break
end
end
end
fetch next from curSel into @.minval, @.maxval
end
Close curSel
DEALLOCATE curSel
end
select distinct(a.minval), a.maxval, sum(qdisc.perval) from #QtyRange a
inner join qdisc on a.minval >= qdisc.minval and a.maxval <= qdisc.maxval
group by a.minval,a.maxval
order by minval
END|||I'll give myself extra credit
You shouldn't, you used a cursor.|||You shouldn't, you used a cursor.
I'm not sure it can be done without a cursor or some other form of looping. I see no way to do it with a simple SELECT statement.|||neither do I, but that was his self-professed requirement for extra points.|||No no no. That was the requirement for YOU to get extra points. He, of course, is free to give himself as much credit as he wants.|||how many points do i need to get a free ship?|||this worked for both the data sets provided by u. i have tried with a couple of other sets and appears to be working. interested to know if it worked at your end or not...
create table #tt1 (Id int identity (1,1), Val int )
insert into #tt1 (Val) select Val from (
select Minval as Val from QDisc
union all
select Maxval as Val from QDisc
) A order by 1
select B.Id,A.Val AVal, B.Val BVal, C.Val CVal into #tt2
from #tt1 A, #tt1 B, #tt1 C where A.Id+1 =* B.Id and C.Id-1 =* B.Id
update #tt2 set BVal=BVal-1 where AVal+1=BVal
update #tt2 set CVal=Null where BVal+1=CVal
update #tt2 set CVal=CVal-1 from #tt2 A where A.CVal= (select BVal from #tt2 where #tt2.id = A.id+1 and BVal=CVal)
select
BVal+ case when BVal=(select min(BVal) from #tt2) then 0 when BVal= CVal then 0 else 1 end as MinVal,
CVal as MaxVal
from #tt2
where CVal is not null
CREATE TABLE [dbo].[QDisc](
[Id] [int] NOT NULL,
[MinVal] [int] NOT NULL,
[MaxVal] [int] NOT NULL,
[PerVal] [int] NOT NULL,
CONSTRAINT [PK_QDisc] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
I need to be able to select unique overlapping sets of data based on the minval and maxval.
Simple Example
minval maxval
5 15
16 25
10 20
query would produce
minval maxval
5 10
11 15
16 20
21 25
More Complex example
minval maxval
5 15
16 25
10 20
7 7
1 100
query would produce
minval maxval
1 5
6 6
7 7
8 10
11 15
16 20
21 25
26 100
Extra points if anyone can do this without SP and cursor but I'd be satisfied if someone supplied it that way.Extra points for doing your homework assignment for you?|||Extra points if anyone can do this without SP and cursor but I'd be satisfied if someone supplied it that way.Gosh - generous and gracious. How super.|||I'll give myself extra credit
ALTER PROCEDURE [dbo].[usp_Select_Disc]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
select minval, maxval into #QtyRange from qdisc
declare @.minval int
declare @.maxval int
declare @.xminval int
declare @.xmaxval int
declare @.pmaxval int
declare @.pmaxva2 int
declare @.loopflg bit
set @.loopflg = 1
while @.loopflg = 1
begin
set @.loopflg = 0
DECLARE curSel CURSOR FOR select minval, maxval from #QtyRange order by minval
open curSel
fetch next from curSel into @.minval, @.maxval
WHILE (@.@.FETCH_STATUS <> -1)
begin
set @.xminval = null
set @.xmaxval = null
select top(1) @.xminval = minval, @.xmaxval = maxval from #QtyRange where minval > @.minval or maxval > @.maxval order by minval
if @.maxval > @.xminval
begin
set @.pmaxval = @.maxval
if @.minval = @.xminval
begin
set @.pmaxva2 = @.xmaxval
update #QtyRange set maxval = @.xminval where minval = @.minval and maxval = @.maxval
update #QtyRange set minval = @.xminval + 1, maxval = @.pmaxval where minval = @.xminval and maxval = @.xmaxval
insert into #QtyRange values (@.pmaxval + 1, @.pmaxva2)
set @.loopflg = 1
break
end
update #QtyRange set maxval = @.xminval - 1 where minval = @.minval and maxval = @.maxval
if @.xmaxval > @.pmaxval
begin
set @.pmaxva2 = @.xmaxval
update #QtyRange set maxval = @.pmaxval where minval = @.xminval and maxval = @.xmaxval
insert into #QtyRange values (@.pmaxval + 1, @.pmaxva2)
set @.loopflg = 1
break
end
if @.xmaxval < @.pmaxval
insert into #QtyRange values (@.xmaxval + 1, @.pmaxval)
set @.loopflg = 1
end
if @.maxval = @.xminval
begin
set @.pmaxval = @.maxval
if @.minval = @.maxval
begin
update #QtyRange set minval = @.pmaxval + 1 where minval = @.xminval and maxval = @.xmaxval
set @.loopflg = 1
break
end
else
begin
update #QtyRange set maxval = @.pmaxval - 1 where minval = @.minval and maxval = @.maxval
if @.xminval <> @.xmaxval
begin
insert into #QtyRange values (@.pmaxval, @.pmaxval)
update #QtyRange set minval = @.pmaxval + 1 where minval = @.xminval and maxval = @.xmaxval
set @.loopflg = 1
break
end
end
end
fetch next from curSel into @.minval, @.maxval
end
Close curSel
DEALLOCATE curSel
end
select distinct(a.minval), a.maxval, sum(qdisc.perval) from #QtyRange a
inner join qdisc on a.minval >= qdisc.minval and a.maxval <= qdisc.maxval
group by a.minval,a.maxval
order by minval
END|||I'll give myself extra credit
You shouldn't, you used a cursor.|||You shouldn't, you used a cursor.
I'm not sure it can be done without a cursor or some other form of looping. I see no way to do it with a simple SELECT statement.|||neither do I, but that was his self-professed requirement for extra points.|||No no no. That was the requirement for YOU to get extra points. He, of course, is free to give himself as much credit as he wants.|||how many points do i need to get a free ship?|||this worked for both the data sets provided by u. i have tried with a couple of other sets and appears to be working. interested to know if it worked at your end or not...
create table #tt1 (Id int identity (1,1), Val int )
insert into #tt1 (Val) select Val from (
select Minval as Val from QDisc
union all
select Maxval as Val from QDisc
) A order by 1
select B.Id,A.Val AVal, B.Val BVal, C.Val CVal into #tt2
from #tt1 A, #tt1 B, #tt1 C where A.Id+1 =* B.Id and C.Id-1 =* B.Id
update #tt2 set BVal=BVal-1 where AVal+1=BVal
update #tt2 set CVal=Null where BVal+1=CVal
update #tt2 set CVal=CVal-1 from #tt2 A where A.CVal= (select BVal from #tt2 where #tt2.id = A.id+1 and BVal=CVal)
select
BVal+ case when BVal=(select min(BVal) from #tt2) then 0 when BVal= CVal then 0 else 1 end as MinVal,
CVal as MaxVal
from #tt2
where CVal is not null
Overlapping Report Items via HTML?
Has anybody managed to get Overlapping Report Items to display properly
in Internet Explorer or some other browser? (Ignoring Microsoft's
statement that overlapping items are not supported in HTML)
Extensions or additions to the Rendering Extension, or any other
suggestions would be welcome.I also want to know has any idea about this issue!
Angi
"Sam" <samadams001@.googlemail.com>
'?:1148978623.733811.128950@.j33g2000cwa.googlegroups.com...
> Has anybody managed to get Overlapping Report Items to display properly
> in Internet Explorer or some other browser? (Ignoring Microsoft's
> statement that overlapping items are not supported in HTML)
> Extensions or additions to the Rendering Extension, or any other
> suggestions would be welcome.
>
in Internet Explorer or some other browser? (Ignoring Microsoft's
statement that overlapping items are not supported in HTML)
Extensions or additions to the Rendering Extension, or any other
suggestions would be welcome.I also want to know has any idea about this issue!
Angi
"Sam" <samadams001@.googlemail.com>
'?:1148978623.733811.128950@.j33g2000cwa.googlegroups.com...
> Has anybody managed to get Overlapping Report Items to display properly
> in Internet Explorer or some other browser? (Ignoring Microsoft's
> statement that overlapping items are not supported in HTML)
> Extensions or additions to the Rendering Extension, or any other
> suggestions would be welcome.
>
Overlapping Records by DateTime
Hi guys and gals,
I need to pick your brains for a date/time series question. I'm trying to
write a query that will displays accounts, with their different account type
s
that overlap using the start and end dates.
So say I have the following records:-
AccountId - AccountType - Start - End
1 1 2006-01-01 2006-01-07
2 1 2006-01-06 2006-01-09
3 2 2006-01-02 2006-01-09
I can see that Account 1 and 2 are the same account type, but they overlap
by 1 day however account 3 is different and therefore is fine.
I need to write a query, to decipher all these account that overlap in date
of the same account type. This is part of a larger system, so you may wonde
r
why I wouldn't just place constraints to prevent this from happening, but th
e
reason is that I will allow accounts to overlap, and I have somewhere else i
n
the system a means to elect overlapped accounts based on merit which isn't
required in the query.
I've done some DDL and Inserts here, any help would be greatly appreciated.
Andy
CREATE TABLE Accounts(
AccountId int not null identity(1,1),
AccountType int not null,
UtcDateStart datetime not null,
UtcDateEnd datetime not null
)
CREATE INDEX PK_Accounts_AccountId
ON Accounts (AccountId)
GO
declare @.utcDateTime datetime
set @.utcDateTime = getutcdate()
INSERT INTO Accounts (AccountType, utcDateStart, utcDateEnd)
VALUES (1,@.utcDateTime, dateadd(dd, 7, @.utcDateTime))
set @.utcDateTime = dateadd(dd, 4, @.utcDateTime)
INSERT INTO Accounts (AccountType, utcDateStart, utcDateEnd)
VALUES (1,@.utcDateTime, dateadd(dd, 7, @.utcDateTime))
set @.utcDateTime = dateadd(dd, 5, @.utcDateTime)
INSERT INTO Accounts (AccountType, utcDateStart, utcDateEnd)
VALUES (2,@.utcDateTime, dateadd(dd, 7, @.utcDateTime))
set @.utcDateTime = dateadd(dd, 6, @.utcDateTime)
INSERT INTO Accounts (AccountType, utcDateStart, utcDateEnd)
VALUES (2,@.utcDateTime, dateadd(dd, 7, @.utcDateTime))
set @.utcDateTime = dateadd(dd, 2, @.utcDateTime)
INSERT INTO Accounts (AccountType, utcDateStart, utcDateEnd)
VALUES (3,@.utcDateTime, dateadd(dd, 5, @.utcDateTime))
SELECT * FROM AccountsIs this what you're after...
select
*
from
Accounts t1
join Accounts t2 on t1.AccountType = t2.AccountType
where
t2.AccountId > t1.AccountId
and (t1.UtcDateStart between t2.UtcDateStart and t2.UtcDateEnd
or t2.UtcDateStart between t1.UtcDateStart and t1.UtcDateEnd)
HTH. Ryan
"Andy Furnival" <AndyFurnival@.discussions.microsoft.com> wrote in message
news:893FFA23-53D8-4909-995D-C40BD71AF394@.microsoft.com...
> Hi guys and gals,
> I need to pick your brains for a date/time series question. I'm trying to
> write a query that will displays accounts, with their different account
> types
> that overlap using the start and end dates.
> So say I have the following records:-
> AccountId - AccountType - Start - End
> 1 1 2006-01-01 2006-01-07
> 2 1 2006-01-06 2006-01-09
> 3 2 2006-01-02 2006-01-09
> I can see that Account 1 and 2 are the same account type, but they overlap
> by 1 day however account 3 is different and therefore is fine.
> I need to write a query, to decipher all these account that overlap in
> date
> of the same account type. This is part of a larger system, so you may
> wonder
> why I wouldn't just place constraints to prevent this from happening, but
> the
> reason is that I will allow accounts to overlap, and I have somewhere else
> in
> the system a means to elect overlapped accounts based on merit which isn't
> required in the query.
> I've done some DDL and Inserts here, any help would be greatly
> appreciated.
> Andy
> CREATE TABLE Accounts(
> AccountId int not null identity(1,1),
> AccountType int not null,
> UtcDateStart datetime not null,
> UtcDateEnd datetime not null
> )
> CREATE INDEX PK_Accounts_AccountId
> ON Accounts (AccountId)
> GO
> declare @.utcDateTime datetime
> set @.utcDateTime = getutcdate()
> INSERT INTO Accounts (AccountType, utcDateStart, utcDateEnd)
> VALUES (1,@.utcDateTime, dateadd(dd, 7, @.utcDateTime))
> set @.utcDateTime = dateadd(dd, 4, @.utcDateTime)
> INSERT INTO Accounts (AccountType, utcDateStart, utcDateEnd)
> VALUES (1,@.utcDateTime, dateadd(dd, 7, @.utcDateTime))
> set @.utcDateTime = dateadd(dd, 5, @.utcDateTime)
> INSERT INTO Accounts (AccountType, utcDateStart, utcDateEnd)
> VALUES (2,@.utcDateTime, dateadd(dd, 7, @.utcDateTime))
> set @.utcDateTime = dateadd(dd, 6, @.utcDateTime)
> INSERT INTO Accounts (AccountType, utcDateStart, utcDateEnd)
> VALUES (2,@.utcDateTime, dateadd(dd, 7, @.utcDateTime))
> set @.utcDateTime = dateadd(dd, 2, @.utcDateTime)
> INSERT INTO Accounts (AccountType, utcDateStart, utcDateEnd)
> VALUES (3,@.utcDateTime, dateadd(dd, 5, @.utcDateTime))
>
> SELECT * FROM Accounts|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:578107
On Tue, 17 Jan 2006 01:41:03 -0800, Andy Furnival wrote:
>Hi guys and gals,
>I need to pick your brains for a date/time series question. I'm trying to
>write a query that will displays accounts, with their different account typ
es
>that overlap using the start and end dates.
>So say I have the following records:-
>AccountId - AccountType - Start - End
>1 1 2006-01-01 2006-01-07
>2 1 2006-01-06 2006-01-09
>3 2 2006-01-02 2006-01-09
>I can see that Account 1 and 2 are the same account type, but they overlap
>by 1 day however account 3 is different and therefore is fine.
>I need to write a query, to decipher all these account that overlap in date
>of the same account type. This is part of a larger system, so you may wond
er
>why I wouldn't just place constraints to prevent this from happening, but t
he
>reason is that I will allow accounts to overlap, and I have somewhere else
in
>the system a means to elect overlapped accounts based on merit which isn't
>required in the query.
>I've done some DDL and Inserts here, any help would be greatly appreciated.
Hi Andy,
Thanks for the DDL and the INSERTS! Made posting a breeze and answering
more fun.
In addition to Ryan's suggestion, here's another one that will work:
SELECT * FROM Accounts
go
SELECT *
FROM Accounts AS a
INNER JOIN Accounts AS b
ON a.AccountType = b.AccountType
AND a.AccountId > b.AccountId
AND a.utcDateStart < b.utcDateEnd
AND a.utcDateEnd > b.utcDateStart
The benefot of this version is that it avoids the use of OR. If the
utcDateStart and utcDateEnd columns in your real table are indexed, my
version will give the optimizer better opportunities to use that index.
Bottom line: test both for performance; choose the one that performs
best or (if there's no significant difference) the one that you find the
easiest to understand.
Hugo Kornelis, SQL Server MVP|||I would use a Calendar table and a query with a BETWEEN predicate. I
would also get a real key as an account_id with a check digit that
comforms to International banking standards instead of that silly and
dangerous IDENTITY pseudo-column.
As a matter of ISO-11179 conventions, the names should be
"start_utedate".
Use a BETWEEN predicate with a COUNT(*) > 1|||> instead of that silly and
> dangerous IDENTITY pseudo-column.
IDENTITY is a property of a column and NOT a column.
There is nothing stopping you creating a check digit based around IDENTITY
either.
There is nothing 'dangerous' about the IDENTITY 'property'.
The IDENTITY property can be successfully used to create surrogate keys or a
natural primary key where no other one may exist, for instance a message
board.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1137635843.478530.301780@.f14g2000cwb.googlegroups.com...
>I would use a Calendar table and a query with a BETWEEN predicate. I
> would also get a real key as an account_id with a check digit that
> comforms to International banking standards instead of that silly and
> dangerous IDENTITY pseudo-column.
> As a matter of ISO-11179 conventions, the names should be
> "start_utedate".
>
> Use a BETWEEN predicate with a COUNT(*) > 1
>
I need to pick your brains for a date/time series question. I'm trying to
write a query that will displays accounts, with their different account type
s
that overlap using the start and end dates.
So say I have the following records:-
AccountId - AccountType - Start - End
1 1 2006-01-01 2006-01-07
2 1 2006-01-06 2006-01-09
3 2 2006-01-02 2006-01-09
I can see that Account 1 and 2 are the same account type, but they overlap
by 1 day however account 3 is different and therefore is fine.
I need to write a query, to decipher all these account that overlap in date
of the same account type. This is part of a larger system, so you may wonde
r
why I wouldn't just place constraints to prevent this from happening, but th
e
reason is that I will allow accounts to overlap, and I have somewhere else i
n
the system a means to elect overlapped accounts based on merit which isn't
required in the query.
I've done some DDL and Inserts here, any help would be greatly appreciated.
Andy
CREATE TABLE Accounts(
AccountId int not null identity(1,1),
AccountType int not null,
UtcDateStart datetime not null,
UtcDateEnd datetime not null
)
CREATE INDEX PK_Accounts_AccountId
ON Accounts (AccountId)
GO
declare @.utcDateTime datetime
set @.utcDateTime = getutcdate()
INSERT INTO Accounts (AccountType, utcDateStart, utcDateEnd)
VALUES (1,@.utcDateTime, dateadd(dd, 7, @.utcDateTime))
set @.utcDateTime = dateadd(dd, 4, @.utcDateTime)
INSERT INTO Accounts (AccountType, utcDateStart, utcDateEnd)
VALUES (1,@.utcDateTime, dateadd(dd, 7, @.utcDateTime))
set @.utcDateTime = dateadd(dd, 5, @.utcDateTime)
INSERT INTO Accounts (AccountType, utcDateStart, utcDateEnd)
VALUES (2,@.utcDateTime, dateadd(dd, 7, @.utcDateTime))
set @.utcDateTime = dateadd(dd, 6, @.utcDateTime)
INSERT INTO Accounts (AccountType, utcDateStart, utcDateEnd)
VALUES (2,@.utcDateTime, dateadd(dd, 7, @.utcDateTime))
set @.utcDateTime = dateadd(dd, 2, @.utcDateTime)
INSERT INTO Accounts (AccountType, utcDateStart, utcDateEnd)
VALUES (3,@.utcDateTime, dateadd(dd, 5, @.utcDateTime))
SELECT * FROM AccountsIs this what you're after...
select
*
from
Accounts t1
join Accounts t2 on t1.AccountType = t2.AccountType
where
t2.AccountId > t1.AccountId
and (t1.UtcDateStart between t2.UtcDateStart and t2.UtcDateEnd
or t2.UtcDateStart between t1.UtcDateStart and t1.UtcDateEnd)
HTH. Ryan
"Andy Furnival" <AndyFurnival@.discussions.microsoft.com> wrote in message
news:893FFA23-53D8-4909-995D-C40BD71AF394@.microsoft.com...
> Hi guys and gals,
> I need to pick your brains for a date/time series question. I'm trying to
> write a query that will displays accounts, with their different account
> types
> that overlap using the start and end dates.
> So say I have the following records:-
> AccountId - AccountType - Start - End
> 1 1 2006-01-01 2006-01-07
> 2 1 2006-01-06 2006-01-09
> 3 2 2006-01-02 2006-01-09
> I can see that Account 1 and 2 are the same account type, but they overlap
> by 1 day however account 3 is different and therefore is fine.
> I need to write a query, to decipher all these account that overlap in
> date
> of the same account type. This is part of a larger system, so you may
> wonder
> why I wouldn't just place constraints to prevent this from happening, but
> the
> reason is that I will allow accounts to overlap, and I have somewhere else
> in
> the system a means to elect overlapped accounts based on merit which isn't
> required in the query.
> I've done some DDL and Inserts here, any help would be greatly
> appreciated.
> Andy
> CREATE TABLE Accounts(
> AccountId int not null identity(1,1),
> AccountType int not null,
> UtcDateStart datetime not null,
> UtcDateEnd datetime not null
> )
> CREATE INDEX PK_Accounts_AccountId
> ON Accounts (AccountId)
> GO
> declare @.utcDateTime datetime
> set @.utcDateTime = getutcdate()
> INSERT INTO Accounts (AccountType, utcDateStart, utcDateEnd)
> VALUES (1,@.utcDateTime, dateadd(dd, 7, @.utcDateTime))
> set @.utcDateTime = dateadd(dd, 4, @.utcDateTime)
> INSERT INTO Accounts (AccountType, utcDateStart, utcDateEnd)
> VALUES (1,@.utcDateTime, dateadd(dd, 7, @.utcDateTime))
> set @.utcDateTime = dateadd(dd, 5, @.utcDateTime)
> INSERT INTO Accounts (AccountType, utcDateStart, utcDateEnd)
> VALUES (2,@.utcDateTime, dateadd(dd, 7, @.utcDateTime))
> set @.utcDateTime = dateadd(dd, 6, @.utcDateTime)
> INSERT INTO Accounts (AccountType, utcDateStart, utcDateEnd)
> VALUES (2,@.utcDateTime, dateadd(dd, 7, @.utcDateTime))
> set @.utcDateTime = dateadd(dd, 2, @.utcDateTime)
> INSERT INTO Accounts (AccountType, utcDateStart, utcDateEnd)
> VALUES (3,@.utcDateTime, dateadd(dd, 5, @.utcDateTime))
>
> SELECT * FROM Accounts|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:578107
On Tue, 17 Jan 2006 01:41:03 -0800, Andy Furnival wrote:
>Hi guys and gals,
>I need to pick your brains for a date/time series question. I'm trying to
>write a query that will displays accounts, with their different account typ
es
>that overlap using the start and end dates.
>So say I have the following records:-
>AccountId - AccountType - Start - End
>1 1 2006-01-01 2006-01-07
>2 1 2006-01-06 2006-01-09
>3 2 2006-01-02 2006-01-09
>I can see that Account 1 and 2 are the same account type, but they overlap
>by 1 day however account 3 is different and therefore is fine.
>I need to write a query, to decipher all these account that overlap in date
>of the same account type. This is part of a larger system, so you may wond
er
>why I wouldn't just place constraints to prevent this from happening, but t
he
>reason is that I will allow accounts to overlap, and I have somewhere else
in
>the system a means to elect overlapped accounts based on merit which isn't
>required in the query.
>I've done some DDL and Inserts here, any help would be greatly appreciated.
Hi Andy,
Thanks for the DDL and the INSERTS! Made posting a breeze and answering
more fun.
In addition to Ryan's suggestion, here's another one that will work:
SELECT * FROM Accounts
go
SELECT *
FROM Accounts AS a
INNER JOIN Accounts AS b
ON a.AccountType = b.AccountType
AND a.AccountId > b.AccountId
AND a.utcDateStart < b.utcDateEnd
AND a.utcDateEnd > b.utcDateStart
The benefot of this version is that it avoids the use of OR. If the
utcDateStart and utcDateEnd columns in your real table are indexed, my
version will give the optimizer better opportunities to use that index.
Bottom line: test both for performance; choose the one that performs
best or (if there's no significant difference) the one that you find the
easiest to understand.
Hugo Kornelis, SQL Server MVP|||I would use a Calendar table and a query with a BETWEEN predicate. I
would also get a real key as an account_id with a check digit that
comforms to International banking standards instead of that silly and
dangerous IDENTITY pseudo-column.
As a matter of ISO-11179 conventions, the names should be
"start_utedate".
Use a BETWEEN predicate with a COUNT(*) > 1|||> instead of that silly and
> dangerous IDENTITY pseudo-column.
IDENTITY is a property of a column and NOT a column.
There is nothing stopping you creating a check digit based around IDENTITY
either.
There is nothing 'dangerous' about the IDENTITY 'property'.
The IDENTITY property can be successfully used to create surrogate keys or a
natural primary key where no other one may exist, for instance a message
board.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1137635843.478530.301780@.f14g2000cwb.googlegroups.com...
>I would use a Calendar table and a query with a BETWEEN predicate. I
> would also get a real key as an account_id with a check digit that
> comforms to International banking standards instead of that silly and
> dangerous IDENTITY pseudo-column.
> As a matter of ISO-11179 conventions, the names should be
> "start_utedate".
>
> Use a BETWEEN predicate with a COUNT(*) > 1
>
Monday, February 20, 2012
Overlapping Permissions
I would think the following scenario should work, but it does not:
I have a table, Products, for which all users, via an NT domain group (e.g.
Domain Users) have only select permissions.
There is another group, ProductManagers, who are also members of the above
group, who need update, delete, and insert permissions. To accomplish this,
I
created a database role ProductMgmt, and added the ProductManagers to it.
This role has select, insert, update and delete permissions on the table.
The members of this group, however, get an error when attempting to delete
from the table. These members belong to both the Domain User and
ProductManagers groups.
I've also given the ProductManager group full permissions on the table. I'm
confused as to why all of this is not working, obviously I'm missing
something.
Thanks for any assistance,
TomtDoes the NT group which has only select permissions have a
deny on delete? Do any users or groups have deny set on the
table?
Permissions are cumulative but deny will take precedence.
-Sue
On Tue, 9 Nov 2004 14:51:03 -0800, "TomT" <tomt@.tomt.com>
wrote:
>I would think the following scenario should work, but it does not:
>I have a table, Products, for which all users, via an NT domain group (e.g.
>Domain Users) have only select permissions.
>There is another group, ProductManagers, who are also members of the above
>group, who need update, delete, and insert permissions. To accomplish this,
I
>created a database role ProductMgmt, and added the ProductManagers to it.
>This role has select, insert, update and delete permissions on the table.
>The members of this group, however, get an error when attempting to delete
>from the table. These members belong to both the Domain User and
>ProductManagers groups.
>I've also given the ProductManager group full permissions on the table. I'm
>confused as to why all of this is not working, obviously I'm missing
>something.
>Thanks for any assistance,
>Tomt
>|||Sue,
Thanks for your reply. No, there are no deny's on delete. I did know that
one, but am missing something...
Thanks
Tom
"Sue Hoegemeier" wrote:
> Does the NT group which has only select permissions have a
> deny on delete? Do any users or groups have deny set on the
> table?
> Permissions are cumulative but deny will take precedence.
> -Sue
> On Tue, 9 Nov 2004 14:51:03 -0800, "TomT" <tomt@.tomt.com>
> wrote:
>
>|||And there are no other Windows groups and no other roles in
that database? Just the two roles and the two NT groups?
And members of the ProductMgmt role can select, insert and
update but not delete?
-Sue
On Tue, 9 Nov 2004 15:44:04 -0800, "TomT" <tomt@.tomt.com>
wrote:
[vbcol=seagreen]
>Sue,
>Thanks for your reply. No, there are no deny's on delete. I did know that
>one, but am missing something...
>Thanks
>Tom
>"Sue Hoegemeier" wrote:
>|||There's the Domain Users and two other non-NT, SQL Server accounts for web
access to the table.
There are no other roles other than the built-in roles. That group has
select, insert, update and delete permissions.
I'm going to have them try it again tomorrow, I might have overlooked
checking the delete permission, which is just due to trying to do too many
things at once...
I'll post the results tomorrow. Thanks for your assistance with this.
Tom
"Sue Hoegemeier" wrote:
> And there are no other Windows groups and no other roles in
> that database? Just the two roles and the two NT groups?
> And members of the ProductMgmt role can select, insert and
> update but not delete?
> -Sue
> On Tue, 9 Nov 2004 15:44:04 -0800, "TomT" <tomt@.tomt.com>
> wrote:
>
>|||Tom
Grant them EXECUTE permission on SP that perform DELETE/INSERT/UPDATE on
this table.
"TomT" <tomt@.tomt.com> wrote in message
news:F6DB5A9A-6ADF-4F89-89E8-9656A8962BFF@.microsoft.com...[vbcol=seagreen]
> There's the Domain Users and two other non-NT, SQL Server accounts for web
> access to the table.
> There are no other roles other than the built-in roles. That group has
> select, insert, update and delete permissions.
> I'm going to have them try it again tomorrow, I might have overlooked
> checking the delete permission, which is just due to trying to do too many
> things at once...
> I'll post the results tomorrow. Thanks for your assistance with this.
> Tom
> "Sue Hoegemeier" wrote:
>
that[vbcol=seagreen]
group (e.g.[vbcol=seagreen]
above[vbcol=seagreen]
accomplish this, I[vbcol=seagreen]
to it.[vbcol=seagreen]
table.[vbcol=seagreen]
delete[vbcol=seagreen]
table. I'm[vbcol=seagreen]|||You really should check the other permissions as well as it
could make it easier for you to determine what has been
missed. Check the select, insert and update permissions as
well.
-Sue
On Tue, 9 Nov 2004 21:31:03 -0800, "TomT" <tomt@.tomt.com>
wrote:
[vbcol=seagreen]
>There's the Domain Users and two other non-NT, SQL Server accounts for web
>access to the table.
>There are no other roles other than the built-in roles. That group has
>select, insert, update and delete permissions.
>I'm going to have them try it again tomorrow, I might have overlooked
>checking the delete permission, which is just due to trying to do too many
>things at once...
>I'll post the results tomorrow. Thanks for your assistance with this.
>Tom
>"Sue Hoegemeier" wrote:
>|||I checked them all, for that particular group, and still no go. I have to
grant the permissions for the Domain Users group for insert, delete, etc.
otherwise the group I really need to have this access does not.
To summarize: Two groups (NT) Domain Users, to which all users belong,
member of the public role, and ProductManagers, member of public and
ProductMgmt roles.
A user, Rod, belongs to both Domain Users and ProductManagers groups.
ProductMangers have select, insert, delete and update permissions on table;
Domain Users have Select permission only, no other permissions granted or
denied.
Database role ProductMgmt has full permissions on the table.
With the scenario above, Rod cannot delete from the table. I have to grant
delete permissions to Domain Users in order for him to be able to delete row
s
from the table.
I gather from your replies that this should work, and I have set it up
correctly, is that right?
Thanks,
Tom
"Sue Hoegemeier" wrote:
> You really should check the other permissions as well as it
> could make it easier for you to determine what has been
> missed. Check the select, insert and update permissions as
> well.
> -Sue
> On Tue, 9 Nov 2004 21:31:03 -0800, "TomT" <tomt@.tomt.com>
> wrote:
>
>|||Yes it will work so you are still missing something. I can't
reproduce the issue rebuilding with the same groups and
roles - it works fine on my end.
Try using xp_logininfo to determine the group membership and
dsiplay information on the Product Managers group at the
Windows level.
-Sue
On Wed, 10 Nov 2004 08:45:01 -0800, "TomT" <tomt@.tomt.com>
wrote:
[vbcol=seagreen]
>I checked them all, for that particular group, and still no go. I have to
>grant the permissions for the Domain Users group for insert, delete, etc.
>otherwise the group I really need to have this access does not.
>To summarize: Two groups (NT) Domain Users, to which all users belong,
>member of the public role, and ProductManagers, member of public and
>ProductMgmt roles.
>A user, Rod, belongs to both Domain Users and ProductManagers groups.
>ProductMangers have select, insert, delete and update permissions on table;
>Domain Users have Select permission only, no other permissions granted or
>denied.
>Database role ProductMgmt has full permissions on the table.
>With the scenario above, Rod cannot delete from the table. I have to grant
>delete permissions to Domain Users in order for him to be able to delete ro
ws
>from the table.
>I gather from your replies that this should work, and I have set it up
>correctly, is that right?
>Thanks,
>Tom
>"Sue Hoegemeier" wrote:
>|||Sue,
I found the problem, the person who set up the NT user group
ProductManagers, set it up as a distribution group, not a security group.
Once that was fixed, everything works correctly.
BTW, I assume this would still work without the database role, i.e., just
the ProductManager group having the delete, etc. permissions assigned,
correct?
thanks for your help and patience,
Tom
"Sue Hoegemeier" wrote:
> Yes it will work so you are still missing something. I can't
> reproduce the issue rebuilding with the same groups and
> roles - it works fine on my end.
> Try using xp_logininfo to determine the group membership and
> dsiplay information on the Product Managers group at the
> Windows level.
> -Sue
> On Wed, 10 Nov 2004 08:45:01 -0800, "TomT" <tomt@.tomt.com>
> wrote:
>
>
I have a table, Products, for which all users, via an NT domain group (e.g.
Domain Users) have only select permissions.
There is another group, ProductManagers, who are also members of the above
group, who need update, delete, and insert permissions. To accomplish this,
I
created a database role ProductMgmt, and added the ProductManagers to it.
This role has select, insert, update and delete permissions on the table.
The members of this group, however, get an error when attempting to delete
from the table. These members belong to both the Domain User and
ProductManagers groups.
I've also given the ProductManager group full permissions on the table. I'm
confused as to why all of this is not working, obviously I'm missing
something.
Thanks for any assistance,
TomtDoes the NT group which has only select permissions have a
deny on delete? Do any users or groups have deny set on the
table?
Permissions are cumulative but deny will take precedence.
-Sue
On Tue, 9 Nov 2004 14:51:03 -0800, "TomT" <tomt@.tomt.com>
wrote:
>I would think the following scenario should work, but it does not:
>I have a table, Products, for which all users, via an NT domain group (e.g.
>Domain Users) have only select permissions.
>There is another group, ProductManagers, who are also members of the above
>group, who need update, delete, and insert permissions. To accomplish this,
I
>created a database role ProductMgmt, and added the ProductManagers to it.
>This role has select, insert, update and delete permissions on the table.
>The members of this group, however, get an error when attempting to delete
>from the table. These members belong to both the Domain User and
>ProductManagers groups.
>I've also given the ProductManager group full permissions on the table. I'm
>confused as to why all of this is not working, obviously I'm missing
>something.
>Thanks for any assistance,
>Tomt
>|||Sue,
Thanks for your reply. No, there are no deny's on delete. I did know that
one, but am missing something...
Thanks
Tom
"Sue Hoegemeier" wrote:
> Does the NT group which has only select permissions have a
> deny on delete? Do any users or groups have deny set on the
> table?
> Permissions are cumulative but deny will take precedence.
> -Sue
> On Tue, 9 Nov 2004 14:51:03 -0800, "TomT" <tomt@.tomt.com>
> wrote:
>
>|||And there are no other Windows groups and no other roles in
that database? Just the two roles and the two NT groups?
And members of the ProductMgmt role can select, insert and
update but not delete?
-Sue
On Tue, 9 Nov 2004 15:44:04 -0800, "TomT" <tomt@.tomt.com>
wrote:
[vbcol=seagreen]
>Sue,
>Thanks for your reply. No, there are no deny's on delete. I did know that
>one, but am missing something...
>Thanks
>Tom
>"Sue Hoegemeier" wrote:
>|||There's the Domain Users and two other non-NT, SQL Server accounts for web
access to the table.
There are no other roles other than the built-in roles. That group has
select, insert, update and delete permissions.
I'm going to have them try it again tomorrow, I might have overlooked
checking the delete permission, which is just due to trying to do too many
things at once...
I'll post the results tomorrow. Thanks for your assistance with this.
Tom
"Sue Hoegemeier" wrote:
> And there are no other Windows groups and no other roles in
> that database? Just the two roles and the two NT groups?
> And members of the ProductMgmt role can select, insert and
> update but not delete?
> -Sue
> On Tue, 9 Nov 2004 15:44:04 -0800, "TomT" <tomt@.tomt.com>
> wrote:
>
>|||Tom
Grant them EXECUTE permission on SP that perform DELETE/INSERT/UPDATE on
this table.
"TomT" <tomt@.tomt.com> wrote in message
news:F6DB5A9A-6ADF-4F89-89E8-9656A8962BFF@.microsoft.com...[vbcol=seagreen]
> There's the Domain Users and two other non-NT, SQL Server accounts for web
> access to the table.
> There are no other roles other than the built-in roles. That group has
> select, insert, update and delete permissions.
> I'm going to have them try it again tomorrow, I might have overlooked
> checking the delete permission, which is just due to trying to do too many
> things at once...
> I'll post the results tomorrow. Thanks for your assistance with this.
> Tom
> "Sue Hoegemeier" wrote:
>
that[vbcol=seagreen]
group (e.g.[vbcol=seagreen]
above[vbcol=seagreen]
accomplish this, I[vbcol=seagreen]
to it.[vbcol=seagreen]
table.[vbcol=seagreen]
delete[vbcol=seagreen]
table. I'm[vbcol=seagreen]|||You really should check the other permissions as well as it
could make it easier for you to determine what has been
missed. Check the select, insert and update permissions as
well.
-Sue
On Tue, 9 Nov 2004 21:31:03 -0800, "TomT" <tomt@.tomt.com>
wrote:
[vbcol=seagreen]
>There's the Domain Users and two other non-NT, SQL Server accounts for web
>access to the table.
>There are no other roles other than the built-in roles. That group has
>select, insert, update and delete permissions.
>I'm going to have them try it again tomorrow, I might have overlooked
>checking the delete permission, which is just due to trying to do too many
>things at once...
>I'll post the results tomorrow. Thanks for your assistance with this.
>Tom
>"Sue Hoegemeier" wrote:
>|||I checked them all, for that particular group, and still no go. I have to
grant the permissions for the Domain Users group for insert, delete, etc.
otherwise the group I really need to have this access does not.
To summarize: Two groups (NT) Domain Users, to which all users belong,
member of the public role, and ProductManagers, member of public and
ProductMgmt roles.
A user, Rod, belongs to both Domain Users and ProductManagers groups.
ProductMangers have select, insert, delete and update permissions on table;
Domain Users have Select permission only, no other permissions granted or
denied.
Database role ProductMgmt has full permissions on the table.
With the scenario above, Rod cannot delete from the table. I have to grant
delete permissions to Domain Users in order for him to be able to delete row
s
from the table.
I gather from your replies that this should work, and I have set it up
correctly, is that right?
Thanks,
Tom
"Sue Hoegemeier" wrote:
> You really should check the other permissions as well as it
> could make it easier for you to determine what has been
> missed. Check the select, insert and update permissions as
> well.
> -Sue
> On Tue, 9 Nov 2004 21:31:03 -0800, "TomT" <tomt@.tomt.com>
> wrote:
>
>|||Yes it will work so you are still missing something. I can't
reproduce the issue rebuilding with the same groups and
roles - it works fine on my end.
Try using xp_logininfo to determine the group membership and
dsiplay information on the Product Managers group at the
Windows level.
-Sue
On Wed, 10 Nov 2004 08:45:01 -0800, "TomT" <tomt@.tomt.com>
wrote:
[vbcol=seagreen]
>I checked them all, for that particular group, and still no go. I have to
>grant the permissions for the Domain Users group for insert, delete, etc.
>otherwise the group I really need to have this access does not.
>To summarize: Two groups (NT) Domain Users, to which all users belong,
>member of the public role, and ProductManagers, member of public and
>ProductMgmt roles.
>A user, Rod, belongs to both Domain Users and ProductManagers groups.
>ProductMangers have select, insert, delete and update permissions on table;
>Domain Users have Select permission only, no other permissions granted or
>denied.
>Database role ProductMgmt has full permissions on the table.
>With the scenario above, Rod cannot delete from the table. I have to grant
>delete permissions to Domain Users in order for him to be able to delete ro
ws
>from the table.
>I gather from your replies that this should work, and I have set it up
>correctly, is that right?
>Thanks,
>Tom
>"Sue Hoegemeier" wrote:
>|||Sue,
I found the problem, the person who set up the NT user group
ProductManagers, set it up as a distribution group, not a security group.
Once that was fixed, everything works correctly.
BTW, I assume this would still work without the database role, i.e., just
the ProductManager group having the delete, etc. permissions assigned,
correct?
thanks for your help and patience,
Tom
"Sue Hoegemeier" wrote:
> Yes it will work so you are still missing something. I can't
> reproduce the issue rebuilding with the same groups and
> roles - it works fine on my end.
> Try using xp_logininfo to determine the group membership and
> dsiplay information on the Product Managers group at the
> Windows level.
> -Sue
> On Wed, 10 Nov 2004 08:45:01 -0800, "TomT" <tomt@.tomt.com>
> wrote:
>
>
Overlapping partitions and join filters
I am using Sql 2005 and merge replication with push subscriptions. I have several dynamic join filters on some of my tables.
The join filters all use a central table that maps say a server location name (something that is returned from HOTNAME() in my case) to an for a store branch ID. This is a retail system database.
When I add a new new subscription I update this table with the new server location name and it's corresponding branch ID. My filtered tables all have a foreign key in them that is the branch ID. I can then effectively join from the server location name to a Branch ID.
What I have noticed is that if I update one row in the map table, sql server will re-generate all partitioned rows for all subscribers, even for rows that haven't been updated.
The net result is that when I add a subscription, my existing subscriptions all get about 52,000 row updates.
Am I seeing this because I said my partitions will overlap when I created the table articles?
Thanks for any help
Graham
Graham, you always ask the toughest questions 
Are you "updating" or "inserting" the central table? If you're updating, are you moving rows from one partition to another?
|||Hi Greg,
I am updating the central table. The data is not moving partitions either. I see it even when I update the talb eon a row that doesn't have a partition yet, i.e. there are no subscriptions that would cause a match in the filter.
Graham
Overlapping or nonoverlapping publication? Which type applies to my design?
I'm going through the documentation for SQL Server 2005 replication
and am trying to understand what type of publication I should be
using. Here is how replication should be working in my system:
I have a database on a server that will be accessible over the
Internet. This database can be used by multiple companies. Tables are
designed so that a company ID is used to identify records that belong
to a specific company. Every record in every table can be filtered out
to belong to a unique company. That means that no two companies ever
share the same data. Each company can have multiple mobile users using
PDAs to communicate with the server. Each user receives data only from
their company. Some of the data is created on the server and
replicated to the PDA. Some data is also created on the PDA and
replicated to the server. Under no condition is data that is created
on the server ever edited on a PDA. Data created on a PDA is always
inserted into the table and once inserted it can never be modified.
Mobile users cannot modify or delete data. When one mobile user
replicates their data with the server, the newly inserted records that
were created on their PDA are also replicated to all other PDAs within
the same company but this data can never be modified by any mobile
user. In essence, the entire database on the server is replicated to
all of the PDA users but the application on the PDA will only allow
inserts into certain tables - no modifications or deletions. All
mobile users should always get newly inserted data made by other
mobile users within the same company.
I'm having a hard time trying to figure out what kind of publication
is required. In a section of the documentation on replication under
the title "Considerations for Nonoverlapping Partitions", it is
written:
* The Subscriber cannot update columns referenced in a parameterized
filter.
*If an insert at a Subscriber does not belong to the partition, it is
not deleted. However, it will not be replicated to other Subscribers.
I'm not sure what these two statements mean. The first statement
mentions "cannot update columns". No columns will be updated, but
inserts will be done.
In the second statement, it refers to an insert "not belonging to the
partition". If all PDA users are subscribing to the same publication,
won't they all get a copy of the inserted records that are made on all
other PDAs?
If you have a nonoverlapping partion, which is what I assume is what I
should be using, how could any insert possibly belong to some other
partition? Isn't there just one partition?
What kind of publication should I be using: overlapping or
nonoverlapping?
Thanks for your help
Polaris
Hi Hilary,
You wrote:
> With overlapping partitions data can be owned by more than two salesmen,
> with non-overlapping it would be only one.
> From what you describe you should use non-overlapping partitions.
So if you recommend non-overlapping, then are you saying that each PDA
client within a single company would be its own "owner"? Yet I want
it such that all the data on ALL PDAs is identical after replication.
Each user on each PDA can insert data but not modify or delete
existing data. Will non-overlapping satisfy this requirement?
Thanks
Johann
On Feb 25, 3:10 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> Basically it means if you are filtering on the salespersonID column for
> example the subscribers will be unable to update this column to have another
> value. Your second question means that if you add a row which has a
> salesmanID value of 5 and your filter for that subscription is 4, this row
> will remain in the subscriber and never move to the publisher.
> The way to look at partitions is that it is a block of related data. So its
> the salesman row, his clients, and all of its orders and all related data.
> Should a salesman take on new clients, the partition will have the new
> clients and their orders added to it. Should the salesman change territories
> with another salesman there would be a partition realignment which would
> include all the new clients, their orders, etc.
> By default if you were to update the client salespersonID on the client
> table, only the client row would move to the subscriber. With join filters
> all the clients data will also move as the update statement on the parent
> table will also affect all child rows.
> With overlapping partitions data can be owned by more than two salesmen,
> with non-overlapping it would be only one.
> From what you describe you should use non-overlapping partitions. You can
> have one partition for every salesman on the publisher, and if you are not
> using hierarchies (or doing republishing) you will only have one partition
> on the subscriber.
> HTH
> --
> Hilary Cotter
|||Perhaps I am missing something here, but I don't think you need to do any
filtering at all, or at least not join filtering which is where partitions
come in.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Polaris431" <polaris431@.gmail.com> wrote in message
news:1172415914.436083.96840@.a75g2000cwd.googlegro ups.com...
> Hi Hilary,
> You wrote:
> So if you recommend non-overlapping, then are you saying that each PDA
> client within a single company would be its own "owner"? Yet I want
> it such that all the data on ALL PDAs is identical after replication.
> Each user on each PDA can insert data but not modify or delete
> existing data. Will non-overlapping satisfy this requirement?
> Thanks
> Johann
>
> On Feb 25, 3:10 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
>
>
and am trying to understand what type of publication I should be
using. Here is how replication should be working in my system:
I have a database on a server that will be accessible over the
Internet. This database can be used by multiple companies. Tables are
designed so that a company ID is used to identify records that belong
to a specific company. Every record in every table can be filtered out
to belong to a unique company. That means that no two companies ever
share the same data. Each company can have multiple mobile users using
PDAs to communicate with the server. Each user receives data only from
their company. Some of the data is created on the server and
replicated to the PDA. Some data is also created on the PDA and
replicated to the server. Under no condition is data that is created
on the server ever edited on a PDA. Data created on a PDA is always
inserted into the table and once inserted it can never be modified.
Mobile users cannot modify or delete data. When one mobile user
replicates their data with the server, the newly inserted records that
were created on their PDA are also replicated to all other PDAs within
the same company but this data can never be modified by any mobile
user. In essence, the entire database on the server is replicated to
all of the PDA users but the application on the PDA will only allow
inserts into certain tables - no modifications or deletions. All
mobile users should always get newly inserted data made by other
mobile users within the same company.
I'm having a hard time trying to figure out what kind of publication
is required. In a section of the documentation on replication under
the title "Considerations for Nonoverlapping Partitions", it is
written:
* The Subscriber cannot update columns referenced in a parameterized
filter.
*If an insert at a Subscriber does not belong to the partition, it is
not deleted. However, it will not be replicated to other Subscribers.
I'm not sure what these two statements mean. The first statement
mentions "cannot update columns". No columns will be updated, but
inserts will be done.
In the second statement, it refers to an insert "not belonging to the
partition". If all PDA users are subscribing to the same publication,
won't they all get a copy of the inserted records that are made on all
other PDAs?
If you have a nonoverlapping partion, which is what I assume is what I
should be using, how could any insert possibly belong to some other
partition? Isn't there just one partition?
What kind of publication should I be using: overlapping or
nonoverlapping?
Thanks for your help
Polaris
Hi Hilary,
You wrote:
> With overlapping partitions data can be owned by more than two salesmen,
> with non-overlapping it would be only one.
> From what you describe you should use non-overlapping partitions.
So if you recommend non-overlapping, then are you saying that each PDA
client within a single company would be its own "owner"? Yet I want
it such that all the data on ALL PDAs is identical after replication.
Each user on each PDA can insert data but not modify or delete
existing data. Will non-overlapping satisfy this requirement?
Thanks
Johann
On Feb 25, 3:10 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> Basically it means if you are filtering on the salespersonID column for
> example the subscribers will be unable to update this column to have another
> value. Your second question means that if you add a row which has a
> salesmanID value of 5 and your filter for that subscription is 4, this row
> will remain in the subscriber and never move to the publisher.
> The way to look at partitions is that it is a block of related data. So its
> the salesman row, his clients, and all of its orders and all related data.
> Should a salesman take on new clients, the partition will have the new
> clients and their orders added to it. Should the salesman change territories
> with another salesman there would be a partition realignment which would
> include all the new clients, their orders, etc.
> By default if you were to update the client salespersonID on the client
> table, only the client row would move to the subscriber. With join filters
> all the clients data will also move as the update statement on the parent
> table will also affect all child rows.
> With overlapping partitions data can be owned by more than two salesmen,
> with non-overlapping it would be only one.
> From what you describe you should use non-overlapping partitions. You can
> have one partition for every salesman on the publisher, and if you are not
> using hierarchies (or doing republishing) you will only have one partition
> on the subscriber.
> HTH
> --
> Hilary Cotter
|||Perhaps I am missing something here, but I don't think you need to do any
filtering at all, or at least not join filtering which is where partitions
come in.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Polaris431" <polaris431@.gmail.com> wrote in message
news:1172415914.436083.96840@.a75g2000cwd.googlegro ups.com...
> Hi Hilary,
> You wrote:
> So if you recommend non-overlapping, then are you saying that each PDA
> client within a single company would be its own "owner"? Yet I want
> it such that all the data on ALL PDAs is identical after replication.
> Each user on each PDA can insert data but not modify or delete
> existing data. Will non-overlapping satisfy this requirement?
> Thanks
> Johann
>
> On Feb 25, 3:10 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
>
>
Overlapping of PDF
I have few reports that I want to get into a single PDF. I am clubbing all of
them as subreports in a single parent report. Then I convert that report to
PDF using SOAP API.
I would like to mention some points :
- I have properly placed rectangles (with 0 inch height and Page Break At
End property set to true) as pagebreaks.
- Also the combination of reports is such that some will fit in a Protrait
while others in Landscape.
The problems I am facing :
- On publishing them to the server, they just look fine. When I try to
render that to PDF, I dont know why they just overlap somewhere.
- Another problem is that the quality of the PDF. It seems to get
distorted(though the printout seems decent). This will definitely be a
problem if the user prefers an online copy of the report.
Can you help me out?Would it be possible for you to send me the rdls, rdl.data file, and the PDF
directly?
Without more information I don't think I can recommend anything to you.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kam" <Kam@.discussions.microsoft.com> wrote in message
news:CBDF9219-D306-412E-9DB4-36783ADCBD9A@.microsoft.com...
> I have few reports that I want to get into a single PDF. I am clubbing all
of
> them as subreports in a single parent report. Then I convert that report
to
> PDF using SOAP API.
> I would like to mention some points :
> - I have properly placed rectangles (with 0 inch height and Page Break At
> End property set to true) as pagebreaks.
> - Also the combination of reports is such that some will fit in a
Protrait
> while others in Landscape.
> The problems I am facing :
> - On publishing them to the server, they just look fine. When I try to
> render that to PDF, I dont know why they just overlap somewhere.
> - Another problem is that the quality of the PDF. It seems to get
> distorted(though the printout seems decent). This will definitely be a
> problem if the user prefers an online copy of the report.
> Can you help me out?
them as subreports in a single parent report. Then I convert that report to
PDF using SOAP API.
I would like to mention some points :
- I have properly placed rectangles (with 0 inch height and Page Break At
End property set to true) as pagebreaks.
- Also the combination of reports is such that some will fit in a Protrait
while others in Landscape.
The problems I am facing :
- On publishing them to the server, they just look fine. When I try to
render that to PDF, I dont know why they just overlap somewhere.
- Another problem is that the quality of the PDF. It seems to get
distorted(though the printout seems decent). This will definitely be a
problem if the user prefers an online copy of the report.
Can you help me out?Would it be possible for you to send me the rdls, rdl.data file, and the PDF
directly?
Without more information I don't think I can recommend anything to you.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kam" <Kam@.discussions.microsoft.com> wrote in message
news:CBDF9219-D306-412E-9DB4-36783ADCBD9A@.microsoft.com...
> I have few reports that I want to get into a single PDF. I am clubbing all
of
> them as subreports in a single parent report. Then I convert that report
to
> PDF using SOAP API.
> I would like to mention some points :
> - I have properly placed rectangles (with 0 inch height and Page Break At
> End property set to true) as pagebreaks.
> - Also the combination of reports is such that some will fit in a
Protrait
> while others in Landscape.
> The problems I am facing :
> - On publishing them to the server, they just look fine. When I try to
> render that to PDF, I dont know why they just overlap somewhere.
> - Another problem is that the quality of the PDF. It seems to get
> distorted(though the printout seems decent). This will definitely be a
> problem if the user prefers an online copy of the report.
> Can you help me out?
overlapping job
Hi,
If a job is scheduled to run every minute, what happens if
the job takes longer than 1 minute to execute.
Will a second occurence of the job run or will it wait for
the first occurence of the job to finish or will it not
run.
Thanks,
ned
To take an example, if the job takes 1 min 10 seconds to run and is
scheduled to run every minute it will in fact run every 2 minutes.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Ned Radenovic" <anonymous@.discussions.microsoft.com> wrote in message
news:00c901c492b7$ea326c20$a401280a@.phx.gbl...
> Hi,
>
> If a job is scheduled to run every minute, what happens if
> the job takes longer than 1 minute to execute.
> Will a second occurence of the job run or will it wait for
> the first occurence of the job to finish or will it not
> run.
> Thanks,
> ned
|||Thanks,
Ned
>--Original Message--
>To take an example, if the job takes 1 min 10 seconds to
run and is
>scheduled to run every minute it will in fact run every 2
minutes.
>--
>HTH
>Jasper Smith (SQL Server MVP)
>http://www.sqldbatips.com
>I support PASS - the definitive, global
>community for SQL Server professionals -
>http://www.sqlpass.org
>"Ned Radenovic" <anonymous@.discussions.microsoft.com>
wrote in message[vbcol=seagreen]
>news:00c901c492b7$ea326c20$a401280a@.phx.gbl...
if[vbcol=seagreen]
for
>
>.
>
If a job is scheduled to run every minute, what happens if
the job takes longer than 1 minute to execute.
Will a second occurence of the job run or will it wait for
the first occurence of the job to finish or will it not
run.
Thanks,
ned
To take an example, if the job takes 1 min 10 seconds to run and is
scheduled to run every minute it will in fact run every 2 minutes.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Ned Radenovic" <anonymous@.discussions.microsoft.com> wrote in message
news:00c901c492b7$ea326c20$a401280a@.phx.gbl...
> Hi,
>
> If a job is scheduled to run every minute, what happens if
> the job takes longer than 1 minute to execute.
> Will a second occurence of the job run or will it wait for
> the first occurence of the job to finish or will it not
> run.
> Thanks,
> ned
|||Thanks,
Ned
>--Original Message--
>To take an example, if the job takes 1 min 10 seconds to
run and is
>scheduled to run every minute it will in fact run every 2
minutes.
>--
>HTH
>Jasper Smith (SQL Server MVP)
>http://www.sqldbatips.com
>I support PASS - the definitive, global
>community for SQL Server professionals -
>http://www.sqlpass.org
>"Ned Radenovic" <anonymous@.discussions.microsoft.com>
wrote in message[vbcol=seagreen]
>news:00c901c492b7$ea326c20$a401280a@.phx.gbl...
if[vbcol=seagreen]
for
>
>.
>
Overlapping integer ranges ?
:confused: Dont know if this will be tough for the rest of you but for someone who is fairly new to SQL...I cannot figure it out...
I have a table:
Rownumber starttime endtime
1 l 30 l 240
2 l 40 l 120
3 l 50 l 260
4 l 1300 l 1400
Rows 1, 2, and 3 over lap with one another and I am trying to obtain the starttime and endtime values which can cover them all.
I would like to find the overlapping (starttime - endtime) ranges and accept the lowest starttime value and the highest endtime value.
Row 1: 30-------240
Row 2: 40-----120
Row 3: 50-------260
Row 4: ...1300---1440
I would like to include starttime-endtime ranges that do not overlap with any other integer range.
which in this case would be:
Rownumber starttime endtime
1 l 30 l 260
2 l 1330 l 1400
I was thinking of using a cursor and comparing each row to all of the other rows in the table and then setting a boolean in that row if it overlaps with another row in the table...is there a better way of doing this?
Thank you for the help!yes, there is a better way
"comparing each row to all of the other rows in the table" is as good a definition of a self-crossjoin as i can think of
a self-crossjoin would be written like this:
select t1.rownumber
, t1.starttime
, t1.endtime
, t2.rownumber
, t2.starttime
, t2.endtime
from yourtable as t1
cross
join yourtable as t2
now, with this as the basis, you can add a WHERE condition for overlapping ranges
e.g. WHERE t1.starttime BETWEEN t2.starttime and t2.endtime
you'd want to be careful about which pairs you want to keep, which would basically be the same idea as your "setting a boolean in that row if it overlaps" except that instead of setting a boolean, the t1 row survives the WHERE filter
make sense?|||What defines an "overlap" for your case? Some overlaps are obvious, since the beginning of one range sits inside of another, but what about more complex cases like:
1 50 250
2 200 450
3 300 650
4 500 800
Is this one range, two ranges, or something different?
-PatP|||I'd think the poster would want this as a result based on Pat's example:
1 50 450
4 500 800
Is my assumption correct?|||I'm just curious, but how the heck did you get there? That was a combination that I never thought of!
-PatP|||rdjabarov: You're close :) but the I am looking for only 50-800 (u can the view diagram below)
PatP: if that question was posed to me, this table is storing a list of times that are blocked off for the a day (i.e. hotel reservations, someone's personal schedule). (BUT Times can overlap in this case). So from the table of overlapping ( or non-overlapping) times (int), I need to find the times where either the entire range or part of the range sit inside another time.
i.e.
1 50 250 --entire range sits inside 2
2 200 450 --entire range sits inside 3
3 300 650 --part of this range (500-650) overlaps with 4
4 500 800 --and accordingly, (500-650) overlaps with 3
This is what I use to visualize it (where --- is a blocked off time):
For Date 07-26-2004:
0 1000
......... 50--------250
......................... 200------450 <sp>
................................300-------650
...................................... ........ 500-----800
so 50-800 would be the time range that would encompass all of the other time ranges since each one overlaps with another...
r937, thank you for your suggestion :)
not even sure how to begin with the where statement to achieve the above
is the select syntax supposed to be?:
select t1.rownumber
, t1.starttime
, t1.endtime
, t2.rownumber
, t2.starttime
, t2.endtime
from yourtable as t1
cross
join yourtable as t2
where t1.Rownumber = t2.rownumber
cause when I do just what you suggested (without the where t1.Rownumber = t2.rownumber)
I get:
Rownumber starttime endtime Rownumber starttime endtime
1 l 30 l 240 1 l 30 l 240
2 l 40 l 120 1 l 30 l 240
3 l 50 l 260 1 l 30 l 240
4 l 1300 l 1400 1 l 30 l 240
1 l 30 l 240 2 l 40 l 120
2 l 40 l 120 2 l 40 l 120
3 l 50 l 260 2 l 40 l 120
4 l 1300 l 1400 2 l 40 l 120
1 l 30 l 240 3 l 50 l 260
2 l 40 l 120 3 l 50 l 260
3 l 50 l 260 3 l 50 l 260
4 l 1300 l 1400 3 l 50 l 260
1 l 30 l 240 4 l 1300 l 1400
2 l 40 l 120 4 l 1300 l 1400
3 l 50 l 260 4 l 1300 l 1400
4 l 1300 l 1400 4 l 1300 l 1400|||Hmmmm...Any previous maximum endtime should be less than any consecutive minimum starttime...I think...Isn't it? But now that I am looking at it, if that's the logic then the only result you can get from Pat's example would be:
1 50 800|||Hey, you beat me to that, that's what I thought. Now the question is how to take into account ranges that DON'T overlap, because if you just do MIN(starttime) and MAX(endtime) it would skip non-overlapping ranges.|||awfdml, the first thing you should do is restate your requirements
a precise statement of requirements is crucial
for example, your first post said "I am trying to obtain the starttime and endtime values which can cover them all"
which can cover them all suggests that MIN() and MAX() is sufficient
but there will be holes, right?
and if you somehow want to see the holes...?
try to give a statement which defines all the start/end pairs you want|||The real problem comes from sequences like:
10-30
30-50
50-70
70-90
90-110
110-130
130-150
etc.
Each trip through the consolidation process brings new consolidations. I don't know of a way to express this using set notation without using iteration, so I think that you'll have to use an iterative (code based) solution.
At least in SQL-2000 you could solve the problem using a table valued function.
-PatP|||Pat: With the scenario you suggested, I would still consider each one overlapping since they share the sametimes
i.e. 10-30 (30 is shared, 50 is shared)...therefore the result would be 10-70
30-50
50-70
Think this would be what you're telling me, Pat?
30-50
40-60
50-70
90-110
110-120
30--50
....40--60
......50---70
......................90----110
...................................110----120
rdjabarov: I think the problem with doing a min and max is in the above scenario, like u stated :)
The result I would need to achieve in this case is:
First 3 times overlap with one another (one set), Last two overlap (second set) (but there is no overlap between)
30-70
90-120
r937: Yes, there will be holes (like above)
I definitely apologize if I did not clarify what I am looking for but I shall try :)
For a given table of blocked times, find all of the ranges(start-end) that overlap (partly, or entirely).
For each set of overlapping ranges, return* the range that will encompass the set.
If a range of the blocked times do not overlap with any another, return the the range.
*return-meaning store into a temp table or return as resultset
I.E.
Scenario 1: All ranges overlap
10-30
30-60
50-70
70-90
Result : 10-90
Scenario 2: Some ranges overlap with others, or not at all
20-40
35-55
40-60
70-90
85-100
150-200
Result: 20-60
70-100
150-200
Scenario 3: None of the times overlap
100-150
180-200
250-300
Result: 100-150
180-200
250-300
I am hoping that I do not have to use an iterative solution. (me in denial :rolleyes: )
The list of blocked times will not be big at all (most it could possibly be is 60 (per minute and NOT realistic)
For my case, the largest list of blocked times I would get could be anywhere from 12-20.
Still open to any suggestions of course as I think away:) Thank you all for your suggestions thus far! :D|||Forgot to ask Pat:
"At least in SQL-2000 you could solve the problem using a table valued function."
I am using SQL 2000, and this probably is a stupid question but what is a "table valued" function? Is it the user defined function?
:confused:
Thanks!|||Check UDF in BOL.|||In SQL-2000, User Defined Functions come in two flavors. Scalar functions return a single value, much like a C function would and are used anywhere an rvalue can be used like the SELECT list the WHERE clause, etc. Table valued functions actually return a table as their value and are used in the FROM clause almost exactly like you'd use a table.
-PatP|||just a dumb question, but what are your units?
your scenarios all use 20, 30, 55, etc.
is 1 the smallest unit here? perhaps 5? these are minutes, right?
because you did mention these were times, and i notice the largest value you're using is 1440 (which should be familiar to anyone who has ever worked with date and time values)|||You are correct r937, the units I am using are minutes and the smallest value is 0 and the largest of course is 1440.
i.e. 0-1440 in a range means entire day is blocked off|||right, well, what i was thinking of was to generate each of the 1440 different minutes, check it against all the ranges, and if no range covers it, then it's "available"
that can be done in a single query
what's next, though, is a bit harder, which is to assemble all the available minutes into "runs"
i.e. if the available minutes are 20, 21, 23, 24, 37, 38, 39
then you want to generate runs of 21-24 and 37-39|||thanks for the idea but I have a question:
would generating each minute be a performance issue? meaning that would I have to have some sort of a loop to check each minute?
I'm trying to watch out for performance since I'm doing this per day and allowing for a search for the available times for a range of dates...|||This is a pretty efficient way to do it. You will need a table of numbers to use it. It creates a string of periods then replaces periods with spaces in character positions within start and end times. Then it searches for occurances of ... ..... ...., that signify the beginning and ending of ranges (just like your number line diagram actually) and displays them.
DECLARE @.line VARCHAR(1500)
SELECT @.line = replicate('.',1500)
SELECT @.line = stuff(@.line,starttime,endtime - starttime,replicate(' ',endtime - starttime))
FROM times
SELECT N1.ID + 1 StartTime,
( SELECT TOP 1 ID + 1
FROM Numbers
WHERE ID > N1.ID
AND ID < len(@.line)
AND SubString(@.line,ID, 2) = ' .'
ORDER BY ID ) EndTime
FROM
Numbers N1
WHERE N1.ID < Len (@.line)
AND SubString(@.line,N1.ID, 2) = '. '
-- Test Data
CREATE TABLE times (rowid int identity, starttime int, endtime int)
INSERT times ( starttime, endtime ) VALUES (20,40)
INSERT times ( starttime, endtime ) VALUES (35,55)
INSERT times ( starttime, endtime ) VALUES (40,60)
INSERT times ( starttime, endtime ) VALUES (70,90)
INSERT times ( starttime, endtime ) VALUES (85,100)
INSERT times ( starttime, endtime ) VALUES (150,200)
StartTime EndTime
---- ----
20 60
70 100
150 200
DELETE FROM times
INSERT times ( starttime, endtime ) values (10,30)
INSERT times ( starttime, endtime ) values (30,60)
INSERT times ( starttime, endtime ) values (50,70)
INSERT times ( starttime, endtime ) values (70,90)
StartTime EndTime
---- ----
10 90
DELETE FROM times
INSERT times ( starttime, endtime ) values (100,150)
INSERT times ( starttime, endtime ) values (180,200)
INSERT times ( starttime, endtime ) values (250,300)
StartTime EndTime
---- ----
100 150
180 200
250 300
DELETE FROM times
INSERT times ( starttime, endtime ) values (180,200)
INSERT times ( starttime, endtime ) values (250,300)
StartTime EndTime
---- ----
180 200
250 300
--To make a Numbers table if you don't have one
SELECT TOP 8000 IDENTITY(INT) AS ID
INTO Numbers
FROM sysobjects s1
CROSS JOIN sysobjects s2|||i am astounded
nice job, vaxman
;) ;) ;)|||High praise indeed. Thanks. I was going to extend it to work for multiple groups in the same dataset. That is a little tricky. Give it a whirl :) I'll put it up if I come up with something elegant.|||I said I would try to figure out a way to do this for multiple groups in the same dataset. This is one way. It's probably not the most efficient way to do it but it's passable.
-- Some test Data
SET NOCOUNT ON
CREATE TABLE times (rowid int identity, groupid int, starttime int, endtime int)
INSERT times ( groupid, starttime, endtime ) VALUES (1,20,40)
INSERT times ( groupid, starttime, endtime ) VALUES (1,35,55)
INSERT times ( groupid, starttime, endtime ) VALUES (1,40,60)
INSERT times ( groupid, starttime, endtime ) VALUES (1,70,90)
INSERT times ( groupid, starttime, endtime ) VALUES (1,85,100)
INSERT times ( groupid, starttime, endtime ) VALUES (1,150,200)
INSERT times ( groupid, starttime, endtime ) values (2,10,30)
INSERT times ( groupid, starttime, endtime ) values (2,30,60)
INSERT times ( groupid, starttime, endtime ) values (2,50,70)
INSERT times ( groupid, starttime, endtime ) values (2,70,90)
INSERT times ( groupid, starttime, endtime ) values (3,100,150)
INSERT times ( groupid, starttime, endtime ) values (3,180,200)
INSERT times ( groupid, starttime, endtime ) values (3,250,300)
-- Return a formated string representing time blocks.
CREATE FUNCTION f_GetLine(@.GroupID Int)
RETURNS VARCHAR(1500)
AS
BEGIN
DECLARE @.line VARCHAR(1500)
SELECT @.line = stuff(Coalesce(@.line,replicate('.',1500)),starttim e,
endtime - starttime,replicate(' ',endtime - starttime))
FROM times
WHERE GroupID = @.GroupID
RETURN @.line
END
-- Generate 106496 rows of test data.
While Coalesce(@.@.Rowcount,0) < 30000
INSERT times ( groupid, starttime, endtime ) select groupid, starttime, endtime from times
-- Results in 16 seconds. 6656 rows/second.
SELECT GroupId, N1.ID + 1 StartTime, (
SELECT TOP 1 ID + 1
FROM Numbers
WHERE ID > N1.ID
AND ID < len(o.line)
AND SubString(o.line,ID, 2) = ' .'
ORDER BY ID ) EndTime
FROM
Numbers N1, (
SELECT GroupId, dbo.f_GetLine(GroupID) line
FROM Times
GROUP BY GroupId
) o
WHERE N1.ID < Len (line)
AND SubString(line,N1.ID, 2) = '. '
Order by GroupID
GroupId StartTime EndTime
---- ---- ----
1 20 60
1 70 100
1 150 200
2 10 90
3 100 150
3 180 200
3 250 300|||I installed SQL 2005 today and I thought I would test this script on it.
Performance was dismal on my Laptop. I realized it was doing a lot of unnecessary work.
Change the query for a single dataset to:
SELECT ID + 1 StartTime, charindex(' .',@.line, ID + 1) + 1 EndTime
FROM Numbers
WHERE ID < Len (@.line)
AND SubString(@.line,ID, 2) = '. '
And change the query for multiple groups in a dataset to:
SELECT GroupId, N1.ID + 1 StartTime, charindex(' .',o.line, N1.ID + 1) + 1 EndTime
FROM
Numbers N1, (
SELECT GroupId, dbo.f_GetLine(GroupID) line
FROM Times
GROUP BY GroupId
) o
WHERE N1.ID < Len (line)
AND SubString(line,N1.ID, 2) = '. '
Order by GroupID
That version will churn through about 20,000 rows per second.|||A final comment about this. SQL 2005 performance was terrible even with the optimization above. It took 8 minutes to return a result. It was due to the ORDER BY. Not sure why, but rewriting it like this:
Declare @.Line Table (GroupID int, line Varchar(1500))
Insert Into @.line
SELECT GroupId , dbo.f_GetLine(GroupID) line
FROM Times
GROUP BY GroupId
SELECT GroupId, N1.ID + 1 StartTime,
charindex(' .',o.line, N1.ID + 1) + 1 EndTime
FROM
Numbers N1, @.line o
WHERE N1.ID < Len (line)
AND SubString(line,N1.ID, 2) = '. '
order by GroupId
Resulted in less than 1 second response time for both SQL 2005 and SQL 2000 for 100,000 rows. I may try to put together a more precise test scenario for the 2005 behaivour.|||Regarding the cross join: No, you must not put t1.rowID = t2.rowID, as that would make it an inner join rather than a cross join; in fact it would compare each row to itself.
You can improve on this query a bit. First of all, you can add the opposite condition to the where clause, namely t1.rowID <> t2.rowID, as it isn't necessary to select rows to know that they overlap themselves. More importantly however, don't select just the rowID, as the only information you'll have is of the form
"the rows in this set that overlap are number 1, 2, 3, 4."
which helps you not at all. We need to know at least what row each one overlaps with to be any closer to establish the subsets. So you should select t2.rowID as well.
As an optimization, you can also add a condition to eliminate rows that don't add any information because another row covers both the start and end time, ie. if row 1 starts at 0 and ends at 10 there's no point selecting a row which starts at 5 and ends at 8. Thus we've got
select t1.rowID, t2.rowID as overlapRow
into #work
from times t1, times t2
where
t2.startTime BETWEEN t1.startTime AND t1.endTime
AND
t2.endTime NOT BETWEEN t1.startTime AND t1.endTime
AND
t1.rowID <> t2.rowID
We need to add those rows that are subsets by themselves, ie. those that don't overlap with any others;
insert into #work
select rowID, -1 from times where rowID not in (select rowID from #work union select overlapRow from #work)
The temp table #work now contains all the information necessary to establish the subsets. This however must be done iteratively or recursively. Perhaps the easiest is to add a subsetID column to the #work table (just select -1 in the two previous queries using the table) and use this to establish the subsets. I wish I could see immediately how to do it, but I can't. If you can figure it out, you can then finally produce the result with this query
select
min(t1.startTime) startTime,
max(case when t2.endTime > t1.endTime then t2.endTime else t1.endTime end) endTime
from
#work w
left join times t1 on t1.rowID = w.rowID
left join times t2 on t2.rowID = w.overlapRow
group by
subsetID
order by
startTime
I hope this helps!
Dag|||Thanks vaxman! That is pretty damn impressive :) I wish I looked at this over the weekend!! I have not had a chance to read through all of the suggestions but I do want to ask this: When creating the Numbers table why is there a reference to sysobjects? Maybe this requires me to research what sysobjects is/does but would I be able to create the Numbers table as just a temp table??
Thanks!|||vaxman:
gotta question...I am trying the solution with several different ranges and ran into a problem with a range of 0-n
I inserted 0 into the Numbers table and then inserted a 0-50 time range into the times table and after executing the select statement I get no rows...not sure how to fix that one...? I'm trying to tweak the select statement...wondering if you have any ideas?
thanks! :)|||Yeah that is because the string it builds starts at character position 1. You would have to modify it to subtract 1 from a few references to adjust for that. I'll do it later this evening.
The Numbers table was built as a product of just a bunch of random objects from sysobjects in order to generate some numbers. The objects themselves were irrevalent.
Other ways to do it in case the reference to sysobjects that freaks you out:
SELECT TOP 8000 ID = IDENTITY(int, 1, 1)
INTO Numbers
FROM pubs..authors t1, pubs..authors t2, pubs..authors t3
Or
CREATE TABLE Numbers(ID INT NOT NULL PRIMARY KEY)
DECLARE @.Index INT
SET @.Index = 1
WHILE @.Index <= 8000
BEGIN
INSERT Numbers (ID) VALUES (@.Index)
SET @.Index = @.Index + 1
END
Or see http://www.bizdatasolutions.com/tsql/tblnumbers.asp for other examples.
You could create a temporary numbers table but if you are going to do that it might be better to create a view instead:
Create View Numbers AS
select (a0.id + a1.id + a2.id + a3.id) ID FROM
(select 0 id union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) a0,
(select 0 id union select 10 union select 20 union select 30 union select 40 union select 50 union select 60 union select 70 union select 80 union select 90) a1,
(select 0 id union select 100 union select 200 union select 300 union select 400 union select 500 union select 600 union select 700 union select 800 union select 900) a2,
(select 0 id union select 1000 union select 2000) a3
I suggest you just create a permanent table called Numbers though. It comes in handy for many things.|||i call it the integers table, instead of numbers
here's how you generate up to a million numbers
create table integers (i integer);
insert into integers (i) values (0);
insert into integers (i) values (1);
insert into integers (i) values (2);
insert into integers (i) values (3);
insert into integers (i) values (4);
insert into integers (i) values (5);
insert into integers (i) values (6);
insert into integers (i) values (7);
insert into integers (i) values (8);
insert into integers (i) values (9);
select 100000*hk.i
+ 10000*tk.i
+ 1000*k.i
+ 100*h.i
+ 10*t.i
+ u.i as num
from integers hk
cross
join integers tk
cross
join integers k
cross
join integers h
cross
join integers t
cross
join integers u
you should be able to figure out how to cut that down to generate just 1440
sometimes i pre-populate the integers table with numbers 0 through 99 just so that i don't have to do a cross join|||Here is the updated version fixed to work with 0 start times.
SET NOCOUNT ON
CREATE TABLE times66 (rowid int identity, groupid int, starttime int, endtime int)
INSERT times66 ( groupid, starttime, endtime ) VALUES (1,0,40)
INSERT times66 ( groupid, starttime, endtime ) VALUES (1,35,55)
INSERT times66 ( groupid, starttime, endtime ) VALUES (1,40,60)
INSERT times66 ( groupid, starttime, endtime ) VALUES (1,70,90)
INSERT times66 ( groupid, starttime, endtime ) VALUES (1,85,100)
INSERT times66 ( groupid, starttime, endtime ) VALUES (1,150,200)
INSERT times66 ( groupid, starttime, endtime ) values (2,10,30)
INSERT times66 ( groupid, starttime, endtime ) values (2,30,60)
INSERT times66 ( groupid, starttime, endtime ) values (2,0,70)
INSERT times66 ( groupid, starttime, endtime ) values (2,70,90)
INSERT times66 ( groupid, starttime, endtime ) values (3,100,150)
INSERT times66 ( groupid, starttime, endtime ) values (3,1,200)
INSERT times66 ( groupid, starttime, endtime ) values (3,250,300)
GO
-- Return a formated string representing time blocks.
CREATE FUNCTION f_GetTimes66(@.GroupID Int)
RETURNS VARCHAR(1500)
AS
BEGIN
DECLARE @.line VARCHAR(1500)
SELECT @.line = stuff(Coalesce(@.line,replicate('.',1500)),
starttime + 2, endtime - starttime ,replicate(' ',endtime - starttime))
FROM times66
WHERE GroupID = @.GroupID
RETURN @.line
END
GO
-- Generate 106496 rows of test data.
While Coalesce(@.@.Rowcount,0) < 30000
INSERT times66 ( groupid, starttime, endtime ) select groupid, starttime, endtime from times66
Declare @.Line Table (GroupID int, line Varchar(1500))
Insert Into @.line
SELECT GroupId , dbo.f_GetTimes66(GroupID) line
FROM times66
GROUP BY GroupId
SELECT GroupId, N1.ID - 1 StartTime, charindex(' .',o.line, N1.ID ) - 1 EndTime
FROM
Numbers N1, @.line o
WHERE N1.ID < Len (line)
AND SubString(line,N1.ID, 2) = '. '
ORDER BY GroupId
Drop Table times66
Drop FUNCTION f_Gettimes66
--To make a Numbers table if you don't have one.
/*
CREATE TABLE Numbers(ID INT NOT NULL PRIMARY KEY)
DECLARE @.Index INT
SET @.Index = 1
WHILE @.Index <= 8000
BEGIN
INSERT Numbers (ID) VALUES (@.Index)
SET @.Index = @.Index + 1
END
*/
GroupId StartTime EndTime
---- ---- ----
1 0 60
1 70 100
1 150 200
2 0 90
3 1 200
3 250 300|||Ooh! Ooh! Can I play?
Select StartTimeList.StartTime, Min(EndTimeList.EndTime) EndTime
from
(select A.StartTime
from YourTable A
left outer join YourTable B on A.StartTime between B.StartTime and B.EndTime
where B.StartTime is null) StartTimeList,
(select A.EndTime
from YourTable A left outer join YourTable B on A.EndTime between B.StartTime and B.EndTime
where B.StartTime is null) EndTimeList
where EndTimeList.EndTime > StartTimeList.StartTime|||blindman, that's even more gorgeous than all the other solutions so far combined
see, i knew it -- there's almost always a set-based solution to any problem that you think will yield only to programming/cursors/looping
nice job|||Thanks Rudy!
I've been up to my butt in Oracle crap for the last two weeks, but I gotta jump on the forums here every once in a while just to keep from getting rusty.|||blindman that is very cool no doubt! I can no doubt relate cuz i gotta do this in oracle too :)
thanks for the help!! :D|||Converting to PL/SQL is an easy two-step process:
Step 1: Look for all possible places in your code where a cursor should be completely unnecessary, and then add cursors there.
Stop 2: Rewrite your application requirements so that no actual output is required.
...and now you are done.|||hmmm...I'm getting an interesting error with the select statement by blindman..
I tried this with a temp table that I have with stored start and end times:
changed YourTable --> #final
Select StartTimeList.StartTime, Min(EndTimeList.EndTime) EndTime
from
(select A.StartTime
from #final A
left outer join #final B on A.StartTime between B.StartTime and B.EndTime
where B.StartTime is null) StartTimeList,
(select A.EndTime
from #final A left outer join #final B on A.EndTime between B.StartTime and B.EndTime
where B.StartTime is null) EndTimeList
where EndTimeList.EndTime > StartTimeList.StartTime
ERROR:
Server: Msg 8118, Level 16, State 1, Line 3
Column 'StartTimeList.StartTime' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
? any ideas as to why I'm getting this...would this not work with temp tables?? Thanks :)|||The use of Min() implies aggregation, which means that any columns referenced "bare" (without an aggregate function) need to be included in the GROUP BY clause.
-PatP|||...so you need to add a GROUP BY clause at the end:
Select C, Min(EndTimeList.EndTime) EndTime
from
(select A.StartTime
from #final A
left outer join #final B on A.StartTime between B.StartTime and B.EndTime
where B.StartTime is null) StartTimeList,
(select A.EndTime
from #final A left outer join #final B on A.EndTime between B.StartTime and B.EndTime
where B.StartTime is null) EndTimeList
where EndTimeList.EndTime > StartTimeList.StartTime
group by Select StartTimeList.StartTime|||I have no clue how i screwed this up but I have a question:
Is this how its supposed to look (without the Select C?)
Select StartTimeList.StartTime, Min(EndTimeList.EndTime) EndTime
from
(select A.StartTime
from #final A
left outer join #final B on A.StartTime between B.StartTime and B.EndTime
where B.StartTime is null) StartTimeList,
(select A.EndTime
from #final A left outer join #final B on A.EndTime between B.StartTime and B.EndTime
where B.StartTime is null) EndTimeList
where EndTimeList.EndTime > StartTimeList.StartTime
group by StartTimeList.StartTime
Im trying it out with a table that looks like:
starttime endtime
---- ----
0 20
10 20
15 25
25 60
75 100
150 500
When I run the select statement above...no rows are returned? I dont know if this has to do with the part of the select statement "B.StartTime is null" ??
Thanks for the help!|||What do you get if you run the subqueries independently?
select A.StartTime
from #final A
left outer join #final B on A.StartTime between B.StartTime and B.EndTime
where B.StartTime is null
...should return 0, 75, and 150.
select A.EndTime
from #final A left outer join #final B on A.EndTime between B.StartTime and B.EndTime
where B.StartTime is null
...should return 60, 100, and 500.|||if i run them seperately, each one returns nothing... im thinking because in no range is the end time null??|||No. Its an outer join.
I'll try this out at work tomorrow and give you an answer tomorrow evening.|||Forgot to exclude identical records between the table instances. This works:
set nocount on
create table #Final (RecordID int, StartTime int, EndTime int)
Insert into #Final values (1, 0, 20)
Insert into #Final values (2, 10, 20)
Insert into #Final values (3, 15, 25)
Insert into #Final values (4, 25, 60)
Insert into #Final values (5, 75, 100)
Insert into #Final values (6, 150, 500)
Select StartTimeList.StartTime, Min(EndTimeList.EndTime) EndTime
from
(select A.StartTime
from #final A
left outer join #final B on A.StartTime > B.StartTime and
A.StartTime <= B.EndTime
where B.StartTime is null) StartTimeList,
(select A.EndTime
from #final A
left outer join #final B on A.EndTime >= B.StartTime and A.EndTime
< B.EndTime
where B.StartTime is null) EndTimeList
where EndTimeList.EndTime > StartTimeList.StartTime
group by StartTimeList.StartTime
drop table #Final
---Output--
StartTime EndTime
---- ----
0 60
75 100
150 500|||worked like a charm! THANK YOU SOO MUCH blindman and to everyone that helped me with this issue! i hope to return the favor one day... :D
I have a table:
Rownumber starttime endtime
1 l 30 l 240
2 l 40 l 120
3 l 50 l 260
4 l 1300 l 1400
Rows 1, 2, and 3 over lap with one another and I am trying to obtain the starttime and endtime values which can cover them all.
I would like to find the overlapping (starttime - endtime) ranges and accept the lowest starttime value and the highest endtime value.
Row 1: 30-------240
Row 2: 40-----120
Row 3: 50-------260
Row 4: ...1300---1440
I would like to include starttime-endtime ranges that do not overlap with any other integer range.
which in this case would be:
Rownumber starttime endtime
1 l 30 l 260
2 l 1330 l 1400
I was thinking of using a cursor and comparing each row to all of the other rows in the table and then setting a boolean in that row if it overlaps with another row in the table...is there a better way of doing this?
Thank you for the help!yes, there is a better way
"comparing each row to all of the other rows in the table" is as good a definition of a self-crossjoin as i can think of
a self-crossjoin would be written like this:
select t1.rownumber
, t1.starttime
, t1.endtime
, t2.rownumber
, t2.starttime
, t2.endtime
from yourtable as t1
cross
join yourtable as t2
now, with this as the basis, you can add a WHERE condition for overlapping ranges
e.g. WHERE t1.starttime BETWEEN t2.starttime and t2.endtime
you'd want to be careful about which pairs you want to keep, which would basically be the same idea as your "setting a boolean in that row if it overlaps" except that instead of setting a boolean, the t1 row survives the WHERE filter
make sense?|||What defines an "overlap" for your case? Some overlaps are obvious, since the beginning of one range sits inside of another, but what about more complex cases like:
1 50 250
2 200 450
3 300 650
4 500 800
Is this one range, two ranges, or something different?
-PatP|||I'd think the poster would want this as a result based on Pat's example:
1 50 450
4 500 800
Is my assumption correct?|||I'm just curious, but how the heck did you get there? That was a combination that I never thought of!
-PatP|||rdjabarov: You're close :) but the I am looking for only 50-800 (u can the view diagram below)
PatP: if that question was posed to me, this table is storing a list of times that are blocked off for the a day (i.e. hotel reservations, someone's personal schedule). (BUT Times can overlap in this case). So from the table of overlapping ( or non-overlapping) times (int), I need to find the times where either the entire range or part of the range sit inside another time.
i.e.
1 50 250 --entire range sits inside 2
2 200 450 --entire range sits inside 3
3 300 650 --part of this range (500-650) overlaps with 4
4 500 800 --and accordingly, (500-650) overlaps with 3
This is what I use to visualize it (where --- is a blocked off time):
For Date 07-26-2004:
0 1000
......... 50--------250
......................... 200------450 <sp>
................................300-------650
...................................... ........ 500-----800
so 50-800 would be the time range that would encompass all of the other time ranges since each one overlaps with another...
r937, thank you for your suggestion :)
not even sure how to begin with the where statement to achieve the above
is the select syntax supposed to be?:
select t1.rownumber
, t1.starttime
, t1.endtime
, t2.rownumber
, t2.starttime
, t2.endtime
from yourtable as t1
cross
join yourtable as t2
where t1.Rownumber = t2.rownumber
cause when I do just what you suggested (without the where t1.Rownumber = t2.rownumber)
I get:
Rownumber starttime endtime Rownumber starttime endtime
1 l 30 l 240 1 l 30 l 240
2 l 40 l 120 1 l 30 l 240
3 l 50 l 260 1 l 30 l 240
4 l 1300 l 1400 1 l 30 l 240
1 l 30 l 240 2 l 40 l 120
2 l 40 l 120 2 l 40 l 120
3 l 50 l 260 2 l 40 l 120
4 l 1300 l 1400 2 l 40 l 120
1 l 30 l 240 3 l 50 l 260
2 l 40 l 120 3 l 50 l 260
3 l 50 l 260 3 l 50 l 260
4 l 1300 l 1400 3 l 50 l 260
1 l 30 l 240 4 l 1300 l 1400
2 l 40 l 120 4 l 1300 l 1400
3 l 50 l 260 4 l 1300 l 1400
4 l 1300 l 1400 4 l 1300 l 1400|||Hmmmm...Any previous maximum endtime should be less than any consecutive minimum starttime...I think...Isn't it? But now that I am looking at it, if that's the logic then the only result you can get from Pat's example would be:
1 50 800|||Hey, you beat me to that, that's what I thought. Now the question is how to take into account ranges that DON'T overlap, because if you just do MIN(starttime) and MAX(endtime) it would skip non-overlapping ranges.|||awfdml, the first thing you should do is restate your requirements
a precise statement of requirements is crucial
for example, your first post said "I am trying to obtain the starttime and endtime values which can cover them all"
which can cover them all suggests that MIN() and MAX() is sufficient
but there will be holes, right?
and if you somehow want to see the holes...?
try to give a statement which defines all the start/end pairs you want|||The real problem comes from sequences like:
10-30
30-50
50-70
70-90
90-110
110-130
130-150
etc.
Each trip through the consolidation process brings new consolidations. I don't know of a way to express this using set notation without using iteration, so I think that you'll have to use an iterative (code based) solution.
At least in SQL-2000 you could solve the problem using a table valued function.
-PatP|||Pat: With the scenario you suggested, I would still consider each one overlapping since they share the sametimes
i.e. 10-30 (30 is shared, 50 is shared)...therefore the result would be 10-70
30-50
50-70
Think this would be what you're telling me, Pat?
30-50
40-60
50-70
90-110
110-120
30--50
....40--60
......50---70
......................90----110
...................................110----120
rdjabarov: I think the problem with doing a min and max is in the above scenario, like u stated :)
The result I would need to achieve in this case is:
First 3 times overlap with one another (one set), Last two overlap (second set) (but there is no overlap between)
30-70
90-120
r937: Yes, there will be holes (like above)
I definitely apologize if I did not clarify what I am looking for but I shall try :)
For a given table of blocked times, find all of the ranges(start-end) that overlap (partly, or entirely).
For each set of overlapping ranges, return* the range that will encompass the set.
If a range of the blocked times do not overlap with any another, return the the range.
*return-meaning store into a temp table or return as resultset
I.E.
Scenario 1: All ranges overlap
10-30
30-60
50-70
70-90
Result : 10-90
Scenario 2: Some ranges overlap with others, or not at all
20-40
35-55
40-60
70-90
85-100
150-200
Result: 20-60
70-100
150-200
Scenario 3: None of the times overlap
100-150
180-200
250-300
Result: 100-150
180-200
250-300
I am hoping that I do not have to use an iterative solution. (me in denial :rolleyes: )
The list of blocked times will not be big at all (most it could possibly be is 60 (per minute and NOT realistic)
For my case, the largest list of blocked times I would get could be anywhere from 12-20.
Still open to any suggestions of course as I think away:) Thank you all for your suggestions thus far! :D|||Forgot to ask Pat:
"At least in SQL-2000 you could solve the problem using a table valued function."
I am using SQL 2000, and this probably is a stupid question but what is a "table valued" function? Is it the user defined function?
:confused:
Thanks!|||Check UDF in BOL.|||In SQL-2000, User Defined Functions come in two flavors. Scalar functions return a single value, much like a C function would and are used anywhere an rvalue can be used like the SELECT list the WHERE clause, etc. Table valued functions actually return a table as their value and are used in the FROM clause almost exactly like you'd use a table.
-PatP|||just a dumb question, but what are your units?
your scenarios all use 20, 30, 55, etc.
is 1 the smallest unit here? perhaps 5? these are minutes, right?
because you did mention these were times, and i notice the largest value you're using is 1440 (which should be familiar to anyone who has ever worked with date and time values)|||You are correct r937, the units I am using are minutes and the smallest value is 0 and the largest of course is 1440.
i.e. 0-1440 in a range means entire day is blocked off|||right, well, what i was thinking of was to generate each of the 1440 different minutes, check it against all the ranges, and if no range covers it, then it's "available"
that can be done in a single query
what's next, though, is a bit harder, which is to assemble all the available minutes into "runs"
i.e. if the available minutes are 20, 21, 23, 24, 37, 38, 39
then you want to generate runs of 21-24 and 37-39|||thanks for the idea but I have a question:
would generating each minute be a performance issue? meaning that would I have to have some sort of a loop to check each minute?
I'm trying to watch out for performance since I'm doing this per day and allowing for a search for the available times for a range of dates...|||This is a pretty efficient way to do it. You will need a table of numbers to use it. It creates a string of periods then replaces periods with spaces in character positions within start and end times. Then it searches for occurances of ... ..... ...., that signify the beginning and ending of ranges (just like your number line diagram actually) and displays them.
DECLARE @.line VARCHAR(1500)
SELECT @.line = replicate('.',1500)
SELECT @.line = stuff(@.line,starttime,endtime - starttime,replicate(' ',endtime - starttime))
FROM times
SELECT N1.ID + 1 StartTime,
( SELECT TOP 1 ID + 1
FROM Numbers
WHERE ID > N1.ID
AND ID < len(@.line)
AND SubString(@.line,ID, 2) = ' .'
ORDER BY ID ) EndTime
FROM
Numbers N1
WHERE N1.ID < Len (@.line)
AND SubString(@.line,N1.ID, 2) = '. '
-- Test Data
CREATE TABLE times (rowid int identity, starttime int, endtime int)
INSERT times ( starttime, endtime ) VALUES (20,40)
INSERT times ( starttime, endtime ) VALUES (35,55)
INSERT times ( starttime, endtime ) VALUES (40,60)
INSERT times ( starttime, endtime ) VALUES (70,90)
INSERT times ( starttime, endtime ) VALUES (85,100)
INSERT times ( starttime, endtime ) VALUES (150,200)
StartTime EndTime
---- ----
20 60
70 100
150 200
DELETE FROM times
INSERT times ( starttime, endtime ) values (10,30)
INSERT times ( starttime, endtime ) values (30,60)
INSERT times ( starttime, endtime ) values (50,70)
INSERT times ( starttime, endtime ) values (70,90)
StartTime EndTime
---- ----
10 90
DELETE FROM times
INSERT times ( starttime, endtime ) values (100,150)
INSERT times ( starttime, endtime ) values (180,200)
INSERT times ( starttime, endtime ) values (250,300)
StartTime EndTime
---- ----
100 150
180 200
250 300
DELETE FROM times
INSERT times ( starttime, endtime ) values (180,200)
INSERT times ( starttime, endtime ) values (250,300)
StartTime EndTime
---- ----
180 200
250 300
--To make a Numbers table if you don't have one
SELECT TOP 8000 IDENTITY(INT) AS ID
INTO Numbers
FROM sysobjects s1
CROSS JOIN sysobjects s2|||i am astounded
nice job, vaxman
;) ;) ;)|||High praise indeed. Thanks. I was going to extend it to work for multiple groups in the same dataset. That is a little tricky. Give it a whirl :) I'll put it up if I come up with something elegant.|||I said I would try to figure out a way to do this for multiple groups in the same dataset. This is one way. It's probably not the most efficient way to do it but it's passable.
-- Some test Data
SET NOCOUNT ON
CREATE TABLE times (rowid int identity, groupid int, starttime int, endtime int)
INSERT times ( groupid, starttime, endtime ) VALUES (1,20,40)
INSERT times ( groupid, starttime, endtime ) VALUES (1,35,55)
INSERT times ( groupid, starttime, endtime ) VALUES (1,40,60)
INSERT times ( groupid, starttime, endtime ) VALUES (1,70,90)
INSERT times ( groupid, starttime, endtime ) VALUES (1,85,100)
INSERT times ( groupid, starttime, endtime ) VALUES (1,150,200)
INSERT times ( groupid, starttime, endtime ) values (2,10,30)
INSERT times ( groupid, starttime, endtime ) values (2,30,60)
INSERT times ( groupid, starttime, endtime ) values (2,50,70)
INSERT times ( groupid, starttime, endtime ) values (2,70,90)
INSERT times ( groupid, starttime, endtime ) values (3,100,150)
INSERT times ( groupid, starttime, endtime ) values (3,180,200)
INSERT times ( groupid, starttime, endtime ) values (3,250,300)
-- Return a formated string representing time blocks.
CREATE FUNCTION f_GetLine(@.GroupID Int)
RETURNS VARCHAR(1500)
AS
BEGIN
DECLARE @.line VARCHAR(1500)
SELECT @.line = stuff(Coalesce(@.line,replicate('.',1500)),starttim e,
endtime - starttime,replicate(' ',endtime - starttime))
FROM times
WHERE GroupID = @.GroupID
RETURN @.line
END
-- Generate 106496 rows of test data.
While Coalesce(@.@.Rowcount,0) < 30000
INSERT times ( groupid, starttime, endtime ) select groupid, starttime, endtime from times
-- Results in 16 seconds. 6656 rows/second.
SELECT GroupId, N1.ID + 1 StartTime, (
SELECT TOP 1 ID + 1
FROM Numbers
WHERE ID > N1.ID
AND ID < len(o.line)
AND SubString(o.line,ID, 2) = ' .'
ORDER BY ID ) EndTime
FROM
Numbers N1, (
SELECT GroupId, dbo.f_GetLine(GroupID) line
FROM Times
GROUP BY GroupId
) o
WHERE N1.ID < Len (line)
AND SubString(line,N1.ID, 2) = '. '
Order by GroupID
GroupId StartTime EndTime
---- ---- ----
1 20 60
1 70 100
1 150 200
2 10 90
3 100 150
3 180 200
3 250 300|||I installed SQL 2005 today and I thought I would test this script on it.
Performance was dismal on my Laptop. I realized it was doing a lot of unnecessary work.
Change the query for a single dataset to:
SELECT ID + 1 StartTime, charindex(' .',@.line, ID + 1) + 1 EndTime
FROM Numbers
WHERE ID < Len (@.line)
AND SubString(@.line,ID, 2) = '. '
And change the query for multiple groups in a dataset to:
SELECT GroupId, N1.ID + 1 StartTime, charindex(' .',o.line, N1.ID + 1) + 1 EndTime
FROM
Numbers N1, (
SELECT GroupId, dbo.f_GetLine(GroupID) line
FROM Times
GROUP BY GroupId
) o
WHERE N1.ID < Len (line)
AND SubString(line,N1.ID, 2) = '. '
Order by GroupID
That version will churn through about 20,000 rows per second.|||A final comment about this. SQL 2005 performance was terrible even with the optimization above. It took 8 minutes to return a result. It was due to the ORDER BY. Not sure why, but rewriting it like this:
Declare @.Line Table (GroupID int, line Varchar(1500))
Insert Into @.line
SELECT GroupId , dbo.f_GetLine(GroupID) line
FROM Times
GROUP BY GroupId
SELECT GroupId, N1.ID + 1 StartTime,
charindex(' .',o.line, N1.ID + 1) + 1 EndTime
FROM
Numbers N1, @.line o
WHERE N1.ID < Len (line)
AND SubString(line,N1.ID, 2) = '. '
order by GroupId
Resulted in less than 1 second response time for both SQL 2005 and SQL 2000 for 100,000 rows. I may try to put together a more precise test scenario for the 2005 behaivour.|||Regarding the cross join: No, you must not put t1.rowID = t2.rowID, as that would make it an inner join rather than a cross join; in fact it would compare each row to itself.
You can improve on this query a bit. First of all, you can add the opposite condition to the where clause, namely t1.rowID <> t2.rowID, as it isn't necessary to select rows to know that they overlap themselves. More importantly however, don't select just the rowID, as the only information you'll have is of the form
"the rows in this set that overlap are number 1, 2, 3, 4."
which helps you not at all. We need to know at least what row each one overlaps with to be any closer to establish the subsets. So you should select t2.rowID as well.
As an optimization, you can also add a condition to eliminate rows that don't add any information because another row covers both the start and end time, ie. if row 1 starts at 0 and ends at 10 there's no point selecting a row which starts at 5 and ends at 8. Thus we've got
select t1.rowID, t2.rowID as overlapRow
into #work
from times t1, times t2
where
t2.startTime BETWEEN t1.startTime AND t1.endTime
AND
t2.endTime NOT BETWEEN t1.startTime AND t1.endTime
AND
t1.rowID <> t2.rowID
We need to add those rows that are subsets by themselves, ie. those that don't overlap with any others;
insert into #work
select rowID, -1 from times where rowID not in (select rowID from #work union select overlapRow from #work)
The temp table #work now contains all the information necessary to establish the subsets. This however must be done iteratively or recursively. Perhaps the easiest is to add a subsetID column to the #work table (just select -1 in the two previous queries using the table) and use this to establish the subsets. I wish I could see immediately how to do it, but I can't. If you can figure it out, you can then finally produce the result with this query
select
min(t1.startTime) startTime,
max(case when t2.endTime > t1.endTime then t2.endTime else t1.endTime end) endTime
from
#work w
left join times t1 on t1.rowID = w.rowID
left join times t2 on t2.rowID = w.overlapRow
group by
subsetID
order by
startTime
I hope this helps!
Dag|||Thanks vaxman! That is pretty damn impressive :) I wish I looked at this over the weekend!! I have not had a chance to read through all of the suggestions but I do want to ask this: When creating the Numbers table why is there a reference to sysobjects? Maybe this requires me to research what sysobjects is/does but would I be able to create the Numbers table as just a temp table??
Thanks!|||vaxman:
gotta question...I am trying the solution with several different ranges and ran into a problem with a range of 0-n
I inserted 0 into the Numbers table and then inserted a 0-50 time range into the times table and after executing the select statement I get no rows...not sure how to fix that one...? I'm trying to tweak the select statement...wondering if you have any ideas?
thanks! :)|||Yeah that is because the string it builds starts at character position 1. You would have to modify it to subtract 1 from a few references to adjust for that. I'll do it later this evening.
The Numbers table was built as a product of just a bunch of random objects from sysobjects in order to generate some numbers. The objects themselves were irrevalent.
Other ways to do it in case the reference to sysobjects that freaks you out:
SELECT TOP 8000 ID = IDENTITY(int, 1, 1)
INTO Numbers
FROM pubs..authors t1, pubs..authors t2, pubs..authors t3
Or
CREATE TABLE Numbers(ID INT NOT NULL PRIMARY KEY)
DECLARE @.Index INT
SET @.Index = 1
WHILE @.Index <= 8000
BEGIN
INSERT Numbers (ID) VALUES (@.Index)
SET @.Index = @.Index + 1
END
Or see http://www.bizdatasolutions.com/tsql/tblnumbers.asp for other examples.
You could create a temporary numbers table but if you are going to do that it might be better to create a view instead:
Create View Numbers AS
select (a0.id + a1.id + a2.id + a3.id) ID FROM
(select 0 id union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) a0,
(select 0 id union select 10 union select 20 union select 30 union select 40 union select 50 union select 60 union select 70 union select 80 union select 90) a1,
(select 0 id union select 100 union select 200 union select 300 union select 400 union select 500 union select 600 union select 700 union select 800 union select 900) a2,
(select 0 id union select 1000 union select 2000) a3
I suggest you just create a permanent table called Numbers though. It comes in handy for many things.|||i call it the integers table, instead of numbers
here's how you generate up to a million numbers
create table integers (i integer);
insert into integers (i) values (0);
insert into integers (i) values (1);
insert into integers (i) values (2);
insert into integers (i) values (3);
insert into integers (i) values (4);
insert into integers (i) values (5);
insert into integers (i) values (6);
insert into integers (i) values (7);
insert into integers (i) values (8);
insert into integers (i) values (9);
select 100000*hk.i
+ 10000*tk.i
+ 1000*k.i
+ 100*h.i
+ 10*t.i
+ u.i as num
from integers hk
cross
join integers tk
cross
join integers k
cross
join integers h
cross
join integers t
cross
join integers u
you should be able to figure out how to cut that down to generate just 1440
sometimes i pre-populate the integers table with numbers 0 through 99 just so that i don't have to do a cross join|||Here is the updated version fixed to work with 0 start times.
SET NOCOUNT ON
CREATE TABLE times66 (rowid int identity, groupid int, starttime int, endtime int)
INSERT times66 ( groupid, starttime, endtime ) VALUES (1,0,40)
INSERT times66 ( groupid, starttime, endtime ) VALUES (1,35,55)
INSERT times66 ( groupid, starttime, endtime ) VALUES (1,40,60)
INSERT times66 ( groupid, starttime, endtime ) VALUES (1,70,90)
INSERT times66 ( groupid, starttime, endtime ) VALUES (1,85,100)
INSERT times66 ( groupid, starttime, endtime ) VALUES (1,150,200)
INSERT times66 ( groupid, starttime, endtime ) values (2,10,30)
INSERT times66 ( groupid, starttime, endtime ) values (2,30,60)
INSERT times66 ( groupid, starttime, endtime ) values (2,0,70)
INSERT times66 ( groupid, starttime, endtime ) values (2,70,90)
INSERT times66 ( groupid, starttime, endtime ) values (3,100,150)
INSERT times66 ( groupid, starttime, endtime ) values (3,1,200)
INSERT times66 ( groupid, starttime, endtime ) values (3,250,300)
GO
-- Return a formated string representing time blocks.
CREATE FUNCTION f_GetTimes66(@.GroupID Int)
RETURNS VARCHAR(1500)
AS
BEGIN
DECLARE @.line VARCHAR(1500)
SELECT @.line = stuff(Coalesce(@.line,replicate('.',1500)),
starttime + 2, endtime - starttime ,replicate(' ',endtime - starttime))
FROM times66
WHERE GroupID = @.GroupID
RETURN @.line
END
GO
-- Generate 106496 rows of test data.
While Coalesce(@.@.Rowcount,0) < 30000
INSERT times66 ( groupid, starttime, endtime ) select groupid, starttime, endtime from times66
Declare @.Line Table (GroupID int, line Varchar(1500))
Insert Into @.line
SELECT GroupId , dbo.f_GetTimes66(GroupID) line
FROM times66
GROUP BY GroupId
SELECT GroupId, N1.ID - 1 StartTime, charindex(' .',o.line, N1.ID ) - 1 EndTime
FROM
Numbers N1, @.line o
WHERE N1.ID < Len (line)
AND SubString(line,N1.ID, 2) = '. '
ORDER BY GroupId
Drop Table times66
Drop FUNCTION f_Gettimes66
--To make a Numbers table if you don't have one.
/*
CREATE TABLE Numbers(ID INT NOT NULL PRIMARY KEY)
DECLARE @.Index INT
SET @.Index = 1
WHILE @.Index <= 8000
BEGIN
INSERT Numbers (ID) VALUES (@.Index)
SET @.Index = @.Index + 1
END
*/
GroupId StartTime EndTime
---- ---- ----
1 0 60
1 70 100
1 150 200
2 0 90
3 1 200
3 250 300|||Ooh! Ooh! Can I play?
Select StartTimeList.StartTime, Min(EndTimeList.EndTime) EndTime
from
(select A.StartTime
from YourTable A
left outer join YourTable B on A.StartTime between B.StartTime and B.EndTime
where B.StartTime is null) StartTimeList,
(select A.EndTime
from YourTable A left outer join YourTable B on A.EndTime between B.StartTime and B.EndTime
where B.StartTime is null) EndTimeList
where EndTimeList.EndTime > StartTimeList.StartTime|||blindman, that's even more gorgeous than all the other solutions so far combined
see, i knew it -- there's almost always a set-based solution to any problem that you think will yield only to programming/cursors/looping
nice job|||Thanks Rudy!
I've been up to my butt in Oracle crap for the last two weeks, but I gotta jump on the forums here every once in a while just to keep from getting rusty.|||blindman that is very cool no doubt! I can no doubt relate cuz i gotta do this in oracle too :)
thanks for the help!! :D|||Converting to PL/SQL is an easy two-step process:
Step 1: Look for all possible places in your code where a cursor should be completely unnecessary, and then add cursors there.
Stop 2: Rewrite your application requirements so that no actual output is required.
...and now you are done.|||hmmm...I'm getting an interesting error with the select statement by blindman..
I tried this with a temp table that I have with stored start and end times:
changed YourTable --> #final
Select StartTimeList.StartTime, Min(EndTimeList.EndTime) EndTime
from
(select A.StartTime
from #final A
left outer join #final B on A.StartTime between B.StartTime and B.EndTime
where B.StartTime is null) StartTimeList,
(select A.EndTime
from #final A left outer join #final B on A.EndTime between B.StartTime and B.EndTime
where B.StartTime is null) EndTimeList
where EndTimeList.EndTime > StartTimeList.StartTime
ERROR:
Server: Msg 8118, Level 16, State 1, Line 3
Column 'StartTimeList.StartTime' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
? any ideas as to why I'm getting this...would this not work with temp tables?? Thanks :)|||The use of Min() implies aggregation, which means that any columns referenced "bare" (without an aggregate function) need to be included in the GROUP BY clause.
-PatP|||...so you need to add a GROUP BY clause at the end:
Select C, Min(EndTimeList.EndTime) EndTime
from
(select A.StartTime
from #final A
left outer join #final B on A.StartTime between B.StartTime and B.EndTime
where B.StartTime is null) StartTimeList,
(select A.EndTime
from #final A left outer join #final B on A.EndTime between B.StartTime and B.EndTime
where B.StartTime is null) EndTimeList
where EndTimeList.EndTime > StartTimeList.StartTime
group by Select StartTimeList.StartTime|||I have no clue how i screwed this up but I have a question:
Is this how its supposed to look (without the Select C?)
Select StartTimeList.StartTime, Min(EndTimeList.EndTime) EndTime
from
(select A.StartTime
from #final A
left outer join #final B on A.StartTime between B.StartTime and B.EndTime
where B.StartTime is null) StartTimeList,
(select A.EndTime
from #final A left outer join #final B on A.EndTime between B.StartTime and B.EndTime
where B.StartTime is null) EndTimeList
where EndTimeList.EndTime > StartTimeList.StartTime
group by StartTimeList.StartTime
Im trying it out with a table that looks like:
starttime endtime
---- ----
0 20
10 20
15 25
25 60
75 100
150 500
When I run the select statement above...no rows are returned? I dont know if this has to do with the part of the select statement "B.StartTime is null" ??
Thanks for the help!|||What do you get if you run the subqueries independently?
select A.StartTime
from #final A
left outer join #final B on A.StartTime between B.StartTime and B.EndTime
where B.StartTime is null
...should return 0, 75, and 150.
select A.EndTime
from #final A left outer join #final B on A.EndTime between B.StartTime and B.EndTime
where B.StartTime is null
...should return 60, 100, and 500.|||if i run them seperately, each one returns nothing... im thinking because in no range is the end time null??|||No. Its an outer join.
I'll try this out at work tomorrow and give you an answer tomorrow evening.|||Forgot to exclude identical records between the table instances. This works:
set nocount on
create table #Final (RecordID int, StartTime int, EndTime int)
Insert into #Final values (1, 0, 20)
Insert into #Final values (2, 10, 20)
Insert into #Final values (3, 15, 25)
Insert into #Final values (4, 25, 60)
Insert into #Final values (5, 75, 100)
Insert into #Final values (6, 150, 500)
Select StartTimeList.StartTime, Min(EndTimeList.EndTime) EndTime
from
(select A.StartTime
from #final A
left outer join #final B on A.StartTime > B.StartTime and
A.StartTime <= B.EndTime
where B.StartTime is null) StartTimeList,
(select A.EndTime
from #final A
left outer join #final B on A.EndTime >= B.StartTime and A.EndTime
< B.EndTime
where B.StartTime is null) EndTimeList
where EndTimeList.EndTime > StartTimeList.StartTime
group by StartTimeList.StartTime
drop table #Final
---Output--
StartTime EndTime
---- ----
0 60
75 100
150 500|||worked like a charm! THANK YOU SOO MUCH blindman and to everyone that helped me with this issue! i hope to return the favor one day... :D
Overlapping Indexes
In examining one of our DBs there is a sales table with the following fields:
WDate: SMALLDATETIME PK
StoreID INT PK
ItemID: INT PK
UnitsSold: SMALLINT
TotalSales: REAL
SalesCode: CHAR(1)
There are also other attributes that do not pertain to the question.
The following Indexes are in the Sales table:
PK_Sales: (Clustered) Composit Primary Key
WDate, StoreID, ItemID
IX_Sales_SalesCode: (Non-Clustered)
SalesCode
IX_Sales_Cover: (Non-Clustered)
WDate, StoreID, ItemID, UnitsSold, TotalSales
Typical queries include:
Querying by the 3 field PK
Querying by the 3 field PK + Sales Code and summing UnitsSold and Total Sales
Querying by the 3 field PK and summing UnitsSold and Total Sales
We have several versions of this same DB with different Data in it for different Store Chains. Since we must keep 2 years of history in the DB, the sales table can be quite large. One of our versions of the DB has nearly 1 billion records and the indexing seems to run quite efficiently. In other words no huge amount of time spend querying (Keep in mind there are nearly a billion records.)
My question is:
Do these indexes look correct? Obviously the PK index is fine but are the other 2 indexes ever being hit? In other words, are the 2 indexes other that the PK index worth keeping or are they just taking up disk space and degradding performance?
Thanks in advance for any help on this.
mcatet
You have multiple ways to find out
(1) you can look at the query plan generated for each query and see what indexes are being used
(2) you can use DMV sys.dm_db_index_usage_stats to find out the usage statistics of each of the indexes since the SQL server started. Please refer to BOL for more details. There are other index related DMVs that you may find useful
Without knowing the text of your queries (we need to know what columns are being selected and what predicated are being used) and the selectivity of each predicate, it is hard to know what indexes will be used.
Some observations:
Query-1: If you are using range predicates and the select clause has columns not covered by indexx-3, then this is definitely useful.
Query-3: since it is covered by index-3 keys, it may be useful as it will avoid accessing the datapage.
Overlapping Indexes
In examining one of our DBs there is a sales table with the following fields:
WDate: SMALLDATETIME PK
StoreID INT PK
ItemID: INT PK
UnitsSold: SMALLINT
TotalSales: REAL
SalesCode: CHAR(1)
There are also other attributes that do not pertain to the question.
The following Indexes are in the Sales table:
PK_Sales: (Clustered) Composit Primary Key
WDate, StoreID, ItemID
IX_Sales_SalesCode: (Non-Clustered)
SalesCode
IX_Sales_Cover: (Non-Clustered)
WDate, StoreID, ItemID, UnitsSold, TotalSales
Typical queries include:
Querying by the 3 field PK
Querying by the 3 field PK + Sales Code and summing UnitsSold and Total Sales
Querying by the 3 field PK and summing UnitsSold and Total Sales
We have several versions of this same DB with different Data in it for different Store Chains. Since we must keep 2 years of history in the DB, the sales table can be quite large. One of our versions of the DB has nearly 1 billion records and the indexing seems to run quite efficiently. In other words no huge amount of time spend querying (Keep in mind there are nearly a billion records.)
My question is:
Do these indexes look correct? Obviously the PK index is fine but are the other 2 indexes ever being hit? In other words, are the 2 indexes other that the PK index worth keeping or are they just taking up disk space and degradding performance?
Thanks in advance for any help on this.
mcatet
You have multiple ways to find out
(1) you can look at the query plan generated for each query and see what indexes are being used
(2) you can use DMV sys.dm_db_index_usage_stats to find out the usage statistics of each of the indexes since the SQL server started. Please refer to BOL for more details. There are other index related DMVs that you may find useful
Without knowing the text of your queries (we need to know what columns are being selected and what predicated are being used) and the selectivity of each predicate, it is hard to know what indexes will be used.
Some observations:
Query-1: If you are using range predicates and the select clause has columns not covered by indexx-3, then this is definitely useful.
Query-3: since it is covered by index-3 keys, it may be useful as it will avoid accessing the datapage.
Overlapping images won't position themselves properly on the web
I have noted that if two images with different zIndex overlapping each other
will display as if they were word-wrapped on the web. In fact, as soon as
the web service generates html, this problem occurs.
Is it a known bug or a misuse I am doing?
Thanks!
Marc Lacoursiere
Programmer/Analyst
CGIOverlapping items are only supported by image-based renderers, but not in
HTML.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"news.microsoft.com" <marcdotlacoursiere@.cgi.com> wrote in message
news:e9joNg0yEHA.2016@.TK2MSFTNGP15.phx.gbl...
> I have noted that if two images with different zIndex overlapping each
other
> will display as if they were word-wrapped on the web. In fact, as soon as
> the web service generates html, this problem occurs.
> Is it a known bug or a misuse I am doing?
> Thanks!
> Marc Lacoursiere
> Programmer/Analyst
> CGI
>
will display as if they were word-wrapped on the web. In fact, as soon as
the web service generates html, this problem occurs.
Is it a known bug or a misuse I am doing?
Thanks!
Marc Lacoursiere
Programmer/Analyst
CGIOverlapping items are only supported by image-based renderers, but not in
HTML.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"news.microsoft.com" <marcdotlacoursiere@.cgi.com> wrote in message
news:e9joNg0yEHA.2016@.TK2MSFTNGP15.phx.gbl...
> I have noted that if two images with different zIndex overlapping each
other
> will display as if they were word-wrapped on the web. In fact, as soon as
> the web service generates html, this problem occurs.
> Is it a known bug or a misuse I am doing?
> Thanks!
> Marc Lacoursiere
> Programmer/Analyst
> CGI
>
Subscribe to:
Posts (Atom)