Latest Customer Location - SQL Server Forums

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.

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

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>

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

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.

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

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources