Need help constructing a Query

[AV1611]

I have a table with the following field:

 

JobNumber | EmployeeNo | Dept | Hours | Date

 

123 | 1 | 200 | 1.5 | 1/1/2005

123 | 2 | 200 | .75 | 1/2/2005

222 | 2 | 200 | .25 | 3/7/2005

 

There are 1.5 million lines of this...

 

I need to write a query that does this:

 

Show me ALL hours on a given JobNumber sorted by Job Number first, the Dept Second, and for all dates from 1/1/2005 to 11/1/2005

 

I just can't seem to work it out...

Thanks!

[ryanlwh]

your date field is stored like that??? that's not going to give you correct sorting result by date, and date comparison is going to be off (that's why mysql dates are yyyy-mm-dd). anyway

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] Hours FROM table1 WHERE JobNumber='$number' AND date BETWEEN '1/1/2005' AND '11/1/2005' ORDER BY JobNumber ASC, Dept ASC [!--sql2--][/div][!--sql3--]

as i mentioned the date format here is not going to give you what you want correctly

[AV1611]

First, I understand the date issue, we can leave that part off for now... I am not running PHP (But I can if I need to).

 

I need to figure out the TOTAL hours for EACH job number... (There are several rows for each job number...

Would this do it?

Select sum(labor.Total),labor.`Date`,labor.Empno,labor.Dept,labor.Job
From labor Group by Job;

 

HELP!

 

[!--quoteo(post=321635:date=Nov 23 2005, 11:50 PM:name=ryanlwh)--][div class=\'quotetop\']QUOTE(ryanlwh @ Nov 23 2005, 11:50 PM) 321635[/snapback][/div][div class=\'quotemain\'][!--quotec--]

your date field is stored like that??? that's not going to give you correct sorting result by date, and date comparison is going to be off (that's why mysql dates are yyyy-mm-dd). anyway

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] Hours FROM table1 WHERE JobNumber='$number' AND date BETWEEN '1/1/2005' AND '11/1/2005' ORDER BY JobNumber ASC, Dept ASC [!--sql2--][/div][!--sql3--]

as i mentioned the date format here is not going to give you what you want correctly

[ryanlwh]

yeah, that's the query to get total hours. throw in the orders as needed.

[AV1611]

Any idea how I fix the dates?

 

 

[!--quoteo(post=321640:date=Nov 24 2005, 12:06 AM:name=ryanlwh)--][div class=\'quotetop\']QUOTE(ryanlwh @ Nov 24 2005, 12:06 AM) 321640[/snapback][/div][div class=\'quotemain\'][!--quotec--]

yeah, that's the query to get total hours. throw in the orders as needed.

 

[ryanlwh]

does that relate to the other post? you should create a new field first, then do this:

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']UPDATE[/span] table1 SET dateField=STR_TO_DATE(textField,'%c/%e/%Y'); [!--sql2--][/div][!--sql3--]

[AV1611]

You are my hero...

 

[!--quoteo(post=321644:date=Nov 24 2005, 12:36 AM:name=ryanlwh)--][div class=\'quotetop\']QUOTE(ryanlwh @ Nov 24 2005, 12:36 AM) 321644[/snapback][/div][div class=\'quotemain\'][!--quotec--]

does that relate to the other post? you should create a new field first, then do this:

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']UPDATE[/span] table1 SET dateField=STR_TO_DATE(textField,'%c/%e/%Y'); [!--sql2--][/div][!--sql3--]