|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
Dell PowerEdge Servers
|
|
#1
April 12th, 2003, 12:32 PM
|
|||
|
|||
|
Oracle Need simple totals for day and by week
How do I do a query that'll give a sum of something by day for a week?
Output for would need to be... Code:
Quantity On Hand
04/01/03 04/02/03 04/03/03 TOTAL
PartA 5 10 25 40
PartB 1 5 5 11
GrandTL 6 15 30 51
Last edited by vrkelley : April 12th, 2003 at 12:41 PM.
|
|
#2
April 12th, 2003, 02:47 PM
|
||||
|
||||
|
you'll need something that can produce "crosstab" output -- some report writer or reporting utility -- because doing it in sql is really difficult
it's easy to produce daily and weekly sums, and even both in the same query, but not to arrange daily sums across the line like that, with weekly totals on the right oracle has a paramater for one of its date functions that extracts the ISO week number for any date see Grouping output by week in your case you'd want to GROUP BY the daily date as well as the week number, and let the reporting program do its crosstabbing by week rudy http://r937.com/
|
|
#3
April 12th, 2003, 03:34 PM
|
|||
|
|||
|
>you'll need something that can produce "crosstab" output
GAHK! The data has no history stuff of what the inventory looked like last week etc...only what's on the shelves with cur date_time! ...do I have to search the backups for each day to get this? If so...how do I define an accumulator would be needed for: Each day Grand Total Is a schema change needed? -V PS Our new DBA from Toronto just handles backups. We engineers are stuck with the rest... 
|
|
#4
April 12th, 2003, 03:36 PM
|
|||
|
|||
|
Err
Perhaps I could create a secondary table that stores totals at the end of each night? Then do the query from that? If so can Oracle do a time-based stored proc? thanks Rudy -V Last edited by vrkelley : April 12th, 2003 at 03:39 PM.
|
|
#5
April 12th, 2003, 04:12 PM
|
||||
|
||||
|
can't really help you, V, without more details
you want a "sum of something" but have no history more than a week old? you may need an accumulator, or secondary table, but the design will depend on so many factors, you should look into all your options before thinking about how to make the output look pretty ("look pretty" is not an epithet, report design cannot be overlooked, but having a good data architecture is of primary importance) please give a brief description of the table(s), how they are updated, and how often rudy
|
|
#6
April 12th, 2003, 04:18 PM
|
|||
|
|||
|
You can do it with a decode.
ie select sum(decode(dayofweek,'monday',onhand,0) monday, sum(decode(dayofweek,'tuesday',onhand,0)... Give me your table structure if you need more help.
|
|
#7
April 12th, 2003, 09:12 PM
|
|||
|
|||
|
The data shows only where the parts are this second.
Each time a part moves, one of the quanities increments or decrements. Schema looks something like this part_number (varchar2) component _key (number)--> foreign key describing category. Ex. Fan, Bracket qty_reserved(number) ----> while their filling the order qty_available(number)-----> sitting on shelf qty_in_chassis(number) ---> sitting in chassy waiting to be boxed description etc stuff stuff -V Last edited by vrkelley : April 12th, 2003 at 09:46 PM.
|
|
#8
April 12th, 2003, 10:26 PM
|
|||
|
|||
|
>you want a "sum of something" but have no history more than a week old?
I'll tell mgt that it's not possible. But we'll need to start storing the data some how. So at the end of the day some how the counts for each product will have to get stored to another table. Is there a way to automatically launch a stored procedure to do that ? -V
|
|
#9
April 12th, 2003, 10:59 PM
|
||||
|
||||
|
yes, regular copying of the data into another table is a great idea -- add a timestamp column
go to whoever set the database up or is currently responsible for it, somebody called "database administrator" preferably, and ask them to set it up for you oracle has utilities for this good luck
|
|
#10
April 13th, 2003, 11:19 AM
|
|||
|
|||
|
hmm....Our "DBA's" only do reboots, back ups and install patches and also would NOT give the Oracle utilities.
Need to insert 1 row of data for each part each day into the table that'll hold the inventory history stuff. A. Is there a command line way to *schedule* this sort replication ? B. In the stored proc, do I have to define a variable for each item extracted on the old table? Something like this prob won't work because it returns too many values? Code:
INSERT INTO tblHistory
VALUES(SELECT x, y, z SUM(z)), sysdate /*x, y, z data format to match new table's schema */
FROM tblpartinfo)
|
|
#11
April 13th, 2003, 01:01 PM
|
||||
|
||||
|
Quote:
Check http://www.databasejournal.com/feat...cle.php/1563801
__________________
My blog about OpenSource Databases PDF tutorials about OSS databases, DBMonster ... Please contribute to Open Source Development, fill bug reports!!! Developer Shed eSupport Commented my.ini/my.cnf (PLEASE ADD YOUR OWN CONFIG TRICK) Understanding SQL Joins An introduction to database normalization Natural or Surrogate key Custom ordering for your results Correlated and uncorrelated subqueries
|
|
#13
April 13th, 2003, 09:40 PM
|
|||
|
|||
|
Wow ultra...that looks like it'll work. I'm thinkng the schema of the history table will look like this...but...
Part_skey Part# Qty On Hand(sum of next three columns reflect daily TL Qty in Chassy Qty in Reserve Lastupdate I don't understand how to get each week's rpt to show the new range of dates in the heading? -------------------------------------------------------------------------------- Code:
Quantity On Hand
04/01/03 04/02/03 04/03/03 TOTAL
PartA 5 10 25 40
PartB 1 5 5 11
GrandTL 6 15 30 51
Last edited by vrkelley : April 13th, 2003 at 09:54 PM.
|
|
| Viewing: Dev Shed Forums > Databases > Database Management > Oracle Need simple totals for day and by week |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
