设计决策：部分日期的表模式为了计算时间跨度（SQL Server） [英] Design decision: Table schema for partial dates in order to calculate time spans (SQL Server)

问题描述

我有一个情况，时间跨度将根据飞行员的出生日期计算。

飞行员必须每6个月获得这么多小时期。我很烦，他们不是所有的1月 - 6月和7月 - 12月，但显然他们必须这样的事情按他们的出生日期。

这里是一个参考表：

现在，我需要能够根据每个飞行员的两个周期计算出每个飞行员的所有飞行小时数。例如，我的生日是10月23日。这意味着我的前6个月期间是11月1日至4月（明年）。我正在寻找任何关于如何创建参考表的想法，这将允许我轻松（或尽可能容易地）计算特定时间段的飞行时间。

这里是我到目前为止：

其中birthMonth和startYear一起是复合主键。 startYear是期间开始的年份。

birthMonth是一个NVARCHAR（50），startYear一个Int，其余的都是Dates。

我的飞行员表有birthDate作为完整的日期，然后飞行经理将能够选择startYear。你怎么看？我应该将出生月份和年份结合到同一个领域吗？我只是试图制定一个模式，将轻松总结一个特定飞行员的半年和每年的飞行小时。也许我可以分解日期不包括年，然后动态添加？我只是认为这个设计中有缺陷，我不在想。

编辑：这里是我目前工作的所有表：
< img src =https://i.stack.imgur.com/wPtbK.jpgalt =enter image description here>

解决方案

你真的需要一张表吗？

  declare @birthday datetime ='2012-01-09'
 
 SELECT DATEADD（dd， - （DAY（DATEADD（m，1，@ birthday）） -  1），DATEADD（m，1，@ birthday））firstSixMonthStart，
 DATEADD （DATEADD（m，1，@ birthday）），DATEADD（m，7，@ birthday））firstSixMonthEnd，
 DATEADD（dd， - （DAYADD DATEADD（m，7，@ birthday））secondSexMonthStart，
 DATEADD（dd，-DAY（DATEADD（m，1，@ birthday）），DATEADD（m，13，@ birthday））secondSixMonthEnd 
 
 firstSixMonthStart firstSixMonthEnd secondSexMonthStart secondSixMonthEnd 
 ----------------------- ---------------- ------- ----------------------- -------------------- --- 
 2012-02-01 00：00：00.000 2012-07-31 00：00：00.000 2012-08-01 00：00：00.000 2013-01-31 00：00：00.000`
  

I have a situation where time spans will be calculated by according to a pilot's date of birth.

Pilots have to get so many hours and such in every 6 month period. I'm annoyed that they aren't all January-June and July-December, but apparently they have to do this sort of thing by their birth date.

Here's a reference table:

Now, I need to be able to calculate all the flight hours for each pilot based on their two periods. For example, my birthday is October 23rd. This means my first 6 month period is November 1st through the end of April (of the next year). I'm looking for any ideas on how to create a reference table that will allow me to easily (or as easily as possible) calculate the flight hours for a particular time period.

Here's what I've got so far:

Where birthMonth and startYear together are a composite primary key. startYear is what year the period starts in.

birthMonth is a NVARCHAR(50), startYear an Int, and the rest are Dates.

My pilot table with have birthDate as a full date, and then the flight manager will be able to select the startYear. What do you think? Should I combine the birth month and year into the same field? I'm just attempting to make a schema that will make for easy summing of a particular pilot's flight hours for their semi-annual and annual. Maybe I could break up the date to not include the year, and then add it dynamically? I just think there are flaws in this design that I'm not thinking of.

EDIT: Here's all the tables I'm currently working on:

解决方案

Do you really need a table?

declare @birthday datetime = '2012-01-09'

SELECT  DATEADD(dd,-(DAY(DATEADD(m,1,@birthday))-1),DATEADD(m,1,@birthday)) firstSixMonthStart,
        DATEADD(dd, -DAY(DATEADD(m,1,@birthday)), DATEADD(m,7,@birthday)) firstSixMonthEnd,
        DATEADD(dd,-(DAY(DATEADD(m,1,@birthday))-1),DATEADD(m,7,@birthday)) secondSexMonthStart,
        DATEADD(dd, -DAY(DATEADD(m,1,@birthday)), DATEADD(m,13,@birthday)) secondSixMonthEnd

firstSixMonthStart      firstSixMonthEnd        secondSexMonthStart     secondSixMonthEnd
----------------------- ----------------------- ----------------------- -----------------------
2012-02-01 00:00:00.000 2012-07-31 00:00:00.000 2012-08-01 00:00:00.000 2013-01-31 00:00:00.000`

这篇关于设计决策：部分日期的表模式为了计算时间跨度（SQL Server）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！