| |
 |
|
| Computers Forum Index » Computer - Databases - IBM DB2 » Aligning rows horisontaly (again)... |
|
Page 1 of 1 |
|
| Author |
Message |
| danfan46... |
 Posted: Wed Oct 21, 2009 10:37 am |
|
|
|
Guest
|
Assume we have a database with cardholders.
Each physical card is customized with the retailers logo.
As the cards expires there must be blank cards in stock for each retailer.
The sql below creates the list I want, but is there another way of doing
it using OLAP functions.
with T1 As
(
select
RETAILER
,EXP_DATE
,count(*) as subtotal
from Cards
where EXP_DATE > current_date
and EXP_DATE < current_date + 12 month
group by RETAILER, EXP_DATE
)
select distinct
RETAILER
,Coalesce((select sum(subtotal) from T1 B where B.RETAILER =
A.RETAILER and month(EXP_DATE) + 100 * year(EXP_DATE) =
month(current_date + 1 month) + 100 * year(current_date + 1 month
)),0) as M1
,Coalesce((select sum(subtotal) from T1 B where B.RETAILER =
A.RETAILER and month(EXP_DATE) + 100 * year(EXP_DATE) =
month(current_date + 2 month) + 100 * year(current_date + 2 month
)),0) as M2
,Coalesce((select sum(subtotal) from T1 B where B.RETAILER =
A.RETAILER and month(EXP_DATE) + 100 * year(EXP_DATE) =
month(current_date + 3 month) + 100 * year(current_date + 3 month
)),0) as M3
-- ......
from T1 A
order by RETAILER
;
RETAILER M1 M2 M3 .... M12
1 8840 462 3615
6 72 1 8
7 48 0 5
8 16 0 11
-- -- -- --
/dg |
|
|
| Back to top |
|
|
|
| Tonkuma... |
| Posted: Wed Oct 21, 2009 12:56 pm |
|
|
|
Guest
|
This may be anotger way.
I don't know how OLAP functions would be related with your
requirement.
select
RETAILER
, count(case
when EXP_YM = CURRENT_YM + 1
then 1
end) as M1
, count(case
when EXP_YM = CURRENT_YM + 2
then 1
end) as M2
/*
.......
*/
, count(case
when EXP_YM = CURRENT_YM + 12
then 1
end) as M12
from (select
RETAILER
, year( EXP_DATE) * 12
+ month(EXP_DATE) as EXP_YM
from Cards
where EXP_DATE > current_date
and EXP_DATE < current_date + 12 month
) as T1
, (values year( current_date) * 12
+ month(current_date)
) as q(CURRENT_YM)
group by RETAILER
order by RETAILER
; |
|
|
| Back to top |
|
|
|
| Tonkuma... |
| Posted: Thu Oct 22, 2009 2:42 pm |
|
|
|
Guest
|
Another example(No sub-query, No join).
If your DB2 doesn't support last_day,
use "EXP_DATE > current_date - day(current_date) days + 1 month"
instead of "EXP_DATE > last_day(current_date)".
select
RETAILER
, count(case
when month(EXP_DATE) = month(current_date + 1 month)
then 1
end) as M1
, count(case
when month(EXP_DATE) = month(current_date + 2 month)
then 1
end) as M2
/*
.......
*/
, count(case
when month(EXP_DATE) = month(current_date + 12 month)
then 1
end) as M12
from Cards
where
EXP_DATE > last_day(current_date)
-- EXP_DATE > current_date - day(current_date) days + 1 month
and EXP_DATE < current_date + 12 month
group by RETAILER
order by RETAILER
; |
|
|
| Back to top |
|
|
|
| Tonkuma... |
| Posted: Thu Oct 22, 2009 2:51 pm |
|
|
|
Guest
|
On Oct 22, 11:42 pm, Tonkuma <tonk... at (no spam) fiberbit.net> wrote:
Quote: Another example(No sub-query, No join).
If your DB2 doesn't support last_day,
use "EXP_DATE > current_date - day(current_date) days + 1 month"
instead of "EXP_DATE > last_day(current_date)".
Sorry, I made mistake.
Correction:
EXP_DATE > current_date + 1 month - day(current_date + 1 month) days |
|
|
| Back to top |
|
|
|
| ChrisC... |
| Posted: Thu Oct 22, 2009 4:30 pm |
|
|
|
Guest
|
On Oct 22, 7:51 am, Tonkuma <tonk... at (no spam) fiberbit.net> wrote:
Quote: On Oct 22, 11:42 pm, Tonkuma <tonk... at (no spam) fiberbit.net> wrote:> Another example(No sub-query, No join).
If your DB2 doesn't support last_day,
use "EXP_DATE > current_date - day(current_date) days + 1 month"
instead of "EXP_DATE > last_day(current_date)".
Sorry, I made mistake.
Correction:
EXP_DATE > current_date + 1 month - day(current_date + 1 month) days
or
EXP_DATE >= current_date - day(current_date) days + 1 month |
|
|
| Back to top |
|
|
|
| ChrisC... |
| Posted: Thu Oct 22, 2009 6:07 pm |
|
|
|
Guest
|
Here is another option with no sub-query:
with months(m, base_date, the_month) as (values (1, current date + 1
month, integer(current date + 1 month) / 100)
union all
select m + 1, base_date + 1 month, integer(base_date + 1 month) / 100
from months where m < 12)
select
RETAILER
, count(case when m = 1 then 1 end) as M1
, count(case when m = 2 then 1 end) as M2
/*
.......
*/
, count(case when m = 12 then 1 end) as M12
from Cards
where EXP_DATE > current_date
and EXP_DATE < current_date + 12 month
join months on integer(EXP_DATE) / 100 = the_month
group by RETAILER
order by RETAILER
;
You are aware that the M12 in this (and the other) queries just has
part of the 12th month of data, right?
-Chris |
|
|
| Back to top |
|
|
|
| danfan46... |
| Posted: Thu Oct 22, 2009 8:08 pm |
|
|
|
Guest
|
Tonkuma wrote:
Quote: On Oct 22, 11:42 pm, Tonkuma <tonk... at (no spam) fiberbit.net> wrote:
Another example(No sub-query, No join).
If your DB2 doesn't support last_day,
use "EXP_DATE > current_date - day(current_date) days + 1 month"
instead of "EXP_DATE > last_day(current_date)".
Sorry, I made mistake.
Correction:
EXP_DATE > current_date + 1 month - day(current_date + 1 month) days
Thanks.
That is pretty neat!
/dg |
|
|
| Back to top |
|
|
|
| danfan46... |
| Posted: Thu Oct 22, 2009 11:44 pm |
|
|
|
Guest
|
ChrisC wrote:
Quote: Here is another option with no sub-query:
with months(m, base_date, the_month) as (values (1, current date + 1
month, integer(current date + 1 month) / 100)
union all
select m + 1, base_date + 1 month, integer(base_date + 1 month) / 100
from months where m < 12)
select
RETAILER
, count(case when m = 1 then 1 end) as M1
, count(case when m = 2 then 1 end) as M2
/*
.......
*/
, count(case when m = 12 then 1 end) as M12
from Cards
where EXP_DATE > current_date
and EXP_DATE < current_date + 12 month
join months on integer(EXP_DATE) / 100 = the_month
group by RETAILER
order by RETAILER
;
You are aware that the M12 in this (and the other) queries just has
part of the 12th month of data, right?
-Chris
Good point.
In this case all expire dates are in practice yymm and dd is always 01
/dg |
|
|
| Back to top |
|
|
|
| Tonkuma... |
| Posted: Fri Oct 23, 2009 2:32 am |
|
|
|
Guest
|
On Oct 23, 1:30 am, ChrisC <cunningham... at (no spam) gmail.com> wrote:
Quote: On Oct 22, 7:51 am, Tonkuma <tonk... at (no spam) fiberbit.net> wrote:
On Oct 22, 11:42 pm, Tonkuma <tonk... at (no spam) fiberbit.net> wrote:> Another example(No sub-query, No join).
If your DB2 doesn't support last_day,
use "EXP_DATE > current_date - day(current_date) days + 1 month"
instead of "EXP_DATE > last_day(current_date)".
Sorry, I made mistake.
Correction:
EXP_DATE > current_date + 1 month - day(current_date + 1 month) days
or
EXP_DATE >= current_date - day(current_date) days + 1 month
If current_date was in March, then
"current_date - day(current_date) days + 1 month" would be yyyy-03-28. |
|
|
| Back to top |
|
|
|
| The Boss... |
| Posted: Fri Oct 23, 2009 11:43 pm |
|
|
|
Guest
|
Tonkuma wrote:
Quote: On Oct 23, 1:30 am, ChrisC <cunningham... at (no spam) gmail.com> wrote:
On Oct 22, 7:51 am, Tonkuma <tonk... at (no spam) fiberbit.net> wrote:
On Oct 22, 11:42 pm, Tonkuma <tonk... at (no spam) fiberbit.net> wrote:> Another
example(No sub-query, No join).
If your DB2 doesn't support last_day,
use "EXP_DATE > current_date - day(current_date) days + 1 month"
instead of "EXP_DATE > last_day(current_date)".
Sorry, I made mistake.
Correction:
EXP_DATE > current_date + 1 month - day(current_date + 1 month) days
or
EXP_DATE >= current_date - day(current_date) days + 1 month
If current_date was in March, then
"current_date - day(current_date) days + 1 month" would be yyyy-03-28.
How about defining last_day as:
date('0001-01-01')
+ (year(current_date) - 1) years
+ (month(current_date)) months
- 1 day
And similar for first_day:
date('0001-01-01')
+ (year(current_date) - 1) years
+ (month(current_date) - 1) months
--
Jeroen |
|
|
| Back to top |
|
|
|
|
|
All times are GMT
The time now is Sun Nov 22, 2009 9:07 am
|
|