InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 10:05:49 GMT20http://www.sqlservercentral.com/Forums/Topic1420945-338-1.aspxHow about including [both] tables? (I'll stop at this point to include the usual disclaimers: "if the key columns are indexed...", etc.)[code="sql"]/* Create some test tables with data */IF OBJECT_ID('tempdb..#ArchiveTable') IS NOT NULL DROP TABLE #ArchiveTableCREATE TABLE #ArchiveTable ( [ID] INT IDENTITY(1,1) NOT NULL, [ReportDate] DATETIME NULL, [Col1] VARCHAR(50) NULL, PRIMARY KEY (ID))IF OBJECT_ID('tempdb..#ProdTable') IS NOT NULL DROP TABLE #ProdTableCREATE TABLE #ProdTable ( [ID] INT IDENTITY(10,1) NOT NULL, [ReportDate] DATETIME NULL, [Col1] VARCHAR(50) NULL, PRIMARY KEY (ID)) INSERT INTO #ArchiveTable ([ReportDate], [Col1]) SELECT '2011-09-06','A' INSERT INTO #ArchiveTable ([ReportDate], [Col1]) SELECT '2011-09-07','B' INSERT INTO #ArchiveTable ([ReportDate], [Col1]) SELECT '2011-09-08','C' INSERT INTO #ProdTable ([ReportDate], [Col1]) SELECT '2012-10-06','D' INSERT INTO #ProdTable ([ReportDate], [Col1]) SELECT '2012-10-07','E' INSERT INTO #ProdTable ([ReportDate], [Col1]) SELECT '2012-10-08','F' INSERT INTO #ProdTable ([ReportDate], [Col1]) SELECT '2012-10-09','G' INSERT INTO #ProdTable ([ReportDate], [Col1]) SELECT '2012-10-10','H' INSERT INTO #ProdTable ([ReportDate], [Col1]) SELECT '2012-10-11','I' INSERT INTO #ProdTable ([ReportDate], [Col1]) SELECT '2012-10-12','J' SELECT * FROM #ArchiveTableSELECT * FROM #ProdTable/* End test tables */ /* Set a variable to use for searching */DECLARE @d DATETIMESELECT @d = '2011-09-08'/* "Stand back! I'm gonna do SQL!" */SELECT Col1, 'A' AS Location FROM #ArchiveTable WHERE [ReportDate] = @d UNIONSELECT Col1, 'P' AS Location FROM #ProdTable WHERE [ReportDate] = @d DROP TABLE #ArchiveTableDROP TABLE #ProdTable[/code]Tue, 26 Feb 2013 07:42:56 GMTYour Name Herehttp://www.sqlservercentral.com/Forums/Topic1420945-338-1.aspxThanks for the reply, really appreciate it. I think you are correct regarding a date table and i'd normally have something like that in a data warehouse design. My opinion is that doing stuff like this with an OLTP system is a bit wonky but it is what it is.I also discovered that there was duplicate data between the ARCHIVE and PROD databases, this was throwing my numbers way off for certain date ranges. The biggest problem I have is that I only have access to the DEV environment and not production, didn't realize that they were out of synch. I was put on this project rather quickly and didn't have a chance to ask all of these questions ahead of time, gotta love dev'ing on the fly!Mon, 18 Feb 2013 17:34:25 GMTPolymorphisthttp://www.sqlservercentral.com/Forums/Topic1420945-338-1.aspxJust off the top of my head, I'd suggest that you need a date table so you don't have to try and calculate the FY and the date range for every row. Create the date table once then join on it or use it as a lookup table to get the date range.[code="sql"]/* Create some test tables */IF OBJECT_ID('tempdb..#ArchiveTable') IS NOT NULLDROP TABLE #ArchiveTableCREATE TABLE #ArchiveTable ( [ID] INT IDENTITY(1,1) NOT NULL, [ReportDate] DATETIME NULL, [Col1] VARCHAR(50) NULL, PRIMARY KEY (ID))IF OBJECT_ID('tempdb..#ProdTable') IS NOT NULLDROP TABLE #ProdTableCREATE TABLE #ProdTable ( [ID] INT IDENTITY(1,1) NOT NULL, [ReportDate] DATETIME NULL, [Col1] VARCHAR(50) NULL, PRIMARY KEY (ID)) INSERT INTO #ArchiveTableSELECT '2012-09-06 00:00:00.000','ABCDEFG' UNIONSELECT '2012-09-07 00:00:00.000','ABCDEFG' UNIONSELECT '2012-09-08 00:00:00.000','ABCDEFG' INSERT INTO #ProdTableSELECT '2012-10-06 00:00:00.000','ABCDEFG' UNIONSELECT '2012-10-07 00:00:00.000','ABCDEFG' UNIONSELECT '2012-10-08 00:00:00.000','ABCDEFG' UNIONSELECT '2013-02-14 00:00:00.000','ABCDEFG' UNIONSELECT '2013-02-15 00:00:00.000','ABCDEFG' UNIONSELECT '2013-02-16 00:00:00.000','ABCDEFG' /* End test tables */ [/code][code="sql"]/* Create the date table as a permanent table ONCE *//* I'm using a temp table just for this example */IF OBJECT_ID('tempdb..#DateTable') IS NOT NULLDROP TABLE #DateTableCREATE TABLE #DateTable ( [ReportDate] DATETIME NOT NULL, [FiscalYearDate] DATETIME NOT NULL, PRIMARY KEY (ReportDate))--populate the date table;WITH cte AS(SELECT CAST('2000-10-01' AS DATETIME) AS ReportDate -- go back as far as required UNION ALL SELECT ReportDate + 1 FROM cte WHERE ReportDate + 1 < '2050-10-01')INSERT INTO #DateTableSELECT r.ReportDate ,r.FiscalYearDateFROM ( SELECT ReportDate ,(CASE WHEN MONTH(ReportDate) > 9 THEN CAST(CAST(YEAR(ReportDate) AS CHAR(4)) + '-10-01' AS DATETIME) ELSE CAST(CAST(YEAR(ReportDate)-1 AS CHAR(4)) + '-10-01' AS DATETIME) END) AS FiscalYearDate FROM cte ) rOPTION (MAXRECURSION 0)/* End date table code */[/code][code="sql"]DECLARE @ReportDate DATETIME ,@FiscalYearDate DATETIME ,@strSQL VARCHAR(8000) --examplesSET @ReportDate = '2012-09-07 00:00:00.000'--SET @ReportDate = '2012-10-07 00:00:00.000'--SET @ReportDate = '2013-02-15 00:00:00.000' /* Method One - use dynamic SQL to pass in the correct table name to query */SET @strSQL = 'SELECT (CASE WHEN DATEDIFF(MONTH,d.FiscalYearDate,d.ReportDate) <= 6 THEN ''ProdTable'' ELSE ''ArchiveTable'' END) AS dbSource ,s.* FROM '+ CASE WHEN (SELECT DATEDIFF(MONTH,FiscalYearDate,ReportDate) FROM #DateTable WHERE ReportDate = @ReportDate) <= 6 THEN '#ProdTable' ELSE '#ArchiveTable' END + 'AS sINNER JOIN #DateTable AS d ON s.ReportDate = d.ReportDateWHERE d.ReportDate = CONVERT(DATETIME,'''+CAST(@ReportDate AS VARCHAR(20))+''',101)'EXEC(@strSQL)/* Method Two - use an IF statement to direct control to the proper query */--do a lookup from the date table firstSELECT @FiscalYearDate = FiscalYearDateFROM #DateTableWHERE ReportDate = @ReportDate --then choose which query to runIF DATEDIFF(MONTH,@FiscalYearDate,@ReportDate) <= 6 BEGIN SELECT 'ProdTable' AS dbSource ,* FROM #ProdTable WHERE ReportDate = @ReportDate ENDELSE BEGIN SELECT 'ArchiveTable' AS dbSource ,* FROM #ArchiveTable WHERE ReportDate = @ReportDate END[/code]Sun, 17 Feb 2013 15:12:45 GMTSteven Willishttp://www.sqlservercentral.com/Forums/Topic1420945-338-1.aspxHello, I'm urgently working away at this but not able to solve my problem. I'm at the point of needing some input. Here's my situation - I'm working with two OLTP databases - one is called ARCHIVE and the other is PROD. Over this weekend they moved all data which is older than six months from PROD to ARCHIVE. I need to update eight stored procedures so that they return data from the appropriate database(s). The big problem is that the procedures are pulling data based on two dates - one is whatever report date is passed to the procedure, and the other is the start of the fiscal year which is based on the report date. So, if a report date of 2013-02-15 is passed in, then the fiscal year start date is 2012-10-01 - 4 months difference between the two dates in this case. What i'm having a problem with logically is that in the month of August the fiscal year start date would be 10 months apart. In that case the data for the report date would be pulled from PROD, but the data going back beyond six months to the start of the fiscal year would need to be pulled from ARCHIVE. How would I do this? I'm really confused as to how to go about it. They told me that doing a UNION between the tables from the two databases would solve the issue, but it's inflating the numbers and nothing matches when I do that. Nothing else is coming to me at the moment and this is killing my weekend. Anyone ever worked on anything like this before? I could really use some ideas. I think having a data warehouse would be a better option, but they ain't got one.Thanks!!Sat, 16 Feb 2013 23:48:31 GMTPolymorphist