How to get this query result(Compare with previous year data)

• asita

  SSCarpal Tunnel

  Points: 4144

  More actions

  February 9, 2009 at 12:46 pm

  #205192

  i have a table Called DailyTrans with columns(Acct#,TransDate, City, State, Amount)

  as below (sample data...)

  Acct#,TransDate, City, State, Amount

  1001,Nov/01/2007,Herndon,VA,-120

  1002,Nov/10/2008,Borofield,VT,-393

  1004,Nov/01/2008,NYC,NY,190

  1002,Nov/17/2007,Jacson,NJ,780

  1002,Nov/21/2007,Elizabeth,NY,8493

  1004,Nov/28/2007,Norway,WI,340

  1002,Nov/09/2007,Dc,VA,-340

  .....

  .....

  ....

  1002,Nov/29/2007,Metropk,OH,-540

  .....

  ...

  how can i get information as below

  account#,Nov 2007 Debits, Nov 2008 Debits, Nov 2007 Credits,Nov 2008 Credits

  here Nov 2007 debits (all negative transaction between nov 1 2007 and nov 30 2007)

  here Nov 2008 debits (all negative transaction between nov 1 2008 and nov 30 2008)

  here Nov 2007 credits (all +ve transaction between nov 1 2007 and nov 30 2007)

  here Nov 2008 credits (all +ve transaction between nov 1 2008 and nov 30 2008)

  Please help me

  Thanks in advance

  Best Regards

  asini

• asita

  SSCarpal Tunnel

  Points: 4144

  More actions

  February 9, 2009 at 1:44 pm

  #940796

  with master peso idea it works as below(it may help ful for other users)

  SELECT SUM(CASE WHEN Amount < 0 AND TransDate BETWEEN '20071101' AND '20071130' THEN Amount ELSE 0 END),

  SUM(CASE WHEN Amount < 0 AND TransDate BETWEEN '20081101' AND '20081130' THEN Amount ELSE 0 END),

  SUM(CASE WHEN Amount > 0 AND TransDate BETWEEN '20071101' AND '20071130' THEN Amount ELSE 0 END),

  SUM(CASE WHEN Amount > 0 AND TransDate BETWEEN '20081101' AND '20081130' THEN Amount ELSE 0 END)

  FROM DailyTrans

  WHERE TransDate BETWEEN '20071101' AND '20071130'

  OR TransDate BETWEEN '20081101' AND '20081130'

  Best Regards

  asin