Prev: SQL0902C on db2 connect
Next: DDL file usage
From: portaldev on 30 Nov 2009 01:22
Hi,
I'm trying to calculate the number of items left based on the
following:

if date = current date then num items = X
else num items = prev num of items - Y

so in SQL:

SELECT CASE
WHEN date = current date THEN ( SELECT X FROM ... )
ELSE "prev items left" - (SELECT Y FROM ...)
END as items_left

I can get items left from the previous row using something like:

MAX(items_left) OVER( PARTITION BY MONTH(date)
ORDER BY date ROWS BETWEEN 1 preceding AND 1 preceding )

but I can't use it inside it's self e.g.

SELECT CASE
WHEN t.date = current date THEN ( SELECT X FROM ... )
ELSE
MAX(items_left) OVER( PARTITION BY MONTH(date)
ORDER BY date ROWS BETWEEN 1 preceding AND 1 preceding ) -
(SELECT Y FROM ...)
END as items_left


I am confused as how to do this, so any help would be greatly
appreciated.
From: Serge Rielau on 30 Nov 2009 10:27
Hmm, this smells suspiciously like the inventory problem I described in
"SQL on Fire (Part I)".
You can download the latest iteration of it here:
http://www.sirdug.org/November_19_2009.htm

--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab
From: Tonkuma on 30 Nov 2009 11:23
I guessed following.
If result in Guess 4) was right, I have some solutions using OLAP
functions.
If this was wrong, would you show me sample data like in Guess 4).


Guess 1) following resemble terms in your descriptions or (psudo)code
are same item.
"number of items left"
"num items"
"items left"
"items_left"

Guess 2)
> if date = current date then num items = X
> else num items = prev num of items - Y
items_left will be set to X if date = current date
without considering previous items_left.

Guess 3)
> I can get items left from the previous row using something like:

> MAX(items_left) OVER( PARTITION BY MONTH(date)
> ORDER BY date ROWS BETWEEN 1 preceding AND 1 preceding )
items_left will be set to zero at the first day of the month.
Because, "PARTITION BY MONTH(date)" was specified.

Guess 4) items_left will be the following, if current_date is
'2009-11-15'.
date x y items_left
---------- --- --- ----------
2009-10-30 250 99 ???
2009-11-02 30 13 -13
2009-11-03 90 16 -29
2009-11-05 70 15 -44
2009-11-10 50 11 -55
2009-11-15 100 7 100 -- current_date
2009-11-18 40 14 86
2009-11-25 10 19 67
2009-11-26 60 12 55
2009-11-29 20 17 38
2009-11-30 80 18 20
2009-12-03 80 77 ???


From: Tonkuma on 30 Nov 2009 20:15
>
> Guess 4) items_left will be the following, if current_date is
> '2009-11-15'.
> date       x   y   items_left
> ---------- --- --- ----------
> 2009-10-30 250  99        ???
> 2009-11-02  30  13        -13
> 2009-11-03  90  16        -29
> 2009-11-05  70  15        -44
> 2009-11-10  50  11        -55
> 2009-11-15 100   7        100 -- current_date
> 2009-11-18  40  14         86
> 2009-11-25  10  19         67
> 2009-11-26  60  12         55
> 2009-11-29  20  17         38
> 2009-11-30  80  18         20
> 2009-12-03  80  77        ???
Here is an example:
------------------------------ Commands Entered
------------------------------
WITH
test_data1(current_date_) AS (
VALUES DATE('2009-11-15')
)
,test_data2 AS (
SELECT DATE(date) AS date
, x, y
FROM (VALUES
('2009-10-30', 250, 99)
, ('2009-11-02', 30, 13)
, ('2009-11-03', 90, 16)
, ('2009-11-05', 70, 15)
, ('2009-11-10', 50, 11)
, ('2009-11-15', 100, 7)
, ('2009-11-18', 40, 14)
, ('2009-11-25', 10, 19)
, ('2009-11-26', 60, 12)
, ('2009-11-29', 20, 17)
, ('2009-11-30', 80, 18)
, ('2009-12-03', 80, 77)
) t(date, x, y)
)
SELECT q.*
, MAX( CASE date WHEN current_date_ THEN x + y ELSE 0 END )
OVER( PARTITION BY MONTH(date)
ORDER BY date )
-
SUM( y )
OVER( PARTITION BY MONTH(date)
, SIGN( SIGN( date - current_date_ ) + 1 )
ORDER BY date )
AS items_left
, current_date_
FROM test_data1 p
, test_data2 q
;
------------------------------------------------------------------------------

DATE X Y ITEMS_LEFT CURRENT_DATE_
---------- ----------- ----------- ----------- -------------
2009-10-30 250 99 -99 2009-11-15
2009-11-02 30 13 -13 2009-11-15
2009-11-03 90 16 -29 2009-11-15
2009-11-05 70 15 -44 2009-11-15
2009-11-10 50 11 -55 2009-11-15
2009-11-15 100 7 100 2009-11-15
2009-11-18 40 14 86 2009-11-15
2009-11-25 10 19 67 2009-11-15
2009-11-26 60 12 55 2009-11-15
2009-11-29 20 17 38 2009-11-15
2009-11-30 80 18 20 2009-11-15
2009-12-03 80 77 -77 2009-11-15

12 record(s) selected.



From: Tonkuma on 1 Dec 2009 07:24
On Dec 1, 12:27 am, Serge Rielau <srie...(a)ca.ibm.com> wrote:
> ...
> "SQL on Fire (Part I)".
> ...
XMLELEMENT was used for "Aggregate concatenation" in the reference.
Like this:
SELECT
Dept,
TRIM
(',' FROM
REPLACE
(REPLACE
(XMLSERIALIZE
(CONTENT XMLAGG(XMLELEMENT(NAME a, name)
ORDER BY name)
AS VARCHAR(60)), '<A>', ''),
'</A>', ',')) AS Names
FROM Employee
GROUP BY Dept;

But, I prefer to use XMLTEXT for "Aggregate concatenation".
Because, it is a little simpler(no tags added) than using XMLELEMENT.
Like this:
SELECT Dept
, SUBSTR(
XMLSERIALIZE(
XMLAGG( XMLTEXT(',' || name)
ORDER BY name)
AS VARCHAR(60) )
, 2) AS Names
FROM Employee
GROUP BY Dept
;


 |  Next  |  Last
Pages: 1 2 3
Prev: SQL0902C on db2 connect
Next: DDL file usage