Solved

What to compare two table items upto a certain date

Posted on 2010-11-11
4
704 Views
Last Modified: 2012-05-10
Hi, I am creating a Crystal report and I need to select information between two tables. The second table tableB is actually audit trial information which I need to compare against the first tableA and from that work out the differences for Quater1, Quarter 2 etc information.

At moment I have a problem after I use an inner join I have more than one row of data from the same row item from tableA. This is working correctly as it is showing is showing all the different times and data each item in tableA was updated. So if one item was updated 5 times in a year it will show 5 rows of data with timestamps for each time. But I need to only show one unique row depending on which date I need.

So for one of my query’s I need to know only the latest updated item. And then I need to know what was the last item update up to a certain date for Quater1, Quater2 etc.

There is something else I should mention. To get the information I require from tableB i have to use 2 PK and FK to join the tables. So I join with both ReviewID and then a RecordID from tableA and link those to table 2 (AUDIT)  ReviewID and RecordID.

One table holds all the data items (TableA) that I want to report on. I then need to retrieve Audit history from another table (AUDIT).

I would like to create a select statement that would choose the latest time stamped item, so that only one row show for each of the tableA items. The problem is that the TableA may be null or  it could have 1 to many values linking to the TableA reviewID and recoredID row.
Please see attached example data:
There are three tabs, TableA, Audit table, and a Inner join of the two tables. On the third tab, you will see 2 of the table values are coloured in blue were the rows of information are the same however the timestamp and also the scores of the data are different. This is only a small sample of the data, so the problem only shows once. However I want to be able to report only the latest instance of that data up to a certain date. For example here I would like to compare the Current scoreA with last score which would be OLDSCOREA. This is current score of 12 and old score of 3. But trying to get the data to only show once is important. How do I restrict the other duplicate Audit table items appearing from other date periods?
 TABLEA-AND-AUDIT-DATA-EXAMPLE.xls
0
Comment
Question by:mumbles22
  • 2
4 Comments
 
LVL 51

Expert Comment

by:HainKurt
ID: 34117250
you should use

select * from (
select *, row_number() over (partition by ReviewName order by TimeStamp desc) rn
where TimeStamp > getdate()-30
) x where rn=1

this will give you only latest (in last 30 days) records per ReviewName

the rest you can find it out I guess...
0
 

Author Comment

by:mumbles22
ID: 34119849
This is brillent, just what I was looking for and almost 95% there. However I seem to be a couple of steps short of my desired output.
1. I don't seem to be able to filter using the rn column? When I enter rn=1 into the where clause I get this message: Msg 207, Level 16, State 1, Line 37
Invalid column name 'RN'.
At moment this is the biggest issue because I don't seem to be able to retrieve just the number 1 item.

2. The second problems is that I get the RowNumber of all the record items, however this also gives me row numbers for the same recordID's which appear in other reviews. So I am unable to just retrieve the top 1 record because it would exclude items from other reviews which for example might have a rn of 3 for the top record. Is there a further step to restart the rn on different reviews as well for the same recordid?

This is my code so far:
Select
RV.REVIEWNAME,
RV.[Name(name)],
RV.ScoreAresult
 AT.FIELDNAME,
 AT.TS,
 AT.RECORDID,
 AT.[CHANGEDFROM],
 RV.[number(number)],
row_number() over (partition by RV.RECORDID order by AT.TS desc) as RN
from TABLEA RV inner join tableB AT on RV.RECORDID = AT.RECORDID
where RV.REVIEWID = AT.REVIEWID
--and RV.REVIEWNAME='London'
and AT.FIELDNAME='scoreA'
--and RN=1

works really well except for when I remove the filter for the london review the same record that would also appear in other reviews has row counts continued rather than starting at 1 for each review?
0
 
LVL 51

Accepted Solution

by:
HainKurt earned 500 total points
ID: 34123379
you should wrap the query in another select

select * from (
  select *, row_number() over (partition by ReviewName order by TimeStamp desc) rn
  where TimeStamp > getdate()-30
) x where rn=1

try again...
0
 
LVL 41

Expert Comment

by:ralmada
ID: 34123410
you need to add another clause to your partition by there.
select * from 

(

	Select 

		RV.REVIEWNAME, 

		RV.[Name(name)], 

		RV.ScoreAresult

		AT.FIELDNAME, 

		AT.TS, 

		AT.RECORDID, 

		AT.[CHANGEDFROM], 

		RV.[number(number)], 

		row_number() over (partition by RV.REVIEWNAME, RV.[Name(name)] order by AT.TS desc) as RN

	from TABLEA RV 

	inner join tableB AT on RV.RECORDID = AT.RECORDID

	where RV.REVIEWID = AT.REVIEWID

	and AT.FIELDNAME='scoreA'

	and RV.REVIEWNAME='London'

) x

where RN=1

Open in new window

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Params not declared?  T-SQL syntax question 3 39
Merge Statement 3 39
email the result out from a T-SQL queries 29 62
Dimension table indexes 8 10
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

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