UDF - 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

Transact-SQL (2000)

UDF

Author

Topic

patshaw
Posting Yak Master

177 Posts

Posted - 2008-02-20 : 11:27:13

Hi,

I have never really used UDF's much and so I need some help creating one or even just finding out if this can be done with a UDF.

I have a column in my table called 'Schedule_Group' (VarChar 30). The column groups up one or multiple rows that contain Bookings data, so each Booking is grouped into a 'Schedule_Group' and one Schedule_Group can contain multiple bookings. In each Booking there is a column called 'Journey_Date' (DateTime).

I need to construct a UDF that, when a Schedule_Group is passed to it, will return a string containing the distinct Journey_Date months (ie. datename(month, JourneyDate)) contained in that Schedule_Group.

Can anyone offer me any help please?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-20 : 11:39:47

have a look at this:-
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=53293

patshaw
Posting Yak Master

177 Posts

Posted - 2008-02-20 : 12:16:17

This almost works perfectly but it is returning duplicate months in the output. How can I eliminate these?

I am using:

CREATE FUNCTION dbo.ReturnDistinctMonths(@Schedule_Group Varchar(30), @DriverID Int)
RETURNS VARCHAR(8000)
AS
BEGIN
	DECLARE @Output VARCHAR(8000)
	SELECT  @Output = COALESCE(@Output+', ', '') + CONVERT(varchar(20), datename(month, JourneyDate))
	FROM	dbo.Bookings
	WHERE	Schedule_Group = @Schedule_Group AND DriverID = @DriverID
	GROUP BY JourneyDate, datename(month, JourneyDate)
	ORDER BY datename(month, JourneyDate)

	RETURN @Output
END
GO

And I get the correct months in the string but I also get duplicates:

"December, January, January, January" are in the output when what I need is just "December, January". I have tried a DISTINCT in the function but this doesn't work.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-20 : 12:21:09

Try taking out JourneyDate field from GROUP BY

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-20 : 12:21:25

Try

CREATE FUNCTION dbo.ReturnDistinctMonths(@Schedule_Group Varchar(30), @DriverID Int)
RETURNS VARCHAR(8000)
AS
BEGIN
	DECLARE @Output VARCHAR(8000)
	SELECT  @Output = COALESCE(@Output+', ', '') + CONVERT(varchar(20), datename(month, JourneyDate))
	FROM	(select distinct JourneyDate from dbo.Bookings) as t
	WHERE	Schedule_Group = @Schedule_Group AND DriverID = @DriverID
	GROUP BY JourneyDate, datename(month, JourneyDate)
	ORDER BY datename(month, JourneyDate)

	RETURN @Output
END
GO

Madhivanan

Failing to plan is Planning to fail

patshaw
Posting Yak Master

177 Posts

Posted - 2008-02-20 : 12:41:45

No, neither of these work.

If I take out JourneyDate from the Group By I get the usual 'Invalid in the select list' error. If I use Madhivannan's method I get:
Invalid column name 'Schedule_Group' and Invalid column name 'DriverId'. If I add these to 'FROM (select distinct JourneyDate from dbo.Bookings) as t' it still returns dupes.

patshaw
Posting Yak Master

177 Posts

Posted - 2008-02-20 : 17:49:41

Got it.

CREATE FUNCTION dbo.ReturnDistinctMonths(@Schedule_Group Varchar(30), @DriverID Int)
RETURNS VARCHAR(8000)
AS
BEGIN
	DECLARE @Output VARCHAR(8000)
	SELECT  @Output = COALESCE(@Output+'/', '') + CONVERT(varchar(20), themonth)
	FROM
	(
	SELECT distinct datename(month, JourneyDate) as themonth
	FROM	dbo.Bookings
	WHERE	Schedule_Group = @Schedule_Group AND DriverID = @DriverID
	GROUP BY datename(month, JourneyDate), JourneyDate
	)a
	RETURN @Output
END
GO

Does the trick. Thanks for your help guys.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources