?
Solved

changing the date based on a comparison restricted to one day

Posted on 2012-03-23
5
Medium Priority
?
314 Views
Last Modified: 2012-06-22
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
0
Comment
Question by:Daniel993
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 18

Expert Comment

by:UnifiedIS
ID: 37758810
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
 

Author Comment

by:Daniel993
ID: 37758869
sorry being vague, but yes that is exactly what I'm going for
0
 
LVL 23

Accepted Solution

by:
Racim BOUDJAKDJI earned 2000 total points
ID: 37784615
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
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 37784845
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
 

Author Closing Comment

by:Daniel993
ID: 37804986
Works as advertised :) thanks for taking the time to help.
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question