check through dates in a table for between match

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

Transact-SQL (2000)

check through dates in a table for between match

Author

Topic

Fearless_Shultz
Starting Member

6 Posts

Posted - 2005-05-06 : 07:17:43

Hello all,

I am trying to write a function whereby when a customer specifies a collection and return date for a car rental, a stored procedure checks to see if any of the dates in the hire relate to a record in the table shown below by having a date that is within that range and if so the number of days that do should be charged at a higher or lower rate as appropriate, whis will be done by getting the alternate rate id from the table


// alternate rates table//
BranchID	DateStart	DateEnd	AltRateID
MANAIR	16/10/2005	18/10/2005	1
MANAIR	12/10/2005	14/10/2005	2

below is the function that I currently have. In this function


(SELECT DateRecord FROM dbo.GetDateRange(@CollectionDate, @ReturnDate) AS test)

is basically a table returned from another function containing every date in the hire period and I want to check to see if any of these dates are in an altrateperiod and if so, I want to get the altrateID and the number of days... the number of days issue aside, I am just currently trying to get the altrateid but when I run the sproc which calls this function I get


Server: Msg 512, Level 16, State 1, Procedure GetRates, Line 14
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I can understand what the problem is, but am not sure how to resolve it as I have only recently moved over to using sql server from access and am not fully aware of what I can do yet... if anyone can offer any assistance it would be hugely appreciated! the function in full is shown below

many many thanks in anticipation

shultz


CREATE FUNCTION [dbo].[GetPeriods] (@collectionDate datetime, @returnDate datetime, @branchID varchar (10))
RETURNS TABLE

 AS  

RETURN

(SELECT altRateID, dbo.GetDaysInPeriod(@CollectionDate, @returnDate,altRateID, @branchID) AS DaysInPeriod
FROM tblalternateRates WHERE  (SELECT DateRecord FROM dbo.GetDateRange(@CollectionDate, @ReturnDate) AS test)  BETWEEN  dateStart AND dateEnd  AND  @branchID = @branchID)

Wisest is he who knows he does not know

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-05-06 : 08:46:53

we need more information.
(SELECT DateRecord FROM dbo.GetDateRange(@CollectionDate, @ReturnDate) AS test)
is obviously a subquery, prefaced with =, !=, <, <= , >, >= or is an expression. That is not allowed. Show us the full query.

Fearless_Shultz
Starting Member

6 Posts

Posted - 2005-05-06 : 13:59:49

Hi Don,

many thanks for you reply but I have now got this query doing what I want using the following:


CREATE FUNCTION [dbo].[GetPeriods] (@collectionDate datetime, @returnDate datetime, @branchID varchar (10))
RETURNS TABLE

 AS  

RETURN
(SELECT altRateID,  COUNT(DISTINCT DateRecord) AS DaysInPeriod
FROM tblalternateRates, dbo.GetDateRange(@CollectionDate, @ReturnDate)

WHERE DateRecord BETWEEN  dateStart AND dateEnd  AND  @branchID = @branchID
GROUP BY altRateID

just thought it only polite to reply that I have solved the issue even though I am not sure how much it will help others...

thanks again

shultz

Wisest is he who knows he does not know

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources