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
Daniel993Asked:
Who is Participating?
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
A few questions that needs answering:
> What is the table name ?
> Is the display of column names the name of the SQL column name ?

Assuming te table name is yourtable  and the column containing the date display is date_time, and the column name of the item information is item, the below query should give all rows that need date subtraction..

select item, date_time 
from yourtable A
where item = 'ITEM2' and exists(select 1 from yourtable where item='ITEM1' and convert(varchar(8), A.date_time, 102) = convert(varchar(8), date_time, 102)  )

Open in new window


and the following query should give update the rows above to subtract one day...

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.datetime and item=AA.item

Open in new window


Hope this helps...Be sure you get a backup before running the second script...
0
 
UnifiedISCommented:
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?
0
 
Daniel993Author Commented:
sorry being vague, but yes that is exactly what I'm going for
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
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

0
 
Daniel993Author Commented:
Works as advertised :) thanks for taking the time to help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.