选择两个日期之间的数据，但不包括某些天 [英] Select data between two dates exclude some days

问题描述

我需要进行查询，以选择两个日期之间的数据，但不包括工作日和其他一些日子，例如公共假期.

I need to make query that selects data between two dates but exclude weekdays and some other days that are for example public holidays.

SELECT 
`tblproduction`.`OperaterID`, `tblproduction`.`OperationID`, 
SUM(`tblproduction`.`TotalProduced`) AS TotalProduced, 
SUM(`tblproduction`.`TotalProducedOperator`) AS TotalProducedOp,
'Normal working day' AS DayType
FROM `tblproduction` 
WHERE tblproduction.StartDateTime >= '2015-02-01 00:00:00' AND (tblproduction.StartDateTime <= '2015-02-28 23:59:59') AND tblproduction.OperaterID = 10
AND (DAYOFWEEK(tblproduction.StartDateTime) IN (1,7)) AND (DATE(tblproduction.StartDateTime) NOT IN (SELECT HolidayDate FROM tblholidays))
GROUP BY `tblproduction`.`OperaterID`, `tblproduction`.`OperationID`
UNION ALL
SELECT 
`tblproduction`.`OperaterID`, `tblproduction`.`OperationID`, 
SUM(`tblproduction`.`TotalProduced`) AS TotalProduced, 
SUM(`tblproduction`.`TotalProducedOperator`) AS TotalProducedOp,
'Weekend' AS DayType
FROM `tblproduction` 
WHERE tblproduction.StartDateTime >= '2015-02-01 00:00:00' AND (tblproduction.StartDateTime <= '2015-02-28 23:59:59') AND tblproduction.OperaterID = 10
AND (DAYOFWEEK(tblproduction.StartDateTime) NOT IN (1,7)) AND (DATE(tblproduction.StartDateTime) NOT IN (SELECT HolidayDate FROM tblholidays))
GROUP BY `tblproduction`.`OperaterID`, `tblproduction`.`OperationID`

为此，我有一个带有预定义日期的表，该表代表名为tblHolidays的公共假期. sql查询的条件之一是避免计算假日期间制造的零件.通过运行此查询，钢将包括那些假期的日期.我应该在查询中更改什么?

For that purpose i have table with predefined dates that represents public holidays named tblHolidays. One of criteria in sql query is avoid counting pieces made during the holiday date. By running this query is steel includes those dates that are holidays. What should I change in query?

推荐答案

也许从此开始...

SELECT p.OperaterID
     , p.OperationID
     , SUM(p.TotalProduced) TotalProduced
     , SUM(p.TotalProducedOperator) TotalProducedOp
     , CASE WHEN DAYOFWEEK(p.startdatetime) IN (1,7) THEN 'Normal working day' ELSE 'Weekend' END DayType
  FROM tblproduction p
 WHERE p.StartDateTime >= '2015-02-01 00:00:00' AND p.StartDateTime <= '2015-02-28 23:59:59'
   AND p.OperaterID = 10
   AND DATE(p.StartDateTime) NOT IN (SELECT HolidayDate FROM tblholidays)
 GROUP 
    BY p.OperaterID
     , p.OperationID
     , CASE WHEN DAYOFWEEK(p.startdatetime) IN (1,7) THEN 'Normal working day' ELSE 'Weekend' END

这篇关于选择两个日期之间的数据，但不包括某些天的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！