Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# changing the date based on a comparison restricted to one day

Posted on 2012-03-23
Medium Priority
320 Views
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                 &#9;                Item&#9;        Date with time
2011-03-04 00:00:00.000&#9;ITEM1&#9;2011-03-04 21.45.00
2011-03-04 00:00:00.000&#9;ITEM2&#9;2011-03-04 03.30.00
2011-03-05 00:00:00.000&#9;ITEM2&#9;2011-03-05 03.30.00
2011-03-06 00:00:00.000&#9;ITEM1&#9;2011-03-06 21.45.00
2011-03-07 00:00:00.000&#9;ITEM1&#9;2011-03-07 21.45.00
2011-03-07 00:00:00.000&#9;ITEM2&#9;2011-03-07 03.30.00
2011-03-08 00:00:00.000&#9;ITEM2&#9;2011-03-08 03.30.00
2011-03-08 00:00:00.000&#9;ITEM1&#9;2011-03-08 21.45.00
2011-03-09 00:00:00.000&#9;ITEM1&#9;2011-03-09 21.45.00
2011-03-09 00:00:00.000&#9;ITEM2&#9;2011-03-09 03.30.00
2011-03-10 00:00:00.000&#9;ITEM2&#9;2011-03-10 03.30.00
2011-03-10 00:00:00.000&#9;ITEM1&#9;2011-03-10 21.45.00
2011-03-11 00:00:00.000&#9;ITEM1&#9;2011-03-11 21.45.00
2011-03-11 00:00:00.000&#9;ITEM2&#9;2011-03-11 03.30.00
2011-03-12 00:00:00.000&#9;ITEM2&#9;2011-03-12 03.30.00
2011-03-13 00:00:00.000&#9;ITEM1&#9;2011-03-13 21.45.00
2011-03-14 00:00:00.000&#9;ITEM1&#9;2011-03-14 21.45.00
2011-03-14 00:00:00.000&#9;ITEM2&#9;2011-03-14 03.30.00
0
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
• 2
• 2

LVL 18

Expert Comment

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

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

LVL 23

Accepted Solution

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)  )
``````

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

``````update yourtable
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
``````

Hope this helps...Be sure you get a backup before running the second script...
0

LVL 23

Expert Comment

ID: 37784845
Ooops..typo

``````update yourtable
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
``````
0

Author Closing Comment

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

## Featured Post

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.
###### Suggested Courses
Course of the Month8 days, 1 hour left to enroll