Repeating data in union query

• paul.sheffield

  SSC Veteran

  Points: 208

  More actions

  February 1, 2010 at 5:03 pm

  #209568

  Hi. Very newbie....

  I have a query I've written. I've taken most of the dates and unions out but sample below. Next month I'll need to add in an additional line for Jan 10 for each select statement. Is there anyway I can set this once and get each query to look at it without creating a new table?

  select 'Jan08'

  , sum((case when c.closing_date >= '2009-11-01' and c.closing_date < '2009-12-01' then 1 else 0 end)) as Nov09

  , sum((case when c.closing_date >= '2009-12-01' and c.closing_date < '2010-01-01' then 1 else 0 end)) as Dec09

  from sales s left outer join closed c

  on s.sortcode = c.sort_code and s.accountnumber = c.acc_num

  where s.datesold >='2008-01-01' and s.datesold < '2008-02-01'

  union

  select 'Feb08'

  , sum((case when c.closing_date >= '2009-11-01' and c.closing_date < '2009-12-01' then 1 else 0 end)) as Nov09

  , sum((case when c.closing_date >= '2009-12-01' and c.closing_date < '2010-01-01' then 1 else 0 end)) as Dec09

  from Stock2008..salesrecord s left outer join ClosedAccounts..customers c

  on s.sortcode = c.sort_code and s.accountnumber = c.acc_num

  where s.datesold >='2008-02-01' and s.datesold < '2008-03-01'

  etc etc

• Jeff Moden

  SSC Guru

  Points: 1004644

  More actions

  February 1, 2010 at 5:51 pm

  #1112027

  Absolutely. See the following link. Almost exactly what you need...

  http://www.sqlservercentral.com/articles/Crosstab/65048/

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)