February 12, 2004 at 1:11 am
Hi all,
strange problem here
I have a storedproc on SQLSERVER :
CREATE PROCEDURE [sp_Overdue_Trans_2]
AS
SELECT TOP 100 PERCENT tblopdracht.*, DATEDIFF(day, VrgDueDateTrans, GETDATE()) AS OverdueTrans,
dbo. udf_GetWorkingDays(duedatetrans,current_timestamp)
AS ODTRA,
dbo.udf_GetWorkingDays(DueDatPdi,current_timestamp) AS ODPDI
FROM dbo.tblOpdracht
WHERE (archief=0) and (DATEDIFF(day, DueDateTrans, GETDATE()) <> '' and isnull(status,'')<>'AFGW' and isnull(status,'') <> 'ANNUL' and isnull(status,'')<>'AFGWZG')
AND swt_TypeTrans = 1
GO
when i run this query it works fine ! no problem.
BUT when i call this storedProc in Access :
execute sp_overdue_Trans_2
then i get the same amount of records but the 2 last fields ODTRA and ODPDI are empty ! and when i run the stp on SQLServer, these fields are filled in properly !
well i figured out that it probably has something to do with the userdefined function
below is the code
CREATE FUNCTION dbo.udf_GetWorkingDays
( @StartDate datetime,
@EndDate datetime )
RETURNS INT
AS
BEGIN
DECLARE @WorkDays int, @FirstPart int
DECLARE @FirstNum int, @TotalDays int
DECLARE @LastNum int, @LastPart int
-- IF (DATEDIFF(day, @StartDate, @EndDate) < 1)
-- BEGIN
-- RETURN ( 0 )
-- END
SELECT
@TotalDays = DATEDIFF(day, @StartDate, @EndDate) ,
@FirstPart = CASE DATENAME(weekday, @StartDate)
WHEN 'Sunday' THEN 6
WHEN 'Monday' THEN 5
WHEN 'Tuesday' THEN 4
WHEN 'Wednesday' THEN 3
WHEN 'Thursday' THEN 2
WHEN 'Friday' THEN 1
WHEN 'Saturday' THEN 0
END,
@FirstNum = CASE DATENAME(weekday, @StartDate)
WHEN 'Sunday' THEN 5
WHEN 'Monday' THEN 4
WHEN 'Tuesday' THEN 3
WHEN 'Wednesday' THEN 2
WHEN 'Thursday' THEN 1
WHEN 'Friday' THEN 0
WHEN 'Saturday' THEN 0
END
IF (@TotalDays < @FirstPart)
BEGIN
SELECT @WorkDays = @TotalDays
END
ELSE
BEGIN
SELECT @WorkDays = (@TotalDays - @FirstPart) / 7
SELECT @LastPart = (@TotalDays - @FirstPart) % 7
SELECT @LastNum = CASE
WHEN (@LastPart < 7) AND (@LastPart > 0) THEN @LastPart - 1
ELSE 0
END
SELECT @WorkDays = @WorkDays * 5 + @FirstNum + @LastNum
END
declare @Holidays int
declare @ReturnValue int
SELECT @Holidays=Count(1) FROM tblVerlof WHERE Datum BETWEEN @StartDate AND @EndDate
select @ReturnValue = @WorkDays-@Holidays
if @returnvalue <0 select @returnvalue = 0
RETURN ( @returnvalue )
END
anyone any idea?? i'm totally lost here !
TIA
February 12, 2004 at 8:20 am
I have 3 comments!
1. why are you using TOP xxx ... WITHOUT ORDER BY in your sp?
2. why are you using TOP 100 PERCENT if that is not a view ?
3. Do you have any Fields with those names on "tblOpdracht" ?
* Noel
March 23, 2004 at 7:57 pm
A bit late sending in a reply (been on holiday!)
My approach to calculating working days is a bit simpler. Holiday is a table containing the dates of all holidays.
datediff(dd,convert(char(11),start_date),@end_date) - (select count(*) from holiday where date between start_date and @end_date and datepart(dw,date) not in (1,7)) - 2 * datediff(dd, dateadd(dd,-datepart(dw,start_date),start_date), dateadd(dd,-datepart(dw,@end_date),@end_date) )/7 as 'Working Days',
The code assumes both starting date and ending date are not in the weekend!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply