仪器预订系统 [英] Instrument booking system

问题描述

亲爱的先生/女士，



我希望这封电子邮件能帮到你。



我是使用MS Access拥有仪器预订系统。我有11个仪器名为HPLC1到HPLC11，现在我有一个表预订，其中包含

hplcname，startdate（日期时间）和enddate（日期时间）。我想要一个查询，它只返回输入特定日期时可用的

时间。例如，我输入了30/08/2013，它将返回所有未预订的可用时间。



现在我要做的就是拥有一张桌子hplc名称列表，然后是一个包含15minuites时隙的表，持续24小时。



然后查询将列出所有hplc名称和15分钟的插槽，这些都不会发生在预订然后将插槽分组在一起。



我认为这是我需要的，但我真的知道如何实际编写查询。



感谢您的时间和耐心。



最好的问候，

Michael Galea

Dear Sir/Madame,

I hope this email finds you well.

I am using MS Access to have an instrument booking system. I have 11 instruments named HPLC1 to HPLC11, now I have a table booking which contains
The hplcname, startdate (date time) and enddate(date time). I would like to have a query which will return only the available
Time when inputting a particular date. For example I input 30/08/2013 and it will return all the available time which is not booked.

Now what I tought of doing is having a table with the list of hplc names, then a table with 15minuites timeslots for 24hr time.

Then a query will list all the hplc names and 15minutes slots which does not happen to occur in the booked and then grouping the slots together.

I think this is what I need but I dnt really know how to actually write the query.

While thanking you for your time and patience.

Best Regards,
Michael Galea

推荐答案

0）我强烈建议使用真正的数据库而不是Access - SQL Server Express就足够了。

1）你的建议是一个非常简单的设计，不会有太大的增长，所以如果这是一个真正的系统，而不仅仅是一个课堂练习，我建议做一个更健壮的设计。

2）我在SQL Server中编写了Instrument和Booking表，用几条记录填充它们，然后编写了一个查询来执行你想要的操作，但可能无法使用Access。



基本上它使用一条记录的EndTime和下一条记录的StartTime创建记录 - 我不知道如何使用带有Access的SQL：

0) I strongly recommend using a "real" database rather than Access -- SQL Server Express should suffice.
1) What you propose is a very simplistic design which won't allow much growth, so if this is a real system rather than just a class exercise, I suggest making a more robust design.
2) I whipped up Instrument and Booking tables in SQL Server, populated them with a few records, and wrote a query that does what you want, but probably won't work with Access.

Basically it makes records with the EndTime of one record and the StartTime of the next -- I don't know how to do that using SQL with Access:

DECLARE @sought DATE='2013-08-29'
;
WITH cte0 AS
(
  SELECT [Name] 
  , CASE WHEN CAST(StartTime AS DATE)<@sought THEN @sought ELSE StartTime END StartTime
  , CASE WHEN CAST(EndTime AS DATE)>@sought THEN DATEADD(dd,1,@sought) ELSE EndTime END EndTime
  FROM Booking
  WHERE CAST(StartTime AS DATE)<=@sought
  AND CAST(EndTime AS DATE)>=@sought)
, cte1 AS
(
  SELECT *
  FROM cte0
UNION ALL
  SELECT A.[Name] 
  , NULL
  , @sought
  FROM Instrument A
  LEFT OUTER JOIN cte0 B
  ON A.[Name]=B.[Name]
  AND B.StartTime=@sought
  WHERE B.[Name] IS NULL
UNION ALL
  SELECT A.[Name] 
  , DATEADD(dd,1,@sought)
  , NULL
  FROM Instrument A
  LEFT OUTER JOIN cte0 B
  ON A.[Name]=B.[Name]
  AND CAST(B.EndTime AS DATE)=DATEADD(dd,1,@sought)
  WHERE B.[Name] IS NULL
)
, cte2 AS
(
  SELECT *
  , ROW_NUMBER() OVER ( PARTITION BY [Name] ORDER BY StartTime ) RN 
  FROM cte1
)
SELECT A.[Name]
, A.EndTime
, B.StartTime
FROM cte2 A
INNER JOIN cte2 B
ON A.[Name]=B.[Name] 
AND A.RN+1=B.RN 
AND A.EndTime!=B.StartTime
ORDER BY A.[Name] 
, A.EndTime

CTE和ROW_NUMBER（）是我最新的锤子，你的问题就像钉子一样。





现在处理跨越午夜的预订甚至整个要求的一天。你考虑过吗？

CTEs and ROW_NUMBER() are my latest hammers and your question looks like a nail.


Now handles bookings which span midnight or even the entire requested day. Have you considered that?

这篇关于仪器预订系统的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！