Running total in reverse order.
-
I'm using Microsoft SQL 2000 and Enterprise Manager to write the code.
This is what I want to do. I have airplanes with different aircraft no’s and they fly on different dates. I want to add up all the flights in the table for a given airplane each time it hits a new date and reset the total when it gets to a new aircraft number.
The basic information already in the database is:
From the Flight_Log table Doc_No , Aircraft_Doc_No, Actual_Departing_Date
From the Flight_ Cycle_Count Flight_Log_Doc_No, Flight_Hours
I want to pull the Aircraft_Doc_no, Actual_Departing_Date, and the Flight_Hours. I want to calculate all of the flying time in the database for each aircraft every time a new flight is entered and I want to know all of the hours flown by the aircraft from that particular flight date. The total needs to reset once it hits a new aircraft.
For example: Aircraft 38 has flown 5 hours altogether and Aircraft 100 has flown 6. The table should look like this: (like a running total only in reverse order)
Aircraft_Doc_No Actual_Departing_Date Flight_Hours Total_Hours
38 8/12/04 2 5
38 9/19/05 1 3
38 9/21/06 2 2
100 9/04/06 1.5 6
100 10/04/06 1 4.5
100 10/06/06 2.5 3.5
100 10/08/06 1 1
This is the code that I pulled from the internet but I’m getting syntax errors and I just got an email stating that my code example was using Oracle analytic function (not Microsoft SQL 2000). Can anyone help me out.
select Aircraft_Doc_No
,Actual_departing_date,Flight_Hours
,sum(Flight_hours) over partition by airno
order by Actual_departing_Date
rows between current row and unbounded following) total
from (Select Distinct
F.Aircraft_Doc_No
,F.Actual_Departing_Date
,C.Flight_Hours
From GDB_01_4_Test.dbo.Flight_Log F,
GDB_01_4_Test.dbo.Flight_Cycle_Count C
Where F.Doc_No = C.Flight_log_Doc_No
-
just use the table to store the raw data, and use a VIEW to total up the information. if you try and store totals in the same row as the data, it'll just be a mess.
CREATE TABLE #TESTDATA (
Aircraft_Doc_No int,
Actual_Departing_Date datetime,
Flight_Hours MONEY ) --4 digit precision, money datatype is nice
INSERT INTO #TESTDATA
SELECT 38, '8/12/04', 2 UNION
SELECT 38, '9/19/05', 1 UNION
SELECT 38, '9/21/06', 2 UNION
SELECT 100, '9/04/06', 1.5 UNION
SELECT 100, '10/04/06', 1 UNION
SELECT 100, '10/06/06', 2.5 UNION
SELECT 100, '10/08/06', 1
select A.Aircraft_Doc_No,A.Actual_Departing_Date, SUM(B.Flight_Hours)
FROM #TESTDATA A
INNER JOIN #TESTDATA B ON A.Aircraft_Doc_No=B.Aircraft_Doc_No
WHERE A.Actual_Departing_Date <= b.Actual_Departing_Date
group by A.Aircraft_Doc_No,A.Actual_Departing_Date
order by A.Aircraft_Doc_No,A.Actual_Departing_Date
results:
Aircraft_Doc_No A.Actual_Departing_Date SUM(B.Flight_Hours) 38 2004-08-12 00:00:00.000 5.0000 38 2005-09-19 00:00:00.000 3.0000 38 2006-09-21 00:00:00.000 2.0000 100 2006-09-04 00:00:00.000 6.0000 100 2006-10-04 00:00:00.000 4.5000 100 2006-10-06 00:00:00.000 3.5000 100 2006-10-08 00:00:00.000 1.0000 final results as desired;
SELECT #TESTDATA.Aircraft_Doc_No,#TESTDATA.Actual_Departing_Date,#TESTDATA.Flight_Hours,X.SFlight_Hours
FROM #TESTDATA
INNER JOIN
(
select A.Aircraft_Doc_No,A.Actual_Departing_Date, SUM(B.Flight_Hours) AS SFlight_Hours
FROM #TESTDATA A
INNER JOIN #TESTDATA B ON A.Aircraft_Doc_No=B.Aircraft_Doc_No
WHERE A.Actual_Departing_Date <= b.Actual_Departing_Date
group by A.Aircraft_Doc_No,A.Actual_Departing_Date
 
XON #TESTDATA.Aircraft_Doc_No=X.Aircraft_Doc_No
AND #TESTDATA.Actual_Departing_Date = X.Actual_Departing_Date
order by #TESTDATA.Aircraft_Doc_No,#TESTDATA.Actual_Departing_Date
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
All of the data is already in a table. Can I build another table from the original table to pull the fields with the union statement the way you have with the insert into and select? I'm confused on whether to build another table, view, or stored procedure or a combination. My database is very large and I'm going to have to build some reports that will require a good deal of calculating so I'm trying to determine which method will return my data the quickest. I can't mess with any of the structure on the original tables.
So I guess at this point, I want to pull the desired fields from a table into another table in a way to get me the first table you have listed above so I can querry against that one. Can you help me with that syntax? I just finished up my first tutorial training program on SQL code and now I'm really confused...
-
based on your example data, I assumed that Flight 38 for example might have more records added at any time; because of that, if I created another table with the data we gathered from the previous example, as soon as a new record for flight 38 was added, the data in the new table would be incorrect.
For that reason, I cannot think of a better way than to use a view. The view will update instantly and automatically, whenever the base data changes. It is much more efficient than creating a "status" table, and updating the status periodically.
We can take the results of this data and join against other tables in this view as well. The view can have additional calculations of course; we were working with a simple example for now.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
I'm a little confused with your example. I understand TestData A is a table that I create (I did that). Is Testdata B a different table or the view that I'm creating. Also because of performance issues, I am trying to figure out how to either update a table at night and run my reports against the updated table or run an updated stored procedure at night.
I've got the first part working except the union: (Can I get a Union statement working with the Actual_Hours, I'm getting syntax errors when I try.)
use dynamicav_custom
create table tbl_Sandy_test (Aircraft_Doc_No int, Doc_No int, Actual_Departing_Date datetime, Actual_Hours dec (12))
insert into tbl_Sandy_test (Aircraft_Doc_No, Doc_No, Actual_Departing_Date, Actual_Hours)
select Aircraft_doc_No, Doc_No, Actual_Departing_Date, Actual_Hours
from gdb_01_4_test.dbo.flight_log
group by aircraft_doc_No, doc_No, Actual_Hours, Actual_Departing_Date, Actual_Hours
order by Aircraft_doc_No, Actual_Departing_Date
Thanks
-
sorry about the confusion.
in my sample, these two tables are the same: #TESTDATA = gdb_01_4_test
i didn't have the data, so i had to create it to confirm that my sample worked.
you seem very insistent on copying the data to a table, instead of using a view to get the data. is there a reason I'm missing as to why you don't want to query the data directly?
what the sql below does is joins the gdb_01_4_test on itself to give you the results you are looking for; the virtual table B(and the final results X) is the alias for the same table so it can be used twice to get the kind of info you were looking for.
i did a find and replace in my sample sql, and then turned the sample sql into a view.
try this and tell me whether it works or not:
use dynamicav_custom
CREATE VIEW VW_SANDY_TEST AS
SELECT gdb_01_4_test.Aircraft_Doc_No,gdb_01_4_test.Actual_Departing_Date,gdb_01_4_test.Flight_Hours,X.SFlight_Hours
FROM gdb_01_4_test
INNER JOIN
(
select A.Aircraft_Doc_No,A.Actual_Departing_Date, SUM(B.Flight_Hours) AS SFlight_Hours
FROM gdb_01_4_test A
INNER JOIN gdb_01_4_test B ON A.Aircraft_Doc_No=B.Aircraft_Doc_No
WHERE A.Actual_Departing_Date <= b.Actual_Departing_Date
group by A.Aircraft_Doc_No,A.Actual_Departing_Date )
X
ON gdb_01_4_test.Aircraft_Doc_No=X.Aircraft_Doc_No
AND gdb_01_4_test.Actual_Departing_Date = X.Actual_Departing_Date
GO
select * from VW_SANDY_TEST order by
Aircraft_Doc_No,Actual_Departing_Date
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
I really don't know enough to decide if View is better than table but we have performance problems so I was thinking that I would create the table and then somehow update the table at night and run all of my reports from the updated table. That way the database would only be hit one time during off hours. I've just been doing this stuff for 3 weeks so I'm really at the mercy of my peers who know more than I do.
It won't let me use the Use GDB_01_4_test statement. I'm creating these views, tables, and stored procedures in a temporary database so I have to reference the complete database and table name in the SQL statements (or at least I think I do). I'm getting error messages. You are really helping me out. I'm actually starting to understand some of this. Do you know what I'm doing wrong? Thanks
CREATE VIEW VW_SANDY_TEST AS
SELECT A.Aircraft_Doc_No,A.Actual_Departing_Date,A.Actual_Hours,X.SActual_Hours
FROM gdb_01_4_test.dbo.Flight_Log A
INNER JOIN
(
select A.Aircraft_Doc_No,A.Actual_Departing_Date, SUM(B.Actual_Hours) AS SFlight_Hours
FROM gdb_01_4_test.dbo.Flight_Log A
INNER JOIN gdb_01_4_test.dbo.Flight_Log B ON A.Aircraft_Doc_No=B.Aircraft_Doc_No
WHERE A.Actual_Departing_Date <= b.Actual_Departing_Date
group by A.Aircraft_Doc_No,A.Actual_Departing_Date )
X
ON gdb_01_4_test.dbo.Flight_Log Aircraft_Doc_No=X.Aircraft_Doc_No
AND gdb_01_4_test.dbo.Flight_Log Actual_Departing_Date = X.Actual_Departing_Date
GO
select * from VW_SANDY_TEST order by
Aircraft_Doc_No,Actual_Departing_Date
I'm getting the following error messages:
Server: Msg 170, Level 15, State 1, Procedure VW_SANDY_TEST, Line 12
Line 12: Incorrect syntax near 'Aircraft_Doc_No'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'VW_SANDY_TEST'.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply