SQLTeam.com | Weblogs | Forums

Creating a derived column from multiple tables


#1

I am isolating some columns to calculate to eventually put into a big report that will encompass 20 or so derived/calculated columns. I need to create a column where the AVERAGE number of days an Order sits on the dock for each customerID . A customerID can have multiple orderid s. So the orderids will need to be added (COUNT or SUM) . Here is some sample DDL for the tables (bear with me, cannot divulge INSERT data , somewhat cumbersome in the environment of these applications)

TABLE dbo.orders --o
(
OrderID (pk, int, NOT NULL),
CustomerID varchar(5) NOT NULL,
Status varchar(50) null,
ordershipped datetime NULL,
orderarrived datetime NULL
CONSTRAINT PK_OrderID PRIMARY KEY CLUSTERED (OrderID ASC)
);

TABLE dbo.customers --cus
(
customerID (pk,varchar(5), NOT NULL),
firstname nvarchar(50) null,
lastname nvarchar(50) not null
CONSTRAINT PK_customerID PRIMARY KEY CLUSTERED (customerID ASC)
);

TABLE dbo.orderdetails --ode
(
odeID smallint NOT NULL,
OrderID (pk, int, NOT NULL),
datebegin datetime NULL
CONSTRAINT PK_odeID PRIMARY KEY CLUSTERED (odeID ASC)
);
TABLE dbo.codes --co
(
codeID (pk uniqueidentifier NOT NULL),
confirmed datetime NULL,
code (varchar(2) NULL),
CONSTRAINT PK_codeID PRIMARY KEY CLUSTERED (codeID ASC)
);

codes.confirmed has to sit in dbo.codes, it is part of a legacy grand scheme(business logic) that is beyond my control.

The derived column DaysDocked needs to look 7 days prior to the current date to see the average of how many days each customerid has their combined orders sitting on hold. We need to not count the weekend days in this process :

select [DaysDocked] = avg(DATEDIFF(dd,convert(datetime,co.confirmed,121),o.ordershipped) -
2 * DATEDIFF(wk,convert(datetime,co.confirmed,121),o.ordershipped))

from orders o , codes co
join orderdetails ode
on ode.OrderID = o.OrderID
where cus.customerid = 'ABC1' and ode.datebegin between getdate() -7 and getdate()

...with this right now i am getting 'The multi-part identifier "o.OrderID" could not be bound.' error .
??
Thanks for help


#2

You have a cross join - and have not identified how to relate the tables. Fix that and it should resolve your issue:

from orders o**, codes co**

if you really want a cross join - use

from orders o CROSS JOIN codes co

I am guessing that you really don't want a cross join - but you don't seem to have any way to relate codes to orders, orderdetails or even customers.

Also - you reference customers (cus) in the where clause but you have not included that table in the from clause.


#3

is this for Microsoft SQL Server?


#4

OK thanks Jeff, I will try a few adjustments, and may re-post if I can .
SQ17