February 8, 2017 at 11:46 am
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
February 8, 2017 at 11:53 am
You can't create a calculated column that references other tables.
Options:
1) create a scalar udf and use that as the derived column definition (slow and not recommended for read-heavy tables)
2) use a normal column and an insert/update/delete trigger to set the column value correctly.
p.s. You've got a cross join in that query, there's no join between codes and the other two tables.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply