SQL equivalent of LastPeriods( ) function

• bpetruzzi

  SSC Rookie

  Points: 42

  More actions

  January 2, 2003 at 10:11 am

  #80101

  I'm trying to write some SQL that generates a report that selects and groups data by month over the last x months. I want to generate something similar to the output rcvd when using LastPeriods in MDX, but am struggling to get the proper date comparison in my where clause and I am not sure how, if possible, to get SQL to show me months for which there is no data (akin to not using the NOT EMPTY clause in MDX).

  Here's what I have thus far. THis pulls back all data in the table by month. I want to alter this code to only go back 3 months (this month, and the two prior).

  SELECT [Date] = REPLACE(RIGHT(STR(MONTH, ab.insert_date)),2),' ',0) + '/' + RIGHT(STR(YEAR(insert_date)),4),

  [Unique Actions] = count(distinct(action_num))

  FROM MyTable

  GROUP BY REPLACE(RIGHT(STR(MONTH(insert_date)),2),' ',0) + '/' + RIGHT(STR(YEAR(insert_date)),4)

  THis pulls back all data in the table by month. I want to alter this code to only go back 4 months (this month, and the three prior).

  Any help would be greatly appreciated.

  bpetruzz@ureach.com

• David Burrows

  SSC Guru

  Points: 65142

  More actions

  January 2, 2003 at 11:09 am

  #445455

  create table #mth (year int,month int)

  declare @d datetime

  set @d = getdate()

  insert into #mth values(year(@d),month(@d))

  set @d = dateadd(m,-1,@d)

  insert into #mth values(year(@d),month(@d))

  set @d = dateadd(m,-1,@d)

  insert into #mth values(year(@d),month(@d))

  set @d = dateadd(m,-1,@d)

  insert into #mth values(year(@d),month(@d))

  select left(str(m.year,4,0)+'/'+replace(str(m.month,2,0),' ','0'),7) as 'Date',

  count(distinct(action_num)) as 'Unique Actions'

  from #mth m

  left outer join MyTable t on year(t.insert_date) = m.year and month(t.insert_date) = m.month

  group by m.year,m.month

  order by m.year,m.month

  Far away is close at hand in the images of elsewhere.
  Anon.