Aligning rows horisontaly (again)
in [DB2]
|
Prev: String functions in SQL
Next: Delete Backup History
From: ChrisC on 22 Oct 2009 12:30 On Oct 22, 7:51 am, Tonkuma <tonk...(a)fiberbit.net> 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 or EXP_DATE >= current_date - day(current_date) days + 1 month
From: ChrisC on 22 Oct 2009 14:07 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
From: danfan46 on 22 Oct 2009 15:44 ChrisC wrote: > 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
From: Tonkuma on 22 Oct 2009 22:32 On Oct 23, 1:30 am, ChrisC <cunningham...(a)gmail.com> wrote: > On Oct 22, 7:51 am, Tonkuma <tonk...(a)fiberbit.net> 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 > > 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.
From: The Boss on 23 Oct 2009 15:43
Tonkuma wrote: > On Oct 23, 1:30 am, ChrisC <cunningham...(a)gmail.com> wrote: >> On Oct 22, 7:51 am, Tonkuma <tonk...(a)fiberbit.net> 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 >> >> 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 |