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.  
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

LVL 53

Accepted Solution

Huseyin KAHRAMAN 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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Can a Trigger trigger a Trigger? 4 47
How do I partition this table on date? 5 54
T-SQL: Wrong Result 7 39
SQL Query 20 22
Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
In an interesting question ( here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

739 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