StartDate and EndDate in same column
- Redmond01 - Monday, October 30, 2017 1:58 AM
Redmond01 - Sunday, October 29, 2017 3:04 PM@Lynn Pettis , why don't you keep you obnoxious ,sarcastic and dumb as rock remarks to yourself. Excuse me?? I have made no obnoxious, sarcastic, dumb as rock remarks.
Good luck with this and any other issues you may have.
Lynn Pettis@Lynn Pettis are you threatening me , do you own this forum?
Although his questions/posts have been a bit more direct than the softsoap that the rest of us have posted, he's said nothing that the rest of us already have. Lynn is one of the folks that can actually help here but you need to provide the data that both Gail and I asked for and that Lynn provided a reminder for. Otherwise, this problem actually isn't properly solvable.
And go easy on the name calling. It's just not necessary especially for something that shouldn't have been taken offense to.
--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) - Redmond01 - Thursday, October 26, 2017 5:54 PM
This does not make sense, follow forum rules or even use the right display format for dates. I am going to guess that you want Kuznetsov’s History Table. This is a SQL idiom which builds a temporal chain from the current row to the previous row. This is easier to show with code:
CREATE TABLE Tasks
(task_id INTEGER NOT NULL,
task_score CHAR(1) NOT NULL,
previous_end_date DATE, -- null means first task
current_start_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
CONSTRAINT previous_end_date_and_current_start_in_sequence
CHECK (prev_end_date <= current_start_date),
current_end_date DATE, -- null means unfinished current task
CONSTRAINT current_start_and_end_dates_in_sequence
CHECK (current_start_date <= current_end_date),
CONSTRAINT end_dates_in_sequence
CHECK (previous_end_date <> current_end_date)
PRIMARY KEY (task_id, current_start_date),
UNIQUE (task_id, previous_end_date), -- null first task
UNIQUE (task_id, current_end_date), -- one null current task
FOREIGN KEY (task_id, previous_end_date) -- self-reference
REFERENCES Tasks (task_id, current_end_date));Well, that looks complicated! Let’s look at it column by column. Task_id explains itself. The previous_end_date will not have a value for the first task in the chain, so it is NULL-able. The current_start_date and current_end_date are the same data elements, temporal sequence and PRIMARY KEY constraints we had in the simple history table schema.
The two UNIQUE constraints will allow one NULL in their pairs of columns and prevent duplicates. Remember that UNIQUE is not like PRIMARY KEY, which implies UNIQUE NOT NULL.
Finally, the FOREIGN KEY is the real trick. Obviously, the previous task has to end when the current task started for them to abut., so there is another constraint. This constraint is a self-reference that makes sure this is true. Modifying data in this type of table is easy, but requires some thought.. Fortunately, Alex has written a Simple Talk article to explain in more detail how it is done.
Disabling Constraints
Just one little problem with that FOREIGN KEY constraint. It will not let you put the first task into the table. There is nothing for the constraint to reference. In Standard SQL, we can declare constraints to be DEFERABLE with some other options. The idea is that you can turn a constraint ON or OFF during a session so the database can be in state that would otherwise be illegal. But at the end of the session all constraints have to be TRUE. or UNKNOWN.
In SQL Server, you can disable constraints and then turn them back on. It actually is restricted to disabling FOREIGN KEY constraint, and CHECK constraints. PRIMARY KEY, UNIQUE, and DEFAULT constraints are always enforced. The syntax for this is part of the ALTER TABLE statement. The syntax is simple:
ALTER TABLE <table name> NOCHECK CONSTRAINT [<constraint name> | ALL];
This is why you want to name the constraints; without user given names, you have to look up what the system gave you and they are always long and messy.. The ALL option will disable all of the constraints in the entire schema. Be careful with it.To re-enable, the syntax is similar and explains itself:
ALTER TABLE <table name> CHECK CONSTRAINT [<constraint name> | ALL];
When a disabled constraint is re-enabled, the database does not check to ensure any of the existing data meets the constraints. So for this table, The body of a procedure to get things started would look like this:BEGIN
ALTER TABLE Tasks NOCHECK CONSTRAINT ALL;
INSERT INTO Tasks (task_id, task_score, current_start_date, current_end_date, previous_end_date)
VALUES (1, 'A', '2010-11-01', '2010-11-03', NULL);
ALTER TABLE Tasks CHECK CONSTRAINT ALL;
END;Please post DDL and follow ANSI/ISO standards when asking for help.
- jcelko212 32090 - Monday, October 30, 2017 12:21 PM
Heh... or the OP could simply post which column maintains the order of the rows and we'd be nearly done. 😉
--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) -
Judging by the response, it's likely the order is maintained by the order of the data file storing this data. If there was something else like a Group Identifier, then he/she wouldn't have a problem. GROUP BY that GroupID and do Min()/Max() on the RunDates with IS NOT NULL as a filter, problem solved. Being that grouping does not exist, he/she needs a way to group up the data. Unfortunately, that may be hard to do unless the underlying source feeding him/her this data does it for him/her.
I've ran into similar, but my issue was the same CustomerID had multiple records on different dates that were in ASC order. I wanted to split the records into N groups. I couldn't split them easily because they essentially all tied to the same CustomerID. I had to generate a new identifier on the the data called the GroupID that partitioned the records based on a marker record (i.e.: NULL in his example) and ORDER BY Date only when the Date of the other records came BEFORE the marker record . That way the CustomerID becomes a unique identifier for a Lifetime History and the GroupID becomes individual histories per order. A sequential ID also helped here to order within each groups and partitioned on that GroupID later.
Same thing is needed here, but the question is: How do you group up the prior records together with a unique GroupID and partition on the NULL's based on the order in the data file? But that's an assumption of the information thus far. Interesting problem 🙂
- Jeff Moden - Monday, October 30, 2017 12:36 PM
Please post DDL and follow ANSI/ISO standards when asking for help.
-
Joe,
First, don't you think it's time you learned how to use this forum software. You mess up the quotes EVERY SINGLE TIME. You made it appear that Jeff said something idiotic, when it was you who actually said it.
After that, there's another little problem at a fundamental level; in temporal data, we have interval data types, which are defined by start and end points in a half open interval model.
This is a flat out lie. T-SQL (which this forum is dedicated to) does not and never has had an interval data type. NEVER. T-SQL is not and never has been ANSI SQL. This forum is dedicated to T-SQL, not ANSI SQL. If you want to help here stick to T-SQL. Telling people to use an approach based on ANSI SQL that CANNOT possibly be implement in T-SQL is harmful rather than helpful.Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA - drew.allen - Tuesday, October 31, 2017 8:44 AM
“He is a barbarian, and thinks that the customs of his tribe and island are the laws of nature.†― George Bernard Shaw, Caesar and Cleopatra
I believe I said "in temporal data, we have interval data types.." which you can confirm by reading anything by Rick Snodgrass since his work influenced the ISO model and SQL Standards. And you can implement it with constraints and the newer temporal data types.Please post DDL and follow ANSI/ISO standards when asking for help.
- jcelko212 32090 - Tuesday, October 31, 2017 10:05 AM
"When in Rome, do as the Romans do." -- Ambrose
I believe I said that "This forum is dedicated to T-SQL, not ANSI SQL." I never said nor implied that T-SQL was the only way. You, on the other hand, should heed your own advice. You think that your customs (ANSI SQL) are the laws of nature. They are not. They are simply customs, and, like it or not, T-SQL does not follow all of those customs.Data Types are constructs. The interval construct does not exist in T-SQL. Modelling something is not the same as having something.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
@joe,
While I agree that you can not depend on some "natural" order in a table, that has nothing to do with storing something in the table to be able to process or display in the desired order. You should know that because you store stuff like YYYYMM00 and YYYY0000 for the main purpose of sort ability.
--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) - jcelko212 32090 - Monday, October 30, 2017 12:21 PM
Did Joe just define an id as an integer? I thought that was verboten, since you won't be doing arithmetic with it.
- gvoshol 73146 - Wednesday, November 1, 2017 5:05 AM
He DOES have the tendency to violate his own rants on a frequent basis. 😉
--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) - gvoshol 73146 - Wednesday, November 1, 2017 5:05 AM
Opps! I will have to fix that! I should checked Kuznetsov’s code in my edit.
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply