I'm hoping for an idea on the best way to approach what I'm trying to do.

I have a table with a list of transactions. Each transactions has a PostDate in DateTime format. I have another table holding the fiscal period values. This table has the following columns; FiscalYear, FiscalMonth, StartDate, EndDate.

I'm trying to write a query that will return all values from my transactions table, along with the FiscalYear and FiscalMonth of the PostDate. So I guess I'm just trying to return the FiscalYear and FiscalMonth values when the PostDate falls between the StartDate and EndDate.

I've tried using a Subbuery, but I have little experience with them and kept returning an error message that the subquery was returning more than 1 value. Help would be appreciated

    SELECT Transactions.PostDate, Transactions.TranKey, Transactions.CustKey, 
                      (SELECT FiscalPeriod.FiscPer
                        FROM FiscalPeriod
                        WHERE (Transactions.PostDate > CONVERT(Datetime, FiscalPeriod.StartDate, 102)) AND (Transactions.PostDate < CONVERT(DATETIME, FiscalPeriod.EndDate, 102))) AS FisPer
    FROM Transactions