SQL Server query takes too long to execute. What could the problem be? 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
Thank you for your effort and suggestions. Here is the solution which took only 6 minutes to execute:

WITH T1
AS (
    SELECT FileCode
        ,dbo.GetLastDayOfMonth(left(f.FileFormationDate, 4), substring(f.FileFormationDate, 6, 2)) AS Datekey
    FROM [StageArea].[Sales].[viwLossCalculation] f
    GROUP BY FileCode
        ,dbo.GetLastDayOfMonth(left(f.FileFormationDate, 4), substring(f.FileFormationDate, 6, 2))
    )
    ,T2
AS (
    SELECT dbo.GetLastDayOfMonth(c.Year, c.Monthkey) AS datekey2
        ,ComplaintCount AS complaintcount
    FROM [StageArea].[Preservation].[viwComplaint] c
    )
SELECT C.DateKey
    ,f.complaintcount
    ,COUNT(DISTINCT c.FileCode) AS FileCodeCount
FROM T1 C
INNER JOIN T2 F ON C.DateKey = F.DateKey2
GROUP BY C.DateKey
    ,f.ComplaintCount;