Hugo Kornelis wrote:
> On 13 Jan 2005 18:06:42 -0800, anuu wrote:
>
> >Thanks all for the info.
> >
> >Here are the details
> (snip)
>
> Hi Anu,
>
> Thanks. Unfortunately, therre still are some questions to ask.
>
[Anu]: No issues, Hugo. Ready to answer the questions. Find below
embedded

> 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


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


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

>
> Here's some code that will produce the requested output from your
sample
> data, but relies very heavy on several assumptions. If my assumptions
are
> wrong, the code will produce incorrect results. I didn't try to
optimize
> it, as this is probably (hopefully!) a one-time operation.
>
> SELECT f.BusinessUnitId, f.UserId, f.SeasonId,
> s.SMonth, f.DivisionId, f.ProductId
> FROM FCast AS f
> INNER JOIN SMonths AS s
> ON s.SeasonId = f.SeasonId
> WHERE f.FMonth = (SELECT MIN(s2.SMonth)
> FROM SMonths AS s2
> WHERE s2.SeasonId = f.SeasonId)
> AND NOT EXISTS (SELECT *
> FROM FCast AS f2
> WHERE f2.BusinessUnitId = f.BusinessUnitId
> AND f2.FMonth = s.SMonth)
>
[Anu]: Thanks, Hugo. I would start working on this and see if I could
accomplish. Meanwhile, let me know if you need more info.....

>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)