How do I add condition for a sum() value in the select statement?

Hi
I'm builing this string dynamically in C# program. I need to add one more
filtering condition of "Sum(Amount) >0" to the Select statement. I added it
but I got an error message. Can someone help on how to do this in one select
statment?
Thanks, Alpha
select exttid,sum(amount) as FeeBalance from tblLedger
where exttid in
(select distinct(exttid) from tblBilling b
where datediff(day,cast('5/17/2005' as datetime),b.formdate) >= 0
and datediff(day,cast('8/17/2005' as datetime),b.formdate) <= 0)
and void = 0
group by exttid
Look at the "Having" clause of the select statement. "Having" works on
"Group By" much like the 'Where" works on "From". In your case, you would
add something like:
HAVING Sum(Amount) > 0
after your Group By section.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:F6DA3969-24F9-4895-B7CB-93ECC06EB852@.microsoft.com...
> Hi
> I'm builing this string dynamically in C# program. I need to add one more
> filtering condition of "Sum(Amount) >0" to the Select statement. I added
> it
> but I got an error message. Can someone help on how to do this in one
> select
> statment?
> Thanks, Alpha
> select exttid,sum(amount) as FeeBalance from tblLedger
> where exttid in
> (select distinct(exttid) from tblBilling b
> where datediff(day,cast('5/17/2005' as datetime),b.formdate) >= 0
> and datediff(day,cast('8/17/2005' as datetime),b.formdate) <= 0)
> and void = 0
> group by exttid

How do I add condition for a sum() value in the select statement?

Hi
I'm builing this string dynamically in C# program. I need to add one more
filtering condition of "Sum(Amount) >0" to the Select statement. I added it
but I got an error message. Can someone help on how to do this in one select
statment?
Thanks, Alpha
select exttid,sum(amount) as FeeBalance from tblLedger
where exttid in
(select distinct(exttid) from tblBilling b
where datediff(day,cast('5/17/2005' as datetime),b.formdate) >= 0
and datediff(day,cast('8/17/2005' as datetime),b.formdate) <= 0)
and void = 0
group by exttidLook at the "Having" clause of the select statement. "Having" works on
"Group By" much like the 'Where" works on "From". In your case, you would
add something like:
HAVING Sum(Amount) > 0
after your Group By section.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:F6DA3969-24F9-4895-B7CB-93ECC06EB852@.microsoft.com...
> Hi
> I'm builing this string dynamically in C# program. I need to add one more
> filtering condition of "Sum(Amount) >0" to the Select statement. I added
> it
> but I got an error message. Can someone help on how to do this in one
> select
> statment?
> Thanks, Alpha
> select exttid,sum(amount) as FeeBalance from tblLedger
> where exttid in
> (select distinct(exttid) from tblBilling b
> where datediff(day,cast('5/17/2005' as datetime),b.formdate) >= 0
> and datediff(day,cast('8/17/2005' as datetime),b.formdate) <= 0)
> and void = 0
> group by exttid|||Oh, I see. I have to use Having and it's after the Group. It works great.
Thank you very much and have a great day!
"Geoff N. Hiten" wrote:
> Look at the "Having" clause of the select statement. "Having" works on
> "Group By" much like the 'Where" works on "From". In your case, you would
> add something like:
> HAVING Sum(Amount) > 0
> after your Group By section.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:F6DA3969-24F9-4895-B7CB-93ECC06EB852@.microsoft.com...
> > Hi
> > I'm builing this string dynamically in C# program. I need to add one more
> > filtering condition of "Sum(Amount) >0" to the Select statement. I added
> > it
> > but I got an error message. Can someone help on how to do this in one
> > select
> > statment?
> >
> > Thanks, Alpha
> >
> > select exttid,sum(amount) as FeeBalance from tblLedger
> > where exttid in
> > (select distinct(exttid) from tblBilling b
> > where datediff(day,cast('5/17/2005' as datetime),b.formdate) >= 0
> > and datediff(day,cast('8/17/2005' as datetime),b.formdate) <= 0)
> > and void = 0
> > group by exttid
>
>

How do I add condition for a sum() value in the select statement?

Hi
I'm builing this string dynamically in C# program. I need to add one more
filtering condition of "Sum(Amount) >0" to the Select statement. I added it
but I got an error message. Can someone help on how to do this in one selec
t
statment?
Thanks, Alpha
select exttid,sum(amount) as FeeBalance from tblLedger
where exttid in
(select distinct(exttid) from tblBilling b
where datediff(day,cast('5/17/2005' as datetime),b.formdate) >= 0
and datediff(day,cast('8/17/2005' as datetime),b.formdate) <= 0)
and void = 0
group by exttidLook at the "Having" clause of the select statement. "Having" works on
"Group By" much like the 'Where" works on "From". In your case, you would
add something like:
HAVING Sum(Amount) > 0
after your Group By section.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:F6DA3969-24F9-4895-B7CB-93ECC06EB852@.microsoft.com...
> Hi
> I'm builing this string dynamically in C# program. I need to add one more
> filtering condition of "Sum(Amount) >0" to the Select statement. I added
> it
> but I got an error message. Can someone help on how to do this in one
> select
> statment?
> Thanks, Alpha
> select exttid,sum(amount) as FeeBalance from tblLedger
> where exttid in
> (select distinct(exttid) from tblBilling b
> where datediff(day,cast('5/17/2005' as datetime),b.formdate) >= 0
> and datediff(day,cast('8/17/2005' as datetime),b.formdate) <= 0)
> and void = 0
> group by exttidsql

How client program get the events?

I know SQL 2005 notification service can send email, file, HTTP protocol to inform the client program the events.

1) How to implement the HTTP solution? My client program is Java or MFC. A sample is really appreciate.

2) If TCP socket could be listened for the events too?

Thanks a lot.

I don't have or even know of an example where the end subscriber is a client application (although that is a commonly asked question in the forum/newsgroup).

You'll need to create a custom delivery channel to provide this functionality.

You may also want to check out Query Notifications, as that technology may also suit your needs.

HTH...

Joesql

How client program get the events?

I know SQL 2005 notification service can send email, file, HTTP protocol to inform the client program the events.

1) How to implement the HTTP solution? My client program is Java or MFC. A sample is really appreciate.

2) If TCP socket could be listened for the events too?

Thanks a lot.

I don't have or even know of an example where the end subscriber is a client application (although that is a commonly asked question in the forum/newsgroup).

You'll need to create a custom delivery channel to provide this functionality.

You may also want to check out Query Notifications, as that technology may also suit your needs.

HTH...

Joe

How can i use embbed sql(e.g. SQC) program to access MSSQL from linux or unix?

hello all!
Help!
I've a lot of programs written in sqlc to mssql on the windows platform.Now the boss wants to use linux server and I've to tune all my programs in sqlc to linux.But how to do it?Does anyone help me?Don't have any experience in linux, thought this link (http://www.linux.org/apps/AppId_8044.html) may give you required information.

HTH|||Thanks, but this link was on MySQL, not MSSQL. I also have some information on this through using freeTDS or DBI, but on embedded sql, I found nothing!:(|||Then may be you need to do other way round, how about this link? (http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=921&lngWId=8)