November 17, 2011 at 3:05 am
Hi All in one of my requirement i need to fetch the months in the given date range ex :
-- input
DECLARE @FromDate DATETIME = '2011-08-10',
@ToDate DATETIME = '2011-10-31'
-- output
Select 'Aug 2010' Months union
select 'Sept 2010' union
select 'Oct 2010'
Thanks & regards
Deepak.A
November 17, 2011 at 3:15 am
probably more efficient ways, but this would do it:
DECLARE @Months TABLE (MonthYear VARCHAR(20))
DECLARE @DATE DATETIME
SELECT @DATE = CONVERT(VARCHAR, @FromDate, 106)
WHILE @DATE < @ToDate
BEGIN
INSERT @Months SELECT RIGHT(CONVERT(VARCHAR, @DATE, 106), 8)
SET @DATE = DATEADD(mm, 1, @DATE)
END
SELECT * FROM @Months
November 17, 2011 at 3:19 am
Use a calendar table. I've done one on the fly here, for performance you'd be better off with a real table.
DECLARE @FromDate DATETIME = '2011-08-10',
@ToDate DATETIME = '2011-10-31'
;WITH t1 AS (SELECT 1 N UNION ALL SELECT 1 N),
t2 AS (SELECT 1 N FROM t1 x, t1 y),
t3 AS (SELECT 1 N FROM t2 x, t2 y),
t4 AS (SELECT 1 N FROM t3 x, t3 y),
tally AS (SELECT DATEADD(MONTH,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),0) AS cal_date
FROM t4 x, t4 y)
SELECT RIGHT(CONVERT(VARCHAR, cal_date, 106), 8)
FROM tally
WHERE cal_date >= DATEADD(DAY, 1, @FromDate - DAY(@FromDate) + 1) -1
AND cal_date <= DATEADD(DAY, 1, @ToDate - DAY(@ToDate) + 1) -1
November 17, 2011 at 3:33 am
Hi Guys , Thanks For your Reply
Regards - Deepak
November 17, 2011 at 3:35 am
Following the footsteps of Mr. Jeff Moden, I believe this would be good enough
DECLARE @StartDate DATETIME, @EndDate DATETIME, @MonthDiff INT
SELECT @StartDate = '1901-08-10', @EndDate = '4850-10-31'
SELECT @MonthDiff = DATEDIFF(MONTH, @StartDate, @EndDate)
;WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows
E5(N) AS (SELECT 1 FROM E2 a, E4 b), --1,000,000 rows max
cteTally(N) AS (--==== This provides the "zero base" and limits the number of rows right up front
-- for both a performance gain and prevention of accidental "overruns"
SELECT 0 UNION ALL
SELECT TOP (@MonthDiff) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E5
)
SELECT RIGHT(CONVERT(VARCHAR, DATEADD(MONTH , N, @StartDate), 106), 8) FROM [cteTally]
November 17, 2011 at 3:44 am
Usman Butt (11/17/2011)
Following the footsteps of Mr. Jeff Moden, I believe this would be good enough
DECLARE @StartDate DATETIME, @EndDate DATETIME, @MonthDiff INTSELECT @StartDate = '20091001', @EndDate = '20111101'
SELECT @MonthDiff = DATEDIFF(MONTH, @StartDate, @EndDate)
;WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (--==== This provides the "zero base" and limits the number of rows right up front
-- for both a performance gain and prevention of accidental "overruns"
SELECT 0 UNION ALL
SELECT TOP (@MonthDiff) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
)
SELECT DATENAME(MONTH, DATEADD(MONTH, N, @StartDate)) + ' ' + DATENAME(YEAR, DATEADD(MONTH, N, @StartDate)) FROM [cteTally]
Try that with the following dates: -
DECLARE @FromDate DATETIME = '1901-08-10',
@ToDate DATETIME = '4850-10-31'
Also, the OP wanted "Aug 2010", not "August 2010".
Small perf test.
BEGIN TRAN
DECLARE @start DATETIME, @end DATETIME
SET NOCOUNT ON
DECLARE @FromDate DATETIME = '1901-08-10',
@ToDate DATETIME = '4850-10-31'
PRINT '========== LOOP =========='
SET @start = GETDATE()
DECLARE @Months TABLE (MonthYear VARCHAR(20))
DECLARE @DATE DATETIME
SELECT @DATE = CONVERT(VARCHAR, @FromDate, 106)
WHILE @DATE < @ToDate
BEGIN
INSERT @Months SELECT RIGHT(CONVERT(VARCHAR, @DATE, 106), 8)
SET @DATE = DATEADD(mm, 1, @DATE)
END
SELECT * FROM @Months
SET @end = GETDATE()
PRINT DATEDIFF(MILLISECOND,@start,@end)
PRINT REPLICATE('=',80)
PRINT '========== CALENDER TALLY =========='
SET @start = GETDATE()
;WITH t1 AS (SELECT 1 N UNION ALL SELECT 1 N),
t2 AS (SELECT 1 N FROM t1 x, t1 y),
t3 AS (SELECT 1 N FROM t2 x, t2 y),
t4 AS (SELECT 1 N FROM t3 x, t3 y),
tally AS (SELECT DATEADD(MONTH,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),0) AS cal_date
FROM t4 x, t4 y)
SELECT RIGHT(CONVERT(VARCHAR, cal_date, 106), 8)
FROM tally
WHERE cal_date >= DATEADD(DAY, 1, @FromDate - DAY(@FromDate) + 1) -1
AND cal_date <= DATEADD(DAY, 1, @ToDate - DAY(@ToDate) + 1) -1
SET @end = GETDATE()
PRINT DATEDIFF(MILLISECOND,@start,@end)
PRINT REPLICATE('=',80)
ROLLBACK
========== LOOP ==========
766
================================================================================
========== CALENDER TALLY ==========
210
================================================================================
Calendar tally table is faster than the loop, but you have to get into stupidly ridiculous dates to notice the difference.
November 17, 2011 at 3:45 am
November 17, 2011 at 5:13 am
Cadavre (11/17/2011)
Usman Butt (11/17/2011)
Following the footsteps of Mr. Jeff Moden, I believe this would be good enough
DECLARE @StartDate DATETIME, @EndDate DATETIME, @MonthDiff INTSELECT @StartDate = '20091001', @EndDate = '20111101'
SELECT @MonthDiff = DATEDIFF(MONTH, @StartDate, @EndDate)
;WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (--==== This provides the "zero base" and limits the number of rows right up front
-- for both a performance gain and prevention of accidental "overruns"
SELECT 0 UNION ALL
SELECT TOP (@MonthDiff) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
)
SELECT DATENAME(MONTH, DATEADD(MONTH, N, @StartDate)) + ' ' + DATENAME(YEAR, DATEADD(MONTH, N, @StartDate)) FROM [cteTally]
Try that with the following dates: -
DECLARE @FromDate DATETIME = '1901-08-10',@ToDate DATETIME = '4850-10-31'
I know the limits which by the way was highlighted in form of comments that it is for 10000 maximum rows. But I will remind you your own words "you have to get into stupidly ridiculous dates" 😉
Also, the OP wanted "Aug 2010", not "August 2010".
Yes, I agree that but it should not be a major issue for the OP. I have changed my original answer for it.
Small performance test of mine.
When I ran both the solutions (I have changed mine a bit) for dates "@FromDate = '1901-08-10' , @ToDate = '4850-10-31'". My solution seems to be better than your solution when I saw the execution plan. You can test it yourself. Besides following was time statistics for my solution
(35391 row(s) affected)
SQL Server Execution Times:
CPU time = 47 ms, elapsed time = 623 ms.
Whereas, your solution's time statistics were
(35391 row(s) affected)
SQL Server Execution Times:
CPU time = 63 ms, elapsed time = 896 ms.
Cheers.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply