Solved

Compare values in sql 2005 tables in specified timeframe.

Posted on 2011-09-02
10
240 Views
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?
0
Comment
Question by:daintysally
  • 3
  • 2
  • 2
  • +2
10 Comments
 
LVL 13

Expert Comment

by:LIONKING
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
FROM b
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?
0
 
LVL 42

Expert Comment

by:dqmq
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())
0
 

Author Comment

by:daintysally
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.  
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 51

Accepted Solution

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

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

Expert Comment

by:Patrick Matthews
ID: 36474134
LIONKING,

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.

Better:

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

:)

Patrick

0
 
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')
0
 
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.
0
 
LVL 42

Expert Comment

by:dqmq
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.
0
 
LVL 13

Expert Comment

by:LIONKING
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.
0
 

Author Closing Comment

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

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Introduction Earlier I wrote an article about the new lookup functions (http://www.experts-exchange.com/A_3433.html) that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
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. …
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

815 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

13 Experts available now in Live!

Get 1:1 Help Now