Compare values in sql 2005 tables in specified timeframe.

Posted on 2011-09-02
Last Modified: 2012-05-12
Hello Experts,

I need to compare the values of two tables in sql '05.  I have table a and table b.  The values in table a are ID, Title, CreatedBy, Created Date, ChangedBy,ChagedDate.  The values in table b are the same with the exception of a datetimestamp field.  I need to create a query that will return all distinct id's, changedby, and changeddate for all of the records that have been changed in a 24 hour time period.  

**For example, Bob logged on and changed the title of a record in table a at 9:12pm on 09/01/11.  Table b captures this information and a record is created with the datetimestamp that Bob made the title change.  I need to show in the query result set that Bob made this change and the time that he did it as long as it is within a 24-hour time period.

Does anyone have any suggestions on how to get this accomplished?
Question by:daintysally
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
  • 3
  • 2
  • 2
  • +2
LVL 13

Expert Comment

ID: 36473910
If the data in tables a and b is the same, then you only need to query table b.
You can do a:

SELECT ID, Title, CreatedBy, Created Date, ChangedBy,ChagedDate
WHERE datetimestamp BETWEEN CONVERT(DATETIME,'2011-01-01 00:00:00',102) AND CONVERT(DATETIME,'2011-01-01 23:59:59',102)

This will give you all the changes made (by any user) during 01-01-2011.
You could add parameters for the dates and the user if you need them.
Is this what you need?
LVL 42

Expert Comment

ID: 36473920
Not sure why you need the other table.

Select b.ID, b.changedby, b.changeddate from tableb as b
  where b.changeddate <= getdate() and >datediff(d,1,getdate())

Author Comment

ID: 36473980
Ok, so I don't need table a because table b is capturing all records and placing a date timestamp on them.  I need to see all changes within the previous 24 hour timeframe.  So, for today, I would need to see all changes made yesterday without hard coding any dates.  
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 56

Accepted Solution

HainKurt earned 350 total points
ID: 36474044
try this part:

select * from myTable
(changeddate  >= cast(floor(cast(getdate()-1 as float)) as datetime)) and
(changeddate < cast(floor(cast(getdate() as float)) as datetime))
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36474134

At the risk of being pedantic, this expression is a little problematic:

WHERE datetimestamp BETWEEN CONVERT(DATETIME,'2011-01-01 00:00:00',102) AND CONVERT(DATETIME,'2011-01-01 23:59:59',102)

A datetime value has precision down to about 3 ms.  Thus, it is possible that you could have a value such as 2011-01-01 23:59:59.300 that your query would falsely reject.


WHERE datetimestamp >= 2011-01-01' AND datetimestamp < '2011-01-02'



LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 150 total points
ID: 36474165
To get yesterday's values, use a condition such as:

WHERE ChangedDate >= DATEADD(d, DATEDIFF(d, '1900-01-01', GETDATE()), '1899-12-31') AND
    ChangedDate < DATEADD(d, DATEDIFF(d, '1900-01-01', GETDATE()), '1900-01-01')
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36474201
EE Expert extraoridnaire and Microsoft MVP angelIII covers this topic very well in his article on using date and time values in various RDBMS.
LVL 42

Expert Comment

ID: 36474407
Correction: here's the up-to-the-minute (millisecond, actually) floating 24-hour window:

Select b.ID, b.changedby, b.changeddate from tableb as b
  where b.changeddate <= getdate() and >dateadd(d,-1,getdate())

Suggest HainKurt solution if "24 hours" equates to anytime yesterday.
LVL 13

Expert Comment

ID: 36474837
Sorry for my mistake matthewspatrick, I guess I was wrong to assume that changes wouldn't be made up to that time.

If you requiere "yesterday's" data, then HainKurt has the solution.

Author Closing Comment

ID: 36477824
Thank you all for your quick responses.  HainKurt's solution worked perfectly!!  Matthewspatrick solution worked as well!  Thank you again!!!

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor ( Top Charts is a view in which you can set seve…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

632 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