I have a date column which was created using the below.
However now I want to add 2 columns next to it which subtract 1 month and 2nd column subtracts 2 months from the original column.
CAST(YEAR(OrderRangeStartDate) AS VARCHAR(4)) + '-' + right('00' + CAST(MONTH(OrderRangeStartDate) AS VARCHAR(2)), 2) as MIN_CREATED_MONTH_YEAR
2017-01
2017-02
2017-03
2017-04
2017-05
2017-06
2017-07
RESULT I AM AFTER
;WITH yourTable AS
(
SELECT
CAST(YEAR(OrderRangeStartDate) AS VARCHAR(4))
+ '-' + right('00' + CAST(MONTH(OrderRangeStartDate) AS VARCHAR(2)), 2) as MIN_CREATED_MONTH_YEAR
FROM
@vt_table AS T
)
SELECT
Min_created_month_year
,CONVERT(CHAR(7),DATEADD(MONTH,-1,CAST(MIN_CREATED_MONTH_YEAR + '-01' AS DATE))) as Month_one
,CONVERT(CHAR(7),DATEADD(MONTH,-2,CAST(MIN_CREATED_MONTH_YEAR + '-01' AS DATE))) as Month_two
FROM
yourTable
Min_created_month_year Month_one Month_two
2017-01 2016-12 2016-11
2017-02 2017-01 2016-12
2017-03 2017-02 2017-01
2017-04 2017-03 2017-02To the best of my knowledge, the following should work on SQL Server 2005...
IF OBJECT_ID('tempdb..#SomeDate', 'U') IS NOT NULL
DROP TABLE #SomeDate;
CREATE TABLE #SomeDate (
YearMonth CHAR(7)
);
INSERT #SomeDate (YearMonth) VALUES
('2017-01'),('2017-02'),('2017-03'),
('2017-04'),('2017-05'),('2017-06'),
('2017-07');
--================================================
SELECT
sd.YearMonth,
Prev1 = CONVERT(CHAR(7), DATEADD(MONTH, -1, dt.YMDate), 20),
Prev2 = CONVERT(CHAR(7), DATEADD(MONTH, -2, dt.YMDate), 20)
FROM
#SomeDate sd
CROSS APPLY ( VALUES (CAST(sd.YearMonth + '-01' AS DATETIME)) ) dt (YMDate);
Results...
YearMonth Prev1 Prev2
--------- ------- -------
2017-01 2016-12 2016-11
2017-02 2017-01 2016-12
2017-03 2017-02 2017-01
2017-04 2017-03 2017-02
2017-05 2017-04 2017-03
2017-06 2017-05 2017-04
2017-07 2017-06 2017-05Good show, Jason, but you can simplify even more if you let DATEADD do the conversion for you. Using your good test data in the following...
IF OBJECT_ID('tempdb..#SomeDate', 'U') IS NOT NULL
DROP TABLE #SomeDate;
CREATE TABLE #SomeDate (
YearMonth CHAR(7)
);
INSERT #SomeDate (YearMonth) VALUES
('2017-01'),('2017-02'),('2017-03'),
('2017-04'),('2017-05'),('2017-06'),
('2017-07');
--================================================
SELECT sd.YearMonth
,Prev1 = CONVERT(CHAR(7),DATEADD(mm,-1,sd.YearMonth+'-01'),20)
,Prev2 = CONVERT(CHAR(7),DATEADD(mm,-2,sd.YearMonth+'-01'),20)
FROM #SomeDate sd
;
Now, I know what you're thinking. Like I used to, you're thinking that the CROSS APPLY causes the concatenation of "-01" and the explicit conversion to DATETIME to happen only once. If you look at the "Defined Values" property of the Compute Scalar node in the Actual Execution plan, you'll see that it still does the full calculation for both values because the CROSS APPLY is actually a correlated subquery by nature.
Mine does an Implicit Cast + the concatenation. Yours does an equally expensive Explicit Cast + the concatenation. All else being the same, I'd go for the simplicity.
Jeff - I do like yours better. I like the cleaner syntax (except for the leading commas 
). IIRC, I did try it your way and it failed. My first thought was that I had to do an explicit conversion before I realized I was just missing the dash in the concatenated value. One I found the missing dash, I just left the explicit conversion.
Looking at both plans, both are showing 100% of the cost on the table scan... Neither having a performance advantage over the other.
Heh... I used to hate leading commas. Then, two things happened... I realized that I hated trailing "AND" in the WHERE clause (which is no different than trailing commas when it comes to readability) and my boss asked that I switch to leading commas, which I begrudgingly did. Then I realized how easy it was to maintain on line comments with leading commas and I was sold.
I've tried it both ways... For whatever reason, the leading comma just rubs my fur in the wrong direction. I have no rational explanation. Of course I'm open to changing my mind... I used to despise the "ColumnAlias = t.ColumnName" syntax of aliasing columns in the select list. Now that I see how readable it is, I can't imagine ever going back to "t.ColumnName AS ColumnAlias". We're in 100% agreement on trailing ANDs.
Heh... exactly what my problem was. Lot's of people tried to justify leading commas by talking about when you had to comment out the last line in a SELECT List and I countered with it having the same problem when you tried to comment out the first line. I've been in a lot of (looking back at it) silly arguments about it.
I don't enforce leading or trailing commas during my peer reviews with Developers. What I do insist on is consistency. If the code is using trailing commas (for example) and someone (a leading comma zealot, for example) needs to make a modification or add a new section of code, they need to be consistent with what's already there even if it gives them computational whiplash. Same holds true for when the code contains leading commas and someone needs to make a modification or addition.
And, yeah... I've grown to like leading commas for a lot of reasons (but mostly because of the "AND" in WHERE clauses)... that and properly prepared asparagus. I would always try it but usually ended up spitting it out until that one day when someone did it right.
I don't see anything that I disagree with... + 1000 on the consistency part.