Prev: String functions in SQL
Next: Delete Backup History
From: danfan46 on 21 Oct 2009 02:37
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
From: Tonkuma on 21 Oct 2009 08:56
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
;

From: Tonkuma on 22 Oct 2009 10:42
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
;

From: Tonkuma on 22 Oct 2009 10:51
On Oct 22, 11:42 pm, Tonkuma <tonk...(a)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
From: danfan46 on 22 Oct 2009 12:08
Tonkuma wrote:
> On Oct 22, 11:42 pm, Tonkuma <tonk...(a)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
 |  Next  |  Last
Pages: 1 2
Prev: String functions in SQL
Next: Delete Backup History