July 27, 2018 at 8:40 am
CREATE TABLE [dbo].[dt](
[dtVal] [datetime] NOT NULL,
[dtVal2] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED
(
[dtVal] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
I insert a date in the table but I want to have the prev value that I insert in dtVal2
Insert into dt values (GETPREV-dtval2 as dtval), getdate() as dtVal2)
July 27, 2018 at 8:52 am
GG_BI_GG - Friday, July 27, 2018 8:40 AMCREATE TABLE [dbo].[dt](
[dtVal] [datetime] NOT NULL,
[dtVal2] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED
(
[dtVal] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]I insert a date in the table but I want to have the prev value that I insert in dtVal2
Insert into dt values (GETPREV-dtval2 as dtval), getdate() as dtVal2)
This is unclear to me. Can you provide some sample data and desired output too, please?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 27, 2018 at 9:00 am
If I understand you correctly, you don't really want to do this. You should only have one date field in your table and you should use LAG/LEAD to get the second date value. With two fields, you're going to have repeated data and run the risk of inconsistencies between rows. With a single date field, you no longer have those issues.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 27, 2018 at 10:18 am
GG_BI_GG - Friday, July 27, 2018 8:40 AMCREATE TABLE [dbo].[dt](
[dtVal] [datetime] NOT NULL,
[dtVal2] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED
(
[dtVal] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]I insert a date in the table but I want to have the prev value that I insert in dtVal2
Insert into dt values (GETPREV-dtval2 as dtval), getdate() as dtVal2)
Assuming you already have at least one row in your table, the following should do the trick...
INSERT dbo.dt (dtVal, dtVal2)
SELECT TOP (1)
dtVal = dt.dtVal2,
dtVal2 = GETDATE()
FROM
dbo.dt
ORDER BY
dt.dtVal DESC;
SELECT * FROM dbo.dt;
July 29, 2018 at 4:06 am
drop table if exists #temp
create table #temp
(
id int identity(1,1) primary key,
dtVal datetime2 not null,
dtVal2 datetime null
)
select * from #temp
update #temp
set
dtVal2 = dtVal,
dtVal = getdate()
where id = 2
select * from #temp
Sample data and expected results would make it a lot clearer
July 29, 2018 at 11:38 am
Kuznetsov’s History Table 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 CHAR(5) 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;
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:BEGINALTER 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;[/code]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.
Please post DDL and follow ANSI/ISO standards when asking for help.
July 30, 2018 at 2:30 am
Tnx for the reactions. I will try the reactions and testing it out what will be suit in the solution.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply