Help with Pilot Semi-annual Tracking SQL


K

KiowaPilot

I track flight time for 30+ Aviators, who have minimums to maintain based on
their birthmonth. I am looking to automatically calculate on a report/query
the ammount of time that they have flown within a 6 month timeframe based on
their b-day, and the current date.
For example Aug is my birthmonth and it is Currently Feb. I would only want
to calculate the data between 31 Aug and 1 Mar because todays date of 25 Feb
falls between that range. And if todays date were 3 Mar it would calculate
the data between 28 Feb and 1 Sep.

I have two tables and I will put sample data in order to get better help.
1. “Flight Inputâ€
Lname Flt_Date ACFT_MDS FS_ID HRS
Joel 8/4/08 2B-24 W 2.0
Billy 8/4/08 UH-1 H 2.5
Mike 10/24/08 UH-1 D 3.0
John 12/9/08 UH-1 N 2.0
Billy 12/10/08 2B-24 W 1.5
Joel 12/17/08 UH-1 NG 3.5
Chris 1/08/09 UH-1 H 1.0
Mike 2/13/09 UH-1 D 2.5

AND table 2 “PILOTSâ€

LNAME BIRTHMON
Joel 5
Billy 12
Mike 1
John 11
Chris 8

The end result would look like this.

Lname Birthmon SumOf2B-24 SumOfD SumOfN SumOfNG SumOfW SumOf H
Billy 12 0 0 0 0 0 0
Chris 8 0 0 0 0 0 1.0
Joel 5 0 0 0 3.5 0 0
John 11 0 0 2.0 0 0 0
Mike 1 0 2.5 0 0 0 0

Thank You,
 
Ad

Advertisements

K

KiowaPilot

with this I was able to get the information that i wanted, but It comes out
vertically and not Horizontally like I wanted.
Suggestions would be most welcomed.

SELECT DISTINCTROW Pilots.LName, Sum(Flight_Input.HRS) AS Totals,
Flight_Input.FS_ID
FROM Pilots LEFT JOIN Flight_Input ON Pilots.Lname=Flight_Input.Lname
WHERE ((Flight_Input.FLT_Date) Between [Start1stSemi] And [End1stSemi]) And
(date()) Or ((Flight_Input.FLT_Date) Between [Start2ndSemi] And [End2ndSemi])
And (date())
GROUP BY Pilots.LName, Flight_Input.FS_ID
ORDER BY Pilots.LName;
 
Ad

Advertisements

K

KiowaPilot

TRANSFORM Sum(Flight_Input.HRS) AS SumOfHRS
SELECT Flight_Input.LName, Sum(Flight_Input.HRS) AS [Total Of HRS]
FROM Pilots LEFT JOIN Flight_Input ON Pilots.Lname = Flight_Input.Lname
WHERE (((Flight_Input.FLT_Date) Between [Start1stSemi] And [End1stSemi]) AND
((Date())<>False)) OR (((Flight_Input.FLT_Date) Between [Start2ndSemi] And
[End2ndSemi]) AND ((Date())<>False))
GROUP BY Flight_Input.LName
PIVOT Flight_Input.FS_ID;

In the Pilots Table I added 4 colums, Start and End Dates for each
Semi-Annual Period. I used a Form to assist in automatically calculating the
end of each month and Left it up to me to change the Year since it Changes
"semi" frequently.




KiowaPilot said:
with this I was able to get the information that i wanted, but It comes out
vertically and not Horizontally like I wanted.
Suggestions would be most welcomed.

SELECT DISTINCTROW Pilots.LName, Sum(Flight_Input.HRS) AS Totals,
Flight_Input.FS_ID
FROM Pilots LEFT JOIN Flight_Input ON Pilots.Lname=Flight_Input.Lname
WHERE ((Flight_Input.FLT_Date) Between [Start1stSemi] And [End1stSemi]) And
(date()) Or ((Flight_Input.FLT_Date) Between [Start2ndSemi] And [End2ndSemi])
And (date())
GROUP BY Pilots.LName, Flight_Input.FS_ID
ORDER BY Pilots.LName;


KiowaPilot said:
I track flight time for 30+ Aviators, who have minimums to maintain based on
their birthmonth. I am looking to automatically calculate on a report/query
the ammount of time that they have flown within a 6 month timeframe based on
their b-day, and the current date.
For example Aug is my birthmonth and it is Currently Feb. I would only want
to calculate the data between 31 Aug and 1 Mar because todays date of 25 Feb
falls between that range. And if todays date were 3 Mar it would calculate
the data between 28 Feb and 1 Sep.

I have two tables and I will put sample data in order to get better help.
1. “Flight Inputâ€
Lname Flt_Date ACFT_MDS FS_ID HRS
Joel 8/4/08 2B-24 W 2.0
Billy 8/4/08 UH-1 H 2.5
Mike 10/24/08 UH-1 D 3.0
John 12/9/08 UH-1 N 2.0
Billy 12/10/08 2B-24 W 1.5
Joel 12/17/08 UH-1 NG 3.5
Chris 1/08/09 UH-1 H 1.0
Mike 2/13/09 UH-1 D 2.5

AND table 2 “PILOTSâ€

LNAME BIRTHMON
Joel 5
Billy 12
Mike 1
John 11
Chris 8

The end result would look like this.

Lname Birthmon SumOf2B-24 SumOfD SumOfN SumOfNG SumOfW SumOf H
Billy 12 0 0 0 0 0 0
Chris 8 0 0 0 0 0 1.0
Joel 5 0 0 0 3.5 0 0
John 11 0 0 2.0 0 0 0
Mike 1 0 2.5 0 0 0 0

Thank You,
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top