Creating a VIEW called vw_HoursWorked

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

General SQL Server Forums

New to SQL Server Programming

Creating a VIEW called vw_HoursWorked

Author

Topic

Arend
Starting Member

17 Posts

Posted - 2007-01-27 : 05:46:02

Hello,

I am a student and is currently busy with an SQL project. I am having trouble with creating a view to calculate and display the hours worked for each call rate..

It goes...

I have two tables: rate and consultancydetail.

The rate table consists of columns named - id, time descripiton and rate.

The consultancydetail table - invoiceID, rateID date, startTime and endTime.

My view should display the rateId column and then there should be a column created in the view for the total hours.

total hours is calculated by adding the difference between startTime and endTime and then added to each total for each rateId.

PLEASE CAN SOMEONE HELP. I am new to this and know it should be a simple thing.

cheers,
Arend

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-27 : 08:20:59

Yes. You are right it is really simple. So what have you tried so far ?

Arend
Starting Member

17 Posts

Posted - 2007-01-27 : 10:57:15

I am using SQL server 2000 programming. It has only a small explanation and its not really helping me. I haven't tried anything yet because I don't know how to do it. Please I'd be really grateful for any explanation that would explain views in more detail. I have created other views but I am battling to create the one that I have explained above.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-27 : 11:13:53

something to kick start you with.

The query below shows how you can join 2 tables using rateid from consultancydetail table with id from rate table


select rateid, starttime, endtime
from   rate r inner join consultancydetail c
       on r.id = c.rateid

You can use datediff() function to find the time difference in hour.

Arend
Starting Member

17 Posts

Posted - 2007-01-29 : 03:22:30

Thanx KH, I will try it and see what happens. Thank you for your help.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-29 : 03:30:15

quote:
Originally posted by Arend

Thanx KH, I will try it and see what happens. Thank you for your help.

No problem. Just give it a try. Post your query here if you still have problem

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources