increment months for two dates combination as a new label column
-
I have to calculate a new Label column for each combination of Start Date and End Date.
If start Date is Jan-18 then if End Date is Jan-18, then Label column is Month 1,
If Start Date is Jan-18 then if end Date is Feb-18 then Label column is Month 2,
If Start Date is Feb-18 then if end date is Feb-18, then Label columns is Month 1,
If Start Date is Feb-18 then if end date is March-18, then Label columns is Month 2 etc etc...StartDate EndDate LabelColumn
Jan-18 Jan-18 Month 1
Jan-18 Feb-18 Month 2
Jan-18 Feb-18 Month 2
Jan-18 Mar-18 Month 3
Jan-18 Apr-18 Month 4
Feb-18 Feb-18 Month 1
Feb-18 Mar-18 Month 2
Feb-18 Mar-18 Month 2
Feb-18 Apr-18 Month 3how do we do it using CTE?
- hegdesuchi - Monday, October 22, 2018 1:22 PM
Can we have some proper consumable sample data?
Whether it requires a CTE or not is another question, unless your requirement specifies that a CTE must be used, regardless of the optimum solution?
- hegdesuchi - Monday, October 22, 2018 1:22 PM
;with myTable (StartDate ,EndDate) AS
(SELECT * FROM (VALUES
('Jan-18', 'Jan-18'),
('Jan-18', 'Feb-18'),
('Jan-18', 'Feb-18'),
('Jan-18', 'Mar-18'),
('Jan-18', 'Apr-18'),
('Feb-18', 'Feb-18'),
('Feb-18', 'Mar-18'),
('Feb-18', 'Mar-18'),
('Feb-18', 'Apr-18')) T(A,B))
SELECT X.StartDate, X.StartDate, DATEDIFF(mm,'01-' + X.StartDate, '01-' + X.EndDate)+1
FROM mytable X -
;with myTable (StartDate ,EndDate) ASThat semicolon 'initiator' is there just to wind us up, right? 🙂
- Phil Parkin - Monday, October 22, 2018 4:58 PM
It's there as a statement separator 😉
- Phil Parkin - Monday, October 22, 2018 4:58 PM
thank you very much. this works. 🙂
- Jonathan AC Roberts - Monday, October 22, 2018 5:07 PM
The semi-colon problem goes way back to the very early days of Sybase SQL Server. In the ANSI ISO standard, it is used to terminate a statement. But Sybase got the BNF wrong and became a separator. Then when Microsoft began adding more standard features, it became an initiator in some places, such as the WITH. When I was on ANSI X3H2, one of our committee members had access to a really good parser and he would check any changes or additions we made to the language before the next meeting. We decided that the language was going to be and LALR (1) since that was pretty fancy in its day, but still easy enough that you could generate parser mechanically.
Please post DDL and follow ANSI/ISO standards when asking for help.
- jcelko212 32090 - Wednesday, October 24, 2018 2:07 PM
It was never an initiator (and I prefer beginninator). That occurred due to piss-poor examples in BOL when CTEs were first introduced. The documentation even stated that the PREVIOUS statement must be terminated by a semicolon. Ever since then it has become a crutch to "insure" that the previous statement is terminated without actually terminating the previous statement with a semicolon.
Sorry, but a big pet peeve of mine.
- Lynn Pettis - Wednesday, October 24, 2018 3:27 PM
Wouldn't the opposite of a beginnator be an endator?
Initiator is a better antonym. - Lynn Pettis - Wednesday, October 24, 2018 3:27 PM
I use ;WITH whenever it's allowed, because it helps prevent future errors. Since T-SQL doesn't require statement terminators in most cases, it's quite common to leave them off. Even if you spend (i.e. waste) valuable time adding ; terminators to the previous statement, a later code change could cause a different previous statement. Thus, yes, it is safer just to code the starting ;. And if the previous statement does end in ;, the leading ; causes no issue anyway.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
- ScottPletcher - Wednesday, October 24, 2018 5:06 PM
Since not using a semicolon to terminate a "statement" has been deprecated for some time, I use a semicolon to properly terminate all statements so that when the deprecation turns into a requirement, I won't have any such problems. Hopefully, I'll live long enough to see that happen so I can hear a good part of the world scream when it becomes a requirement. 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden - Wednesday, October 24, 2018 6:34 PM
Jeff, do you use IF BEGIN END?
If so do you terminate the BEGIN and END with semi colon?
Just curious 🙂Far away is close at hand in the images of elsewhere.
Anon. - David Burrows - Thursday, October 25, 2018 2:02 AM
Yes to both. You do have to be a little careful when using ELSE. As with anything else the code parser doesn't currently check, I might miss a semicolon here or there but I try not to. It's good practice for what's on the horizon. Heh.... I've actually found an alternate use for semicolons... I use it as a marker to indicate that I'm done writing that particular piece of code and have tested it. While developing, I use a --todo comment to indicate that I may not be done with it and I can easily do a search for --todo to easily find such things. I don't "forward" the code until there are no --todo's left in the code.
BTW.... officially, I hate the use of semicolons and I hate the use of things that require "line continuation". But not using them is a bit like spitting into the wind because of the looming deprecation threat and the fact that they already require previous statement termination in order to use CTEs that follow.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
SQL Prompt can do all of the terminator legwork for you.
- Jeff Moden - Thursday, October 25, 2018 7:54 AM
And there are statements, MERGE is the only one I can think of, that requires them to be terminated with a semicolon.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply