SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


In a Jam - Need Some Help


In a Jam - Need Some Help

Author
Message
Polymorphist
Polymorphist
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 269
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!!
Steven Willis
Steven Willis
SSC Eights!
SSC Eights! (841 reputation)SSC Eights! (841 reputation)SSC Eights! (841 reputation)SSC Eights! (841 reputation)SSC Eights! (841 reputation)SSC Eights! (841 reputation)SSC Eights! (841 reputation)SSC Eights! (841 reputation)

Group: General Forum Members
Points: 841 Visits: 1721
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



Polymorphist
Polymorphist
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 269
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!
Your Name Here
Your Name Here
SSC Veteran
SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)

Group: General Forum Members
Points: 258 Visits: 814
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



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search