Solved

Compare values in sql 2005 tables in specified timeframe.

Posted on 2011-09-02
10
241 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
View SQL 2005 Job package 16 70
string fuctions 4 28
Need some help to cast ntext to nvarchar SQL 2000 7 38
question about results where i dont have a match 3 23
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
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…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) 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…

830 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