Solved

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

Posted on 2009-07-15
22
2,177 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
  • 7
  • 7
  • 3
  • +3
22 Comments
 
LVL 18

Expert Comment

by:daveslash
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:daveslash
ID: 24864128

Can you give me a mocked-up sample of the output you're looking for?
0
 
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 45

Expert Comment

by:Kdo
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 32

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 32

Assisted Solution

by:shalomc
shalomc earned 100 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 34

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 45

Expert Comment

by:Kdo
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 45

Expert Comment

by:Kdo
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 45

Expert Comment

by:Kdo
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 45

Accepted Solution

by:
Kdo earned 400 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 34

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 45

Expert Comment

by:Kdo
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 45

Expert Comment

by:Kdo
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:daveslash
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Windows 10 is here and for most admins this means frustration and challenges getting that first working Windows 10 image. As in my previous sysprep articles, I've put together a simple help guide to get you through this process. The aim is to achiev…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
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…

705 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now