Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2009-07-15
22
Medium Priority
?
2,485 Views
Last Modified: 2013-12-06
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
Comment
Question by:ctsuhako
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7
  • 3
  • +3
22 Comments
 
LVL 18

Expert Comment

by:Dave Ford
ID: 24864089

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
 

Author Comment

by:ctsuhako
ID: 24864120
Sorry. I would like to have the data aggregated by Year, Month and Day.
0
 
LVL 18

Expert Comment

by:Dave Ford
ID: 24864128

Can you give me a mocked-up sample of the output you're looking for?
0
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 37

Expert Comment

by:momi_sabag
ID: 24864139
select ....
from table
group by substr(orddate,1,4) as year, substr(orddate,5,2) as month, substr(orddate,7,2) as day
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 24865215
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
 
LVL 33

Expert Comment

by:shalomc
ID: 24867304
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
 

Author Comment

by:ctsuhako
ID: 24869394
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
 
LVL 33

Assisted Solution

by:shalomc
shalomc earned 400 total points
ID: 24869461
SELECT SUM(QTYOR) AS QTYOR, MFG AS MFG, SUBSTR(ORDDATE,1,4) AS ORDDATE
FROM DATABASE
GROUP BY MFG, SUBSTR(ORDDATE,1,4)


0
 
LVL 35

Expert Comment

by:Gary Patterson
ID: 24872215
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
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 24872276
@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
 

Author Comment

by:ctsuhako
ID: 24873211
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
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 24873269
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
 

Author Comment

by:ctsuhako
ID: 24873320
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
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 24873387
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
 

Author Comment

by:ctsuhako
ID: 24873421
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
 
LVL 46

Accepted Solution

by:
Kent Olsen earned 1600 total points
ID: 24873544
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
 
LVL 35

Expert Comment

by:Gary Patterson
ID: 24874513
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
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 24875016
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
 

Author Comment

by:ctsuhako
ID: 24878292
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
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 24878402
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
 
LVL 18

Expert Comment

by:Dave Ford
ID: 24879226

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
 

Author Comment

by:ctsuhako
ID: 24904490
The recursive SQL does not work on our box, but Kent's original query is getting the job done. Thanks all
0

Featured Post

The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
What we learned in Webroot's webinar on multi-vector protection.
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question