EM Taskpad Error

In the EM, when selecting a few databases I receive an error dialog offering
to debug.
Error Runtime, Line 306, Unspecified
Canceling shows the taskpad view w/out any info.
Any reasons or clues how to repair?
TIA
Sorry, I found my ans. Right-click on the db name, select |View choose
anything except taskpad, then repeat, choose taskpad this time... all is
much better.
Thanks to...
NetBy TelDBA
Chris Wood
and Arron [SQL Server MVP] provided this link
http://www.aspfaq.com/2293
HTH
"JeffP->" wrote:

> In the EM, when selecting a few databases I receive an error dialog offering
> to debug.
> Error Runtime, Line 306, Unspecified
> Canceling shows the taskpad view w/out any info.
> Any reasons or clues how to repair?
> TIA
>
|||Yup, this bug has been around since day 1 and they never bother to fix it.
That is the only way I know of to fix it for a little while anyway.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"JeffP->" <JeffP@.discussions.microsoft.com> wrote in message
news:AB06D182-E42F-4D2E-BD15-ECDDEF17E42E@.microsoft.com...[vbcol=seagreen]
> Sorry, I found my ans. Right-click on the db name, select |View choose
> anything except taskpad, then repeat, choose taskpad this time... all is
> much better.
> Thanks to...
> NetBy TelDBA
> Chris Wood
> and Arron [SQL Server MVP] provided this link
> http://www.aspfaq.com/2293
> HTH
> "JeffP->" wrote:

EM Taskpad Error

In the EM, when selecting a few databases I receive an error dialog offering
to debug.
Error Runtime, Line 306, Unspecified
Canceling shows the taskpad view w/out any info.
Any reasons or clues how to repair?
TIASorry, I found my ans. Right-click on the db name, select |View choose
anything except taskpad, then repeat, choose taskpad this time... all is
much better.
Thanks to...
NetBy TelDBA
Chris Wood
and Arron [SQL Server MVP] provided this link
http://www.aspfaq.com/2293
HTH
"JeffP->" wrote:
> In the EM, when selecting a few databases I receive an error dialog offering
> to debug.
> Error Runtime, Line 306, Unspecified
> Canceling shows the taskpad view w/out any info.
> Any reasons or clues how to repair?
> TIA
>|||Yup, this bug has been around since day 1 and they never bother to fix it.
That is the only way I know of to fix it for a little while anyway.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"JeffP->" <JeffP@.discussions.microsoft.com> wrote in message
news:AB06D182-E42F-4D2E-BD15-ECDDEF17E42E@.microsoft.com...
> Sorry, I found my ans. Right-click on the db name, select |View choose
> anything except taskpad, then repeat, choose taskpad this time... all is
> much better.
> Thanks to...
> NetBy TelDBA
> Chris Wood
> and Arron [SQL Server MVP] provided this link
> http://www.aspfaq.com/2293
> HTH
> "JeffP->" wrote:
>> In the EM, when selecting a few databases I receive an error dialog
>> offering
>> to debug.
>> Error Runtime, Line 306, Unspecified
>> Canceling shows the taskpad view w/out any info.
>> Any reasons or clues how to repair?
>> TIA
>>

eliminating duplicate records from a table

Hi All

I am having problem in selecting the data from oracle. The problem is there are multiple enteries per customer, I want to select the latest updated value. I know the customer ids which has multiple entries corresssponding to them.

The columns are Cust_ID, Update_Date, Cust_Name,Cust_address.

Select ADDRESS_LINE1, LAST_UPDATE_DATE,ADDRESS_LINE2, ADDRESS_LINE3, CITY, STATE, ZIP,COUNTRY

from Customer_Table s where s.cust_id in (101,102,103,104,105,106)

Group By ADDRESS_LINE1, LAST_UPDATE_DATE,ADDRESS_LINE2, ADDRESS_LINE3, CITY, STATE, ZIP,COUNTRY

having .........(no idea)

I know this is not correct as I am still getting multiple records.

Any help will be useful.

Well, we don't really do oracle around these parts, but...

You should be able to say

count(*) > 1

in the having clause to get groups where there are > 1 rows in there.

|||

I guess that the later means the one with latest LAST_UPDATE. I do not know if this works for "oracle", but it does for SQL Server.

select *

from Customer_Table s

where s.cust_id in (101,102,103,104,105,106)

and last_update = (

select max(a.last_update)

from Customer_Table as a

where a.cust_id = s.cust_id

)

go

AMB

|||

Thanks for replying..I am sorry fro posting oracle query here..

I found the solution.....Thanks a lot...

effects of inserting while selecting

We have a situation where a burst of inserts occurs while a select is being
done. This causes both clients to time out. As a whole does selecting from a
table during the time that inserts are being down slow down the inserts? or
do we have some indexing problems?
thanks
mlblastvm wrote:
> We have a situation where a burst of inserts occurs while a select is being
> done. This causes both clients to time out. As a whole does selecting from a
> table during the time that inserts are being down slow down the inserts? or
> do we have some indexing problems?
> thanks
My money is on an indexing problem... Review the execution plan for
some of your SELECT statements - any scans, particularly table scans,
are likely sources of contention and opportunities for improved indexing.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||the execution plans look fine. One application is performing at least 50,000
inserts in succession. The other application is reading around 22000 rows.
Actually to be more specific here is what happens. Application A reads out
20k records from Table A and then inserts around that many to the database
(different table though). Application B at the same time inserts records into
Table A. Do the inserts from one application slow down the inserts from the
other application because the heads have to move around?
"Tracy McKibben" wrote:

> mlblastvm wrote:
> My money is on an indexing problem... Review the execution plan for
> some of your SELECT statements - any scans, particularly table scans,
> are likely sources of contention and opportunities for improved indexing.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
|||mlblastvm wrote:
> the execution plans look fine. One application is performing at least 50,000
> inserts in succession. The other application is reading around 22000 rows.
> Actually to be more specific here is what happens. Application A reads out
> 20k records from Table A and then inserts around that many to the database
> (different table though). Application B at the same time inserts records into
> Table A. Do the inserts from one application slow down the inserts from the
> other application because the heads have to move around?
>
Well, inserting data into a database certainly does produce disk I/O,
particularly against the transaction log. The rule of thumb is that the
transaction log and database files are place on seperate volumes, with
the transaction log being on an I/O channel that provides the fastest
possible write throughput.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||In our case the logs are on separate volumes. I assume based on what you are
saying that doing a select while inserts are being done will have an impact
on both the select and the insert?
"Tracy McKibben" wrote:

> mlblastvm wrote:
> Well, inserting data into a database certainly does produce disk I/O,
> particularly against the transaction log. The rule of thumb is that the
> transaction log and database files are place on seperate volumes, with
> the transaction log being on an I/O channel that provides the fastest
> possible write throughput.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
|||mlblastvm wrote:
> In our case the logs are on separate volumes. I assume based on what you are
> saying that doing a select while inserts are being done will have an impact
> on both the select and the insert?
>
I was simply responding to your question about the "heads moving around".
An INSERT can block a SELECT if that insert has locked data pages (or
even the entire table) that the SELECT is attempting to read. The
SELECT will be forced to wait until the lock has been released.
Conversely, if an INSERT wants to lock a page (or a table) that a SELECT
is currently reading, the INSERT will potentially have to wait. It all
depends on the lock types being used. Having good indexes in place to
support the SELECT statements will help to alleviate some of this.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||I guess the real question I have is this. In a case where locking is not
occuring is it reasonable to assume that selecting from a database while
doing inserts to it will cause both the select and insert to take longer than
if they were to be done independantly. If that is the case what exactly
accounts for this?
thanks
"Tracy McKibben" wrote:

> mlblastvm wrote:
> I was simply responding to your question about the "heads moving around".
> An INSERT can block a SELECT if that insert has locked data pages (or
> even the entire table) that the SELECT is attempting to read. The
> SELECT will be forced to wait until the lock has been released.
> Conversely, if an INSERT wants to lock a page (or a table) that a SELECT
> is currently reading, the INSERT will potentially have to wait. It all
> depends on the lock types being used. Having good indexes in place to
> support the SELECT statements will help to alleviate some of this.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
|||mlblastvm wrote:
> I guess the real question I have is this. In a case where locking is not
> occuring is it reasonable to assume that selecting from a database while
> doing inserts to it will cause both the select and insert to take longer than
> if they were to be done independantly. If that is the case what exactly
> accounts for this?
> thanks
You will ALWAYS have locking - that locking may or may not cause
contention, which will appear to slow things down.
If things slow down during these large inserts, the only reasons that I
can think of are:
1. Locks being created by the INSERT are blocking other processes
2. The disks are not capable of keeping up with the I/O demands
produced by the influx of new data from the INSERT
3. The new data from the INSERT is requiring the database and/or
transaction log file to grow, resulting in a delay while that growth
takes place.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||I would add:
4) Not enough memory to hold all the pages required for the insert and all
the pages required for the select in memory at once causing extra IO and
processor activity swapping pages in and out.
5) Enough data being transferred in and out to cause network contention on
the TDS connections
6) Becoming processor bound - especially if the select requires sorting of
results
7) Checkpoint interval low enough so inserts are causing frequent
checkpoints saturating the mdf disk drive
In general, if you're running that close to the margins, you probably need
to bump up your timeouts.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45537D29.2060702@.realsqlguy.com...
> mlblastvm wrote:
> You will ALWAYS have locking - that locking may or may not cause
> contention, which will appear to slow things down.
> If things slow down during these large inserts, the only reasons that I
> can think of are:
> 1. Locks being created by the INSERT are blocking other processes
> 2. The disks are not capable of keeping up with the I/O demands produced
> by the influx of new data from the INSERT
> 3. The new data from the INSERT is requiring the database and/or
> transaction log file to grow, resulting in a delay while that growth takes
> place.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

effects of inserting while selecting

We have a situation where a burst of inserts occurs while a select is being
done. This causes both clients to time out. As a whole does selecting from a
table during the time that inserts are being down slow down the inserts? or
do we have some indexing problems?
thanksmlblastvm wrote:
> We have a situation where a burst of inserts occurs while a select is being
> done. This causes both clients to time out. As a whole does selecting from a
> table during the time that inserts are being down slow down the inserts? or
> do we have some indexing problems?
> thanks
My money is on an indexing problem... Review the execution plan for
some of your SELECT statements - any scans, particularly table scans,
are likely sources of contention and opportunities for improved indexing.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||the execution plans look fine. One application is performing at least 50,000
inserts in succession. The other application is reading around 22000 rows.
Actually to be more specific here is what happens. Application A reads out
20k records from Table A and then inserts around that many to the database
(different table though). Application B at the same time inserts records into
Table A. Do the inserts from one application slow down the inserts from the
other application because the heads have to move around?
"Tracy McKibben" wrote:
> mlblastvm wrote:
> > We have a situation where a burst of inserts occurs while a select is being
> > done. This causes both clients to time out. As a whole does selecting from a
> > table during the time that inserts are being down slow down the inserts? or
> > do we have some indexing problems?
> > thanks
> My money is on an indexing problem... Review the execution plan for
> some of your SELECT statements - any scans, particularly table scans,
> are likely sources of contention and opportunities for improved indexing.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||mlblastvm wrote:
> the execution plans look fine. One application is performing at least 50,000
> inserts in succession. The other application is reading around 22000 rows.
> Actually to be more specific here is what happens. Application A reads out
> 20k records from Table A and then inserts around that many to the database
> (different table though). Application B at the same time inserts records into
> Table A. Do the inserts from one application slow down the inserts from the
> other application because the heads have to move around?
>
Well, inserting data into a database certainly does produce disk I/O,
particularly against the transaction log. The rule of thumb is that the
transaction log and database files are place on seperate volumes, with
the transaction log being on an I/O channel that provides the fastest
possible write throughput.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||In our case the logs are on separate volumes. I assume based on what you are
saying that doing a select while inserts are being done will have an impact
on both the select and the insert?
"Tracy McKibben" wrote:
> mlblastvm wrote:
> > the execution plans look fine. One application is performing at least 50,000
> > inserts in succession. The other application is reading around 22000 rows.
> > Actually to be more specific here is what happens. Application A reads out
> > 20k records from Table A and then inserts around that many to the database
> > (different table though). Application B at the same time inserts records into
> > Table A. Do the inserts from one application slow down the inserts from the
> > other application because the heads have to move around?
> >
> Well, inserting data into a database certainly does produce disk I/O,
> particularly against the transaction log. The rule of thumb is that the
> transaction log and database files are place on seperate volumes, with
> the transaction log being on an I/O channel that provides the fastest
> possible write throughput.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||mlblastvm wrote:
> In our case the logs are on separate volumes. I assume based on what you are
> saying that doing a select while inserts are being done will have an impact
> on both the select and the insert?
>
I was simply responding to your question about the "heads moving around".
An INSERT can block a SELECT if that insert has locked data pages (or
even the entire table) that the SELECT is attempting to read. The
SELECT will be forced to wait until the lock has been released.
Conversely, if an INSERT wants to lock a page (or a table) that a SELECT
is currently reading, the INSERT will potentially have to wait. It all
depends on the lock types being used. Having good indexes in place to
support the SELECT statements will help to alleviate some of this.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I guess the real question I have is this. In a case where locking is not
occuring is it reasonable to assume that selecting from a database while
doing inserts to it will cause both the select and insert to take longer than
if they were to be done independantly. If that is the case what exactly
accounts for this?
thanks
"Tracy McKibben" wrote:
> mlblastvm wrote:
> > In our case the logs are on separate volumes. I assume based on what you are
> > saying that doing a select while inserts are being done will have an impact
> > on both the select and the insert?
> >
> I was simply responding to your question about the "heads moving around".
> An INSERT can block a SELECT if that insert has locked data pages (or
> even the entire table) that the SELECT is attempting to read. The
> SELECT will be forced to wait until the lock has been released.
> Conversely, if an INSERT wants to lock a page (or a table) that a SELECT
> is currently reading, the INSERT will potentially have to wait. It all
> depends on the lock types being used. Having good indexes in place to
> support the SELECT statements will help to alleviate some of this.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||mlblastvm wrote:
> I guess the real question I have is this. In a case where locking is not
> occuring is it reasonable to assume that selecting from a database while
> doing inserts to it will cause both the select and insert to take longer than
> if they were to be done independantly. If that is the case what exactly
> accounts for this?
> thanks
You will ALWAYS have locking - that locking may or may not cause
contention, which will appear to slow things down.
If things slow down during these large inserts, the only reasons that I
can think of are:
1. Locks being created by the INSERT are blocking other processes
2. The disks are not capable of keeping up with the I/O demands
produced by the influx of new data from the INSERT
3. The new data from the INSERT is requiring the database and/or
transaction log file to grow, resulting in a delay while that growth
takes place.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I would add:
4) Not enough memory to hold all the pages required for the insert and all
the pages required for the select in memory at once causing extra IO and
processor activity swapping pages in and out.
5) Enough data being transferred in and out to cause network contention on
the TDS connections
6) Becoming processor bound - especially if the select requires sorting of
results
7) Checkpoint interval low enough so inserts are causing frequent
checkpoints saturating the mdf disk drive
In general, if you're running that close to the margins, you probably need
to bump up your timeouts.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45537D29.2060702@.realsqlguy.com...
> mlblastvm wrote:
>> I guess the real question I have is this. In a case where locking is not
>> occuring is it reasonable to assume that selecting from a database while
>> doing inserts to it will cause both the select and insert to take longer
>> than if they were to be done independantly. If that is the case what
>> exactly accounts for this?
>> thanks
> You will ALWAYS have locking - that locking may or may not cause
> contention, which will appear to slow things down.
> If things slow down during these large inserts, the only reasons that I
> can think of are:
> 1. Locks being created by the INSERT are blocking other processes
> 2. The disks are not capable of keeping up with the I/O demands produced
> by the influx of new data from the INSERT
> 3. The new data from the INSERT is requiring the database and/or
> transaction log file to grow, resulting in a delay while that growth takes
> place.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

effects of inserting while selecting

We have a situation where a burst of inserts occurs while a select is being
done. This causes both clients to time out. As a whole does selecting from a
table during the time that inserts are being down slow down the inserts? or
do we have some indexing problems?
thanksmlblastvm wrote:
> We have a situation where a burst of inserts occurs while a select is bein
g
> done. This causes both clients to time out. As a whole does selecting from
a
> table during the time that inserts are being down slow down the inserts? o
r
> do we have some indexing problems?
> thanks
My money is on an indexing problem... Review the execution plan for
some of your SELECT statements - any scans, particularly table scans,
are likely sources of contention and opportunities for improved indexing.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||the execution plans look fine. One application is performing at least 50,000
inserts in succession. The other application is reading around 22000 rows.
Actually to be more specific here is what happens. Application A reads out
20k records from Table A and then inserts around that many to the database
(different table though). Application B at the same time inserts records int
o
Table A. Do the inserts from one application slow down the inserts from the
other application because the heads have to move around?
"Tracy McKibben" wrote:

> mlblastvm wrote:
> My money is on an indexing problem... Review the execution plan for
> some of your SELECT statements - any scans, particularly table scans,
> are likely sources of contention and opportunities for improved indexing.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||mlblastvm wrote:
> the execution plans look fine. One application is performing at least 50,0
00
> inserts in succession. The other application is reading around 22000 rows.
> Actually to be more specific here is what happens. Application A reads out
> 20k records from Table A and then inserts around that many to the databas
e
> (different table though). Application B at the same time inserts records i
nto
> Table A. Do the inserts from one application slow down the inserts from th
e
> other application because the heads have to move around?
>
Well, inserting data into a database certainly does produce disk I/O,
particularly against the transaction log. The rule of thumb is that the
transaction log and database files are place on seperate volumes, with
the transaction log being on an I/O channel that provides the fastest
possible write throughput.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||In our case the logs are on separate volumes. I assume based on what you are
saying that doing a select while inserts are being done will have an impact
on both the select and the insert?
"Tracy McKibben" wrote:

> mlblastvm wrote:
> Well, inserting data into a database certainly does produce disk I/O,
> particularly against the transaction log. The rule of thumb is that the
> transaction log and database files are place on seperate volumes, with
> the transaction log being on an I/O channel that provides the fastest
> possible write throughput.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||mlblastvm wrote:
> In our case the logs are on separate volumes. I assume based on what you a
re
> saying that doing a select while inserts are being done will have an impac
t
> on both the select and the insert?
>
I was simply responding to your question about the "heads moving around".
An INSERT can block a SELECT if that insert has locked data pages (or
even the entire table) that the SELECT is attempting to read. The
SELECT will be forced to wait until the lock has been released.
Conversely, if an INSERT wants to lock a page (or a table) that a SELECT
is currently reading, the INSERT will potentially have to wait. It all
depends on the lock types being used. Having good indexes in place to
support the SELECT statements will help to alleviate some of this.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I guess the real question I have is this. In a case where locking is not
occuring is it reasonable to assume that selecting from a database while
doing inserts to it will cause both the select and insert to take longer tha
n
if they were to be done independantly. If that is the case what exactly
accounts for this?
thanks
"Tracy McKibben" wrote:

> mlblastvm wrote:
> I was simply responding to your question about the "heads moving around".
> An INSERT can block a SELECT if that insert has locked data pages (or
> even the entire table) that the SELECT is attempting to read. The
> SELECT will be forced to wait until the lock has been released.
> Conversely, if an INSERT wants to lock a page (or a table) that a SELECT
> is currently reading, the INSERT will potentially have to wait. It all
> depends on the lock types being used. Having good indexes in place to
> support the SELECT statements will help to alleviate some of this.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||mlblastvm wrote:
> I guess the real question I have is this. In a case where locking is not
> occuring is it reasonable to assume that selecting from a database while
> doing inserts to it will cause both the select and insert to take longer t
han
> if they were to be done independantly. If that is the case what exactly
> accounts for this?
> thanks
You will ALWAYS have locking - that locking may or may not cause
contention, which will appear to slow things down.
If things slow down during these large inserts, the only reasons that I
can think of are:
1. Locks being created by the INSERT are blocking other processes
2. The disks are not capable of keeping up with the I/O demands
produced by the influx of new data from the INSERT
3. The new data from the INSERT is requiring the database and/or
transaction log file to grow, resulting in a delay while that growth
takes place.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I would add:
4) Not enough memory to hold all the pages required for the insert and all
the pages required for the select in memory at once causing extra IO and
processor activity swapping pages in and out.
5) Enough data being transferred in and out to cause network contention on
the TDS connections
6) Becoming processor bound - especially if the select requires sorting of
results
7) Checkpoint interval low enough so inserts are causing frequent
checkpoints saturating the mdf disk drive
In general, if you're running that close to the margins, you probably need
to bump up your timeouts.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45537D29.2060702@.realsqlguy.com...
> mlblastvm wrote:
> You will ALWAYS have locking - that locking may or may not cause
> contention, which will appear to slow things down.
> If things slow down during these large inserts, the only reasons that I
> can think of are:
> 1. Locks being created by the INSERT are blocking other processes
> 2. The disks are not capable of keeping up with the I/O demands produced
> by the influx of new data from the INSERT
> 3. The new data from the INSERT is requiring the database and/or
> transaction log file to grow, resulting in a delay while that growth takes
> place.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Effective Date selection in SQL

Problem: Selecting the most appropriate row based upon the row's effective date (a user defined field of the row) verses the current date and time.

Platform: Microsoft SQL Server 2000

Details:
In a table of products, each record is a product, or a version of a product. These products or versions of products are "Effective Dated", meaning, a row is activated by the date it becomes effective by (do not be confused by an active row, and an active product - active row is the currently effective row, an active product is a flag set by the user as to define whether the product is active when it's effective date is reached). Example:

Product 1 (version 1)
Effective Date: 6/1/2003 12:00:00 AM
Price: $30
Status: Active

Product 1 (version 2)
Effective Date: 7/11/2003 12:00:00 AM
Price: $20
Status: Active

Product 1 (version 3)
Effective Date: 12/25/2003 12:00:00 AM
Price: $15
Status: Active

Product 1 (version 4)
Effective Date: 1/1/2004 12:00:00 AM
Status: Inactive

In the above case, this SINGLE product has four records in the product table representing different versions. Product 1 version 1 has expired since Product 1 version 2 is effective today (7/11). Product 1 version 3 and version 4, however, have not come about yet due to their effective dating in the future. Product versions 1 through 3 are all active, and the product is no longer available after 1/1/2004, when version 4 becomes effective and the status changes to "Inactive".

The problem I'm experiencing with my SQL is that I cannot differentiate between the version effective currently and the future versions.

My current SQL looks as such:

SELECT sProdName
FROM tblProducts
WHERE NOT EXISTS
(
SELECT P.cProdCode, P.dtEffDate
FROM tblProducts AS P
WHERE
tblProducts.cProdCode = P.cProdCode AND
tblProducts.dtEffDate < P.dtEffDate
)
AND bActive = 1
AND cProdCode = 'somecode'
ORDER BY sProdName

In the above SQL, bActive is a flag that declares this product as an active product (verses inactive) and cProdCode is a code that all versions of the Product share. Ex:

iID: 1
sProdName: Product 1
cProdcode: PROD1
bActive: 1
dtEffDate: 6/1/2003 12:00:00 AM

iID: 2
sProdName: Product 1
cProdcode: PROD1
bActive: 1
dtEffDate: 7/11/2003 12:00:00 AM

iID: 3
sProdName: Product 1
cProdCode: PROD1
bActive: 0
dtEffDate: 1/1/2004 12:00:00 AM

The above SQL is effective in ignoring the expired versions of the product, but returns the current and future versions. I've attempted to massage the SQL to ignore the expired and future versions (leaving me with the single, currently active, version).

Ideally, I'd like to do this without having to create flags for the records that deam them as past, present, future, or having to export versions out into an archive table. This should be able to be determined by strictly SQL, but haven't been able to get the data to return as I'd like it..

In the end, the final result would be a single record of the version of the product which is currently effective, and alertnately, the ability to retrieve a set of records listing all of the currently effective active products.

Any ideas?Now, I should mention that I have a version of this SQL that works, but I suppose my ultimate question is "Is this the most efficient and proper way to do this or is there a better way?"

Here is the SQL that does work, but it nags at me with the use of TOP and ORDER BY. It doesn't seem as though this is the proper elegant solution:

SELECT TOP 1 *
FROM tblProducts AS P
WHERE
P.iProdID IN
(
SELECT iProdID
FROM tblProducts P2
WHERE
P2.dtEffDate < GETDATE()
)
AND P.bActive = 1
AND P.cProdCode = 'somecode'
ORDER BY P.dtEffDAte DESC

If I do not use TOP, it returns every version of a product except the future versions. If I do not use ORDER BY it returns them in an order inconsistant with their effective date. So combined together, the records are sorted most current->least current, then chopped at the top for the most current effective dated product.

Now, this comes into play heavily when we start talking about retrieving the most recent version of all of the products. Sure the above works when getting the most current version of a single product, but (due to the "TOP") does not work properly when attempting to retrieve the most current version of all active products. The above SQL's "TOP" directive is in there to drop off the expired version of the product, and in this case, TOP drops off more then just expired versions of products.. it drops everything except the lucky record at the top of the set.|||i really don't understand your various definitions of active, inactive, flags, effective dates, etc.

your design sounds fairly complicated

however, i did pick up on one thing

you said your sql works, but only for one product at a time, and "does not work properly when attempting to retrieve the most current version of all active products"

okay, i think i can translate your sql so it will work for all products:
select *
from tblProducts AS P
where dtEffDate =
( select max(dtEffDate)
from tblProducts
where cProdCode = P.cProdCode
and dtEffDate < GETDATE()
and bActive = 1
)
and bActive = 1 this is a correlated subquery so it picks the highest date in each group, where the group is defined as all product rows with the same cProdCode (the P inside the subquery is the correlation variable)

let me know if it works

rudy
http://r937.com/|||Hi Rudy,

I apologize for the complexity of the project, but I think you nailed the solution. I've done some cursory testing, and just looking over the SQL, it looks right on. Tonight I'll take some time to do some more testing, but it looks good. I hadn't even thought of using the MAX function. Thanks for your assistance, I appreciate you taking the time to go through all my details (even if they were a bit long and confusing) and coming up with a solid solution.|||It looks good.. I've done some more thorough testing and it seems to work well. Thanks again!|||It looks good.. I've done some more thorough testing and it seems to work well. Thanks again!