This query works fine when I run it in the query analyzer:
DECLARE @mo_start datetime,
SET @mo_start = '10/01/1998'
SET @mo_end = '10/31/1998'
FROM ORG_EPISODES AS e INNER JOIN ORG_TIME_PERIODS AS t
ON e.PERIOD_ID = t.PERIOD_ID
AND t.PERIOD_START BETWEEN @mo_start AND @mo_end
Since I need to export the results of the query to an Excel spreadsheet I used the Data Transformation Services wizard to set up this process and have it using this query.
I get an error when it executes because it builds a results table and tries to dump all the field names from both tables into it; both tables have a field called PERIOD_ID (that's what I joined on)and SQL is rejecting the process on the basis of results having 2 fields named PERIOD_ID. Renaming one of the fields is not a viable option because it's someone else's database and the conflict only is occurring because SQL is building an intermediary table and populating it with the fields from both tables, when the "run" query only has the values from one table.
Does anyone have any idea how to get around this and get my query result out to Excel?
Also, if I continue with the Data Transformation Services how can I set up the dates as global variables which can be changed each month when the user runs this. I saw a "holding" place for this under properties for the local Data Transformation Package - but don't know how to work with this.