how to compare date periods ?

Hi,
I need to compare 2 periods (start date / end date) in order to find out if the first period overlaps or in included in the second period.
any idea ?
thankswhere [P1].[StartDate] <= [P2].[EndDate]
and [P1].[EndDate] >= [P2].[StartDate]|||blindman, that's only a complete overlap (2nd period completely within the first)

what about partial overlaps

july 15 to august 15 partially overlaps august 1 to august 31|||What about:

select case
when P1.Startdate between P2.startdate and P2.Enddate and P1.Enddate between P2.startdate and P2.Enddate then 'Include'
when P1.Startdate between P2.startdate and P2.Enddate or P1.Enddate between P2.startdate and P2.Enddate then 'OverLaps'
else 'Does not Overlap or is included'
end|||nice, DMWCincy, but you're missing blindman's conditions :)

this topic has been covered before, e.g. http://www.dbforums.com/t1147226.html, but no point looking that up, because this site has lost its archives for old posts (which is one of the reasons i'll be leaving shortly)|||blindman, that's only a complete overlap (2nd period completely within the first)

what about partial overlaps

july 15 to august 15 partially overlaps august 1 to august 31

Actually, blindman's logic is correct and will handle partial overlaps such as the scenario you specified as well as complete overlaps.|||blindman, that's only a complete overlap (2nd period completely within the first)

what about partial overlaps

july 15 to august 15 partially overlaps august 1 to august 31

Since blindman is offline, I'll note that I use the same technique, and it will find partial overlaps, including your sample. Substituting your values into his WHERE clause:

where july 15 <= august 31
and august 15 >= august 1

You can see that your sample record would be returned by his WHERE clause.

Edit: Sorry RogerWilco, your post wasn't there when I started typing.|||Actually, blindman's logic is correct .aaargh, you're right, i read it wrong

sorry :o :o|||OK, now I don't get to post some snooty and tactless comment to Rudy about how my code DOES work, 'cause you guys already took all the fun away.

--Humph!--|||that's okay, i feel sufficiently blindman-chastised already|||I can't believe it was so easy !
thanks blindman|||this topic has been covered before, e.g. http://www.dbforums.com/t1147226.html, but no point looking that up, because this site has lost its archives for old posts (which is one of the reasons i'll be leaving shortly)
Sorry to hear you will be leaving us rudeboy.

But I just can't help myself - don't you keep copies of posts like this?:p|||yes, i do :) :)

and how did you know rudeboy was one of my nicknames?

will i be seeing you on one of the other sites i mentioned in this thread (http://www.dbforums.com/t1198344.html)?|||and how did you know rudeboy was one of my nicknames?
I imagine your friends have the same colossal imagination as I :p


will i be seeing you on one of the other sites i mentioned in this thread (http://www.dbforums.com/t1198344.html)?Ever so kind of you to ask - I must admit Tek Tips regularly comes up on Googles - wish they would get rid of the little drop-down-over-the-text-you-are-reading-just-as-it-gets-interesting box. I might indeed have a look - as you know I have a penchant for ANSII SQL also.

I quite like playing around on this (http://www.experts-exchange.com/)site (another that came up regularly on searches, so I joined). They give you points and from these you are assigned worth. It's ever so self validating. I'm not sure that there are the sort of heavy weights in residence as there are on this forum though.

I see from your link a lot of you are going to be moving. I may not post in this forum often but I visit frequently - I've learnt lots and have been tremendously entertained, expecially when pickings are poor and you are forced to turn on one another ;)

I hope you all do well - drop us a post in the baby db forum (AKA Access) to let us know your new roaming grounds.|||...They give you points and from these you are assigned worth. It's ever so self validating. devshed forums are like that, you build up "rep points" (reputation) which you can then spread around to others

see http://forums.devshed.com/member.php?userid=22396

How to compact & repair a database in SQL Server?

Hi,
I am facing a problem. I have a table with indexes (integers). These
occur out of sequence i.e. not in serial order. Due to this the performance
has decreased. I have tried the following commands but of no help :
1) dbcc checkdb
2) dbcc dbreindex
3) dbcc indexdefrag
Is there any other way to resolve this problem?
Regards,
HarshadHow did you determine that fragmented indexes are your problem? DBCC SHOWCON
TIG? Can you post the
result from DBCC SHOWCONTIG?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Harshad Phadnis" <HarshadPhadnis@.discussions.microsoft.com> wrote in messag
e
news:4845A140-D9BC-4B76-965B-1C29E3121893@.microsoft.com...
> Hi,
> I am facing a problem. I have a table with indexes (integers). These
> occur out of sequence i.e. not in serial order. Due to this the performanc
e
> has decreased. I have tried the following commands but of no help :
> 1) dbcc checkdb
> 2) dbcc dbreindex
> 3) dbcc indexdefrag
> Is there any other way to resolve this problem?
> Regards,
> Harshad|||Are you refering to gaps in the values of an identity column? Those are
completely irrelevant.
Use "dbcc showcontig" to analyze actual data fragmentation.
ML|||What do you mean by "not in serial order"? A table is an unordered set of
data. It has no serial order. Unless you can explain differently I suspect
you just need to use an ORDER BY statement in your queries.
David Portas
SQL Server MVP
--
"Harshad Phadnis" wrote:

> Hi,
> I am facing a problem. I have a table with indexes (integers). Thes
e
> occur out of sequence i.e. not in serial order. Due to this the performanc
e
> has decreased. I have tried the following commands but of no help :
> 1) dbcc checkdb
> 2) dbcc dbreindex
> 3) dbcc indexdefrag
> Is there any other way to resolve this problem?
> Regards,
> Harshad

How to combine 2 records into 1 unique record

Hi all,

We have an app that uses SQL 2000. I am trying to track when a code field
(selcode) is changed on an order which then causes a status field (status)
to change. I tried a trigger but the app may use 2 different update
statements to change these fields depending on what the user does. When the
trigger fires (on update to selcode), the status field has already been
changed. So my trigger to record the changes from inserted and deleted do
not get the true 'before' value of the status field.

The app does use a log table that tracks these changes. The problem I am
having is that 2 records are created, one for the change to selcode and
another for the change to status.

I am looking for help with a script to combine the existence of these 2 log
records into 1 unique record or occurance that I can track.

example:
ordlog: table that logs order changes
ordernr: order number
varname: name of field being changed
old_value: contents of field before change
new_value: contents of field after change
syscreated: date/time of log entry

SELECT ordernr, varname, old_value, new_value, syscreated
FROM ordlog
where varname = 'selcode' and ordernr = '10580'

SELECT ordernr, varname, old_value, new_value, syscreated
FROM ordlog
where varname = 'status' and ordernr = '10580' and old_value = 'A' and
new_value = 'O'

So I need a way to combine these 2 log entries into a unique occurance. The
ordernr and syscreated could be used to link records. syscreated always
appears to be the same for the 2 log entries down to the second. Selcode
can change from NULL to a number of different values or back to NULL.Status
is either 'A' for approved or 'O' for open. An order can have many log
entries during its life. The selcode may be changed several times for the
same order.

Ideally, I would like a result that links 2 log entries and shows the status
changed from 'A' to 'O' when selcode changed.

Thanks for your time.rdraider (rdraider@.sbcglobal.net) writes:

Quote:

Originally Posted by

SELECT ordernr, varname, old_value, new_value, syscreated
FROM ordlog
where varname = 'selcode' and ordernr = '10580'
>
>
SELECT ordernr, varname, old_value, new_value, syscreated
FROM ordlog
where varname = 'status' and ordernr = '10580' and old_value = 'A' and
new_value = 'O'
>
>
So I need a way to combine these 2 log entries into a unique occurance.
The ordernr and syscreated could be used to link records. syscreated
always appears to be the same for the 2 log entries down to the second.
Selcode can change from NULL to a number of different values or back to
NULL.Status is either 'A' for approved or 'O' for open. An order can
have many log entries during its life. The selcode may be changed
several times for the same order.
>
Ideally, I would like a result that links 2 log entries and shows the
status changed from 'A' to 'O' when selcode changed.

Could this do:

SELECT a.ordernr, a.syscreated,
oldselcode = a.old_value, newselcode = a.new_value,
oldstatus = b.old_value, newstatus = b.new_value
FROM ordlog a
JOIN ordlog b ON a.ordernr = b.ordernr
AND datediff(seconds, a.syscreated, b.syscreated) <= 1
WHERE a.varname = 'selcode'
AND b.varname = 'status'
AND coalesce(a.old_value, '') <coalesce(a.new_value, '')
AND a.old_value = 'A'
AND b.new_value = 'B'

Note: this is an untested query.

If the does not return the expected results, I suggest that you post:

o CREATE TABLE statments for the involved table(s).
o INSERT statements with sample data.
o The desired output given the sample.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx