T-SQL Sample with Sequence 1,2,3,6,12,24,36…



;WITH mycte AS
(
SELECT 1 as i
UNION ALL
SELECT Case WHen i>=3 Then m.i*2 Else m.i+1 End as i
FROM mycte m
WHERE i<36
)

SELECT
CAST(i AS VARCHAR(2)) +
CASE WHEN i = 1 THEN ' Month' ELSE ' Months' END AS NumPreviousMonth
,CAST(DATEADD(month, -i, Current_Timestamp) AS DATE) PreviousDate
FROM mycte


Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s