5 Replies Latest reply: Aug 13, 2013 1:38 PM by Tan Soo Chin RSS

    Count product Help

    Tan Soo Chin

      Hi

       

      I need to count how many transactions have been made for each of the product from the every solddate + 7 days. This  is my table:

      ProductSoldDate
      100101-Jul-13
      100103-Jul-13
      100103-Jul-13
      100104-Jul-13
      100110-Jul-13
      100204-Jul-13
      100205-Jul-13
      100206-Jul-13
      100226-Jul-13

      I wish to achieve the following result:

      ProductSoldDateCount
      100101-Jul-134
      100103-Jul-134
      100103-Jul-133
      100104-Jul-132
      100110-Jul-131
      100204-Jul-133
      100205-Jul-132
      100206-Jul-131
      100226-Jul-131

      For example, for product 1001, count for 01-Jul-13 is 4 because there are 4 transactions before 08-Jul-13 where solddate>=solddate but <=solddate+7.

      Count for 04-Jul-13 is 2 because there are 2 transactions before 11-Jul-13.

      It would be great if anyone can help me on this. Thanks in advance.