使用c#和SQL检查日期范围的最有效方法

本文关键字：有效 方法 范围 日期 SQL 检查 使用 | 更新日期: 2023-09-27 18:19:00

我正在为我们公司正在进行的笔记本电脑更新编写预订系统。我需要能够计算哪些天有可用的插槽，并返回它们在日历控件上呈现(即，如果一天有可用的日子，它是可选择的，否则不是。

逻辑如下:

  • 一个技术人员每天可以制造3台笔记本电脑。
  • 每天可能有1、2或3名技术人员可用。
  • 一个表将保存已做的预订

每天可用的插槽总数为(伪代码):

((laptops per day) * (technicians available)) - slots already booked

我的问题是，得到这个最有效的方法是什么?我想最好在SQL端完成，用一个函数返回带有可用槽的日期表。(不管有多少个，只要至少有一个槽是可用的。)

到目前为止，我可以理解所有这些。我所坚持的一点是，我不想拥有一个包含所有可能日期的表，因为它看起来有点低效。我希望能够做的是有效地迭代从现在到未来3个月的日期范围，并从中计算可用的日期。

我可以在c#中做到这一点，但它让我觉得效率低下，因为它必须每天都要访问SQL服务器。似乎在SQL方面做得更好，但我不知道如何以这种方式迭代可能的日期。

解决方案

使用@Chris的方法，我可以得到一个日期范围，其中插槽书不超过设置的最大值:

DECLARE @startDate DATE
DECLARE @endDate DATE
SET @startDate = GETDATE()
SET @endDate = DATEADD(m,3,@startDate)
;
WITH dates(Date) AS 
(
    SELECT @startdate as Date
    UNION ALL
    SELECT DATEADD(d,1,[Date])
    FROM dates 
    WHERE DATE < @enddate
)
SELECT Date
FROM dates 
EXCEPT
SELECT date
        FROM tl_sb_booking
        GROUP BY date
        HAVING COUNT(date) < 3

这只是设置了3个预订的任意最大值。下一步是添加技术人员可用性!

使用c#和SQL检查日期范围的最有效方法

这个答案解决了问题的一部分:"我希望能够有效地迭代从现在到未来3个月的日期范围，并从中计算可用的日期。"

如果您正在使用MSSQL，那么cte可能会有所帮助。要像下面这样动态地生成日期范围代码，可以使用:

DECLARE @startDate DATE
DECLARE @endDate DATE
SET @startDate = GETDATE()
SET @endDate = DATEADD(m,3,@startDate)
;
WITH dates(Date) AS 
(
    SELECT @startdate as Date
    UNION ALL
    SELECT DATEADD(d,1,[Date])
    FROM dates 
    WHERE DATE < @enddate
)
SELECT Date
FROM dates

最后一个SELECT可以连接到你的其他表来获取该日期的数据，做计算，等等。

然而，这并不一定是最好的方法。假设你有办法获得每天有多少工程师有空的列表，那么就不需要迭代任何其他日期(因为你知道这些日期将没有可用性)。因此，为了达到这个目的，像这样:

SELECT count(1), Date
From EngineerWorkDays
GROUP BY Date
Where Date>= Getdate()
and Date < DATEADD(m,3,Getdate())

将返回有可能完成工作的日期列表。另一个选择来获得已经分配的工作，然后将为您提供所需的所有数据。在c#的显示代码中，你可以每天迭代并检查你的数据集是否有任何相关数据(工程师是否可用，有多少，已经预订了多少工作，等等)。

如果这是一个共享的应用程序，请不要忘记在预订之前检查日期是否还有空间，以防其他人在您完成初始查询后预订了某些内容

试试这个:

Select [DatesAvailable] From TableName
Where [DatesAvailable] Between BeginningDate And DateAdd(m, 3, BeginningDate) And
      SlotsAvailable > 0

这应该会给你未来3个月有空位的任何日期…

有一种方法:

DECLARE @iToday As INT; SET @iToday = CAST(GetDate() As INT);
DECLARE @iDays As INT;  SET @iDays = 90;
WITH cte_0to9 As
(
    Select 0 As Num UNION ALL Select 1 UNION ALL Select 2 UNION ALL Select 3
    UNION ALL Select 4 UNION ALL Select 5 UNION ALL Select 6 UNION ALL Select 7
    UNION ALL Select 8  UNION ALL Select 9
)
, cte_0to99 As
(
    SELECT  (Tens.Num * 10) + Ones.Num As Num
    FROM        cte_0to9 As Ones
    CROSS JOIN  cte_0to9 As Tens
)
, cteDays As 
(
    SELECT  CAST(@iToday + Num As DATETIME) As PossibleDate
    FROM    cte_0to99
    WHERE   Num <= @iDays
)
, cteTechnicianUtilization As
(
    SELECT  t.Technician,
            d.PossibleDate,
            (Select COUNT(*) From Bookings b
             Where b.Technician = t.Technician
               And b.BookDate = d.PossibleDate) As SlotsUsed
    FROM    Technicians As t
    CROSS JOIN cteDays d
)
SELECT  PossibleDate, SUM(Util-3) As TotalAvailableSlots
FROM    cteTechnicianUtilization
WHERE   Util < 3
GROUP BY PossibleDate
HAVING  SUM(Util-3) > 0

请注意，这种方法虽然较长(代码方面)，但比使用递归更有效。

相关文章：