Results 1 to 2 of 2
  1. #1
    Join Date
    May 2005
    Posts
    40

    Unanswered: num of checks written in 4 day period for more than $400

    I have a transaction table which has Date as datetime field, amount and account number. i want to find out count of checks that were written in a period of 4 days which exceeded i.e. > $400, between 401 and 500, > 501 for a single month. the table has data for more than a year and i want the results then grouped in monthly format like in
    OCT between 300 & 400 #30 (30 customers gave checks total worth $300-$400 within any 4 consecutive days period in the month of OCT )
    between 400 & 500 # 20
    > 501 # 10

    NOV between 300 & 400 #30
    between 400 & 500 # 20
    > 501 # 10

    and so on for a 6 month period.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You will need to use a self join. Something like this:

    select Table1.CustomerID, sum(Table2.CheckValue)
    from YourTable Table1 inner join YourTable Table2
    on Table1.CustomerID = Table2.CustomerID
    and Table1.CheckDate > Table2.CheckDate
    and datediff(day, Table2.CheckDate, Table1.CheckDate) <= 4
    group by Table1.CustomerID
    having sum(Table2.CheckValue) > 400
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •