Need one result for each - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Need one result for each

Author

Topic

cjhardie
Yak Posting Veteran

58 Posts

Posted - 2007-01-23 : 16:15:34

I need to make my result set only show one row any help??

declare @startdate datetime, @enddate datetime
set @startdate = '1/1/2007'
set @enddate = '1/1/2020'

select
b.businessname,
bb.businessname,
(select sum(amount) from billingschedule bs1 where billingdate >= @startdate and bs1.clientcontractid = cc.clientcontractid)
from clientcontract cc
inner join contact c on cc.salesrepcontactid = c.contactid
inner join business b on c.businessid = b.businessid
inner join business bb on cc.businessid = bb.businessid
inner join venue v on cc.venueid = v.venueid
inner join contact c1 on cc.businessid = c1.businessid and c1.deletedate is null
inner join billingschedule bs on cc.clientcontractid = bs.clientcontractid and bs.deletedate is null

where cc.startdate < @startdate and cc.enddate >= @startdate
and cc.deletedate is null
and b.businessid != 1
order by b.businessname

AOM-Austin American Home Mortgage 225.00
AOM-Austin American Home Mortgage 225.00
AOM-Austin American Home Mortgage 225.00
AOM-Austin American Home Mortgage 225.00
AOM-Austin American Home Mortgage 225.00
AOM-Austin American Home Mortgage 225.00
AOM-Austin American Home Mortgage 225.00
AOM-Austin American Home Mortgage 225.00
AOM-Austin American Home Mortgage 225.00
AOM-Austin American Home Mortgage 225.00
AOM-Austin American Home Mortgage 225.00
AOM-Austin American Home Mortgage 225.00

sshelper
Posting Yak Master

216 Posts

Posted - 2007-01-23 : 17:12:07

Try adding a DISTINCT clause to your SELECT statement:

declare @startdate datetime, @enddate datetime
set @startdate = '1/1/2007'
set @enddate = '1/1/2020'

select DISTINCT
b.businessname,
bb.businessname,
(select sum(amount) from billingschedule bs1 where billingdate >= @startdate and bs1.clientcontractid = cc.clientcontractid)
from clientcontract cc
inner join contact c on cc.salesrepcontactid = c.contactid
inner join business b on c.businessid = b.businessid
inner join business bb on cc.businessid = bb.businessid
inner join venue v on cc.venueid = v.venueid
inner join contact c1 on cc.businessid = c1.businessid and c1.deletedate is null
inner join billingschedule bs on cc.clientcontractid = bs.clientcontractid and bs.deletedate is null

where cc.startdate < @startdate and cc.enddate >= @startdate
and cc.deletedate is null
and b.businessid != 1
order by b.businessname

SQL Server Helper
http://www.sql-server-helper.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources