Recursive CTE for Item supersession question

Question

Post reply

Recursive CTE for Item supersession question

Viewing 6 posts - 31 through 35 (of 35 total)

You must be logged in to reply to this topic. Login to reply

F.L Right there with Babe Points: 734 More actions · Answer 1

F.L

Right there with Babe

Points: 734

April 15, 2012 at 12:13 am

#1473706

Jeff Moden (4/14/2012)

So where did you go??? I checked your profile for additional posts and didn't find one.

Hi Jeff.

A new topic is now started with the name "finding top parent using CTE"

Sorry for the delay.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

CELKO (4/14/2012)

Unfortunately, Mr. Celko, this isn't MySQL. This is MS SQL Server and it doesn't have the same capabilities as MySQL or Oracle.

You do know that T-SQL now has a DATE type that defaults to the ISO-8601/ ANSI SQL display format? When I want to pass data to the front end for display -- not backend calculations -- I can now use the CAST() function to get a string and UNION the monthly and annual MySQL report period names.

Do you have a better way to display a month report period that is language free?

Please cite the ISO standard that specifically says that it's ok to display a Year as YYYY-00-00 and a month as YYYY-MM-00.

--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)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

CELKO (4/15/2012)

Why was everyone so hung up on the MySQL month names? Wait until they learn about the rest of the ISO-8601 date formats!

It's real simple, Joe. You keep throwing them in our face. Now, show us what I asked. Please cite where YYYY-00-00 and YYYY-MM-00 are acceptable date formats according to ISO-8601. Also show us the ISO list of legal values where "00" is an acceptable value for a month or a day.

--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)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

It actually doesn't matter whether you called it a "standard" or not, you're advocating and even recommending the use of a date/time format not specifically covered by ISO 8601. Yet, you're the one that normally gets after everyone at the slightest hint of an ISO violation. Make up your mind, will ya? 😉

Part of the reason why I dislike ISO 8601 is because it doesn't state a specific data-type for any whole date or part of a date in computational systems which also means that, while I agree that YYYYMM isn't a legal representation (Section 4.1.2.3), the representation of YYYYMMDD (a legal format according to Section 4.1.2.2) as an INTEGER is just fine according to ISO 8601. Certainly, it's no less effective than your "MySQL" method (well, the INTEGER method does take fewer bytes than yours does). Both will sort properly. Both are equally useless for date/time calculations :pinch:

Last but not least, please don't talk to folks at ISO about "new options" especially if you intend to propose any new ones. They've already made life difficult enough. I mean really... 32 pages (not including the Biography or front matter) to explain such a simple concept is just insane. I know why they do it though... they sell the specs based on the number of sheets. :Whistling:

And, no.. the standard isn't for "single dates only" or at least not the way you're saying it. Section 4.1.2.3 covers dates that require "Representations with reduced accuracy". It has very specific instructions for how to handle YYYY-MM and other "partial date" representations like the one's we're talking about.

--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)
Intro to Tally Tables and Functions

shannonjk SSCarpal Tunnel Points: 4286 More actions · Answer 5

So just to throw my hat into the discussion regarding integer vs date iso standardized formats;

I just used the YYYYMM format as that is the default for financial 'period' in my core ERP system. I realize the generally 'agreed' upon format is YYYYMMDD. For the sake of the post it was the most simple method, but in reality the format of my date is so completely irrelevant to the point of the question.

Additionally, every major ERP I have worked with stores its dates in YYYYMMDD format, so I have habitually adopted that since I have been typing that format for the last 7 years.

Link to my blog http://notyelf.com/

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 6

CELKO (7/9/2012)

Unfortunately, Mr. Celko, this isn't MySQL. This is MS SQL Server and it doesn't have the same capabilities as MySQL or Oracle.

And that is why I use the MySQL convention as a name, not a temporal type. What do you use? Name in Bulgarian with years in Toman numerals?

I like the MySQL convention of using double zeroes for months and years, That is 'yyyy-mm-00' for a month within a year and 'yyyy-00-00' for the whole year. The advantages are

1. It will sort with the ISO-8601 data format required by Standard SQL.

2. It is language independent

3. Compresses in columnar databases

4. The fixed length strings are easy to scan

5. Twenty years of data fit into main storage; the join is faster than doing temporal math.

Actually, why do you care? What ever I use is probably wrong in your book, which is why I don't have , nor will ever have, any of your books.

If you like MySQL so much, why not go over there and leave us alone.