Need to identify break in

[Darrel77]

Here's what I need...

My simple query looks like this....

SELECT acctnum, checknum, postdate, amt
FROM trans
WHERE checknum is not null
AND postdate between '30-JAN-06' AND '02-FEB-06'
GROUP BY acctnum, checknum, postdate, amt
ORDER BY acctnum, checknum

...I need to create a report that identifies checks that clear an account during any given week that are off by
more than 100 check numbers. For example, check # 821 clears October 26 and then check # 989 clears on October 28. This would be identified in the report as a warning sign for review.

Please assist. This project is driving me insane!!!

Thanks,
Darell

 

[SQLSister]

Try something like:

SELECT a.acctnum, checknum, postdate, amt
FROM trans t
join (select  acctnum from trans WHERE checknum is not null
AND postdate between '30-JAN-06' AND '02-FEB-06' and max(checknum)- Min(checknum)>100
group by acctnum) a 
on t.acctnum = a.acctnum
ORDER BY a.acctnum, checknum

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[Darrel77]

SQLSister...

I tried your post, but I get an error saying...

"ORA-00934: group function is not allowed here"

Please advise.

Thanks,
Darrel

 

[ca8msm]

That will be because you are using Oracle and asking the question in a SQL Server forum...


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[Darrel77]

Sorry about that....I'll repost in the Oracle forum

 

[ca8msm]

It's not a problem and all you'll need to do is modify the join slightly and change the between statement (maybe Oracle can use between, I'm can't remember). Something like this should work:

SELECT a.acctnum, checknum, postdate, amt
FROM trans t,
(select  acctnum from trans WHERE checknum is not null
AND postdate >= '30-JAN-06' AND postdate <= '02-FEB-06' and max(checknum)- Min(checknum)>100
group by acctnum) a
WHERE t.acctnum = a.acctnum
ORDER BY a.acctnum, checknum

____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[Darrel77]

ca8msm...

Thanks for the quick response....but I get the same error.

 

[ca8msm]

Hmmm not sure why. Maybe it would be best to take your question to the Oracle forum then as they may have an alternative method.


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.