asked on

changing the date based on a comparison restricted to one day

Hi Experts

I’m trying to solve a problem of changing a date for one of the variables based on a comparison within a period span of one day and quite frankly I’m stuck...below is a more detailed description.

If Item2 has a “Date With Time” that is less than what item1 has for “Date With Time” then the “Date” for item2 should be changed by -1.

But my main stumbling block was that this needs to be done inside a period of 1 day, meaning that simply comparing Date with time is not sufficient and I have no idea how to restrict such a procedure to do the aforementioned comparison for a certain period...in my case a day.

I know it seems strange to change only the “Date” and not “Date with time” but I´m hoping that the procedure will run on “Date with Time” and then I could query the changed “Date”

What would be the best way of achieving this...?

Date 	 Item	 Date with time
2011-03-04 00:00:00.000	ITEM1	2011-03-04 21.45.00
2011-03-04 00:00:00.000	ITEM2	2011-03-04 03.30.00
2011-03-05 00:00:00.000	ITEM2	2011-03-05 03.30.00
2011-03-06 00:00:00.000	ITEM1	2011-03-06 21.45.00
2011-03-07 00:00:00.000	ITEM1	2011-03-07 21.45.00
2011-03-07 00:00:00.000	ITEM2	2011-03-07 03.30.00
2011-03-08 00:00:00.000	ITEM2	2011-03-08 03.30.00
2011-03-08 00:00:00.000	ITEM1	2011-03-08 21.45.00
2011-03-09 00:00:00.000	ITEM1	2011-03-09 21.45.00
2011-03-09 00:00:00.000	ITEM2	2011-03-09 03.30.00
2011-03-10 00:00:00.000	ITEM2	2011-03-10 03.30.00
2011-03-10 00:00:00.000	ITEM1	2011-03-10 21.45.00
2011-03-11 00:00:00.000	ITEM1	2011-03-11 21.45.00
2011-03-11 00:00:00.000	ITEM2	2011-03-11 03.30.00
2011-03-12 00:00:00.000	ITEM2	2011-03-12 03.30.00
2011-03-13 00:00:00.000	ITEM1	2011-03-13 21.45.00
2011-03-14 00:00:00.000	ITEM1	2011-03-14 21.45.00
2011-03-14 00:00:00.000	ITEM2	2011-03-14 03.30.00

UnifiedIS

I don't understand what you mean by "this needs to be done inside a period of 1 day"

Are these a pair to compare because they share the same date?
2011-03-04 00:00:00.000 ITEM1 2011-03-04 21.45.00
2011-03-04 00:00:00.000 ITEM2 2011-03-04 03.30.00

And you want the end result to be like this?
2011-03-04 00:00:00.000 ITEM1 2011-03-04 21.45.00
2011-03-03 00:00:00.000 ITEM2 2011-03-04 03.30.00

Changing Item2 by -1, does that mean minus 1 day?

Daniel993

ASKER

sorry being vague, but yes that is exactly what I'm going for

ASKER CERTIFIED SOLUTION

Racim BOUDJAKDJI

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Racim BOUDJAKDJI

Ooops..typo

update yourtable
set date_time=dateadd(dd, -1, AA.date_time)
from
(
select item, date_time 
from yourtable A
where item = 'ITEM2' and exists(select 1 from your table where item='ITEM1' and convert(varchar(8), A.date_time, 102) = convert(varchar(8), date_time, 102))
) AA
where date_time=AA.date_time and item=AA.item

Open in new window

Daniel993

ASKER

Works as advertised :) thanks for taking the time to help.