Forum Moderators: open
I'm using MS SQL server. I'm trying to SELECT * FROM 4 different tables WHERE (DueDate < Today'sDate), and therefore overdue.
I would like to display all of this information in a WebGrid and have the last column marked "Days Overdue".
I have a UserFunction created that will return the number of days overdue (posted at bottom) and return a column of "Total".
I need to send this function the due date for each of the rows within their respective tables.
How do I send all of the duedates to this function and make it work? Is there a better way to go about doing this?
Thanks in advance!
Theoretical SQL Statement:
SELECT dbo.Table1.Document name, dbo.Table2.Document name, dbo.Table3.Document name, dbo.Table4.Document name, dbo.OverdueFunction(DUEDATES) as "Days Overdue"
FROM dbo.Table1, dbo.Table2, dbo.Table3, dbo.Table4
WHERE DueDates < Today's Date
Function:
CREATE FUNCTION [dbo].[TotalOverDue]
(@CurrentDate as DateTime,
@DueDate as DateTime)
RETURNS varchar(50)
BEGIN
DECLARE @TotalOverDue as Integer
DECLARE @HourTotal as Integer
DECLARE @NetTimeOverdue as Varchar(50)
SET @TotalOverDue = DATEDIFF(minute,@CurrentDate,@DueDate)
SET @HourTotal=(@TotalOverDue/60)
SET @NetTimeOverdue = Cast((@HourTotal) as varchar(10)) + ' Days '
RETURN @NetTimeOverdue
END
SELECT table1.*, dateDiff("d", table1.dueDate, now()) as daysOverdue
FROM table1
WHERE dueDate < now()
