What is wrong with this Function

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)

What is wrong with this Function

Author

Topic

wangyc77
Yak Posting Veteran

65 Posts

Posted - 2007-02-05 : 08:22:27

This thing always has a error, but it is fine when I only have one select in it

what is wrong with it?
CREATE FUNCTION dbo.RJ_PriceRequest
(
@fromdate SMALLdatetime, @todate SMALLdatetime, @ecity varchar(30), @NoOfPeople int
)
RETURNS TABLE
AS
RETURN
(
select SID,sum(price) from
(
select t.sid,
d.seqdate as adate,
(
CASE datepart(weekday, d.seqdate)
WHEN 1 THEN t.MondayP
WHEN 2 THEN t.TuesdayP
WHEN 3 THEN t.WednesdayP
WHEN 4 THEN t.ThursdayP
WHEN 5 THEN t.FridayP
WHEN 6 THEN t.SaturdayP
WHEN 7 THEN t.SundayP
END) as Price
from fnseqdates(@fromdate, @todate, 1) as d
left join TempRJ as t on t.startdate <= d.seqdate and t.enddate >= d.seqdate
where t.ecity=@ecity and t.NoOfPeople=@NoOfPeople
)
GROUP BY sid
HAVING Count(*)= datediff( d, @fromdate, @Todate )+1
)

**Jonathan**

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-05 : 08:29:27

[code]select SID,
sum(price)
from (
select t.sid,
d.seqdate as adate,
CASE datepart(weekday, d.seqdate)
WHEN 1 THEN t.MondayP
WHEN 2 THEN t.TuesdayP
WHEN 3 THEN t.WednesdayP
WHEN 4 THEN t.ThursdayP
WHEN 5 THEN t.FridayP
WHEN 6 THEN t.SaturdayP
WHEN 7 THEN t.SundayP
END as Price
from fnseqdates(@fromdate, @todate, 1) as d
left join TempRJ as t on t.startdate <= d.seqdate
and t.enddate >= d.seqdate
and t.ecity = @ecity
and t.NoOfPeople = @NoOfPeople
) as d
GROUP BY sid
HAVING Count(*) = datediff(day, @fromdate, @Todate) + 1[/code]

Peter Larsson
Helsingborg, Sweden

wangyc77
Yak Posting Veteran

65 Posts

Posted - 2007-02-05 : 19:51:57

select SID,
sum(price)
from (
select t.sid,
d.seqdate as adate,
CASE datepart(weekday, d.seqdate)
WHEN 1 THEN t.MondayP
WHEN 2 THEN t.TuesdayP
WHEN 3 THEN t.WednesdayP
WHEN 4 THEN t.ThursdayP
WHEN 5 THEN t.FridayP
WHEN 6 THEN t.SaturdayP
WHEN 7 THEN t.SundayP
END as Price
from fnseqdates(@fromdate, @todate, 1) as d
left join TempRJ as t on t.startdate <= d.seqdate
and t.enddate >= d.seqdate
and t.ecity = @ecity
and t.NoOfPeople = @NoOfPeople
) as d
GROUP BY sid
HAVING Count(*) = datediff(day, @fromdate, @Todate) + 1

I tried Peter s code but it still got some error somewhere when i run it in a function

**Jonathan**

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-02-05 : 19:58:45

quote:
Originally posted by wangyc77
"...I tried Peter s code but it still got some error somewhere when i run it in a function..."

Peter does such sloppy work sometimes.

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-06 : 02:10:48

Thank you. I needed to come down to earth

What is the error you get, wangyc77?

Peter Larsson
Helsingborg, Sweden

wangyc77
Yak Posting Veteran

65 Posts

Posted - 2007-02-06 : 08:29:54

I think I got it.
select SID,
sum(price) as APrice
from (
select t.sid.....

I found that I need to give it a name inside a function so that there will no error
Thanks everyone

**Jonathan**

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources