| View previous topic :: View next topic |
| Author |
Message |
mixt
Newbie
Joined: 04 May 2012
Posts: 2
|
 Posted: Fri May 04, 2012 7:51 pm Post subject: Calculations and Lead-In Record |
 |
|
So I'm working on a self proposed project of sorts. My professor doesn't quite understand the concept of a relational database and deemed a textbook example wrong simply because it was One-to-Many instead of One-to-One. That was an easy hurdle but there is some manipulation of data I don't get, and might not be possible in OOo.
So the idea is that we start with an Inventory table [Product Number, Current Stock] and a Shipment table [Order Number, Product Number, Shipment Date, Quantity] and query (or otherwise produce) a table showing the projected inventory [Date, Received Deliveries, Projected Stock]. This means...
1) I need a line showing what is currently in stock, but that doesn't correspond to any incoming shipment.
2) I need to calculate what the stock would be based off of that days deliveries and the projected stock of the previous (including the dummy record of point one.
Are those possible in OOo, or would this require some sort of exporting to a program that can? I mean I could probably build an overly simplistic Java program to make a CSV table how I need it to look. |
|
| Back to top |
|
 |
dacm
Super User
Joined: 07 Jan 2010
Posts: 734
|
|
| Back to top |
|
|
mixt
Newbie
Joined: 04 May 2012
Posts: 2
|
| Posted: Fri May 04, 2012 8:30 pm Post subject: |
|
|
| Not for a grade, no. It just annoyed me that he called the textbook wrong so casually, and since I've disagreed with him on other things in the class I wanted to see if he was right about this. I figured the best way to show the textbook as being correct would be to reproduce the example. If I show him this it is about as likely to piss him off as it is going to do my any favors. |
|
| Back to top |
|
|
caravas
General User
Joined: 05 Sep 2011
Posts: 31
|
| Posted: Sat May 05, 2012 4:31 am Post subject: |
|
|
Well,
Let me see if I got your point:
You need a query to sum your stock and future stock according to your shipment delivery table, using a textbox to set a specific date?
Try something like this:
| Code: | | SELECT ”ProductTable”.“Product Number”, SUM(”ProductTable”.“Current Stock”) AS PRESENTSTOCK, (SELECT SUM("ShipmentTable"."Quantity") FROM "ShipmentTable" WHERE( ”ProductTable”.“Product Number” = ”ShipmentTable”.“Product Number” AND ”ShipmentTable”.“Shipment Date” <= ?)) AS FORECASTDELIVERY, (SUM(”ProductTable”.“Current Stock”) + ISNULL((SELECT SUM("ShipmentTable"."Quantity") FROM "ShipmentTable" WHERE( ”ProductTable”.“Product Number” = ”ShipmentTable”.“Product Number” AND ”ShipmentTable”.“Shipment Date” <= ?)),0)) AS TOTALONTHATDAY FROM “ProductTable” GROUP BY ”ProductTable”.“Product Number” ORDER BY ”ProductTable”.“Product Number” ASC |
You must to use this SQL code, that I believe it will works nicely, straight on SQL query design on Base. Do not use the query constructor, it will mess the SQL code. I've never used this kind of SQL in embedded databases, which I presume is your case.
You might want to create a form based on this query, and with some back code, you will be able to fill up the date value and requery your database.
Look out about date standards of hsqldb: “yyyy-mm-dd”!!
Regards. |
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
Powered by phpBB © 2001, 2005 phpBB Group
|
