Designing Databse - 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

General SQL Server Forums

New to SQL Server Programming

Designing Databse

Author

Topic

lubinski
Starting Member

1 Post

Posted - 2007-09-21 : 22:05:19

Im creating a sample database for purposes of keeping track of employee's jobs + billing codes for that job. currently they just use an excel spreadsheet to keep track of billing.

Question 1:

What would be the easiest way for the manager to enter billing codes? Billing codes are numbers like 956, 958, 805 and they co-relate to prices for each billing code.

1. I want the manager to enter the billing code and have the sql database find out how much that code is and add them up daily. This would prevent the manager from having to input prices and billing codes, and extra step.

Any links on the above topic would be helpful. I'm not looking for anyone to spoon feed me code just point me in the right direction. Im relatively new to sql having only a class of sql @ ITT.

Heres the way I have the db tables laid out currently.

employee table
employee_id
technumber
fname
lname
address
workphone
homephone
trucknumber
officelocation

employeejobs table
employee_id
jobs_id

jobs table
jobs_id
codes
date
timeframe
city

hours table
hours_id
employee_id
hoursworked
month

Any info would be much appreciated, Im going to go find a sql book now ;).

Kristen
Test

22859 Posts

Posted - 2007-09-22 : 02:06:08

"What would be the easiest way for the manager to enter billing codes?"

If the managers know the numbers I would get them to type them in, and then display the name/description of that code as confirmation.

If there are less than, say, 100 such codes I would use a pick list

More than that and you need to provide a "finder" ... some sort of [...] button that pops up a search window and allows them to type part of the name, displays matches, they either try again, or pick one, and the code is copied to the form below.

"I want the manager to enter the billing code and have the sql database find out how much that code is and add them up daily."

So you have a BillingRate table with columns:

brCode
brDescription
brRatePerHour

and then some sort of TimeSheet table with columns:

tsDate
tsHoursWorked
tsEmployeeID
tsBillingCode

and then you can report date / billing code / hours / money as follows:
[code]
SELECT tsDate,
tsBillingCode,
[Hours] = SUM(tsHoursWorked),
[Money] = SUM(tsHoursWorked) * brRatePerHour
FROM TimeSheet
JOIN BillingRate
ON brCode = tsBillingCode
WHERE tsDate >= '20070901' -- Just show September 2007
AND tsDate < '20071001'
GROUP BY tsDate, tsBillingCode
ORDER BY tsDate, tsBillingCode
[code]
Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources