Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 View must have most recent date

Author  Topic 

Karmashock
Starting Member

4 Posts

Posted - 2013-06-13 : 14:24:06
I need to compare two tables.

One table has customer information and another has a log of all service done for those customers. In the log is a date of WHEN the service was done.

What I need to do is create a view that filters the log so it only shows most recent service.

OR

Ideally, I'd love to have that most recent date in the log added as a field along with information from the customer table.


The issue is that we're integrating two databases. Our database has a "done last" variable for every customer. The data we're adding has no such variable. What we have is the full schedule.




I am an SQL newbie. I really know very little about its syntax. But if you throw some code at me, I'll probably figure it out pretty quickly.


Anyone have any ideas or am I posting this in the wrong place?

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-06-13 : 14:37:29
First are the two databases on the same instance of SQL? Or do you mean you have two SQL servers?

djj
Go to Top of Page

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-06-13 : 14:44:24
Considering the log table is named: CustomerServiceLog
SELECT * FROM (
SELECT CustomerServiceLogID --Primary/Unique Key
,CustomerID
,LogDate
,Row_Number() Over (Partition By Customer_ID Order by LogDate Desc) MostRecentLogSequence
FROM CustomerServiceLog
)A
WHERE MostRecentLogSequence=1

Cheers
MIK
Go to Top of Page

Karmashock
Starting Member

4 Posts

Posted - 2013-06-14 : 01:16:02
quote:
Originally posted by MIK_2008

Considering the log table is named: CustomerServiceLog
SELECT * FROM (
SELECT CustomerServiceLogID --Primary/Unique Key
,CustomerID
,LogDate
,Row_Number() Over (Partition By Customer_ID Order by LogDate Desc) MostRecentLogSequence
FROM CustomerServiceLog
)A
WHERE MostRecentLogSequence=1

Cheers
MIK



Thanks. This did the trick. You're the man.
Go to Top of Page
   

- Advertisement -