Time based query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Time based query

Author

Topic

tiss0183
Starting Member

18 Posts

Posted - 2008-10-30 : 16:53:37

I am working on a query to report possible money laundering.

A simple query can identify high activity accounts OVER EACH MONTH of time. The fields:
Month
Account#
Wire Transactions In (Count)
Wire Transactions Out (Count)

However, management wants to find accounts that had high activity over any 30 consecutive day period. I want to make a report which shows all accounts and their highest activity month by 30-day period:

Start Date
End Date
Account#
Wire Transactions In (Count)
Wire Transactions Out (Count)

Can you please point me in the right direction to write a query to solve this? Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-30 : 23:53:48

can you show table structures with some sample data?

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-10-31 : 06:26:11

I tried this from my transaction table and I belive that it should return the corect results

SELECT DISTINCT
  StartDate = DATEADD(dd, -30, a.DateTimeColumn),
  EndDate = DateTimeColumn,
  CountOut = (SELECT COUNT(*) 
    FROM transactiontable b 
    WHERE DateTimeColumn BETWEEN DATEADD(dd, -30, a.DateTimeColumn) AND a.DateTimeColumn 
      AND AccountNumber = a.AccountNumber
      AND Amount_Out > 0),
  CountIn = (SELECT COUNT(*) 
    FROM transactiontable b 
    WHERE DateTimeColumn BETWEEN DATEADD(dd, -30, a.DateTimeColumn) AND a.DateTimeColumn 
      AND AccountNumber = a.AccountNumber
      AND Amount_In > 0)
FROM transactiontable a
WHERE AccountNumber = @AccountNumber
ORDER BY StartDate DESC

- Lumbago

tiss0183
Starting Member

18 Posts

Posted - 2008-10-31 : 12:16:18

Thank you very much. Still trying to figure out how it works but it works!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources