May 26, 2016 at 1:32 pm
I have a table with DviStartTime and DviEndTime
Format is as follows
DviStartTime DviEndTime
2016-04-18 22:27:33.0002016-04-18 22:36:21.000
2016-04-18 10:11:23.0002016-04-18 10:16:29.000
2016-04-17 20:14:01.0002016-04-17 20:36:46.000
2016-04-16 03:57:52.0002016-04-16 22:36:12.000
2016-04-15 08:44:21.0002016-04-15 08:55:11.000
I want to update all records so that it looks like it is current data...to to most current start and end should be today and go back from there.
2016-05-26 22:27:33.0002016-05-26 22:36:21.000
2016-05-26 10:11:23.0002016-05-26 10:16:29.000
2016-05-25 20:14:01.0002016-05-25 20:36:46.000
2016-05-24 03:57:52.0002016-05-24 22:36:12.000
2016-05-23 08:44:21.0002016-05-23 08:55:11.000
How can I do this?
May 26, 2016 at 2:14 pm
SELECT DviStartTime, DviEndTime
FROM ImaginaryTable
ORDER DviStartTime ASC, DviEndTime DESC
May 26, 2016 at 2:24 pm
This might give you an idea.
DECLARE @Sample TABLE(
DviStartTime datetime,
DviEndTime datetime
);
INSERT INTO @Sample
VALUES
('2016-04-18 22:27:33.000', '2016-04-18 22:36:21.000'),
('2016-04-18 10:11:23.000', '2016-04-18 10:16:29.000'),
('2016-04-17 20:14:01.000', '2016-04-17 20:36:46.000'),
('2016-04-16 03:57:52.000', '2016-04-16 22:36:12.000'),
('2016-04-15 08:44:21.000', '2016-04-15 08:55:11.000');
SELECT DATEADD(dd, m.Days, DviStartTime) AS DviStartTime,
DATEADD(dd, m.Days, DviEndTime) AS DviEndTime
FROM @Sample
CROSS JOIN (SELECT DATEDIFF( dd, MAX(i.DviStartTime), GETDATE()) FROM @Sample i) m(Days)
EDIT: Included sample data generation.
May 27, 2016 at 6:51 am
I want to modify the data in the existing table not place it in a temp table...do a direct modification to all records so the data is current and not in the past.
The table name is dbo.PrmDeviceVisits
I want to update only the date columns so the complete data set is current.
EX: if newest record is 345 days ago then add 345 days to all values in DviStartTime and DviEndTime.
May 27, 2016 at 7:03 am
I was just showing you the formula. You need to generate the UPDATE yourself.
May 27, 2016 at 7:11 am
I don't know how to do that, I'm very new at SQL, I only basic select statements etc... that's why I'm here to get the exact method...
May 27, 2016 at 7:21 am
domleg (5/27/2016)
I don't know how to do that, I'm very new at SQL, I only basic select statements etc... that's why I'm here to get the exact method...
try reading thro this article and see if you can do the update yourself.
http://www.sqlservercentral.com/articles/Stairway+Series/88680/
if at the end, you are still struggling....post what you have attempted and I ma sure you will get an answer
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 27, 2016 at 7:49 am
How does this look? 🙂
Declare @DayInterval INT;
Set @DayInterval = Datediff(day,(select MAX(dviendtime) from PrmDeviceVisit),GetDate());
Update dbo.PrmDeviceVisit
Set DviEndTime = DATEADD(DAY,@DayInterval,DviEndTime)
Update dbo.PrmDeviceVisit
Set DviStartTime = DATEADD(DAY,@DayInterval,DviStartTime)
May 27, 2016 at 8:04 am
That's a good effort. You can do updates to several columns in a single statement.
Declare @DayInterval INT;
Set @DayInterval = Datediff(day,(select MAX(dviendtime) from PrmDeviceVisit),GetDate());
Update dbo.PrmDeviceVisit
Set DviEndTime = DATEADD(DAY,@DayInterval,DviEndTime),
DviStartTime = DATEADD(DAY,@DayInterval,DviStartTime);
I'd usually won't use this on a heavily used environment, but as I assume you're using a personal db to learn, it's fine for now, but you'll have to learn to deal with concurrency later on.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply