0

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:

enter image description here

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:

enter image description here

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: enter image description here

  • You need to show the schema for the data you are totalling. – BonyT Jul 19 '12 at 15:46
  • I think maybe it'd be easier to do that calculation when you're actually presenting the data. It might be harder to try and do this all on the database side. – jeschafe Jul 19 '12 at 15:48
  • @BonyT I've added the other schema information. At jeschafe: You're probably right, I'm just thinking that as I am a novice, I may be overlooking some tricks sql masters have up their sleeves. –  Jul 19 '12 at 15:56
  • How are you querying the data - do you want the totals for individual pilots, or all the pilots as a report? – BonyT Jul 19 '12 at 15:58
  • @BonyT - It's going to be both. They want a Letter of X, which lists all pilots along with their total MC hours, as well as their total flight hours (which includes MC hours). Then, I'm going to be doing an individual profile page, which will list a particular pilot's current totals as well as their history –  Jul 19 '12 at 16:09
  • what version of SQL server are you using? – BonyT Jul 19 '12 at 16:22

1 Answers1

2

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`
paul
  • 21,653
  • 1
  • 53
  • 54