Solved

changing the date based on a comparison restricted to one day

Posted on 2012-03-23
5
282 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
  • 2
  • 2
5 Comments
 
LVL 18

Expert Comment

by:UnifiedIS
Comment Utility
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
Comment Utility
sorry being vague, but yes that is exactly what I'm going for
0
 
LVL 23

Accepted Solution

by:
Racim BOUDJAKDJI earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
Works as advertised :) thanks for taking the time to help.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

743 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now