On 16 Jan 2005 16:25:23 -0800, anuu wrote:

(snip)
>[Anu_again]: Hugo, it is foreign key and not primary key. primary key
>is an identity column which I did not incude
>in the structure as I thought that will not make any difference.

Hi Anu,

You're right, knowing that the primary key is an identity column doesn't
help me to help you. But knowing the natural key of your data would have
helped. I assume that you do know the difference between an artificial key
(identity) and a natural key? I also assume that you are aware than even
if you use an identity column as primary key, the natural key should still
be declared (using a UNIQEU constraint)?

If you had posted your table structure and illustrative sample data, as I
requested in my previous message, then I would now be able to see the
PRIMARY KEY constraint on the identity column, as well as the UNIQUE
constraint on whatever combination of columns makes up the natural key for
this table. This is information I really *need* in order to write a query
that returns the rows you need.


(snip)
>>Also, please tell me the expected output if the input looks like this:
>>
>>
>>BU UI SI DI Mo PI
>>
>>
>>-------------------------
>>92, 8, 40, 2, 2, 10057
>>92, 7, 40, 2, 3, 10057
>>92, 7, 40, 3, 5, 10057
>>
>>>From your description above, I guess there should be one extra row, for
>>BU
>>92, PPI 10057, SI 40 and Mo 4 - but what should be the values for UI
>>and
>>DI?
>
>[Anu_again] : should be 92, 7, 40, 3, 4, 10057. You are right

While I still don't know the natural key of your table, your answer
supports my hunch that the natural key is the combination of (business
unit, productid, seasonid, month).
On the other hand, your answer also raises some questions. WHY should the
user id in the extra row be 7 (as in the rows for march and may), not 8
(as in the row for february)? And why should the division in the extra row
be 3 (as in the row for may), not 2 (as in the rows for february and
march)? This part of the specifications is still unclear!


(snip)
>>Please post better sample data (as INSERT statements - see the link I
>>supplied above), indicating all possible situations. The "garbage in,
>>garbage out" principle applies in this group as much as anywhere else!
>
>[Anu_again]: I feel, I did not communicate properly and this caused the
>confusion otherwise you are in
>the right track.

You're right. The proper way to communicate in this group, is to post your
table structure as CREATE TABLE statements, including all constraints and
properties, some illustrative sample data as INSERT statements and the
output expected from that sample data.
You did post a partial table structure in an earlier post, but you didn't
include the constraints. You also posted some sample data, but it was not
illustrative of your problem, so the query I wrote and tested against that
set of sample data will probably not be of much use.

If you still need assistance, I strongly urge you (again!) to read the
information at http://www.aspfaq.com/etiquette.asp?id=5006 and follow
those instructions to post the information and specifications that are
required to get a good working solution to your problem.
Without clear specifications, table structure and good sample data, I
really don't think I can help you.

Best, Hugo
--

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