April 8, 2009 at 10:24 pm
How to join below two table returned value. please Help me...
Create FUNCTION [dbo].[fnGetDatesforDday] ---Departure Date Calculation
(
-- Add the parameters for the function here
@DtFrom DATETIME,
@DtTo DATETIME,
@DayName VARCHAR(12)
)
RETURNS @DateList TABLE (id int,[Day] varchar(20),Dt datetime)
AS
BEGIN
IF NOT (@DayName = 'Mon' OR @DayName = 'Sun' OR @DayName = 'Tue' OR @DayName = 'Wed' OR @DayName = 'Thu' OR @DayName = 'Fri' OR @DayName = 'Sat')
BEGIN
--Error Insert the error message and return
INSERT INTO @DateList
SELECT null,'Invalid Day',NULL AS DAT
RETURN
END
DECLARE @TotDays INT
DECLARE @CNT INT
SET @TotDays = DATEDIFF(DD,@DTFROM,@DTTO)-- [NO OF DAYS between two dates]
SET @CNT = 0
WHILE @TotDays >= @CNT -- repeat for all days
BEGIN
-- Pick each single day and check for the day needed
IF substring(DATENAME(DW, (@DTTO - @CNT)),1,3) = @DAYNAME
BEGIN
INSERT INTO @DateList
SELECT 1,@DAYNAME,(@DTTO - @CNT) AS DAT
END
SET @CNT = @CNT + 1
END
RETURN
END
Create FUNCTION [dbo].[fnGetDatesforAday] -----Arrival Date Calculation
(
-- Add the parameters for the function here
@DtFrom DATETIME,
@DtTo DATETIME,
@DayName VARCHAR(12)
)
RETURNS @DateList TABLE (id int,[Day] varchar(20),Dt datetime)
AS
BEGIN
IF NOT (@DayName = 'Mon' OR @DayName = 'Sun' OR @DayName = 'Tue' OR @DayName = 'Wed' OR @DayName = 'Thu' OR @DayName = 'Fri' OR @DayName = 'Sat')
BEGIN
--Error Insert the error message and return
INSERT INTO @DateList
SELECT null,'Invalid Day',NULL AS DAT
RETURN
END
DECLARE @TotDays INT
DECLARE @CNT INT
SET @TotDays = DATEDIFF(DD,@DTFROM,@DTTO)-- [NO OF DAYS between two dates]
SET @CNT = 0
WHILE @TotDays >= @CNT -- repeat for all days
BEGIN
-- Pick each single day and check for the day needed
IF substring(DATENAME(DW, (@DTTO - @CNT)),1,3) = @DAYNAME
BEGIN
INSERT INTO @DateList
SELECT 1,@DAYNAME,(@DTTO - @CNT) AS DAT
END
SET @CNT = @CNT + 1
END
RETURN
END
April 10, 2009 at 3:38 am
Hi,
Just put the normal union
Select * from dbo.fnGetDatesforDday(‘’,’’,’’)
Union all
Select * from dbo.fnGetDatesforAday(‘’,’’,’’)
ARUN SAS
April 10, 2009 at 6:52 am
It is not clear what you want to do. Could you show us what you have so far or what you have already tried? That might help us to understand it better.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply