[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2699
  • Last Modified:

AS400 DB2 Query To Group Date By Year, Month and Day

I have a table on an AS400 that has a date field (ORDDATE) in the following format: YYYYMMDD. I wish to run a SQL query (or queries) that groups the the date field by Year, Month and Day. There can be a separate query for each. Thank you.
0
ctsuhako
Asked:
ctsuhako
  • 7
  • 7
  • 3
  • +3
2 Solutions
 
Dave FordSoftware Developer / Database AdministratorCommented:

Greetings, ctsuhako.

What exactly do you mean when you say "groups the date field by Year, Month, and Day"?

Do you want the data ORDERED by that date, or do you want some aggregation on that date?

e.g.

select column1,
       column2,
       column3,
       orddate
from   MyTable
order by orddate
;
 
 
select orddate,
       count(*)
from   MyTable
group by orddate

Open in new window

0
 
ctsuhakoAuthor Commented:
Sorry. I would like to have the data aggregated by Year, Month and Day.
0
 
Dave FordSoftware Developer / Database AdministratorCommented:

Can you give me a mocked-up sample of the output you're looking for?
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
momi_sabagCommented:
select ....
from table
group by substr(orddate,1,4) as year, substr(orddate,5,2) as month, substr(orddate,7,2) as day
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi ctsuhako,

Because an oddity in the Date math, both Dave and Momi are correct.  You can group by just ORDDATE, or you can extract the year, month, and day values from ORDDATE and group by year,month,day.  The report will have exactly the same rows.  The only difference being whether the date field(s) are in 1 column or 3.


Kent
0
 
shalomcCTOCommented:
I guess that you want something like the following query, that includes yearly and monthly subtotals in the same result set.

select sum(something) ,         substr(orddate,1,4), 'Year'
from MyTable
group by    substr(orddate,1,4)            
union
select sum(something) , substr(orddate,1,6), 'Month'
from MyTable
group by         substr(orddate,1,6)            
order by 2
0
 
ctsuhakoAuthor Commented:
Thanks for the help. When I run this query:
SELECT SUM(QTYOR) AS QTYOR, MFG AS MFG, SUBSTR(ORDDATE,1,4) AS ORDDATE
FROM DATABASE
GROUP BY MFG, ORDATE

I recieve:

QTYOR     MFG     ORDATE
625           A          2009
1299         B          2009
253           C          2009
953           A         2009

The GROUP BY MFG does not seem to be aggregating the MFG field since I see several of them.
0
 
shalomcCTOCommented:
SELECT SUM(QTYOR) AS QTYOR, MFG AS MFG, SUBSTR(ORDDATE,1,4) AS ORDDATE
FROM DATABASE
GROUP BY MFG, SUBSTR(ORDDATE,1,4)


0
 
Gary PattersonVP Technology / Senior Consultant Commented:
You are getting multiples since you are grouping by the original ORDDATE field (YYYYMMDD), not the substringed version, so you are getting one grouping for every manufacturer for each day in the underlying data.  It is confusing becuse you are then reusing the name field name and only showing the first 4 bytes.

As shalomc shows above, you need to group based on original fields, not result fields.

While it may be legal SQL, I think it is confusing to name the year subfield the same as the parent field.  Do something like this instead:

SELECT sum(QTYOR) as QTYOR, MFG, substr(ORDDATE,1,4) as OrderYear
FROM table
GROUP BY MFG, substr(ORDDATE,1,4)

For monthly and daily aggregation, so you mean by month number alone (All orders received in January, regardless of year; or all orders for January 2009.  All orders for January 1st of any year, or all orders for January 1st, 2009, or all orders received on Tuesdays, regardless of year?)

- Gary Patterson
0
 
Kent OlsenData Warehouse Architect / DBACommented:
@Gary,

>Do something like this instead:
>
>SELECT sum(QTYOR) as QTYOR, MFG, substr(ORDDATE,1,4) as OrderYear
>FROM table
>GROUP BY MFG, substr(ORDDATE,1,4)

This syntax is required in DB2/LUW as the synomym "OrderYear" isn't propogated to the filters and controls.  (I've heard that DB2/LUW is supposed to do away with this restriction, but haven't seen it yet.)


Kent
0
 
ctsuhakoAuthor Commented:
shalomc has gotten me pointed in the right direction. Running his SQL returns the following:

QTYOR     MFG     ORDATE
625           A          2009
1299         B          2009
253           C          2009

and this has eliminated the duplicate MFG. Currently, this database only has records from 2009 in it. My next question is how do I only show the ORDDATE field once, i.e.:

QTYOR     MFG     ORDATE
625           A          2009
1299         B          
253           C          

Is this possible?
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi ctsuhako,

It's possible, but not very easy using "straight" SQL.

Do you have the OLAP exenstions installed?  The ROLLUP and GROUPING SETS capability will do this for you.

Here's a great primer on the OLAP extensions:

  http://www.inf.uni-konstanz.de/dbis/teaching/ws0405/information-systems/tutorial-04.pdf


Kent
0
 
ctsuhakoAuthor Commented:
Hi, Kdo:

I don't know, but I doubt it. To be specific, here is what I would like to see the SQL generate:

ORDATE     MFG-A     MFG-B     MFG-C
2009           625          1299        253

This data is going to be pulled into a dashboard chart and I can't seem to get the SQL correct to give me this type of output. Thank you.

P.S. Please forgive my lack of knowledge in SQL!
0
 
Kent OlsenData Warehouse Architect / DBACommented:
If you know the manufacturers that you have, you can build the SQL to do this by joining the results for each manufacturer.

That's not practical if you don't really know the manufacturers or if it's likely to change.

But if your version of DB2 supports "recursive SQL", we're back to making this possible.

-- Do you have a small and known list of manufacturers?
-- Does your version of DB2 support recursive SQL?  (Probably not since it doesn' extend OLAP.)


Kent
0
 
ctsuhakoAuthor Commented:
Kent:

The manufacturers list is small (no more than 20) and it does not change very often. All the data resides in one large file. I don't know if the version of DB2 for ISeries has the "recursive SQL".
Thanks.

Clay
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi Clay,

It's ugly, slow, and repetive, but it works.

The basic form of the query will be:

SELECT t0.orderyear, t1.qtyor as mfg_a, t2.qtyor as mfg_b, (etc)
FROM
(
  SELECT distinct substr (orddate, 1, 4) as orderyear FROM mytalbe
) t0
LEFT OUTER JOIN
(
  SELECT sum(QTYOR) as QTYOR, substr(ORDDATE,1,4) as OrderYear
  FROM table
  WHERE mfg = 'MFG-A'
  GROUP BY substr(ORDDATE,1,4)
) t1
  ON t0.orderyear = t1.orderyear
LEFT OUTER JOIN
(
  SELECT sum(QTYOR) as QTYOR, substr(ORDDATE,1,4) as OrderYear
  FROM table
  WHERE mfg = 'MFG-B'
  GROUP BY substr(ORDDATE,1,4)
) t2
  ON t0.orderyear = t2.orderyear
etc...


Kent
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
Kent,

I thought the question was about DB2 for System i, not LUW, so I answered in that vein.

My syntax works great in native queries (STRSQL, Query Manager, Navigator, RUNSQLSTM, RPG-embedded SQL).  What tool are you referring to?

Can't tell what tool(s) Clay is using from is question, unless I missed it along the way.

Anyway, looks like you are well past that point, so I will leave you to it!

- Gary Patterson


0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi Gary,

I'm certainly more comfortable with DB2 for LUW, Z/OS, and AS/400, in that order.  :)  The AS/400 version varies a lot on release level, with the older (more established base) seemingly having the greatest number of systems and the newer versions the greater capability.  The latest release supports recursive SQL (and I believe the OLAP extensions) but most folks haven't upgraded to it.

My last code sample was a way for him to format the report the way that he wants it, without having to use any of the nifty tools that will make his life a lot easier when his site finally upgrades to a "modern" release.


Kent


0
 
ctsuhakoAuthor Commented:
Hi, Kent:
The code seems to be able to do what I wat. FYI, we are on V5R4M0 on an ISeries 5. I am not very familiar with what capabilties are on this version, however. Is there a way I can check to see if this version supports the recursive SQL? Thanks!

Clay
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi Clay,

V5R4M0 is about 3 years old.  I don't believe that those features were available in 2006.

Check to see if it supports the WITH syntax.  That should improve the query.

Kent

WITH t0 (year, manufacturer, quantity)
AS
(
  SELECT substr(ORDDATE,1,4), mfg, sum(QTYOR) 
  FROM table
  GROUP BY substr(ORDDATE,1,4), mfg
)
SELECT t0.year, t1.quantity as mfg_a, t2.quantity as mfg_b
FROM t0
LEFT OUTER JOIN t0 t1
  ON t0.year = t1.year
 AND t1.manufacturer = 'MFG-A'
LEFT OUTER JOIN t0 t2
  ON t0.year = t2.year
 AND t2.manufacturer = 'MFG-B'

Open in new window

0
 
Dave FordSoftware Developer / Database AdministratorCommented:

The WITH clause has supported for several years on DB2 for iSeries. It's definitely in V5R4.

As for recursive SQL, Kent is correct. It is supported if you purchased OLAP extensions, but most shops don't spend the extra money for that.

HTH,
DaveSlash
0
 
ctsuhakoAuthor Commented:
The recursive SQL does not work on our box, but Kent's original query is getting the job done. Thanks all
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 7
  • 7
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now