How do I create a report in sql for the following:?

Question

I have some tables for my database(SQL) and was wondering how to create a report so that:

for a member of staff, list his/ her schedule

Thanks

Answer 1

I have some questions before I can give you an exact answer :

1. Do you want the report to list everyone, and their schedule, or do you want to be able to select a specific person, and just list their schedule?

2. What database product are you using? MS Access? SQL Server? MySQL?

3. Do you want the report to come directly from the database, or do you have a web based front end, or perhaps a reporting app like Crystal Reports?

I ask all this because the answer depends on all these criteria. A simple answer would be to have 2 tables - one listing the employees (call it 'tb_Emp') and one listing the days of the month, with who is scheduled for each day (call it 'tb_schedule'). Then, you could create a query like :

SELECT * FROM tb_schedule
WHERE empname = "some_employee"

This would return just one employee's schedule, and to make it dynamic, you would need to pass the name to the proc as a variable.

Answer 2

If this is SQL Server, I would start with a SP that retrieves a member of staff's schedule when you pass in the staff number as a parameter.

I would then set up a scheduled job that called the SP, outputs the results to a file, then called DatabaseMail to email the member of staff with the query results attached.

To gloss it up a bit, you put some HTML in the SELECT statement in the SP and set DatabaseMail to include the query results in the email body.

Answer 3

First create an php page with all the fields that you require. You can hard program the query until you get it right. I assume that you have a field in the db of staffs. You can create an php page that will pass that variable to this page. That way you one have to create one php page for the report itself.

There are a number of good websites out there that show how to do the reports. Look in the source list below for the links. Feel free to message me or email me with more detailed questions.

Answer 4

In get top of entry to you are able to write queries in sq. view which grant you with fantastically a lot each little thing you opt for. surely as far as varieties go besides, they're basically depending on the data from one or extra sq. queries. comments are not a lot diverse from varieties, except they grant function to team on particular fields. it truly is surely basic sufficient to copy this function by taking the question the record is in accordance with, then including the mandatory team by syntax.