On 14 Jan 2005 17:10:05 -0800, anuu wrote:

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!


>> 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?

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.

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?


>> 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?
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]: 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)