Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2010
    Posts
    16

    Unanswered: could use some help on this advanced SQL. [SOLVED]

    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
    Last edited by dockraz; 03-24-10 at 18:14.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    could you do a SHOW CREATE TABLE for the InvJobs and InvLabor tables please
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2010
    Posts
    16
    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2010
    Posts
    16
    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


  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    [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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •