Comparing Dates and Amount - 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

Comparing Dates and Amount

Author

Topic

praveen.green
Starting Member

2 Posts

Posted - 2011-06-14 : 02:55:52

Hi there!
Well I've a table like this one:
MainCode| TranDate | Amount

What I want to do is to list those maincodes along with the other two columns where
-transactions have been done for consecutive 2 or 3 days and
-the summation of amount of those transaction is above 1million.

I'd be more than glad for your help.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-06-14 : 03:19:29

[code]
select *
from [a table] t
where exists
(
select *
from [a_table] x
where x.MainCode = t.MainCode
and x.TranDate <> t.TranDate
and (
x.TranDate >= dateadd(day, -2, t.TranDate)
or x.TranDate <= dateadd(day, +2, t.TranDate)
)
)
and exists
(
select x.MainCode
from [a table] x
where x.MainCode = t.MainCode
group by x.MainCode
having sum(x.Amount) > 1000000
)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2011-06-14 : 03:20:47

select maincode,trandate from <tablename>
where trandate between getdate()-3 and getdate()
and sum(amount)>1000000

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-06-14 : 04:00:54

quote:
Originally posted by ahmeds08

select maincode,trandate from <tablename>
where trandate between getdate()-3 and getdate()
and sum(amount)>1000000

It is not possible to use sum function in the where clause

Madhivanan

Failing to plan is Planning to fail

praveen.green
Starting Member

2 Posts

Posted - 2011-06-14 : 04:45:10

Thanks Khtan for your effort. But the desired result isn't fulfilled yet. This query gave all those transactions which are done in 2 or 3 consecutive days. I only require those transactions whose sum exceed 10,000,000.00 and which is executed in 2 or 3 consecutive days.
Here I've made the changes in the name to make it look more plausible. Hope you'll sort this out.

select MainCode,TranDate,LCYAmount
from TransDetail t
where exists
(
select *
from TransDetail x
where x.MainCode = t.MainCode
and x.TranDate <> t.TranDate
and (
x.TranDate >= dateadd(day, -2, t.TranDate)
or x.TranDate <= dateadd(day, +2, t.TranDate)
)
)
and exists
(
select x.MainCode
from TransDetail x
where x.MainCode = t.MainCode
group by x.MainCode
having sum(x.Amount) > 1000000
)

In addition to this what if I input the date range parameters to narrow down my output.

Appreciation is always first-hand. Thanks!!!

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2011-06-14 : 04:49:48

thank you madhivan for the comment...
i missed it totally

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-06-14 : 16:04:38

quote:
Originally posted by madhivanan

quote:
Originally posted by ahmeds08

select maincode,trandate from <tablename>
where trandate between getdate()-3 and getdate()
and sum(amount)>1000000

It is not possible to use sum function in the where clause

Madhivanan

Failing to plan is Planning to fail

However, if you add a group by (needed to determine the sum) - you can then use the HAVING clause.

Jeff

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-06-20 : 10:24:53

quote:
Originally posted by jeffw8713

quote:
Originally posted by madhivanan

quote:
Originally posted by ahmeds08

select maincode,trandate from <tablename>
where trandate between getdate()-3 and getdate()
and sum(amount)>1000000

It is not possible to use sum function in the where clause

Madhivanan

Failing to plan is Planning to fail

However, if you add a group by (needed to determine the sum) - you can then use the HAVING clause.

Jeff

Yes that was my point. Anyway you can not use it in the WHERE clause

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources