June 5, 2013 at 3:02 am
Hi All,
I Need all your help to find out the best approach for fetching out the past Records in a table.
1.I have a CREATE_DT column in my Table .
2.Based on that date i need to fetch the X Months Old data , Parameter @Months
3. IF @Months =0 then i need to fetch all months data in my table .
4. i have 2 approaches to achieve this but , considering the amount of volume I'm dealing with it's giving a performance hit.
Could you guys please help me in achieving the best way to achieve this functionality
CREATE TABLE #DummyData
(
ID INT IDENTITY(1,1)
,NAME VARCHAR(200)
,CREATE_DT DATETIME
)
INSERT INTO #DummyData
VALUES('User 1',DATEADD(MONTH,-22,GETDATE()))
INSERT INTO #DummyData
VALUES('User 2',DATEADD(MONTH,-20,GETDATE()))
INSERT INTO #DummyData
VALUES('User 3',DATEADD(MONTH,-12,GETDATE()))
INSERT INTO #DummyData
VALUES('User 4',DATEADD(MONTH,-11,GETDATE()))
INSERT INTO #DummyData
VALUES('User 5',DATEADD(MONTH,-8,GETDATE()))
INSERT INTO #DummyData
VALUES('User 6',DATEADD(MONTH,-7,GETDATE()))
INSERT INTO #DummyData
VALUES('User 7',DATEADD(MONTH,-6,GETDATE()))
INSERT INTO #DummyData
VALUES('User 8',DATEADD(MONTH,-5,GETDATE()))
INSERT INTO #DummyData
VALUES('User 9',DATEADD(MONTH,-4,GETDATE()))
INSERT INTO #DummyData
VALUES('User 10',DATEADD(MONTH,-3,GETDATE()))
INSERT INTO #DummyData
VALUES('User 11',DATEADD(MONTH,-2,GETDATE()))
INSERT INTO #DummyData
VALUES('User 12',GETDATE())
DECLARE @Months INT =1
DECLARE @MIN_DATE DATETIME
DECLARE @MAX_DATE DATETIME =GETDATE()
/*********************************
Approach 1
***********************************/
SET @MIN_DATE =DATEADD(MONTH,-@Months,GETDATE())
SELECT 'APPROACH1',*
FROM #DummyData
WHERE --CREATE_DT BETWEEN @MIN_DATE AND @MAX_DATE
(
(@Months >0 AND CREATE_DT BETWEEN @MIN_DATE AND @MAX_DATE)
OR
(@Months<=0 AND CREATE_DT BETWEEN CREATE_DT AND @MAX_DATE)
)
/*********************************
Approach 2
***********************************/
IF @Months >0
BEGIN
SET @MIN_DATE =DATEADD(MONTH,-@Months,GETDATE())
END
ELSE
BEGIN
SELECT @MIN_DATE =MIN(CREATE_DT) FROM #DummyData
END
SELECT 'APPROACH2', * FROM #DummyData
WHERE CREATE_DT BETWEEN @MIN_DATE AND @MAX_DATE
DROP TABLE #DummyData
June 5, 2013 at 8:38 am
Sounds like a situation where you might want to consider the ideas from Gail's article about multiple execution paths.
http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply