Solved

Compare values in sql 2005 tables in specified timeframe.

Posted on 2011-09-02
10
239 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
 
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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL query 4 47
How to read BOM (Byte Order Mark) from csv file. 4 36
Need help debbuging stored procedure 21 33
Query Same ID From Multi-Record To One Record 4 26
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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.
This is an introductory video for CloudBerry Managed Backup. You will learn how to sign up with the service and get started in a few minutes.
CodeTwo Sync for iCloud (http://www.codetwo.com/sync-for-icloud?sts=6554) automatically synchronizes your Outlook 2016, 2013, 2010 or 2007 folders with iCloud folders available via iCloud Control Panel. This lets you automatically sync them with…

911 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

22 Experts available now in Live!

Get 1:1 Help Now