Clicky

Hello - I have a file with some data something like this;

Date                 Location      Start Qty      Outbound Qty      Inbound Qty      End Qty
12/13/2011      Chicago              100        25                            20                 95
12/13/2011      Los Angeles      200       100                       50                150

What I would like to be able to do is use the End Qty (Quantity) from the previous day as the Start Qty for each location for the next day. (The formula is Start Qty - (Outbound Qty-Inbound Qty)=End Qty). I'm stumbling over how to start...how do I get the initial Start Qty and work from there with it?

asked 12/14/2011 04:35

MilburnDrysdale's gravatar image

MilburnDrysdale ♦♦


16 Answers:
you will have to provide more details
you have this data in a file or a table?
you need to define what the first start qty should be and i can try to help you get there, but you need to know what you need
link

answered

momi_sabag's gravatar image

momi_sabag

Presuming you have stored the end quantity for yesterday:
1:
select date, location, (select endqty from yourtable y where date = (select current date - 1 day from sysibm.sysdummy1) and y.location = location) startqty, sum(outboundqty), sum(inboundqty), startqty + inboundqty - outboundqty endqty from yourtable where date = (select current date from sysibm.sysdummy1) group by date, location



If not, then the presumption of the very very first start qty becomes important and needs to be defined.
link

answered 2011-12-14 at 13:37:53

sweetfa2's gravatar image

sweetfa2

Thanks to all that have answered. Here is what I have so far;
 
1:
2:
3:
4:
SELECT T1.DATE, T1.TERMINAL,                                                 
(SELECT T2.NETBARS FROM TAFWORK/ASSETTRK07 T2 WHERE T2.ROWNUM+1=T1.ROWNUM AND
T1.TERMINAL=T2.TERMINAL) AS SBARS, T1.OBARS, T1.DBARS                        
FROM TAFWORK/ASSETTRK07 T1


The table TAFWORK/ASSETTRK07 has a row number assigned partitioned by terminal (aka location). Some sample data would look like this (the "BARS" are simply the items we are tracking);

Date  Terminal  S_Bars  OBARS  IBARS  NETBARS  ROWNUM
12/12   LAX       200           50         25           175            1
12/13   LAX         0             75          25             50           2

The initial value in S_Bars is a one time number, a starting number. The issue I'm having is how to carry that number down to the S_Bars column in rownum 2. The above code works, but only for one row.
link

answered 2011-12-14 at 17:23:22

MilburnDrysdale's gravatar image

MilburnDrysdale

Hi Milburn,

There are a number of ways to do this.  The DB2 OLAP extensions mentioned above are certainly one of them, but it's also possible to do this without them.

Several questions:  (There are ALWAYS questions!)  :)

1.  Are the dates in the table contiguous?  (No missing dates.)
2.  Are the dates unique?  (No duplications.)
3.  How many rows are there?  (Performance may be an issue.)
4.  What's the original quantity?  (You'll need to know this or invent a value.)

All that said, something like the following should be a good starting point.  It assumes that the dates are NOT contiguous (perhaps no data on weekends or holidays).


Kent
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
WITH IQ (initial_qty)
AS
(
  SELECT 50 FROM sysibm.sysdummy1
),
DD (id, effective_date, in_qty, out_qty, start_qty)
(
  SELECT row_number () over (), date, in_qty, out_qty,
    (select sum (start_qty) + sum (in_qty) - sum (out_qty) FROM mytable T1
     where t1.date < t0.date) start_qty,
    (SELECT initial_qty FROM iq)
  FROM mytable T0
)
SELECT 
  T0.effective_date, 
  T0.start_qty + initial_qty as start_qty
  T0.in_qty,
  T0.out_qty,
  T0.start_qty + initial_qty - out_qty as end_qty
FROM dd;
link

answered 2011-12-15 at 06:37:21

Kdo's gravatar image

Kdo

Kent - the dates will be contiguous (ie activity over the weekend). There is only one date per location and I am "forcing" that in so that even if there is no activity, each location will show an entry for each date (zero). There are roughly 50 locations so will be adding a row each day but could probably drop data after 3 months or so. The original quantity can vary by location. Could be 100 for one and zero for another. Thanks!
link

answered 2011-12-15 at 06:45:02

MilburnDrysdale's gravatar image

MilburnDrysdale

No problem.  :)  (Just more involved...)

We'll still need a way to obtain the starting quantity.  Since you may be deleting/archiving data along the way you've got some architecture level decisions to make.

--  You can store the starting quantity in another table, but if you do that you MUST make sure that the value is accurate for the smallest date for each location.  That type of synchronization is prone to programming errors.

--  You can store the starting quantity in the row with the smallest date and compute it for all other dates.  That has similar issues at the solution above, but typically is less fallible.

--  You can compute the value with each new row stored.  A simple INSERT trigger does the calculations.  That solves all of the data synchronization issues, but updates are more of a challenge.  If LAX comes forward and says "we missed an invoice last month where we moved 4 more items" you now have to update the start/end quantities for the affected date and all records after that date.


Your thoughts?
link

answered 2011-12-15 at 06:52:35

Kdo's gravatar image

Kdo

I have already set up a table with the starting values and assigned a date to that, ie like this (let's say the report will start on 12/12);

Terminal    Date   Start_Bars
LAX            12/12     200
ORD            12/12     100
etc.

One record for each terminal
link

answered 2011-12-15 at 07:14:44

MilburnDrysdale's gravatar image

MilburnDrysdale


Cool.  Now it's a piece of cake.  :)

And since you're dealing with such a small amount of data (200 terminals x 100 days) the query doesn't have to be particularly efficient so simple SQL may be preferable.

Something like the query below should be close.....


Kent
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
WITH stuff (date, terminal, initial_qty, in_qty, out_qty, end_qty)
AS
(

--  Create rows for each terminal/date that include the base qty
--  Ignore rows in mytable that are older than the base date in starttable

  SELECT t0.date, t0.terminal. t1.initial_qty as base_qty, t0.in_qty, t0.out_qty,
  FROM mytable T0
  INNER JOIN starttable T1
    ON t0.terminal = t1.terminal
  WHERE t1.date >= t0.date
)
SELECT 
  T0.date, 
  T0.initial_qty + select (sum (in_qty) - sum(out_qty) from dd t1 where t0.terminal = t1.terminal and t1.date < t0.date) start_qty
  T0.in_qty,
  T0.out_qty,
  T0.start_qty + select (sum (in_qty) - sum(out_qty) from dd t2 where t0.terminal = t2.terminal and t1.date <= t2.date) start_qty
FROM stuff t0
link

answered 2011-12-15 at 07:22:07

Kdo's gravatar image

Kdo

Kent - what is the DD table reference? Would that be the same as mytable as you have it above?
link

answered 2011-12-15 at 07:38:53

MilburnDrysdale's gravatar image

MilburnDrysdale

Yeah, sorry....

dd = stuff

The original query used two derived tables.  They were combined into one this time and I should have referenced only the one table here....


Kent
link

answered 2011-12-15 at 07:57:48

Kdo's gravatar image

Kdo

Kent - here is the translated sql;
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
WITH TAB(DATE, TERMINAL, OTRIPS, DTRIPS, SBARS, OBARS, DBARS) AS          
(SELECT T0.DATE, T0.TERMINAL, T0.OTRIPS, T0.DTRIPS, T1.S_BARS AS SBARS,   
T0.OBARS, T0.DBARS                                                        
FROM TAFWORK/ASSETTRK07 T0                                                
INNER JOIN LTL400TAF1/ASSETSTART T1                                       
 ON T0.TERMINAL=T1.S_TERM                                                 
WHERE T1.S_DATE>=T0.DATE)                                                 
SELECT T0.DATE, T0.TERMINAL, T0.SBARS + (SELECT DBARS-OBARS               
FROM TAB T1 WHERE T0.TERMINAL=T1.TERMINAL AND                             
T1.DATE<T0.DATE) AS SBARS, T0.OBARS, T0.DBARS,                            
T0.SBARS + (SELECT DBARS-OBARS FROM TAB T2 WHERE T0.TERMINAL=T2.TERMINAL  
AND T0.DATE<=T2.DATE) AS EBARS                                            
FROM TAB T0


Two issues are occuring...the SBAR field is null and the query is only returning rows for the first date (ie 12/12). Here is what is returned;



 
1:
2:
3:
4:
5:
6:
7:
8:
9:
DATE	TERMINAL	SBARS	OBARS	DBARS	EBARS
12/12/2011	ATL	0	1	0	199
12/12/2011	DFW	0	0	0	300
12/12/2011	EWR	0	11	2	291
12/12/2011	IAD	0	1	0	199
12/12/2011	IND	0	1	1	200
12/12/2011	LAX	0	2	0	298
12/12/2011	ORD	0	2	0	398
12/12/2011	PHX	0	0	0	100


The initial value for ATL is 200, for DFW is 300, EWR is 300 etc.

Tim
link

answered 2011-12-15 at 08:06:42

MilburnDrysdale's gravatar image

MilburnDrysdale

Hmm...

Ok.  We're actually close.  :)   The start/end qty need


Is there a NULL anywhere in the data?  If so, we'll have to apply a COALESCE (item, 0) to items as they're read from the database.
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
WITH TAB(DATE, TERMINAL, OTRIPS, DTRIPS, SBARS, OBARS, DBARS) AS          
(
  SELECT T0.DATE, T0.TERMINAL, T0.OTRIPS, T0.DTRIPS, T1.S_BARS AS SBARS, T0.OBARS, T0.DBARS                                                        
  FROM TAFWORK/ASSETTRK07 T0                                                
  INNER JOIN LTL400TAF1/ASSETSTART T1                                       
    ON T0.TERMINAL=T1.S_TERM                                                 
  WHERE T1.S_DATE>=T0.DATE
)
SELECT 
  T0.DATE, 
  T0.TERMINAL, 
  T0.SBARS + (SELECT sum (DBARS-OBARS) FROM TAB T1 WHERE T0.TERMINAL=T1.TERMINAL AND T1.DATE < T0.DATE) AS SBARS,
  T0.OBARS, 
  T0.DBARS,
  T0.SBARS + (SELECT sum (DBARS-OBARS) FROM TAB T2 WHERE T0.TERMINAL=T2.TERMINAL AND T2.DATE <= T0.DATE) AS EBARS                                            
FROM TAB T0
link

answered 2011-12-15 at 08:21:27

Kdo's gravatar image

Kdo

There are no nulls. When I test the output of the WITH TAB statement, I only get data for one day (the starting date, 12/12) yet the lower select statement is looking for dates greater than that.
link

answered 2011-12-15 at 08:43:40

MilburnDrysdale's gravatar image

MilburnDrysdale

Ahh...

I'm getting in too big a hurry.  Lots going on in my office..  :)

The date test in the inner query is backwards.
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
WITH TAB(DATE, TERMINAL, OTRIPS, DTRIPS, SBARS, OBARS, DBARS) AS          
(
  SELECT T0.DATE, T0.TERMINAL, T0.OTRIPS, T0.DTRIPS, T1.S_BARS AS SBARS, T0.OBARS, T0.DBARS                                                        
  FROM TAFWORK/ASSETTRK07 T0                                                
  INNER JOIN LTL400TAF1/ASSETSTART T1                                       
    ON T0.TERMINAL = T1.S_TERM                                                 
  WHERE T1.S_DATE <= T0.DATE
)
SELECT 
  T0.DATE, 
  T0.TERMINAL, 
  T0.SBARS + (SELECT sum (DBARS-OBARS) FROM TAB T1 WHERE T0.TERMINAL=T1.TERMINAL AND T1.DATE < T0.DATE) AS SBARS,
  T0.OBARS, 
  T0.DBARS,
  T0.SBARS + (SELECT sum (DBARS-OBARS) FROM TAB T2 WHERE T0.TERMINAL=T2.TERMINAL AND T2.DATE <= T0.DATE) AS EBARS                                            
FROM TAB T0
link

answered 2011-12-15 at 08:52:59

Kdo's gravatar image

Kdo

Awesome! That did it...thanks for sticking with me on this. It helped a ton!
link

answered 2011-12-15 at 10:04:20

MilburnDrysdale's gravatar image

MilburnDrysdale

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

×11
×132

Asked: 12/14/2011 04:35

Seen: 387 times

Last updated: 12/15/2011 03:28

Categories