Re: SQL (RETURN MEDIAN?)

sorry to burden you... I primarily write in RPGIV and DB2...
 
 
 
 
 
Thanks,
 
 
Jay Vaughn
iSeries Senior Programmer/Analyst
RateWatch
jayv@xxxxxxxxxxxxxx
251-633-8929


-----Original Message-----
From: Michael_Schutte@xxxxxxxxxxxx
To: RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>
Date: Fri, 15 Dec 2006 12:07:20 -0500
Subject: Re: SQL (RETURN MEDIAN?)


You seriously asked that...  I was hoping that you would be able to plug
and play.

Here's what you want....

SELECT x.QDAPY INTO :WKAPYHIGH
FROM BRDETAIL x
CROSS JOIN BRDETAIL y
WHERE x.QDDGRP = :WKGRP AND x.QDSDT >= :WKRUNDATE
   AND x.QDINST = :FIINST
GROUP BY x.QDAPY
HAVING SUM(SIGN(x.QDAPY-y.QDAPY)) IN (1,0)
ORDER BY x.QDAPY DESC




\
Ciao!
Michael Schutte
Admin Professional
Bob Evans Farms, Inc.
3776 South High Street, Columbus, OH 43207
Phone: 614/492-7419    Fax: 614/409-2017
Visit us on the web! www.bobevans.com

(Embedded image moved to file: pic21724.gif)

Classic Italian dishes with a Bob Evans Twist - Enjoy our new Homestyle
Pastas!


rpg400-l-bounces@xxxxxxxxxxxx wrote on 12/15/2006 11:59:48 AM:

Hmmm. do you have a working example of that?





Thanks,


Jay Vaughn
iSeries Senior Programmer/Analyst
RateWatch
jayv@xxxxxxxxxxxxxx
251-633-8929


-----Original Message-----
From: Michael_Schutte@xxxxxxxxxxxx
To: RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>
Date: Fri, 15 Dec 2006 11:46:51 -0500
Subject: Re: SQL (RETURN MEDIAN?)

 SELECT x.Value AS median

 FROM Vals x

 CROSS JOIN Vals y

 GROUP BY x.Value

 HAVING SUM(SIGN(x.Value-y.Value)) IN (1,0)

Ciao!
Michael Schutte
Admin Professional
Bob Evans Farms, Inc.
3776 South High Street, Columbus, OH 43207
Phone: 614/492-7419    Fax: 614/409-2017
Visit us on the web! www.bobevans.com

(Embedded image moved to file: pic18935.gif)

Classic Italian dishes with a Bob Evans Twist - Enjoy our new Homestyle
Pastas!


rpg400-l-bounces@xxxxxxxxxxxx wrote on 12/15/2006 11:39:50 AM:

I am using the following sql to return the high and low values of an

apy...

But how do I get the middle/median value?


C/exec sql
C+ SELECT QDAPY INTO :WKAPYHIGH
C+   FROM BRDETAIL
C+   WHERE QDDGRP = :WKGRP AND QDSDT >= :WKRUNDATE
C+   AND QDINST = :FIINST
C+   ORDER BY QDAPY DESC
C/end-exec


 C/exec sql
C+ SELECT QDAPY INTO :WKAPYLOW
C+   FROM BRDETAIL
C+   WHERE QDDGRP = :WKGRP AND QDSDT >= :WKRUNDATE
C+   AND QDINST = :FIINST AND QDAPY <> 0
C+   ORDER BY QDAPY ASC
C/end-exec




Thanks,


Jay Vaughn
iSeries Senior Programmer/Analyst
RateWatch
jayv@xxxxxxxxxxxxxx
251-633-8929
--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing

list

To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.

--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing

list

To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.