could use some help on this advanced SQL.

dockraz · #1 (**permalink**) 03-23-10, 16:48

Here is my query:

select t2.FirstName, t2.LastName,
sec_to_time( sum( time_to_sec( t1.TimeWorked ) ) ) as WrenchTime,
q1.ClockTime, q2.BillTime, q2.BillAmt
from invLabor AS t1
INNER JOIN conAdditionalContacts AS t2 ON t1.WorkPerformedBy = t2.UserID
LEFT JOIN ( select UserID, sec_to_time( sum( time_to_sec( TimeLogged ) ) ) as ClockTime
from prlTimeClock WHERE date( TimeIn ) BETWEEN '2010-03-23' and '2010-03-23' AND TimeTypeID = 3
GROUP BY UserID ) AS q1 ON t1.WorkPerformedBy = q1.UserID
LEFT JOIN ( select WorkPerformedBy, sum( LaborHours ) as BillTime, sum( JobLaborTotal ) as BillAmt
from invJobs INNER JOIN invLabor ON invJobs.JobID = invLabor.JobID
WHERE LaborRateID > 0 AND date( invLabor.DateCreated ) BETWEEN '2010-03-23' and '2010-03-23'
GROUP BY WorkPerformedBy ) AS q2 ON t1.WorkPerformedBy = q2.WorkPerformedBy
WHERE date( t1.DateCreated ) BETWEEN '2010-03-23' and '2010-03-23'
group by t2.FirstName, t2.LastName
;

and here is the results it pulls:

+-----------+----------+------------+-----------+----------+---------+
| FirstName | LastName | WrenchTime | ClockTime | BillTime | BillAmt |
+-----------+----------+------------+-----------+----------+---------+
| Glenn | Hancock | 01:00:00 | NULL | NULL | NULL |
| Jeff | Schink | 02:30:00 | 03:00:00 | 9.00 | 765.00 |
+-----------+----------+------------+-----------+----------+---------+

The problem is that the BillTime and BillAmt are wrong for the second person... (should be 6.00 and 510.00)

Here is the issue:
In the invLabor table, there are 3 records... 2 for Job 1, and 1 for Job 2 (the labor total does = 2:30 )
What's happening is that since there are 2 jobs in invLabor, its adding Job 1 twice and Job 2 once for BillTime and BillAmt...

Any way to fix this???

Thanks in advance...

Chris

r937 · #2 (**permalink**) 03-23-10, 22:21

could you do a SHOW CREATE TABLE for the InvJobs and InvLabor tables please

dockraz · #3 (**permalink**) 03-24-10, 10:04

As requested:

| invLabor | CREATE TABLE `invLabor` (
`LaborID` int(10) unsigned NOT NULL auto_increment,
`JobID` int(10) unsigned NOT NULL default '0' COMMENT 'links to invJobs',
`DateCreated` datetime NOT NULL default '0000-00-00 00:00:00' COMMENT 'when record was created',
`CreatedBy` int(10) unsigned NOT NULL default '0' COMMENT 'who created the record',
`WorkPerformedBy` int(10) unsigned NOT NULL default '0' COMMENT 'who performed the service',
`TimeStarted` datetime default NULL COMMENT 'when did they start loggins time',
`TimeEnded` datetime default NULL COMMENT 'when did they stop their time',
`TimeWorked` time default NULL COMMENT 'how long did they work',
`Date` date default NULL,
`ManualEntry` tinyint(4) NOT NULL default '0',
`PayRate` decimal(9,2) default NULL,
`Cost` decimal(9,2) NOT NULL default '0.00' COMMENT 'stores the total cost of this item',
`WarrantyVendorID` int(10) unsigned default NULL COMMENT 'if warranty, this will hold the manufacturer or vendorid',
`RateID` int(10) unsigned default NULL COMMENT 'links to warReimbursementLaborRates and holds the labor rate chosen',
PRIMARY KEY (`LaborID`),
KEY `iinvLabor_primary` (`JobID`),
KEY `iinvLabor_date` (`TimeStarted`),
KEY `iinvLabor_performedby` (`WorkPerformedBy`,`TimeStarted`)
) ENGINE=InnoDB AUTO_INCREMENT=188 DEFAULT CHARSET=utf8 COMMENT='Holds Job Labor Information for service tickets' |

| invJobs | CREATE TABLE `invJobs` (
`JobID` int(10) unsigned NOT NULL auto_increment,
`InvoiceID` int(10) unsigned NOT NULL default '0' COMMENT 'links to invInvoice',
`DateCreated` datetime NOT NULL default '0000-00-00 00:00:00' COMMENT 'when job was created',
`CreatedBy` int(10) unsigned NOT NULL default '0' COMMENT 'userid of the person that created the job',
`Estimate` tinyint(3) unsigned default '0' COMMENT 'Is this an estimate',
`JobTitle` varchar(100) default NULL COMMENT 'general title for the job',
`ServiceRequired` text COMMENT 'Holds the description of what is required',
`ShopMaterials` decimal(9,2) default NULL COMMENT 'Shop materials charge for this job',
`PickDelivery` decimal(9,2) default NULL COMMENT 'Pickup delivery charge',
`EPACharge` decimal(9,2) default NULL COMMENT 'EPA Charge',
`ServiceCharge` decimal(9,2) default NULL COMMENT 'General Service Charge',
`ServicePerformed` text COMMENT 'Holds what was done',
`LaborHours` decimal(9,2) NOT NULL default '0.00',
`JobLaborTotal` decimal(9,2) NOT NULL default '0.00',
`UnitID` int(10) unsigned default '0' COMMENT 'links to untUnitInfo table',
`Complete` tinyint(3) unsigned NOT NULL default '0' COMMENT 'flag to tell if job is complete, 0=false, 1=true',
`LaborRateID` int(10) unsigned NOT NULL default '0',
`Discount` decimal(9,2) NOT NULL default '0.00',
`JobTemplateID` int(10) unsigned NOT NULL default '0',
`Warranty` tinyint(3) unsigned default '0' COMMENT 'flag to show if job is warranty or not',
`PickupRequired` tinyint(3) unsigned default '0' COMMENT 'yes or no flag',
`DeliveryRequired` tinyint(3) unsigned default '0' COMMENT 'yes or no flag',
`ScheduledPickupDate` date default NULL,
`PickupRateID` int(10) unsigned default NULL COMMENT 'links to optPickUpDeliveryRates',
`AwaitingApproval` tinyint(3) unsigned default '0' COMMENT 'yes or no flag for job awaiting approval',
`ApprovedBy` varchar(50) default NULL COMMENT 'holds customer name that approved the job when awaiting approval',
`DateApproved` date default NULL COMMENT 'date job was approved',
`JobType` tinyint(3) unsigned NOT NULL default '0' COMMENT 'For internal ROs, tells whether its Setup (1), Repair (2), Installation (3) or PDI (4)',
`ExpenseJob` tinyint(4) NOT NULL default '0' COMMENT 'flag to tell us if user wants to expense an internal job rather than add it to the unit cost',
PRIMARY KEY (`JobID`),
KEY `iinvJobs_UnitID` (`UnitID`)
) ENGINE=InnoDB AUTO_INCREMENT=371 DEFAULT CHARSET=utf8 COMMENT='Holds Job Information for service tickets' |

Again - thanks for the aid!
Chris

r937 · #4 (**permalink**) 03-24-10, 10:13

sweet, thanks, i wasn't sure which tables the columns being summed were in (you should always qualify every column in a query which has more than one table)

Code:

LEFT 
  JOIN ( select WorkPerformedBy
              , sum( LaborHours ) as BillTime
              , sum( JobLaborTotal ) as BillAmt 
           from invJobs 
         INNER 
           JOIN ( SELECT DISTINCT JobID
                    FROM invLabor 
                   WHERE LaborRateID > 0 
                     AND date( DateCreated ) 
                         BETWEEN '2010-03-23' and '2010-03-23'
                ) AS uneeklabor
             ON uneeklabor.JobID = invJobs.JobID
         GROUP 
             BY WorkPerformedBy ) AS q2 
    ON t1.WorkPerformedBy = q2.WorkPerformedBy

by the way, your date range tests DATE(datecolumn) BETWEEN thisdate AND thisdate are inefficient because you are applying a function to the date columns

change those so that the code looks like this --

Code:

WHERE TimeIn >= '2010-03-23' 
  AND TimeIn  < '2010-03-24' -- less than the following day

this will allow an index to be utilized on the date column

dockraz · #5 (**permalink**) 03-24-10, 17:13

I made the date changes as suggested...

Your section was slightly off - but it led me to understand what you were doing, so I was able to fix it myself based on what you had given...

I also added some references, simply because I was starting to get confused as to which query was referring to which column, etc.

But with you suggested changes, and my slight repair to them, all is working!!! Thank you so much for your help!!!

For the record, here is the change I made:

LEFT JOIN ( select WorkPerformedBy, sum( LaborHours ) as BillTime, sum( JobLaborTotal ) as BillAmt from invJobs AS st1
INNER JOIN ( select distinct WorkPerformedBy, JobID from invLabor WHERE DateCreated >= '2010-03-23'
AND DateCreated < '2010-03-25' ) as sq1 ON sq1.JobID = st1.JobID WHERE LaborRateID > 0
GROUP BY WorkPerformedBy ) AS q2 ON t1.WorkPerformedBy = q2.WorkPerformedBy

r937 · #6 (**permalink**) 03-24-10, 19:08

[QUOTE=dockraz;6453566]... confused as to which query was referring to which column, etc.[/quote i know

that's why, in any query of more than one table, it is important always to qualify every column with its table name

Internet Services

Web Development

Digital Marketing

Consumer Tech