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.

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.

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 )

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?


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)


Best, Hugo
--

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