Jump to content


Photo

Need help constructing a Query


  • Please log in to reply
6 replies to this topic

#1 AV1611

AV1611
  • Members
  • PipPipPip
  • Advanced Member
  • 997 posts

Posted 24 November 2005 - 04:44 AM

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!

#2 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 24 November 2005 - 04:50 AM

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
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...

#3 AV1611

AV1611
  • Members
  • PipPipPip
  • Advanced Member
  • 997 posts

Posted 24 November 2005 - 04:58 AM

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) View Post[/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
[/quote]

#4 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 24 November 2005 - 05:06 AM

yeah, that's the query to get total hours. throw in the orders as needed.
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...

#5 AV1611

AV1611
  • Members
  • PipPipPip
  • Advanced Member
  • 997 posts

Posted 24 November 2005 - 05:30 AM

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) View Post[/div][div class=\'quotemain\'][!--quotec--]
yeah, that's the query to get total hours. throw in the orders as needed.
[/quote]


#6 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 24 November 2005 - 05:36 AM

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--]
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...

#7 AV1611

AV1611
  • Members
  • PipPipPip
  • Advanced Member
  • 997 posts

Posted 24 November 2005 - 02:06 PM

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) View Post[/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--]
[/quote]





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users