SQL Query, return value from table with no join
- by jdenomme19
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
