Hi Anu,


(snip)



>> 1. What are the keys for your tables? For SMonths, either SMonth or
>> (SeasonId, SMonth) are logical possibilities. For FCast, I can't
even
>> begin to guess.

>[Anu]: For SMonths (SeasonId, SMonth) and for FCast (SeasonId, FMonth)
>which relates to SMonths



Huh? The sample data you posted in your original post in this thread
violates the key you state for FCast - it has two rows for SeasonId 40,
FMonth 2, which would not be possible with the key you state above!

[Anu_Again] Hugo, the key mentioned is Foreign keys and not Primary.

- Hide quoted text -
- Show quoted text -

>> 2. In your example, the input for business unit 125 consists of one
>row;
>> the output has three rows, with the "missing" months and the
>remaining
>> columns taken from the one row that is present. Fine. For business
>unit
>> 92, the situation gets muddy: you start withh three rows and want to
>> create one extra row for the "missing" month, again with the
remainig
>> columns taken from the rows already presen. But which one? In your
>> example, the three rows for BU 92 all have user 8, division 4 and
>product
>> 10057. What would be the expected output if the input changes to

>> INSERT INTO [FCast] VALUES ( 92, 8, 40, 2, 4, 10057 )
>> INSERT INTO [FCast] VALUES ( 92, 7, 40, 3, 3, 10056 )
>> INSERT INTO [FCast] VALUES ( 92, 6, 40, 4, 2, 10055 )
>[Anu]: Fine. If SMonths has these values


>SI Mo SI = SeasonId, Mo = Month


>--------
>40, 2
>40, 3
>40, 4
>40, 5

>then for the above input below would be output


>BU = Business Unit, UI = User Id, SI = Season ID, DI =
>Division ID, Mo = Month, PI = Product ID

>BU UI SI DI Mo PI


>-------------------------
>92, 8, 40, 2, 2, 10057
>92, 8, 40, 2, 3, 10057
>92, 8, 40, 2, 5, 10057

>92, 7, 40, 3, 2, 10056
>92, 7, 40, 3, 4, 10056
>92, 7, 40, 3, 5, 10056


>92, 6, 40, 4, 3, 10055
>92, 6, 40, 4, 4, 10055
>92, 6, 40, 4, 5, 10055


>In short, FCast table would have per ProductId, per BU, all the months
>available for a season.



Again: huh? This data would never be accepted in the table if the
primary
key for FCast is (SeasonID, FMonth), as you state above. So I guess
that's
not the primary key after all.

Also, in a previous post you wrote "


for every month in smonths there would
be a row in fcast for a productid


". Now, you write that you need to have a
row for every month "per ProductId, per BU". Not exactly the same,
right?

[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.
Nope, it is same. all these ProductID, BU are all foreign keys in FCast
table. the primary key
is only an identity column.


I guess I could now make a new guess at the primary key in FCast, then
change the code I posted before to reflect my new guess. But there
would
still be a lot of uncertainty. So instead of wasting time on writing a
new
query on insufficient specs, I'll now refer you to www.aspfaq.com/5006,
where you will find instructions on how to assemble the details you
should
post here to get help, in the best format for this group: SQL.

[Anu_again]: No guesses......


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

>> 3. From your examples, it appears that there always is a row for the
>> "first" month of the season (month 2), but rows for subsequent
months
>> might be missing. Is this a correct assumption or is your example
>> incomplete?

>[Anu]: Nope, the assumption is not correct. For a season, the months
>spread would be defined in SMonths
>table. So, for example, the SeasonId 40 has 12,1,2,3,4 defined then
the
>output for the above input (in point 2) would differ. The available
>rows in FCast would _be_ the ones defined in SMonths.



And if the SeasonId 40 has months 12, 1, 2, 3, and 4, would there than
be
any months that is "complete", such as month 2 was "complete" in your
original sample data?
[Anu_again]: Complete ? The available months in FCast table are
considered as complete and the ones
not are to be INSERTed

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.

>[Anu]: Thanks, Hugo. I would start working on this and see if I could
>accomplish. Meanwhile, let me know if you need more info.....


I don't "need" more info. But if could probably help you better if you
provided more info...

If you need more help, then please provide table structure (as CREATE
TABLE statements, including constrainst but excluding irrelevant
columns),
sample data (as INSERT statements) and expected output. In case you
missed
the link above: see

www.aspfaq.com/5006.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address