MySQL存储过程-计算不同薪资率的薪资

Question

我正面临一个问题，试图为薪资报告提供一个MySQL存储过程。以前，只有在薪资期间开始时才允许更改员工薪资。然而，现在他们可以在一个发薪期内改变，而报告需要考虑到在一个工资期间不同的工资率。工资期是两周。

我有以下的查询，这是非常混乱的，它的工作，但不考虑工资率变化在一个期间的中间。

注意:日期是硬编码的例子。

SELECT
    e.EmployeeID AS EmployeeID,
    CONCAT(e.LastName, ', ', e.FirstName) AS Resource,
    (SELECT rt.Description FROM Roles rt WHERE rt.RoleId = e.RoleId) as Role,
    (
        SELECT
            SUM(td.BillableHours)
        FROM
            Timesheets t,
            TimesheetDetails td,
            Projects p
        WHERE e.EmployeeID = t.EmployeeID
        AND t.TimeCardID = td.TimeCardID
        AND p.ProjectID = td.ProjectID
        AND p.ProjectID NOT IN (24,29,606,614,746)
        AND DATE(td.WorkDate) >= DATE('2015-01-17')
        AND DATE(td.WorkDate) <= DATE('2015-01-30')
    ) AS RegularHours,
    (
        SELECT
            SUM(td.BillableHours)
        FROM
            Timesheets t,
            TimesheetDetails td,
            Projects p
        WHERE e.EmployeeID = t.EmployeeID
        AND t.TimeCardID = td.TimeCardID
        AND p.ProjectID = td.ProjectID
        AND (p.ProjectID = 29 OR p.ProjectID = 614)
        AND DATE(td.WorkDate) >= DATE('2015-01-17')
        AND DATE(td.WorkDate) <= DATE('2015-01-30')
    ) AS PTO,
    (
        SELECT
            SUM(td.BillableHours)
        FROM
            Timesheets t,
            TimesheetDetails td,
            Projects p
        WHERE e.EmployeeID = t.EmployeeID
        AND t.TimeCardID = td.TimeCardID
        AND p.ProjectID = td.ProjectID
        AND p.ProjectID = 24
        AND DATE(td.WorkDate) >= DATE('2015-01-17')
        AND DATE(td.WorkDate) <= DATE('2015-01-30')
    ) AS Holiday,
    (
        SELECT
            SUM(td.BillableHours)
        FROM
            Timesheets t,
            TimesheetDetails td,
            Projects p
        WHERE e.EmployeeID = t.EmployeeID
        AND t.TimeCardID = td.TimeCardID
        AND p.ProjectID = td.ProjectID
        AND p.ProjectID = 746
        AND DATE(td.WorkDate) >= DATE('2015-01-17')
        AND DATE(td.WorkDate) <= DATE('2015-01-30')
    ) AS FloatingHoliday,
    (
        SELECT
            SUM(td.BillableHours)
        FROM
            Timesheets t,
            TimesheetDetails td
        WHERE e.EmployeeID = t.EmployeeID
        AND t.TimeCardID = td.TimeCardID
        AND td.ProjectID NOT IN (606)
        AND DATE(td.WorkDate) >= DATE('2015-01-17')
        AND DATE(td.WorkDate) <= DATE('2015-01-30')
    ) AS Total,
    (
        SELECT 
            epr.Rate 

        FROM 
            EmployeePayRate epr ,
            Timesheets t,
            TimesheetDetails td
        WHERE e.EmployeeID = t.EmployeeID
        AND t.TimeCardID = td.TimeCardID
        AND epr.EmployeeID = t.EmployeeID
        AND DATE(td.WorkDate) >= DATE('2015-01-17')
        AND DATE(td.WorkDate) <= DATE('2015-01-30')
        AND epr.StartDate <= td.WorkDate
        ORDER BY epr.StartDate DESC
        LIMIT 1
    ) AS PayRate,
    (
        (SELECT
            SUM(td.BillableHours)
        FROM
            Timesheets t,
            TimesheetDetails td
        WHERE e.EmployeeID = t.EmployeeID
        AND t.TimeCardID = td.TimeCardID
        AND td.ProjectID NOT IN (606)
        AND DATE(td.WorkDate) >= DATE('2015-01-17')
        AND DATE(td.WorkDate) <= DATE('2015-01-30')
        )

        *

        (SELECT 
            epr.Rate 
        FROM 
            EmployeePayRate epr ,
            Timesheets t,
            TimesheetDetails td
        WHERE e.EmployeeID = t.EmployeeID
        AND t.TimeCardID = td.TimeCardID
        AND epr.EmployeeID = t.EmployeeID
        AND DATE(td.WorkDate) >= DATE('2015-01-17')
        AND DATE(td.WorkDate) <= DATE('2015-01-30')
        AND epr.StartDate <= td.WorkDate
        ORDER BY epr.StartDate DESC
        LIMIT 1)
    ) AS GrossEarnings

FROM
    Employee e
WHERE (e.EmployeeID IN (SELECT t.EmployeeID 
    FROM TimesheetDetails tsd, Timesheets t 
    WHERE tsd.BillableHours > 0 
    AND tsd.WorkDate BETWEEN '2015-01-17' AND '2015-01-30'
    AND tsd.TimeCardID = t.TimeCardID
    ) OR e.Status = 'Active')
AND (e.ResourceTypeID = 2 OR e.ResourceTypeID = 4)
GROUP BY Resource
ORDER BY Role, Resource ASC

最终，报告的各栏应该是。

EmployeeID，LastName，FirstName，RegularHours，PTO，Holiday，FloatingHoliday，Total和GrossEarnings。

我对存储过程不是很有经验，但我想我需要用光标循环每一个日期，并找出适用于它的支付率。

无论如何，这个SQL将创建数据结构。

-- MySQL dump 10.13  Distrib 5.6.17, for Win64 (x86_64)
--
-- Host: localhost    Database: timesheet_test
-- ------------------------------------------------------
-- Server version   5.6.22-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `employee`
--

DROP TABLE IF EXISTS `employee`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `employee` (
  `EmployeeID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `FirstName` varchar(25) NOT NULL,
  `LastName` varchar(30) NOT NULL,
  `Address` varchar(255) DEFAULT NULL,
  `CountryID` int(10) unsigned DEFAULT NULL,
  `Phone1` varchar(50) DEFAULT NULL,
  `Phone2` varchar(50) DEFAULT NULL,
  `ClientSitePhone` varchar(50) DEFAULT NULL,
  `Email` varchar(150) DEFAULT NULL,
  `PersonalEmail` varchar(150) DEFAULT NULL,
  `ClientEmail` varchar(150) DEFAULT NULL,
  `Comments` longtext,
  `HRComments` longtext,
  `Status` varchar(10) DEFAULT NULL,
  `Expertise` varchar(100) DEFAULT NULL,
  `Years` varchar(10) DEFAULT NULL,
  `Payroll` tinyint(1) NOT NULL DEFAULT '1',
  `PartTime` tinyint(1) NOT NULL DEFAULT '0',
  `City` varchar(100) DEFAULT '',
  `State` varchar(2) DEFAULT NULL,
  `Zip` varchar(20) DEFAULT NULL,
  `DateOfBirth` date DEFAULT NULL,
  `RoleId` int(10) unsigned DEFAULT NULL,
  `ImmiId` int(10) unsigned DEFAULT NULL,
  `ResourceTypeId` int(10) unsigned DEFAULT NULL,
  `UnderHoursAlerts` tinyint(1) NOT NULL DEFAULT '1',
  `DateHired` date DEFAULT NULL,
  `DateLastWorked` date DEFAULT NULL,
  `RecruiterEmployeeID` int(10) unsigned DEFAULT NULL,
  `PrimaryPhone` varchar(1) DEFAULT 'H',
  `PreHire` tinyint(1) NOT NULL DEFAULT '0',
  `SSN` varchar(132) DEFAULT NULL,
  `Benefits` varchar(100) NOT NULL DEFAULT 'None',
  `HasPTO` tinyint(1) NOT NULL DEFAULT '0',
  `IsReviewer` tinyint(1) NOT NULL DEFAULT '0',
  `ReviewerEmployeeID` int(10) unsigned DEFAULT NULL,
  `OffshoreGroupID` int(10) unsigned DEFAULT NULL,
  `CertificateOfInsuranceExpiration` date DEFAULT NULL,
  `CertificateOfInsuranceNotes` longtext,
  `WorkersCompExpiration` date DEFAULT NULL,
  `WorkersCompNotes` longtext,
  PRIMARY KEY (`EmployeeID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `employeepayrate`
--

DROP TABLE IF EXISTS `employeepayrate`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `employeepayrate` (
  `EmployeePayRateID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `EmployeeId` int(10) unsigned NOT NULL,
  `StartDate` date NOT NULL,
  `Rate` double NOT NULL,
  PRIMARY KEY (`EmployeePayRateID`),
  UNIQUE KEY `unq_EmployeePayRate` (`EmployeeId`,`StartDate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `projects`
--

DROP TABLE IF EXISTS `projects`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `projects` (
  `ProjectID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ProjectName` varchar(150) NOT NULL,
  `ProjectDescription` longtext,
  `ClientID` int(10) unsigned NOT NULL,
  `ProjectBeginDate` date NOT NULL,
  `ProjectEndDate` date NOT NULL,
  `Active` char(1) NOT NULL,
  `Billable` tinyint(1) NOT NULL DEFAULT '0',
  `EngagementID` int(10) unsigned DEFAULT NULL,
  `ClassificationID` int(10) unsigned NOT NULL DEFAULT '9',
  `ProjectTypeID` int(10) unsigned NOT NULL DEFAULT '5',
  PRIMARY KEY (`ProjectID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `timesheetdetails`
--

DROP TABLE IF EXISTS `timesheetdetails`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `timesheetdetails` (
  `TimeCardDetailID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `TimeCardID` int(10) unsigned NOT NULL,
  `ProjectID` int(10) unsigned NOT NULL,
  `BillableHours` decimal(4,2) NOT NULL,
  `WorkDate` date NOT NULL,
  PRIMARY KEY (`TimeCardDetailID`),
  KEY `fk_TimesheetDetails_Project_idx` (`ProjectID`),
  KEY `fk_TimesheetDetails_Timesheets_idx` (`TimeCardID`),
  KEY `idx_TimesheetDetails_WorkDate` (`WorkDate`),
  CONSTRAINT `fk_TimesheetDetails_Project` FOREIGN KEY (`ProjectID`) REFERENCES `projects` (`ProjectID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_TimesheetDetails_Timesheets` FOREIGN KEY (`TimeCardID`) REFERENCES `timesheets` (`TimeCardID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `timesheets`
--

DROP TABLE IF EXISTS `timesheets`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `timesheets` (
  `TimeCardID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `EmployeeID` int(10) unsigned NOT NULL,
  `DateEntered` date NOT NULL,
  `PPEDate` date NOT NULL,
  PRIMARY KEY (`TimeCardID`),
  KEY `fk_Timesheets_Employee_idx` (`EmployeeID`),
  KEY `idx_Timsheets_PPEDate` (`PPEDate`),
  CONSTRAINT `fk_Timesheets_Employee` FOREIGN KEY (`EmployeeID`) REFERENCES `employee` (`EmployeeID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2015-02-23 15:49:19

希望这个样本数据足够了。

INSERT INTO `timesheet_test`.`employee` (`EmployeeID`, `FirstName`, `LastName`, `Address`, `CountryID`, `Phone1`, `Email`, `Comments`, `Status`, `Payroll`, `PartTime`, `ResourceTypeId`, `Benefits`, `HasPTO`) VALUES ('1', 'Joe', 'Somebody', '123 Fake Street', '1', '555-555-5555', 'jsomebody@gmail.com', 'Not a real person', 'Active', '1', '0', '2', '1', '1');
INSERT INTO `timesheet_test`.`employee` (`EmployeeID`, `FirstName`, `LastName`, `Address`, `CountryID`, `Phone1`, `Email`, `Comments`, `Status`, `Payroll`, `PartTime`, `ResourceTypeId`, `Benefits`, `HasPTO`) VALUES ('2', 'Roger', 'Rabbit', '123 Crazy Street', '1', '111-111-1111', 'rrabbit@gmail.com', 'Not a real person', 'Active', '1', '0', '2', '1', '1');

INSERT INTO `timesheet_test`.`employeepayrate` (`EmployeePayRateID`, `EmployeeId`, `StartDate`, `Rate`) VALUES ('1', '1', '2015-01-17', '20');
INSERT INTO `timesheet_test`.`employeepayrate` (`EmployeePayRateID`, `EmployeeId`, `StartDate`, `Rate`) VALUES ('2', '2', '2015-01-17', '25');
INSERT INTO `timesheet_test`.`employeepayrate` (`EmployeePayRateID`, `EmployeeId`, `StartDate`, `Rate`) VALUES ('3', '1', '2015-01-24', '50');

INSERT INTO `timesheet_test`.`projects` (`ProjectID`, `ProjectName`, `ProjectDescription`, `ClientID`, `ProjectBeginDate`, `ProjectEndDate`, `Active`, `Billable`, `EngagementID`, `ClassificationID`, `ProjectTypeID`) VALUES ('1', 'Project Mayhem', 'Create as much Mayhem as possible', '1', '2015-01-17', '2020-01-01', 'Y', '1', '1', '1', '1');

INSERT INTO `timesheet_test`.`timesheets` (`TimeCardID`, `EmployeeID`, `DateEntered`, `PPEDate`) VALUES ('1', '1', '2015-01-17', '2015-01-30');
INSERT INTO `timesheet_test`.`timesheets` (`TimeCardID`, `EmployeeID`, `DateEntered`, `PPEDate`) VALUES ('2', '2', '2015-01-17', '2015-01-30');
INSERT INTO `timesheet_test`.`timesheets` (`TimeCardID`, `EmployeeID`, `DateEntered`, `PPEDate`) VALUES ('3', '1', '2015-01-24', '2015-01-30');
INSERT INTO `timesheet_test`.`timesheets` (`TimeCardID`, `EmployeeID`, `DateEntered`, `PPEDate`) VALUES ('4', '2', '2015-01-24', '2015-01-30');

INSERT INTO `timesheet_test`.`timesheetdetails` (`TimeCardDetailID`, `TimeCardID`, `ProjectID`, `BillableHours`, `WorkDate`) VALUES ('1', '1', '1', '8', '2015-01-19');
INSERT INTO `timesheet_test`.`timesheetdetails` (`TimeCardDetailID`, `TimeCardID`, `ProjectID`, `BillableHours`, `WorkDate`) VALUES ('2', '1', '1', '8', '2015-01-20');
INSERT INTO `timesheet_test`.`timesheetdetails` (`TimeCardDetailID`, `TimeCardID`, `ProjectID`, `BillableHours`, `WorkDate`) VALUES ('3', '1', '1', '8', '2015-01-21');
INSERT INTO `timesheet_test`.`timesheetdetails` (`TimeCardDetailID`, `TimeCardID`, `ProjectID`, `BillableHours`, `WorkDate`) VALUES ('4', '1', '1', '8', '2015-01-22');
INSERT INTO `timesheet_test`.`timesheetdetails` (`TimeCardDetailID`, `TimeCardID`, `ProjectID`, `BillableHours`, `WorkDate`) VALUES ('5', '1', '1', '8', '2015-01-23');
INSERT INTO `timesheet_test`.`timesheetdetails` (`TimeCardDetailID`, `TimeCardID`, `ProjectID`, `BillableHours`, `WorkDate`) VALUES ('6', '2', '1', '8', '2015-01-19');
INSERT INTO `timesheet_test`.`timesheetdetails` (`TimeCardDetailID`, `TimeCardID`, `ProjectID`, `BillableHours`, `WorkDate`) VALUES ('7', '2', '1', '8', '2015-01-20');
INSERT INTO `timesheet_test`.`timesheetdetails` (`TimeCardDetailID`, `TimeCardID`, `ProjectID`, `BillableHours`, `WorkDate`) VALUES ('8', '2', '1', '8', '2015-01-21');
INSERT INTO `timesheet_test`.`timesheetdetails` (`TimeCardDetailID`, `TimeCardID`, `ProjectID`, `BillableHours`, `WorkDate`) VALUES ('9', '2', '1', '8', '2015-01-22');
INSERT INTO `timesheet_test`.`timesheetdetails` (`TimeCardDetailID`, `TimeCardID`, `ProjectID`, `BillableHours`, `WorkDate`) VALUES ('10', '2', '1', '8', '2015-01-23');
INSERT INTO `timesheet_test`.`timesheetdetails` (`TimeCardDetailID`, `TimeCardID`, `ProjectID`, `BillableHours`, `WorkDate`) VALUES ('11', '3', '1', '8', '2015-01-26');
INSERT INTO `timesheet_test`.`timesheetdetails` (`TimeCardDetailID`, `TimeCardID`, `ProjectID`, `BillableHours`, `WorkDate`) VALUES ('12', '3', '1', '8', '2015-01-27');
INSERT INTO `timesheet_test`.`timesheetdetails` (`TimeCardDetailID`, `TimeCardID`, `ProjectID`, `BillableHours`, `WorkDate`) VALUES ('13', '3', '1', '8', '2015-01-28');
INSERT INTO `timesheet_test`.`timesheetdetails` (`TimeCardDetailID`, `TimeCardID`, `ProjectID`, `BillableHours`, `WorkDate`) VALUES ('14', '3', '1', '8', '2015-01-29');
INSERT INTO `timesheet_test`.`timesheetdetails` (`TimeCardDetailID`, `TimeCardID`, `ProjectID`, `BillableHours`, `WorkDate`) VALUES ('15', '3', '1', '8', '2015-01-30');
INSERT INTO `timesheet_test`.`timesheetdetails` (`TimeCardDetailID`, `TimeCardID`, `ProjectID`, `BillableHours`, `WorkDate`) VALUES ('16', '4', '1', '8', '2015-01-26');
INSERT INTO `timesheet_test`.`timesheetdetails` (`TimeCardDetailID`, `TimeCardID`, `ProjectID`, `BillableHours`, `WorkDate`) VALUES ('17', '4', '1', '8', '2015-01-27');
INSERT INTO `timesheet_test`.`timesheetdetails` (`TimeCardDetailID`, `TimeCardID`, `ProjectID`, `BillableHours`, `WorkDate`) VALUES ('18', '4', '1', '8', '2015-01-28');
INSERT INTO `timesheet_test`.`timesheetdetails` (`TimeCardDetailID`, `TimeCardID`, `ProjectID`, `BillableHours`, `WorkDate`) VALUES ('19', '4', '1', '8', '2015-01-29');
INSERT INTO `timesheet_test`.`timesheetdetails` (`TimeCardDetailID`, `TimeCardID`, `ProjectID`, `BillableHours`, `WorkDate`) VALUES ('20', '4', '1', '8', '2015-01-30');

我对上面的测试数据运行了下面的查询。

SELECT
    e.EmployeeID AS EmployeeID,
    CONCAT(e.LastName, ', ', e.FirstName) AS Resource,
    (
        SELECT
            SUM(td.BillableHours)
        FROM
            Timesheets t,
            TimesheetDetails td,
            Projects p
        WHERE e.EmployeeID = t.EmployeeID
        AND t.TimeCardID = td.TimeCardID
        AND p.ProjectID = td.ProjectID
        AND p.ProjectID NOT IN (24,29,606,614,746)
        AND DATE(td.WorkDate) >= DATE('2015-01-17')
        AND DATE(td.WorkDate) <= DATE('2015-01-30')
    ) AS RegularHours,
    (
        SELECT
            SUM(td.BillableHours)
        FROM
            Timesheets t,
            TimesheetDetails td,
            Projects p
        WHERE e.EmployeeID = t.EmployeeID
        AND t.TimeCardID = td.TimeCardID
        AND p.ProjectID = td.ProjectID
        AND (p.ProjectID = 29 OR p.ProjectID = 614)
        AND DATE(td.WorkDate) >= DATE('2015-01-17')
        AND DATE(td.WorkDate) <= DATE('2015-01-30')
    ) AS PTO,
    (
        SELECT
            SUM(td.BillableHours)
        FROM
            Timesheets t,
            TimesheetDetails td,
            Projects p
        WHERE e.EmployeeID = t.EmployeeID
        AND t.TimeCardID = td.TimeCardID
        AND p.ProjectID = td.ProjectID
        AND p.ProjectID = 24
        AND DATE(td.WorkDate) >= DATE('2015-01-17')
        AND DATE(td.WorkDate) <= DATE('2015-01-30')
    ) AS Holiday,
    (
        SELECT
            SUM(td.BillableHours)
        FROM
            Timesheets t,
            TimesheetDetails td,
            Projects p
        WHERE e.EmployeeID = t.EmployeeID
        AND t.TimeCardID = td.TimeCardID
        AND p.ProjectID = td.ProjectID
        AND p.ProjectID = 746
        AND DATE(td.WorkDate) >= DATE('2015-01-17')
        AND DATE(td.WorkDate) <= DATE('2015-01-30')
    ) AS FloatingHoliday,
    (
        SELECT
            SUM(td.BillableHours)
        FROM
            Timesheets t,
            TimesheetDetails td
        WHERE e.EmployeeID = t.EmployeeID
        AND t.TimeCardID = td.TimeCardID
        AND td.ProjectID NOT IN (606)
        AND DATE(td.WorkDate) >= DATE('2015-01-17')
        AND DATE(td.WorkDate) <= DATE('2015-01-30')
    ) AS Total,
    (
        SELECT 
            epr.Rate 

        FROM 
            EmployeePayRate epr ,
            Timesheets t,
            TimesheetDetails td
        WHERE e.EmployeeID = t.EmployeeID
        AND t.TimeCardID = td.TimeCardID
        AND epr.EmployeeID = t.EmployeeID
        AND DATE(td.WorkDate) >= DATE('2015-01-17')
        AND DATE(td.WorkDate) <= DATE('2015-01-30')
        AND epr.StartDate <= td.WorkDate
        ORDER BY epr.StartDate DESC
        LIMIT 1
    ) AS PayRate,
    (
        SELECT
            SUM(td.BillableHours)
            *
            (
                SELECT epr.Rate 
                FROM EmployeePayRate epr 
                WHERE epr.EmployeeID = t.EmployeeID
                AND epr.StartDate <= td.WorkDate
                AND td.ProjectID NOT IN (606) 
                ORDER BY epr.StartDate DESC
                LIMIT 1
            )
        FROM
            Timesheets t,
            TimesheetDetails td
        WHERE e.EmployeeID = t.EmployeeID
        AND t.TimeCardID = td.TimeCardID
        AND DATE(td.WorkDate) >= DATE('2015-01-17')
        AND DATE(td.WorkDate) <= DATE('2015-01-30')
    ) AS GrossEarnings

FROM
    Employee e
WHERE (e.EmployeeID IN (SELECT t.EmployeeID 
    FROM TimesheetDetails tsd, Timesheets t 
    WHERE tsd.BillableHours > 0 
    AND tsd.WorkDate BETWEEN '2015-01-17' AND '2015-01-30'
    AND tsd.TimeCardID = t.TimeCardID
    ) OR e.Status = 'Active')
AND (e.ResourceTypeID = 2 OR e.ResourceTypeID = 4)
GROUP BY Resource
ORDER BY Resource ASC

返回以下记录集。

EmployeeID, Resource, RegularHours, PTO, Holiday, FloatingHoliday, Total, PayRate, GrossEarnings
'2', 'Rabbit, Roger', '80.00', NULL, NULL, NULL, '80.00', '25', '2000'
'1', 'Somebody, Joe', '80.00', NULL, NULL, NULL, '80.00', '50', '4000'

Answer 1

让我首先将您正在使用的sql压缩为更具可读性的内容：

Select e.employeeid as EmployeeID
  , Concat(e.LastName, ', ', e.FirstName) AS Resource
  , sum(case when p.projectid not in (24,29,606,614,746) then td.BillableHours else 0 end) as RegularHours
  , sum(case when p.projectid in (29,614) then td.BillableHours else 0 end) as PTO
  , sum(case when p.projectid = 24 then td.BillableHours else 0 end) as Holiday
  , sum(case when p.projectid = 746 then td.BillableHours else 0 end) as FloatingHoliday
  , sum(case when p.projectid != 606 then td.BillableHours else 0 end) as Total
  , EPR.Rate as PayRate
  , sum(case when p.projectid != 606 then td.BillableHours else 0 end) * EPR.Rate as GrossEarnings
From Employee e
  Join Timesheets t on e.EmployeeID = t.EmployeeID
  join TimesheetDetails td on t.TimeCardID = td.TimeCardID
  join Projects p on p.ProjectID = td.ProjectID
  join EmployeePayRate epr on epr.EmployeeID = e.EmployeeID
    and epr.StartDate <= td.WorkDate
where ((td.BillableHours > 0)
        or (e.Status = 'Active'))
  and td.WorkDate BETWEEN '2015-01-17' AND '2015-01-30'
Group by e.employeeid
  , Concat(e.LastName, ', ', e.FirstName)
  , EPR.Rate

如果这看起来是可以接受的，那么现在我们开始讨论加入员工薪酬的问题。我们正在从1到1到多到1，所以这可能会改变分组。问题是，如果一个雇员在一段时间内有两个(或更多)的薪酬，你想要显示他在每个工资率中所做的事情的细目吗？我看到你一次不做一周的工作，所以你没有计算加班，所以我猜这是一份加班前概览报告，用来粗略估计工资，所以知道有多少小时的工资等级可能并不重要。考虑到这一点，这里是我们如何改变加入：

  join EmployeePayRate epr on epr.EmployeeID = e.EmployeeID
    and epr.StartDate <= td.WorkDate

如果有薪资的开始日期和结束日期，则联接更改为

  join EmployeePayRate epr on epr.EmployeeID = e.EmployeeID
    and epr.StartDate <= '2015-01-17'
    and ((epr.endDate is null) or (epr.endDate >= '2015-01-17'))
  join TimesheetDetails td on t.TimeCardID = td.TimeCardID
    and ((epr.EndDate is null and td.WorkDate between epr.StartDate and '2015-01-30')
      or (epr.EndDate is not null and td.WorkDate between epr.StartDate and epr.EndDate))

您将注意到或语句，我们试图避免使用isnull()之类的函数，以便可以使用索引。这就引出了下一个问题，如果没有结束日期列，事情就会变得更加复杂。但是，与其写一个可能不需要的完整解决方案，不如让我知道如果是这样的话，我们会解决的。