In a Jam - Need Some Help

  • 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!!

  • 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

  • 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!

  • 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