There are two tables
Table 1 :
Year char(4) MonthKey char(2) ComplaintCount int
Table 2 :
FileCode varchar(50) StartDate varchar(10) EndDate varchar(10)
I wrote a query and the query takes a long long time to execute. Of course there is no result and its still executing and i think it is in a loop maybe, which is not expected!
The query is:
SELECT dbo.GetLastDayOfMonth(c.Year,c.Monthkey) AS DateKey, ComplaintCount, Count(FileCode) FROM Table1 C INNER JOIN Table2 F ON dbo.GetLastDayOfMonth(c.Year,c.Monthkey) = dbo.GetLastDayOfMonth(LEFT(f.FileFormationDate,4),SUBSTRING(f.FileFormationDate,6,2)) GROUP BY dbo.GetLastDayOfMonth(c.Year,c.Monthkey), c.ComplaintCount
The function GetLastDayOfMonth() returns the last day of month according to the Persian Calendar. There are 3,000,000 records in Table 1 and 3 records in Table 2. Even running the
JOIN by itself (without the
GROUP BY) is still executing and until now i can see 1,000,000 records in the Results pane.
What could the problem be and how can I fix it?
This is the code for the function:
CREATE FUNCTION [dbo].[GetLastDayOfMonth] ( @year CHAR(4) ,@Month CHAR(2) ) RETURNS VARCHAR(10) AS BEGIN DECLARE @result VARCHAR(10) SET @result = ( SELECT max(datekey) FROM StageArea.Common.DimTime WHERE [Year] = @year AND MonthKey = @Month AND LastDayOfMonthKey = 1 ) RETURN @result END