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)
 Latest Customer Location

Author  Topic 

Indsqlbeginner
Starting Member

21 Posts

Posted - 2014-01-15 : 00:28:42
Hi Team

kindly help me to find out the lastest location details for a customer and table details are below.

1. tblCustomer


IntCustomerId StrCustomerName StrCustomerEmail DtCustActive
1 abc abc@abc.com Null
2 xyz xyz@xyz.com 2013-12-31
3 Lmn lmn@lmn.com Null



2. tblCurrentLocation


IntlocationId IntCustomerId DtFromDate DtToDate
1 1 2009-10-05 2013-12-30
2 1 2013-12-31 9999-12-31
4 2 2011-11-05 9999-12-31
1 3 2008-1-06 2013-12-30
9 3 31-12-2013 9999-12-31


OutPut Expected:

to find out the lastet location id for the active customers.

Active customer : where DtCustActive is null from customer tbl



CustomerID LocationId
1 2
3 9


Kindly let me know if you'd require any more details to help me.

nagino
Yak Posting Veteran

75 Posts

Posted - 2014-01-15 : 01:16:55
How dose that look?
I hope this will help.

----------------------------------------------
SELECT
TBL.IntCustomerId CustomerId,
TBL.IntLocationId LocationId
FROM (
SELECT
ROW_NUMBER() OVER(
PARTITION BY tblCurrentLocation.IntCustomerID
ORDER BY tblCurrentLocation.DtToDate DESC
) RowNum,
*
FROM tblCurrentLocation
WHERE EXISTS( --filter : only active customer
SELECT
*
FROM tblCustomer
WHERE tblCustomer.DtCustActive IS NULL
AND tblCustomer.IntCustomerId = tblCurrentLocation.IntCustomerId
)) TBL
WHERE RowNum = 1 --filter : only latest location


-------------------------------------
From Japan
Sorry, my English ability is limited.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-15 : 05:20:54
[code]
SELECT c.*,l.LatestLoc
FROM tblCustomer c
INNER JOIN (SELECT MAX(IntLocationId) AS LatestLoc,IntCustomerId
FROM tblCurrentLocation
GROUP BY IntCustomerId
)l
ON l.IntCustomerId = c.IntCustomerId
WHERE c.DtCustActive IS NULL
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Indsqlbeginner
Starting Member

21 Posts

Posted - 2014-01-15 : 07:49:17
quote:
Originally posted by visakh16


SELECT c.*,l.LatestLoc
FROM tblCustomer c
INNER JOIN (SELECT MAX(IntLocationId) AS LatestLoc,IntCustomerId
FROM tblCurrentLocation
GROUP BY IntCustomerId
)l
ON l.IntCustomerId = c.IntCustomerId
WHERE c.DtCustActive IS NULL


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs




Thanks Visakh for your reply.

Kindly note this:
there is a tbl called tblLocation and this( intlocationId) has referreed in customer table.at given point of time customer could be in any of these location and hence in your query MAX(IntLocationId) willnot work for my case..sorry if i'm not clearer earlier..
<code>
intLocationId strLocationName
1 UK
2 US
3 Australia
4 India

</code>
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-15 : 08:02:22
[code]
SELECT c.*,l.LatestLoc
FROM tblCustomer c
CROSS APPLY(SELECT TOP 1 IntLocationId) AS LatestLoc
FROM tblCurrentLocation
WHERE IntCustomerId = c.IntCustomerId
ORDER BY DtFromDate DESC
)l
WHERE c.DtCustActive IS NULL
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Indsqlbeginner
Starting Member

21 Posts

Posted - 2014-01-16 : 23:30:16
quote:
Originally posted by visakh16


SELECT c.*,l.LatestLoc
FROM tblCustomer c
CROSS APPLY(SELECT TOP 1 IntLocationId) AS LatestLoc
FROM tblCurrentLocation
WHERE IntCustomerId = c.IntCustomerId
ORDER BY DtFromDate DESC
)l
WHERE c.DtCustActive IS NULL


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs




Thank you very much for your help visakh.
This is exactly what i was looking for.appreciate your help.

i just checked the relationship among these customer and location table and came to know that there is no relationship is being made btw these two tables.

basically it should have relationship.

ex: IntCustomerId should be created as a forienkey column in the
tblCurrentLocation since IntCustomerId is primary key tblCustomer tbl.

my concern is,since this has been in system for quite long time and size of the table is pretty huge.

so, what if i go ahead and modify that column as foreinkey

--will that improve the performance
--- what are the side effects

Reg index creation on DtFromDate

what if i create index now on the dtfromdate column

---improve the performance

request your help on this.




Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-17 : 02:35:13
Creating foreign key relationship doesnt have any effect of performance. Its just a way of enforcing referential integrity ie making sure value you populate corresponds to a valid customer id value in tblCustomer
Adding an index on the column would help as it will be used for join operations.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Indsqlbeginner
Starting Member

21 Posts

Posted - 2014-01-19 : 23:43:24
quote:
Originally posted by visakh16

Creating foreign key relationship doesnt have any effect of performance. Its just a way of enforcing referential integrity ie making sure value you populate corresponds to a valid customer id value in tblCustomer
Adding an index on the column would help as it will be used for join operations.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs




Thanks for your valuable suggestion.
i was tried the below query.the issue i'm facing was some duplicate entries.. the reason being there are 4 records in the currentlocation table which is having same location id for some customers in that case this query yields duplicate records. how to avoild that.


SELECT c.*,l.LatestLoc
FROM tblCustomer c
CROSS APPLY(SELECT TOP 1 IntLocationId) AS LatestLoc
FROM tblCurrentLocation
WHERE IntCustomerId = c.IntCustomerId
ORDER BY DtFromDate DESC
)l
WHERE c.DtCustActive IS NULL



Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-20 : 06:46:49
quote:
Originally posted by Indsqlbeginner

quote:
Originally posted by visakh16

Creating foreign key relationship doesnt have any effect of performance. Its just a way of enforcing referential integrity ie making sure value you populate corresponds to a valid customer id value in tblCustomer
Adding an index on the column would help as it will be used for join operations.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs




Thanks for your valuable suggestion.
i was tried the below query.the issue i'm facing was some duplicate entries.. the reason being there are 4 records in the currentlocation table which is having same location id for some customers in that case this query yields duplicate records. how to avoild that.


SELECT c.*,l.LatestLoc
FROM tblCustomer c
CROSS APPLY(SELECT TOP 1 IntLocationId AS LatestLoc
FROM tblCurrentLocation
WHERE IntCustomerId = c.IntCustomerId
ORDER BY DtFromDate DESC
)l
WHERE c.DtCustActive IS NULL






In such cases also you will not get duplicate so far as you've TOP 1 logic in subquery

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -