Adding columns to a view - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Adding columns to a view

Author

Topic

killerzmp
Starting Member

3 Posts

Posted - 2013-11-02 : 22:11:52

Basically I have a view with 10 columns from 4 different tables at the present time the task requires me to create 2 column's that does some basic mathematical functions, here is my current code :

CREATE VIEW Job_Details

AS SELECT Job_ID, Job_date, Job_starttime, Job_endtime, Job_paid, acc_firstname, acc_surname, client_firstname, client_surname, Job_type_name
FROM dbo.Accountants INNER JOIN
dbo.Job ON dbo.Accountants.Staff_ID = dbo.Job.Staff_ID INNER JOIN
dbo.Clients ON dbo.Job.Client_TFN = dbo.Clients.Client_TFN INNER JOIN
dbo.Job_type ON dbo.Job.Job_type_ID = dbo.Job_type.Job_type_ID

This is the task I need to accomplish:
Create a view which shows all details of all jobs. As well as all details of jobs, the view should contain the following columns:
• The full name of the accountant and the client involved in each job
• The name of the job type
• A column named “job_duration”, which uses DATEDIFF and the job start and end times to calculate the number of minutes that the job took.
• A column named “job_cost”, which multiplies the number of minutes that the job took by the cost per minute in the job type table.

Please help..... I'm quite inexperienced with SQL

Kalaiselvan
Posting Yak Master

112 Posts

Posted - 2013-11-02 : 22:29:39

CREATE VIEW Job_Details AS
SELECT Job_ID,
Job_date,
Job_starttime,
Job_endtime,
Job_paid,
acc_firstname + ' ' + acc_surname Accountant_Fullname,
client_firstname + ' ' + client_surname Client_Fullname,
Job_type_name,
DATEDIFF(MINUTE,Job_starttime,Job_endtime) job_duration,
CAST((DATEDIFF(MINUTE,Job_starttime,Job_endtime) *
dbo.Job_type.Cost_Per_Min) AS NUMERIC(18,2)) Job_Cost
FROM dbo.Accountants INNER JOIN
dbo.Job ON dbo.Accountants.Staff_ID = dbo.Job.Staff_ID INNER JOIN
dbo.Clients ON dbo.Job.Client_TFN = dbo.Clients.Client_TFN INNER JOIN
dbo.Job_type ON dbo.Job.Job_type_ID = dbo.Job_type.Job_type_ID

Use the above Query to get your Expected Output. Cost Per Minute Column is from Job Type Table as you mentioned.
Note that Cost Per minute is Numeric Column or else Cast that Column as CAST(dbo.Job_type.Cost_Per_Min AS NUMERIC(18,2)).

Regards,
Kalai

killerzmp
Starting Member

3 Posts

Posted - 2013-11-02 : 22:36:03

Cheers, it worked. Thank you so much :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources