Re: SQL question

This works on older versions of DB2 for i

with
cur_yr as (select ...)
, prior_yr as (select ...)
select
c.month, c.sales, p.py_sales
from cur_yr C
join prior_yr P on c.month = p.month


If you happen to be running 7.3, you could use the new LAG() or LEAD()
functions to pull data from one row of the results set into another.
Should be a better performer, but I'm guessing your not on 7.3.

Charles



Charles

On Thu, Sep 29, 2016 at 2:04 PM, James H. H. Lampert <
jamesl@xxxxxxxxxxxxxxxxx> wrote:

We have the following SQL query, that works very well:

select year,

month,
sum(sales) as sales
from foo
where (year * 100 + month) > ((year(current_date) -1) * 100 +
month(current_date))
group by year, month
order by year, month

But now, we want to add a fourth column, for the previous year's sales.
Now, I can get this by itself (I think) with something like this:

select year+1 as year,

month,
sum(sales) as py_sales
from foo
where ((year * 100 + month) > ((year(current_date) -2) * 100 +
month(current_date)))
and ((year * 100 + month) <= ((year(current_date) -1) * 100 +
month(current_date)))
group by year, month
order by year, month

But how would I combine these to get both the "sales" and "py_sales"
columns? Some kind of a join, maybe? But what would the syntax be?

--
JHHL
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.