SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Adding columns to a view
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

killerzmp
Starting Member

Australia
3 Posts

Posted - 11/02/2013 :  22:11:52  Show Profile  Reply with Quote
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

India
110 Posts

Posted - 11/02/2013 :  22:29:39  Show Profile  Reply with Quote
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
Go to Top of Page

killerzmp
Starting Member

Australia
3 Posts

Posted - 11/02/2013 :  22:36:03  Show Profile  Reply with Quote
Cheers, it worked. Thank you so much :)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000