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.
ctsuhakoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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
Shalom CarmelCTOCommented:
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
Shalom CarmelCTOCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Operating Systems

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.