On 29-Sep-2016 13:04 -0500, James H. H. Lampert 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
The following query I expect [untested] is the /same/ for effect, but
generates a true-temporary calendar-table to which the existing data can
be joined, and then selection performed without use of derived
expressions for the column data; each of the result columns of the CTE
could be cast to the type\size of the respective column compared in the
equal-predicates of the join [presuming that might allow for even better
performance; for lack of any DDL given, I could not include that casting
in my example, and for lack of any sample data, I did not spend any time
testing anything but the results from the derived table(s)]:
with
py1 ( dt, yr , mo ) as
( values( date (current_date - 1 years + 1 month)
, year (current_date - 1 years + 1 month)
, month(current_date - 1 years + 1 month)
)
union all
select date ( dt + 1 month )
, year ( dt + 1 month )
, month( dt + 1 month )
from py1
where dt < current_date
)
select year
, month
, sum(sales) as sales
from foo
join py1
on year = yr
and month = mo
group by year, month
order by year, month
Of course the derived table expression [i.e. the Common Table
Expression (CTE) used] as table-reference PY1 for the prior twelve MONTH
values up to the present, could have that same data derived instead,
from a persistent\permanent calendar-table.
But now, we want to add a fourth column, for the previous year's
sales.
Consider also, the possibility of a union of the data; i.e, the same
columns, but combining the result sets from each year as rows, instead
of combined as another column of the aggregated values.
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?
Neither of the following was tested, but the first is much the same
as what was offered by Charles, from the prior month's archive
[
http://archive.midrange.com/midrange-l/201609/msg00855.html], but with
the aforementioned CTE as calendar-table results, to allow for a JOIN
and the simplified selection predicates:
with
py1 ( dt, yr , mo ) as
( values( date (current_date - 1 years + 1 month)
, year (current_date - 1 years + 1 month)
, month(current_date - 1 years + 1 month)
)
union all
select date ( dt + 1 month )
, year ( dt + 1 month )
, month( dt + 1 month )
from py1
where dt < current_date
)
, py2 ( dt, yr, mo ) as
( select date ( dt - 1 year )
, year ( dt - 1 year )
, month( dt - 1 year )
from py1
)
select ty.month
, /* ty.year, */ ty.sales as "sales past 12 mos"
, /* py.year, */ py.sales as "sales prior 12 mos"
from table
( select year
, month
, sum(sales) as sales
from foo
join py1
on year = yr
and month = mo
group by year, month
) as ty /* This past Year */
join table
( select year
, month
, sum(sales) as sales
from foo
join py2
on year = yr
and month = mo
group by year, month
) as py /* Prior Year to this past year */
order by 1
Or as UNION of the data:
with
py1 ( dt, yr , mo ) as
( values( date (current_date - 1 years + 1 month)
, year (current_date - 1 years + 1 month)
, month(current_date - 1 years + 1 month)
)
union all
select date ( dt + 1 month )
, year ( dt + 1 month )
, month( dt + 1 month )
from py1
where dt < current_date
)
, py2 ( dt, yr, mo ) as
( select date ( dt - 1 year )
, year ( dt - 1 year )
, month( dt - 1 year )
from py1
)
( select year
, month
, sum(sales) as sales
from foo
join py1
on year = yr
and month = mo
group by year, month
)
UNION ALL
( select year
, month
, sum(sales) as sales
from foo
join py2
on year = yr
and month = mo
group by year, month
)
order by 1, 2
midrange.com
