On 26 Mar 2004 07:24:08 -0800, Maryam wrote:

>Hi,
>
>I have got a huge sproc which contains queries that create various
>temporary tables.
>Scenario - In simplistic terms - I end up with 4 temporary tables
>containing data that looks like: (The data comes from different sql
>tables)
>
>Month Sales A
>Jan 2004 20
>Feb 2004 30
>Mar 2004 50
>
>Month Sales B
>Jan 2004 20
>Feb 2004 30
>April 2004 15
>
>Month Sales C
>Feb 2004 30
>Jun 2004 5
>
>Month Sales D
>Jan 2004 20
>Feb 2004 30
>
>I want the final table to ultimately look like this including zero
>entries.
>
>Month Sales A Sales B Sales C Sales D
>Jan 2004 20 20 0 20
>Feb 2004 30 30 30 30
>Mar 2004 40 0 0 0
>Apr 2004 0 15 0 0
>Jun 2004 0 0 5 0
>
>I can only use a sql 2000 stored procedure get the data to look like
>this.
>
>Can any one help me.
>
>Any help/guidance will be much appreciated.
>
>Thanks
>Maryam

> The following should do the trick:
>
> SELECT A.Month, A.Sales AS "Sales A", B.Sales AS "Sales B",
> C.Sales AS "Sales C", D.Sales AS "Sales D"
> FROM SalesA AS A
> FULL OUTER JOIN SalesB AS B
> ON B.Month = A.Month
> FULL OUTER JOIN SalesC AS C
> ON C.Month = A.Month
> FULL OUTER JOIN SalesD AS D
> ON D.Month = A.Month
>
> (untested)

On Sat, 27 Mar 2004 23:46:44 +0000 (UTC), Erland Sommarskog wrote:

>Had you tested it, you would have found that it does not do the trick.

Erland Sommarskog <sommar@algonet.se> wrote in message news:<Xns94BA7A687715Yazorman@127.0.0.1>...
> Hugo Kornelis (hugo@pe_NO_rFact.in_SPAM_fo) writes:
> > The following should do the trick:
> >
> > SELECT A.Month, A.Sales AS "Sales A", B.Sales AS "Sales B",
> > C.Sales AS "Sales C", D.Sales AS "Sales D"
> > FROM SalesA AS A
> > FULL OUTER JOIN SalesB AS B
> > ON B.Month = A.Month
> > FULL OUTER JOIN SalesC AS C
> > ON C.Month = A.Month
> > FULL OUTER JOIN SalesD AS D
> > ON D.Month = A.Month
> >
> > (untested)
>
> Had you tested it, you would have found that it does not do the trick. The
> problem you have is you have singled out A as a base table, but all tables
> are equal. If a month is present both in B and D, but on in A, you will
> get two rows for this month in the output. (See the repro below.)
>
> This can be handled in several ways. This query is simple, you can just
> use a GROUP BY and SUM. And you can evade full joins altogether by using
> UNION and SUM instead. But for a more general case you need to full-join
> two tables at a time, and full-join that result with the next guys.
>
> Below is a repro that fills some demo tables, and then follows three
> queries. The first is a variation on Hugo's, and you will see that
> the result is not the desired one. The follows two queries that does
> this properly.
>
>
>
> CREATE TABLE a_sales (month char(4) NOT NULL PRIMARY KEY,
> sales int NOT NULL)
> go
> CREATE TABLE b_sales (month char(4) NOT NULL PRIMARY KEY,
> sales int NOT NULL)
> go
> CREATE TABLE c_sales (month char(4) NOT NULL PRIMARY KEY,
> sales int NOT NULL)
> go
> CREATE TABLE d_sales (month char(4) NOT NULL PRIMARY KEY,
> sales int NOT NULL)
> go
> INSERT a_sales (month, sales) VALUES('0401', 10)
> INSERT a_sales (month, sales) VALUES('0402', 30)
> INSERT a_sales (month, sales) VALUES('0403', 40)
> go
> INSERT b_sales (month, sales) VALUES('0401', 10)
> INSERT b_sales (month, sales) VALUES('0402', 30)
> INSERT b_sales (month, sales) VALUES('0404', 56)
> go
> INSERT c_sales (month, sales) VALUES('0406', 11)
> INSERT c_sales (month, sales) VALUES('0407', 70)
> INSERT c_sales (month, sales) VALUES('0408', 57)
> go
> INSERT d_sales (month, sales) VALUES('0401', 15)
> INSERT d_sales (month, sales) VALUES('0404', 230)
> INSERT d_sales (month, sales) VALUES('0408', 140)
> go
> -- This query give two rows for April and August.
> SELECT month = coalesce(a.month, b.month, c.month, d.month),
> "Sales A" = coalesce(a.sales, 0),
> "Sales B" = coalesce(b.sales, 0),
> "Sales C" = coalesce(c.sales, 0),
> "Sales D" = coalesce(d.sales, 0)
> FROM a_sales a
> FULL JOIN b_sales b ON a.month = b.month
> FULL JOIN c_sales c ON a.month = c.month
> FULL JOIN d_sales d ON a.month = d.month
> ORDER BY 1
> go
> -- This is how must do it.
> SELECT month = coalesce(ab.month, cd.month),
> "Sales A" = coalesce(ab.a_sales, 0),
> "Sales B" = coalesce(ab.b_sales, 0),
> "Sales C" = coalesce(cd.c_sales, 0),
> "Sales D" = coalesce(cd.d_sales, 0)
> FROM (SELECT month = coalesce(a.month, b.month),
> a_sales = a.sales, b_sales = b.sales
> FROM a_sales a
> FULL JOIN b_sales b ON a.month = b.month) AS ab
> FULL JOIN (SELECT month = coalesce(c.month, d.month),
> c_sales = c.sales, d_sales = d.sales
> FROM c_sales c
> FULL JOIN d_sales d ON c.month = d.month) AS cd
> ON ab.month = cd.month
> ORDER BY 1
> go
> -- This may be simpler.
> SELECT month, "Sales A" = SUM(a_sales), "Sales B" = SUM(b_sales),
> "Sales C" = SUM(c_sales), "Sales D" = SUM(d_sales)
> FROM (SELECT month, a_sales = sales, b_sales = 0,
> c_sales = 0, d_sales = 0
> FROM a_sales
> UNION ALL
> SELECT month, a_sales = 0, b_sales = sales,
> c_sales = 0, d_sales = 0
> FROM b_sales
> UNION ALL
> SELECT month, a_sales = 0, b_sales = 0,
> c_sales = sales, d_sales = 0
> FROM c_sales
> UNION ALL
> SELECT month, a_sales = 0, b_sales = 0,
> c_sales = 0, d_sales = sales
> FROM d_sales) AS x
> GROUP BY month
> ORDER BY month
> go