February 16, 2013 at 11:48 pm
Hello,
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!!
February 17, 2013 at 3:12 pm
Just 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.
/* Create some test tables */
IF OBJECT_ID('tempdb..#ArchiveTable') IS NOT NULL
DROP TABLE #ArchiveTable
CREATE 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 #ProdTable
CREATE TABLE #ProdTable (
[ID] INT IDENTITY(1,1) NOT NULL,
[ReportDate] DATETIME NULL,
[Col1] VARCHAR(50) NULL,
PRIMARY KEY (ID))
INSERT INTO #ArchiveTable
SELECT '2012-09-06 00:00:00.000','ABCDEFG' UNION
SELECT '2012-09-07 00:00:00.000','ABCDEFG' UNION
SELECT '2012-09-08 00:00:00.000','ABCDEFG'
INSERT INTO #ProdTable
SELECT '2012-10-06 00:00:00.000','ABCDEFG' UNION
SELECT '2012-10-07 00:00:00.000','ABCDEFG' UNION
SELECT '2012-10-08 00:00:00.000','ABCDEFG' UNION
SELECT '2013-02-14 00:00:00.000','ABCDEFG' UNION
SELECT '2013-02-15 00:00:00.000','ABCDEFG' UNION
SELECT '2013-02-16 00:00:00.000','ABCDEFG'
/* End test tables */
/* 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 NULL
DROP TABLE #DateTable
CREATE 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 #DateTable
SELECT
r.ReportDate
,r.FiscalYearDate
FROM
(
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
) r
OPTION (MAXRECURSION 0)
/* End date table code */
DECLARE
@ReportDate DATETIME
,@FiscalYearDate DATETIME
,@strSQL VARCHAR(8000)
--examples
SET @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 s
INNER JOIN
#DateTable AS d
ON s.ReportDate = d.ReportDate
WHERE
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 first
SELECT
@FiscalYearDate = FiscalYearDate
FROM
#DateTable
WHERE
ReportDate = @ReportDate
--then choose which query to run
IF DATEDIFF(MONTH,@FiscalYearDate,@ReportDate) <= 6
BEGIN
SELECT
'ProdTable' AS dbSource
,*
FROM
#ProdTable
WHERE
ReportDate = @ReportDate
END
ELSE
BEGIN
SELECT
'ArchiveTable' AS dbSource
,*
FROM
#ArchiveTable
WHERE
ReportDate = @ReportDate
END
February 18, 2013 at 5:34 pm
Thanks 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!
February 26, 2013 at 7:42 am
How about including [both] tables? (I'll stop at this point to include the usual disclaimers: "if the key columns are indexed...", etc.)
/* Create some test tables with data */
IF OBJECT_ID('tempdb..#ArchiveTable') IS NOT NULL
DROP TABLE #ArchiveTable
CREATE 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 #ProdTable
CREATE 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 #ArchiveTable
SELECT * FROM #ProdTable
/* End test tables */
/* Set a variable to use for searching */
DECLARE @d DATETIME
SELECT @d = '2011-09-08'
/* "Stand back! I'm gonna do SQL!" */
SELECT Col1, 'A' AS Location
FROM #ArchiveTable
WHERE [ReportDate] = @d
UNION
SELECT Col1, 'P' AS Location
FROM #ProdTable
WHERE [ReportDate] = @d
DROP TABLE #ArchiveTable
DROP TABLE #ProdTable
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply