I have a table named Holding_Value that has several fields in it among
which are UID, fkHolding, EffDate, Units, MarketValue, AssetPrice. UID
is an identity field and fkHolding is a foreign key to a different
table. EffDate is the the effective date while units and marketvalue
are values stored in the table.
what i'm trying to do is get all the values (fkHolding, Effdate, Units,
MarketValue) for all fkHolding for a specific date. That would be
pretty easy if there each unique fkHolding had a corresponding value
for every date. The exception is that if no date is found than you
would have to get the next date less then or equal ot the query date.
To furhter explain assume that there 100 records in the table and there
are only 10 distinct fkHolding values. My result will need to include
only 10 records. Each record will have the values of the row containing
the values less than or equal to the given date for a specific given
date. so if given date (EffDate) is 12/1/2004 and 5 of the 10 distinct
fkHolding have been priced on that date, than we get those values, the
rest 5 rows in the resultset need to be the values of of the latest
date less than the given date.

Now the second problem is that this needs to be efficient because this
is only a part of my subquery and the table does not have 100 records
but a few million records. Now what i can do is get the latest value if
i were given an fkHolding for example i would write

declare @fkHolding as integer
declare @DateValue as datetime
select @fkHolding = 2981
select @DateValue = '9/2/2004'

select Holding_Values.UID, Holding_Values.EffDate,
Holding_Values.fkHolding, Holding_Values.AssetPrice,
Holding_Values.MarketValue
from Holding_Values INNER JOIN
(select max(Holding_Values.effdate) as DatePriced from
Holding_Values INNER JOIN
(select * from Holding_values where fkHolding = @fkHolding and
Holding_Values.EffDate < @DateValue) as a
on a.UID = Holding_values.UID ) as b
on Holding_Values.EffDate = b.DatePriced and Holding_Values.fkHolding =
@fkHolding

or also would write it in the same way taking a different approach:

declare @fkHolding as integer
declare @DateValue as datetime
select @fkHolding = 2981
select @DateValue = '9/2/2004'

select Top 1 Holding_Values.UID, Holding_Values.EffDate,
Holding_Values.fkHolding,
Holding_Values.AssetPrice, Holding_Values.MarketValue from
Holding_Values INNER JOIN
(select * from Holding_values where fkHolding = @fkHolding and
Holding_Values.EffDate < @DateValue) as a
on a.UID = Holding_values.UID
Order by Holding_Values.EffDate desc

Both these queries produce a row each when ran for a specific date and
fkHolding. Now the challege is to get all the latest distinct fkHolding
values given only a date.
Thank you for your time and help.

Gent Metaj

On 15 Dec 2004 12:07:48 -0800, Gent wrote:

(snip)
>what i'm trying to do is get all the values (fkHolding, Effdate, Units,
>MarketValue) for all fkHolding for a specific date. That would be
>pretty easy if there each unique fkHolding had a corresponding value
>for every date. The exception is that if no date is found than you
>would have to get the next date less then or equal ot the query date.
(snip)

Hi Gent,

Since you didn't post CREATE TABLE and INSERT statements to recreate your
situation, I couldn't test it, but something like this should do the
trick:

SELECT h.UID, h.EffDate, h.fkHolding, h.AsseetPrice, h.MarketValue
FROM Holding_Values AS h
WHERE h.EffDate <= @DateValue
AND NOT EXISTS (SELECT *
FROM Holding_Values AS h2
WHERE h2.EffDate <= @DateValue
AND h2.EffDate > h.EffDate)

Another way to do it (test them both to see which one gives you the best
performance):

SELECT h.UID, h.EffDate, h.fkHolding, h.AsseetPrice, h.MarketValue
FROM Holding_Values AS h
INNER JOIN (SELECT fkHolding, MAX(EffDate) AS EffDate
FROM Holding_Values
WHERE EffDate <= @DateValue
GROUP BY fkHolding) AS h2
ON h.fkHolding = h2.fkHolding
AND h.EffDate = h2.EffDate

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

It would have been a lot easier to say if you'd posted some DDL, sample data
and expected results.
http://www.aspfaq.com/etiquette.asp?id=5006

What are the key(s)? Please include them with CREATE TABLE statements. Keys
are going to make a big difference to any query. Without sample data I'm
also unclear if your required result is to include ALL rows for the latest
date for each fkholding or just ONE row for each.

Is this it?

CREATE TABLE Holding_Values (uid INTEGER NOT NULL, effdate DATETIME NOT
NULL, fkholding INTEGER NOT NULL, assetprice INTEGER NOT NULL, marketvalue
INTEGER NOT NULL /* ??? PRIMARY KEY NOT SPECIFIED */)

SELECT H.uid, H.effdate, H.fkholding, H.assetprice, H.marketvalue
FROM Holding_Values AS H,
(SELECT fkholding, MAX(effdate) AS effdate
FROM Holding_Values
WHERE effdate <= @datevalue
GROUP BY fkholding) AS D
WHERE H.fkholding = D.fkholding
AND H.effdate = D.effdate

--
David Portas
SQL Server MVP
--

>> have a table named Holding_Value that has several fields [sic] in
it .. <<

Where is the DDL? And tables have columns which are completely
different from fields

>> .. among which are UID, fkHolding, EffDate, Units, MarketValue,
AssetPrice. UID is an identity field [sic] and fkHolding is a foreign
key to a different
table. EffDate is the the effective date while units and marketvalue
are values stored in the table. <<

INDENTITY is never a key and should not be used. There is no magical
"Univerisal Identifier"; do you also believe that God put a 17-letter
hebrew number to everything in Creation? That is how silly using
IDENTITY for a key in an RDBMS is.

A name element tells us what the entity or attribute is in terms of a
data model. You do not use affixes to tell us HOW it is used in one
occurrence in one table. The name "fkHolding" looks slightly obscene
(sorry, but it looks like "F**kHolding" to me).

"Holding_Values" is an attribute, not an entity name. This is a
hisotry, so use that in the name. If you have an asset_price, where is
the asset? I am guessing that used two names for the same entity, so
the holding is the asset.

Get the ISO-11179 Standards or any book on data modeling.

Again, without DDL and proper keys, here is my wild guess:

SELECT H1.*
FROM HoldingHistory AS H1
INNER JOIN
(SELECT asset_id, MAX(eff_date)
FROM HoldingHistory
WHERE eff_date <= @report_date
GROUP BY asset_id)
AS H2(asset_id, eff_date)
ON H1.asset_id = H2.asset_id
AND H1.eff_date = H2. eff_date;

"--CELKO--" <jcelko212@earthlink.net> wrote in message >
.....
>INDENTITY[sic] is never a key and should not be used. .
.....
I keep hearing this, and to some extent agree, however, I also keep seeing
it in use, and when I'm running a
quick scenario, I use identity to generate a key for small data sets.
Can you expound or give links / ref to articles that go into detail on this.

>The name "fkHolding" looks slightly obscene.....
fk is used as prefix to indicate Foreign Key.

Kevin Ruggles

kevin ruggles wrote:
> "--CELKO--" <jcelko212@earthlink.net> wrote in message >
> ....
>
>>INDENTITY[sic] is never a key and should not be used. .
>
> ....
> I keep hearing this, and to some extent agree, however, I also keep seeing
> it in use, and when I'm running a
> quick scenario, I use identity to generate a key for small data sets.
> Can you expound or give links / ref to articles that go into detail on this.

Do a google search of this group. It is usually discussed, passionately,
every couple months.

Zach

Hugo you got it right. I guess i had a brain fart, i was not grouping
by fkHolding when i was trying to do the query. The first approach is a
drag. I let it run for over 2 minutes with no results (indexes might
have something to do wiht it too) but the second one worked like a
charm. It took less than 1 second for a 2.5 million record table. David
Portas solutions works as well.

In response to CELKO's comment about Identity and Primary key I
remember one of my database professors recommending against a while
back ago, but we seem to use that quite often here at work, and i see
identity used a lot as primary key. My prof did not elaborate too much
on why it was a stupid idea to use an identity as primary key but i
would appreciate if someone else has more info.
And fk is a convention often used to mean Foreign Key.

Thanks,

Gent

> I use identity to generate a key for small data sets.
> Can you expound or give links / ref to articles that go into detail
on this

A search of the web and the microsoft.public.sqlserver.programming
hierarchy will find you many, many articles by Celko and others on this
topic.

> when I'm running a
> quick scenario, I use identity to generate a key for small data sets

What for? IDENTITY is part of a physical implementation not part of the
logical model of your data. If you post a CREATE TABLE statement here,
for example, just with just an IDENTITY column but don't identify any
other key then that tells us nothing about the entities involved and
we'll probably have a much harder time trying to solve your problem. In
modelling and problem-solving scenarios it is usually the natural key
of your data matters. (IDENTITY prompts other problems of its own of
course, but that's where you came in...)

> fk is used as prefix to indicate Foreign Key

Reasonable people differ when it comes to naming conventions. However,
I'll bet if you take a quick poll you'll find that most SQL pros (good
ones anyway) loathe to see prefixes on column and table names. One
reason is that if you represent structure and datatype and other info
in identifiers and then that metadata changes you have to change the
identifier even though the data element itself hasn't changed. Another
reason is that they are harder to type and remember. The standards
document that Joe cited defines some naming conventions for data
elements.

--
David Portas
SQL Server MVP
--

>> one of my database professors recommending against a while back ago,
but we seem to use that quite often here at work, and I see identity
used a lot as primary key. <<

For the technical reasons that have to do with portability, relational
design and data integrity, you can Google my name and IDENTITY to some
of my rants.

The *real* reasons have to do with human behavior. In the working world,
RDBMS systems come from legacy file systems and untrained legacy file
system programmers. They mimic the designs they learned in the old
technology.

There was little separation of logical and physical data. Physically
contigous fields made up *physically contigous* records in files that
were in a sequence on a magnetic tape. The tape was sorted on a key and
all the EDP depended on that sort order to locate a record -- you did
not do random access on a tape.

Then comes the RDBMS, with the concept of relational keys. This meant
you had to know your data model, you had to do research! That's work!
It is so much easier to use some proprietary exposed physical locator
like IDENTITY or a row number to mimic the physical position of record
at the end of a magnetic tape. You can write code with cursors and
completely mimic a 3GL programming language.

You get to SQL and you have to think in sets and in much more complex
logic. It is hard work if you have never had a class in set theory or
formal logic. So people avoid it with IDENTITY and often miss needed
constraints for real key. It also gives them the feeling that the table
is normalized because it has this "false key" and they leave all kinds
of flaws in the schema. You can clean the results in the front end,
just like you did with COBOL in 1968, right?

>> And fk is a convention often used to mean Foreign Key.<<

I know, I know, but that was too good a straight line not to use

Seriously, you name something for *what it is* in the logical data
model, not for *how it is used* in one particular table. Would drop the
FK- prefix if it were used as a non-key column in another table? Do you
use a PK- prefix on it in the the referenced table? Would add "<table
name>-" prefixes for every occurence of the data element? When you sign
a check, do you change your name to include the room you are in at the
time?

The rule is that a data element has one and only one name, one and only
one meaning. This why a data dictionary can work. The only time you
change a data element name is when it occurs in two or more roles in a
query. Thus, "boss_emp_id" and "worker_emp_id" are both "emp_id"
values, but play two different roles.

--CELKO--
Please post DDL in a human-readable format and not a machne-generated
one. This way people do not have to guess what the keys, constraints,
Declarative Referential Integrity, datatypes, etc. in your schema are.
Sample data is also a good idea, along with clear specifications.


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

--CELKO-- (remove.jcelko212@earthlink.net) writes:
> The *real* reasons have to do with human behavior. In the working world,
> RDBMS systems come from legacy file systems and untrained legacy file
> system programmers. They mimic the designs they learned in the old
> technology.
>
> There was little separation of logical and physical data. Physically
> contigous fields made up *physically contigous* records in files that
> were in a sequence on a magnetic tape. The tape was sorted on a key and
> all the EDP depended on that sort order to locate a record -- you did
> not do random access on a tape.

This is complete bullshit. You and few more people may actually have
programmed against tapes, but most of us haven't.

There's no need to involved tapes and other forms of arcane computer
technology to explain why the concept of an artificial key is popular.
Simpler and even older technique is more applicable, technique that is
still in use: pen and paper.

> It is so much easier to use some proprietary exposed physical locator
> like IDENTITY or a row number to mimic the physical position of record

IDENTITY has nothing to do with a physical location. The row may be
send around - the automatically assigned value for the row will be the
same.

You seriously need to learn how modern RDBMS work, Celko. And unlearn
what you happen to know about magtapes.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp