5 table SQL Statement

[jdrawmer]

I am effectively trying to create a timesheet system for an engineering company. They have a list of 'favourite jobs' that automatically populate their timesheet for the week. Another problem is, that jobs all have sub-jobs, for the purposes of booking time to them, to then later invoice... What also populates the timesheet, is any other job, IF it has time booked to it.

So Basically, when the timesheet system is launched, i already have in place, the automatic selection of the current week, with the ability to go back a week...

I have these tables

==== Job ====
Job_ID
Job_Description
...

==== User ====
User_ID
User_Name
...

==== FavouriteJobs ====
Job_ID
User_ID

==== SubJobs ====
Job_ID
SubJ_ID
SubJ_Description

==== Timesheet ====
Job_ID
SubJ_ID
User_ID
Time_Date
Time_Hours
Time_Mileage

In the end i need all this information, in the statement...

Job_ID, Job_Description, Subj_ID, Subj_Description, Time_Date, Time_Hours

I want to in effect do this....

SELECT All the sub jobs, of favourite jobs for the current user, AND All subjobs of jobs with time booked to them, between the start and end of a week

I can either do this in two statement, such as

Select all the subjobs of the favourite jobs for the current user; showing time booked to them IF there is any

and

Select all the subjobs of the jobs for the current user that have time booked to them

This will then allow me to populate a data grid, with a row for each subjob, in the format:

Job_ID - Job_Description - Subj_ID - Subj_Description

5645 - IT Document Control - 1 - MAIN SUBJOB

with then, a column for each day of the week, filled with the amount of time thye have booked...

If this makes ABSOLUTELY no sense and is total babble, please ask me to try and explain further, or make it a bit clearer on whatever parts...

Thanks!

 

[AlexCuse]

Without having any data this is very hard to test, but this should get you started (untested):

select x.[User_Name]
, y.Job_ID
, a.Job_Description
, b.Subj_ID
, b.Subj_Description
, c.Time_Date
, c.Time_Hours
from [User] x
inner join FavouriteJobs y
on x.[User_ID] = y.[User_ID]
inner join Job a
on y.Job_ID = a.Job_ID
inner join SubJob b
on a.Job_ID = b.Job_ID
inner join Timesheet c
on a.Job_ID = b.Job_ID
where Time_Date >= [Week Start Date]
and Time_Date <= [Week End Date]
and Time_Hours > 0

It needs start and end dates supplied. Post back with any inconsistencies in the result, I expect you could get some funny stuff going on in the joins.

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[jdrawmer]

Hey dude,

Thanks for getting back to me - and understanding my ridiculous thread! lol

I think this is getting towards what I need to be honest. Getting some inexplainable results in the joins though as you imagined... I'm currently testing the data with...

==== FavouriteJobs ====

+-------------------+
| Job_ID | User_ID |
+-------------------+
| 5645 | 4 |
| 5648 | 4 |
+-------------------+

==== Job ====

+------------------------------+
| Job_ID | Job_Description |
+------------------------------+
| 5645 | IT Document Control |
| 5646 | Test Job |
| 5647 | Another Job |
| 5648 | Yet Another Job |
+------------------------------+

==== SubJob ====

+-----------------------------------------+
| Job_ID | Subj_ID | Subj_Description |
+-----------------------------------------+
| 5645 | 1 | MAIN SUBJOB |
| 5645 | 2 | Artwork |
| 5648 | 1 | MAIN SUBJOB |
| 5648 | 2 | Another |
| 5648 | 3 | Yet Another |
| 5648 | 4 | Getting Ridiculous |
+-----------------------------------------+

==== Timesheet ====

+--------------------------------------------------------+
| Job_ID | Subj_ID | User_ID | Time_Date | Time_Hours |
+--------------------------------------------------------+
| 5645 | 1 | 4 | 27/02/2007 | 4.5 |
| 5646 | 1 | 1 | 27/02/2007 | 3.5 |
| 5645 | 1 | 4 | 28/02/2007 | 7.5 |
| 5648 | 1 | 4 | 27/02/2007 | 6.0 |
| 5648 | 3 | 4 | 01/03/2007 | 7.0 |
+--------------------------------------------------------+

==== User ====

+---------------------------+
| User_ID | User_Name |
+---------------------------+
| 1 | John |
| 3 | Peter |
| 4 | Jason |
+---------------------------+



This in mind, i SHOULD be getting the following information out of the table if you set the date to 27/02/2007 OR 28/02/2007, when I am the user...

+------------------------------------------------------+
| User_ID | Job_ID | Subj_ID | Time_Date | Time_Hours |
+------------------------------------------------------+
| 4 | 5645 | 1 | 27/02/2007 | 4.5 |
| 4 | 5645 | 2 | 27/02/2007 | NULL |
| 4 | 5648 | 1 | 27/02/2007 | 6.0 |
| 4 | 5648 | 2 | 27/02/2007 | NULL |
| 4 | 5648 | 3 | 27/02/2007 | NULL |
| 4 | 5648 | 4 | 27/02/2007 | NULL |
| 4 | 5645 | 1 | 28/02/2007 | 7.5 |
| 4 | 5645 | 2 | 28/02/2007 | NULL |
| 4 | 5648 | 1 | 28/02/2007 | NULL |
| 4 | 5648 | 2 | 28/02/2007 | NULL |
| 4 | 5648 | 3 | 28/02/2007 | NULL |
| 4 | 5648 | 4 | 28/02/2007 | NULL |
+------------------------------------------------------+

hope this makes more sense

 

[AlexCuse]

Your problem is that you will not be able to show all the dates where you are showing NULL in the Hours. Here is the results I got with a slightly tweaked query (these are for user name Jason, I just chopped off that column to keep this thread readable for IE 6 users):

User_ID     Job_ID      Subj_ID     Time_Date                Time_Hours   
----------- ----------- ----------- ------------------------ ------------ 
4           5645        2           NULL                     NULL
4           5648        2           NULL                     NULL
4           5648        4           NULL                     NULL
4           5645        1           2007-02-27 00:00:00.000  4.50
4           5648        1           2007-02-27 00:00:00.000  6.00
4           5645        1           2007-02-28 00:00:00.000  7.50

If these are not acceptable, we will need to figure out how to get you a table containing all the dates and perform some joins through this table. What DBMS are you using for this timesheet system?

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[jdrawmer]

hey,

i'm working on a simpler method, but more long winded of firstly finding all the users favourite jobs and their subjobs... then, for each one of those putting a blank entry into my VB.NET DataGridView.

Now i've got a query that finds any of those subjobs, and matching dates that have any time booked to them... however, i'm struggling, very confusingly with the search condition for the datetime data type???

I'm searching for, as a test


...
AND Time_Date >= 26/03/2007 AND Time_Date < 03/03/2007
...

and i'm getting no results; In the database, the data is in the format...

27/03/2007 00:00:00 - is this why? Why isn't the search condition working like it should?

Thanks

 

[PHV]

You may try this:
AND Time_Date >= #2007-02-26# AND Time_Date < #2007-03-03#

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[AlexCuse]

Or for SQL Server, put single quotes around your date, like '20070226'.

What are you using for your database?

Ignorance of certain subjects is a great part of wisdom

 

[jdrawmer]

i'm using SQL Server; and i'm writing my system with VB.NET. Its strange. it doesn't accept the #'s, but also says it cannot convert from char to datetime (because of the single quotes).

I've found yet another way round it, and that is to say...

for each row
for each cell in rows
perform the query on 1 single date; changing the datetime type to string
next cell
next row

seems to be ok, just not as nice as i first wanted!

Thanks for the help guys

 

[PHV]

OOps, I thought I was in an Access forum :~/
You have to know the syntax for date literal in your RDBMS.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

And this ?
AND Time_Date >= '2007-02-26' AND Time_Date < '2007-03-03'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[AlexCuse]

jdrawmer - Are you trying to run the query through VB.net or in SQL Query Analyzer/Management studio? Single quotes should work, but I am thinking you might be trying to query a DataTable object? If you set up a SQL Data Source I think you will be able to query it in the way you wish (and it is very easy to alter the SelectCommand property of this when user changes the date or anything like that). Another thing you might consider is setting up your query as a stored procedure that accepts a few parameters, this could help with your date conversion woes.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[jdrawmer]

well the format in the SQL database is **/**/****. and to my knowledge the format of a VB.NET DateTime was the same, however it just doesn't seem to. I may have to write a little conversation function to convert the string into the EXACT format of the database, but having changed it to a string and adding the for each cell for loop, it seems to have fixed matters

Thanks guys!

 

[jdrawmer]

Hi Alex,

I'm running the query through my VB.NET code. Its straight from the SQL Server database and table, as i have no data bound objects set up its all code based.

it doesn't like the quotes at all, but i'm reckoning it must be some form of date formatting i'm missing on first, second and third glance

 

[AlexCuse]

You might have an issue with the apostrophes because they are used in VB.net to indicate comments.

I am not sure what characters are used for VB.net, but in C# you need a \ before certain characters (/ and " most notably). Because // is used for comments in c#, I wonder if VB would have the same problem with the apostrophe?

This is just a guess, you might be better off taking that question to the VB.net forum.

One more thought, if your input is Date/Time, you probably need to convert it to a string before adding it to the mix with your SQL query (and place the quotes around resulting string).

Hope it helps,
Alex

Ignorance of certain subjects is a great part of wisdom