Jump to content

Archived

This topic is now archived and is closed to further replies.

AV1611

Need help constructing a Query

Recommended Posts

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!

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

 

Share this post


Link to post
Share on other sites

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--]

Share this post


Link to post
Share on other sites

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--]

 

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.