Hi,

I have 2 tables as follows:

CREATE TABLE [PlannedSales] (
[PlanYear] [smallint] ,
[PlanMonth] [smallint] ,
[PlanProdCode] [nvarchar] (20) ,
[PlanQty] [numeric](28, 8) ,
[PlanAmt] [money]
) ON [PRIMARY]
GO


CREATE TABLE [ActualSales] (
[SaleYear] [smallint] ,
[SaleMonth] [smallint] ,
[SoldProdCode] [nvarchar] (20) ,
[SaleQty] [numeric](28, 8) ,
[SaleAmt] [money]
) ON [PRIMARY]
GO


So basically I have Year,Month,and Product wise totals in the above
tables (one table has the Plan while the 2nd table has the Actuals).


Now I created a View and linked the Year,Month, and ProdCode fields
between 2 tables but as a end result am getting incorrect number of
rows. One logical reason could be that a Product might Not be planned
(for a given Year or Month combination)but might have been sold in that
month. Similarly it is possible that for a given Year and Month
combination, a Product might have been planned but might not have been
sold for the same year and month combination...

I tried also tried the option to return all rows from both sides (which
gives FULL OUTER JOIN). I am sure that I am on the wrong track and will
appreciate your help and guidance.

Many TIA.

Looks like FULL JOIN is the way to go. Try this:

SELECT
COALESCE(P.planyear,A.saleyear),
COALESCE(P.planmonth,A.salemonth),
COALESCE(P.planprodcode,A.soldprodcode),
P.planqty, P.planamt, A.saleqty, A.saleamt
FROM PlannedSales AS P
FULL JOIN ActualSales AS A
ON P.planyear = A.saleyear
AND P.planmonth = A.salemonth
AND P.planprodcode = A.soldprodcode

If that's not what you want then it would help if you could post a few rows
of example data and show us your required result.

--
David Portas
SQL Server MVP
--

Hi

Thanks for the DDL it is also useful to post example data as Insert
statements e.g

CREATE TABLE [PlannedSales] (
[PlanYear] [smallint] ,
[PlanMonth] [smallint] ,
[PlanProdCode] [nvarchar] (20) ,
[PlanQty] [numeric](28, 8) ,
[PlanAmt] [money]
) ON [PRIMARY]
GO


CREATE TABLE [ActualSales] (
[SaleYear] [smallint] ,
[SaleMonth] [smallint] ,
[SoldProdCode] [nvarchar] (20) ,
[SaleQty] [numeric](28, 8) ,
[SaleAmt] [money]
) ON [PRIMARY]
GO

INSERT INTO [PlannedSales] (
[PlanYear], [PlanMonth], [PlanProdCode], [PlanQty], [PlanAmt])
VALUES ( 2004, 1, 'Product A', 1, 100 )
GO

INSERT INTO [PlannedSales] (
[PlanYear], [PlanMonth], [PlanProdCode], [PlanQty], [PlanAmt])
VALUES ( 2004, 2, 'Product A', 2, 200 )
GO


INSERT INTO [PlannedSales] (
[PlanYear], [PlanMonth], [PlanProdCode], [PlanQty], [PlanAmt])
VALUES ( 2004, 2, 'Product B', 6, 50 )
GO

INSERT INTO [PlannedSales] (
[PlanYear], [PlanMonth], [PlanProdCode], [PlanQty], [PlanAmt])
VALUES ( 2004, 3, 'Product A', 3, 300 )
GO

INSERT INTO [PlannedSales] (
[PlanYear], [PlanMonth], [PlanProdCode], [PlanQty], [PlanAmt])
VALUES ( 2004, 3, 'Product B', 2, 150 )
GO

CREATE TABLE [ActualSales] (
[SaleYear] [smallint] ,
[SaleMonth] [smallint] ,
[SoldProdCode] [nvarchar] (20) ,
[SaleQty] [numeric](28, 8) ,
[SaleAmt] [money]
) ON [PRIMARY]
GO

INSERT INTO [ActualSales] (
[SaleYear], [SaleMonth], [SoldProdCode] , [SaleQty], [SaleAmt] )
VALUES ( 2004, 1, 'Product A', 2, 200 )
GO

INSERT INTO [ActualSales] (
[SaleYear], [SaleMonth], [SoldProdCode] , [SaleQty], [SaleAmt] )
VALUES ( 2004, 3, 'Product A', 3, 300 )
GO


INSERT INTO [ActualSales] (
[SaleYear], [SaleMonth], [SoldProdCode] , [SaleQty], [SaleAmt] )
VALUES ( 2004, 2, 'Product B', 8, 100 )
GO

INSERT INTO [ActualSales] (
[SaleYear], [SaleMonth], [SoldProdCode] , [SaleQty], [SaleAmt] )
VALUES ( 2004, 4, 'Product A', 4, 800 )
GO

INSERT INTO [ActualSales] (
[SaleYear], [SaleMonth], [SoldProdCode] , [SaleQty], [SaleAmt] )
VALUES ( 2004, 3, 'Product B', 2, 150 )
GO

SELECT * FROM [PlannedSales]
SELECT * FROM [ActualSales]

Although there are 5 rows in each table 4 match on Year, Month and Product
code therefore with a query like:

SELECT
ISNULL(P.PlanYear,A.[SaleYear]) AS Year,
ISNULL(P.PlanMonth,A.[SaleMonth]) AS Month,
ISNULL(P.PlanProdCode,A.[SoldProdCode]) AS ProductCode,
ISNULL(P.[PlanQty],0) AS PlanQty,
ISNULL(P.[PlanAmt],0) AS PlanAmt,
ISNULL(A.[SaleQty],0) AS SaleQty,
ISNULL(A.[SaleAmt],0) AS SaleAmt
FROM [PlannedSales] p
FULL OUTER JOIN [ActualSales] a ON
A.[SaleYear] = P.[PlanYear]
AND A.[SaleMonth] = P.[PlanMonth]
AND A.[SoldProdCode] = P.[PlanProdCode]
ORDER BY
ISNULL(P.PlanYear,A.[SaleYear]),
ISNULL(P.PlanMonth,A.[SaleMonth]),
ISNULL(P.PlanProdCode,A.[SoldProdCode])

Returns 6 rows.

Year Month ProductCode PlanQty PlanAmt
SaleQty SaleAmt
------ ------ -------------------- ------------------------------ ----------
----------- ------------------------------ ---------------------
2004 1 Product A 1.00000000 100.0000
2.00000000 200.0000
2004 2 Product A 2.00000000 200.0000
..00000000 .0000
2004 2 Product B 6.00000000 50.0000
8.00000000 100.0000
2004 3 Product A 3.00000000 300.0000
3.00000000 300.0000
2004 3 Product B 2.00000000 150.0000
2.00000000 150.0000
2004 4 Product A .00000000 .0000
4.00000000 800.0000

(6 row(s) affected)

John


"Learner" <wantnospam (AT) email (DOT) com> wrote

Hi David and John,

Thanks for the help. Will give it a try and if still problems then I
will post the insert statements too.

Till then a question to David:


IS " a reserved word/function??? This is the first time I'm coming
across this

--
Thanks.

I am not sure what you mean by this... I guess you have a typo? The queries
David and I posted are essentially the same...


John

Hi John,

It was a typo. Sorry.


My re-phrased question:
Is 'COALESCE' a reserved word? Have never come across this before.

Thanks.

Hi David and John,

Many thanks...got it to work and getting the results. However I have to
say that this was one nasty join that I ran into


Many thanks for your help.

Regards.

Yes. COALESCE is a Standard SQL function. It returns the first non-NULL
value among its arguments.

John used ISNULL in his query. ISNULL is a Microsoft proprietary function
which when used with two arguments of the same datatype gives the same
result as COALESCE.

--
David Portas
SQL Server MVP
--