pete.trudell (8/19/2008)
Sorry, nine year of Access, 3 mos. of SQL.Here's my most recent attempt to create a computer column from several computed columns.
EMonth = DATEPART(m,dbo.employee.LGD)
EDay=DATEPART(d,dbo.employee.LGD)
These work fine, then I attempt to create a column with the following:
PPR Due Date = EMonth& ' / '&EDay
The structure is changed to 'EMonth& ' / '&EDay' , and thats what ends up in my column, not calculations.
If I create a column of data :
Lunch Time = DateDiff(s,[End Lunch],[Start Lunch]) it's okay, but then every time I attempt to use the new column [Lunch Time] i get an error - [Lunch Time] in expression is not part of the query.
Thanks for any direction.
It would be very helpful if you would read the articles that Jack links to in his signature. Having those would make it a lot easier to provide you with a working example.
With that said - you can use one of the following constructs:
-- Example using CTE
CREATE PROCEDURE dbo.MyProcedure AS
;WITH myComputed (col1, EMonth, EDay, LunchTime)
AS (SELECT col1
,DATEPART(m,dbo.employee.LGD) AS EMonth
,DATEPART(d,dbo.employee.LGD) AS EDay
,DATEDIFF(s,[End Lunch],[Start Lunch]) AS LunchTime
FROM MyTable)
SELECT col1
,EMonth + '/' + EDay AS PPRDueDate
,LunchTime
FROM myComputed;
GO
-- Example using derived table
CREATE PROCEDURE dbo.MyProcedure AS
SELECT col1
,EMonth + '/' + EDay AS PPRDueDate
,LunchTime
FROM (SELECT col1
,DATEPART(m,dbo.employee.LGD) AS EMonth
,DATEPART(d,dbo.employee.LGD) AS EDay
,DATEDIFF(s,[End Lunch],[Start Lunch]) AS LunchTime
FROM MyTable) AS t;
GO
I prefer using the CTE myself.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs