Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

In a Jam - Need Some Help Expand / Collapse
Author
Message
Posted Saturday, February 16, 2013 11:48 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 11, 2014 5:43 PM
Points: 26, Visits: 168
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!!
Post #1420945
Posted Sunday, February 17, 2013 3:12 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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


Post #1421008
Posted Monday, February 18, 2013 5:34 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 11, 2014 5:43 PM
Points: 26, Visits: 168
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!
Post #1421391
Posted Tuesday, February 26, 2013 7:42 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, December 15, 2014 6:52 AM
Points: 141, Visits: 642
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


Post #1424083
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse