[quoted text, click to view]

One method to generate the DDL is using Query Analyzer. Right-click on the
object in the object browser and select 'script object...as CREATE'.

[quoted text, click to view]

You can use a LEFT join to select all products regardless of whether or not
you have purchases or sales. A CROSS JOIN will return all possible
combinations of Product and Dates. Example below.


CREATE TABLE Product
(
ProductCode int NOT NULL
CONSTRAINT PK_Product PRIMARY KEY,
ProductName varchar(30) NOT NULL
)

CREATE TABLE Calendar
(
CalendarDate datetime NOT NULL
CONSTRAINT PK_Dates PRIMARY KEY
)

CREATE TABLE Sales
(
ProductCode int NOT NULL,
SaleDate datetime NOT NULL,
Qty int NOT NULL,
CONSTRAINT PK_Sales PRIMARY KEY
(ProductCode, SaleDate),
CONSTRAINT FK_Sales_Producct FOREIGN KEY
(ProductCode) REFERENCES Product(ProductCode)
)

CREATE TABLE Purchases
(
ProductCode int NOT NULL,
PurchaseDate datetime NOT NULL,
Qty int NOT NULL,
CONSTRAINT PK_Purchases PRIMARY KEY
(ProductCode, PurchaseDate),
CONSTRAINT FK_Purchases_Producct FOREIGN KEY
(ProductCode) REFERENCES Product(ProductCode)
)

SET NOCOUNT ON

INSERT INTO Product VALUES(1,'Product1')
INSERT INTO Product VALUES(2,'Product2')
INSERT INTO Product VALUES(3,'Product3')
INSERT INTO Product VALUES(4,'Product4')
INSERT INTO Product VALUES(5,'Product5')

INSERT INTO Calendar VALUES('20041110')
INSERT INTO Calendar VALUES('20041111')
INSERT INTO Calendar VALUES('20041112')
INSERT INTO Calendar VALUES('20041113')
INSERT INTO Calendar VALUES('20041114')

INSERT INTO Purchases VALUES(1, '20041111', 3)
INSERT INTO Purchases VALUES(2, '20041113', 4)
INSERT INTO Purchases VALUES(3, '20041114', 5)

INSERT INTO Sales VALUES(2, '20041111', 2)
INSERT INTO Sales VALUES(3, '20041113', 1)
INSERT INTO Sales VALUES(4, '20041113', 4)

SELECT
Calendar.CalendarDate,
Product.ProductCode,
Purchases.Qty AS PurchasesQuantity,
Sales.Qty AS SalesQuantity
FROM Product
CROSS JOIN Calendar
LEFT JOIN Purchases ON
Purchases.ProductCode = Product.ProductCode AND
Purchases.PurchaseDate = Calendar.CalendarDate
LEFT JOIN Sales ON
Sales.ProductCode = Product.ProductCode AND
Sales.SaleDate = Calendar.CalendarDate
ORDER BY
Calendar.CalendarDate,
Product.ProductCode

--
Hope this helps.

Dan Guzman
SQL Server MVP

[quoted text, click to view]

Ciar?n (chudson007@hotmail.com) writes:
[quoted text, click to view]

If you don't have any CREATE TABLE statements, you don't have any tables...

[quoted text, click to view]

....and if you don't have any tables, you don't have any need of any
statements. So I suspect that you have tables anyway.

You can script your tables from Query Analyzer, by using the Object
Browser (press F8 to make it appear, if you don't see it), finding the
table, and then right-click to pick the script alternative for CREATE
TABLE.

Be sure that your constraints are included.

And we don't ask this only to be pedantic. We ask this, because we don't
like to guess, but want to give you an accurate answer.

It also often helps to include sample data in form of INSERT statements,
and the desired output given the sample.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at

[quoted text, click to view]

Sure you can. And you can generate new create statements from them,
but once the tables have been created, you don't really need the old
commands anymore.

[quoted text, click to view]

The desired results is really the thing. What the pk/fk relationships