在特定时间段内选择所有可用项目

Question

在特定时间段内选择所有可用项目

5

所以我有两个表格 caring 和 client，就像这样

client {
  id,
  name
}

caring {
  id,
  startDate,
  endDate,
  clientId
}

我需要获取在提供的两个日期之间至少有一天可用的所有客户端，您可以参考我的截图。

在截图中，我有两个客户，需要将它们都返回。正如您所看到的，第一个客户在提供的期间（16.5.-29.5.）之间有三天空闲时间（21.5.-23.5.），而第二个客户没有任何关注期。

到目前为止，我尝试了类似这样的东西

SELECT * FROM client cl
WHERE cl.id NOT IN (SELECT clientId FROM caring 
WHERE endDate >= CURDATE() AND endDate <= DATE_ADD(CURDATE(), INTERVAL 14 DAY))

这个查询仅返回没有任何护理的客户。这部分是我需要的，因为这个查询不包括我截图中的第一个客户。然后我尝试了下面的查询。

SELECT ca.startDate, ca.endDate, cl.firstName, cl.lastName
FROM caring ca
LEFT JOIN client cl on cl.id = ca.clientId
WHERE ca.startDate NOT IN (
    SELECT endDate
    FROM caring 
) AND ca.startDate <= '2017-05-29' AND ca.endDate >= '2017-05-16'

但我没有得到期望的结果。

有什么想法可以实现这个目标吗？谢谢！

- Jasko

1

我有一个解决方案（通过差异计算天数+分组/求和），我将在晚上发布作为答案... - fheub

感谢您的解决方案... :) - Jasko

2个回答

1

选择那些结束日期早于所提供时间段的最后一天，并且在指定时间段内，结束日期和开始日期之间存在间隔的客户。

SELECT * FROM client FULL OUTER JOIN caring ON client.id = caring.clientId WHERE endDate <= '2017-05-28' AND DATEDIFF(day, startDate, endDate) > DATEDIFF(day,  '2017-05-16' , endDate);

- Jorge Barrios

1

我不确定这会如何运作，如果我正确理解问题，他想要在特定时间内拥有至少一天空闲（日历上的白色字段）的所有客户。 - Sahbaz

谢谢您的帮助，但是您的解决方案对我的情况不起作用... :) fheub 的上面的解决方案非常完美... :) - Jasko

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- fheub · Accepted Answer

在感兴趣的时期内选择“关心的事项”，并将开始/结束日期限制在该时期内。此限制将使后面更容易计算“已预订”的即非免费日期。请保留 HTML 标签。

SELECT ca.id,
       -- Limit start/end dates to period of interest, respectively
       GREATEST (ca.startDate, '2017-05-16') AS `effectiveStartDate`,
       LEAST (ca.endDate, '2017-05-29') AS `effectiveEndDate`,
       ca.clientId
  FROM carings ca
 WHERE ca.startDate <= '2017-05-29' AND ca.endDate >= '2017-05-16';

接下来，计算预订天数：

DATEDIFF (DATE_ADD (LEAST (ca.endDate, '2017-05-29'), INTERVAL 1 DAY),
          GREATEST (ca.startDate, '2017-05-16'))
   AS `effectiveDays`

最后，过滤掉在整个时间段内都被预订的客户。这可以通过比较以下两个值来完成：

每个客户预定天数的总和（GROUP BY）与
整个时间段的天数（HAVING sumDays < DATEDIFF(...)）。

由于您还需要包括在整个时间段内未被预订的客户，因此建议从clients表开始，并“仅”LEFT JOIN（有效的）carings：

  SELECT cl.id, cl.name, IFNULL (SUM (eca.effectiveDays), 0) AS `sumDays`
    FROM clients cl
         LEFT JOIN
         (SELECT ca.id,
                 -- Limit start/end dates to period of interest, respectively
                 GREATEST (ca.startDate, '2017-05-16') AS `effectiveStartDate`,
                 LEAST (ca.endDate, '2017-05-29') AS `effectiveEndDate`,
                 DATEDIFF (
                    DATE_ADD (LEAST (ca.endDate, '2017-05-29'), INTERVAL 1 DAY),
                    GREATEST (ca.startDate, '2017-05-16'))
                    AS `effectiveDays`,
                 ca.clientId
            FROM carings ca
           WHERE ca.startDate <= '2017-05-29' AND ca.endDate >= '2017-05-16')
         eca                                               -- effectiveCarings
            ON eca.clientId = cl.id
GROUP BY cl.id, cl.name
  HAVING sumDays <
            DATEDIFF (DATE_ADD ('2017-05-29', INTERVAL 1 DAY), '2017-05-16')
ORDER BY cl.id;

参见http://sqlfiddle.com/#!9/1038b9/19

（也可查看）