SQL ISSUE... - 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)

SQL ISSUE...

Author

Topic

gvmk27
Starting Member

44 Posts

Posted - 2011-09-15 : 11:24:05

each center will have one instructor and each instructor can have multiple centers...this relation exists in Center table.

instructor can place a combined orders for all his centers and request to deliver at single address...

requirement is...

I need to get latest order shipping address for center's which exists in OrderSummary table....so If a instructor has multiple centers, for all his centers we need to get last placed order shipping address among his centers

In ordersummary table I have centerid, address1, address1, city, state columns

I was trying to implement below in a stored procedure..

CREATE TABLE #OrderAddress
(
CustNumber Varchar(10),
InstructorId Varchar(10),
ShipToName Varchar(100),
Address1 Varchar(150),
City Varchar(50),
StateCode Varchar(20),
ZipCode Varchar(15),
Country Varchar(20),
)

DECLARE @CustNumber Varchar(15)
DECLARE @InstructorId Varchar(15)
DECLARE @ShipToName Varchar(100)
DECLARE @Address1 Varchar(150)
DECLARE @City Varchar(50)
DECLARE @StateCode Varchar(20)
DECLARE @ZipCode Varchar(15)
DECLARE @Country Varchar(20)

DECLARE CustomerNumbers CURSOR FAST_FORWARD READ_ONLY FOR
-- Get All distinct CustNumbers from OrderSummary table
SELECT Distinct CustNumber FROM OrderSummary

OPEN CustomerNumbers
FETCH NEXT FROM CustomerNumbers INTO @CustNumber
WHILE @@FETCH_STATUS = 0
BEGIN

SELECT TOP 1
@ShipToName = ShipToName,
@Address1 = Address1,
@City = City,
@StateCode = State,
@ZipCode = ZipCode,
@Country = Country
From
OrderSummary
WHERE
CustNumber IN (SELECT B.CenterID
FROM Center A
INNER JOIN Center B ON A.InstructorID = B.InstructorID
where A.CenterID = @CustNumber)
Order By CreateDate DESC

if (@ShipToName != '')
INSERT INTO #OrderAddress(CustNumber,InstructorId,ShipToName,Address1,City,StateCode,ZipCode,Country)
VALUES(@CustNumber,@InstructorId,@ShipToName,@Address1,@City,@StateCode,@ZipCode,@Country)

SET @ShipToName = ''
SET @Address1 = ''
SET @City = ''
SET @StateCode = ''
SET @ZipCode = ''
SET @Country = ''

FETCH NEXT FROM CustomerNumbers INTO @CustNumber
END

CLOSE CustomerNumbers
DEALLOCATE CustomerNumbers

SELECT * FROM #OrderAddress
DROP table #OrderAddress

but its taking 4 minutes to run....just a note center table exists in a different database.

Appreciate your help !

Regs
MOhan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-15 : 11:27:12

dont you have shippingdate? without which how do you define 'latest shipping'?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

gvmk27
Starting Member

44 Posts

Posted - 2011-09-15 : 11:32:43

Yes I have CreateDate..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-15 : 11:34:45

can you post what all columns you've in tables?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

gvmk27
Starting Member

44 Posts

Posted - 2011-09-15 : 11:42:01

Below is the OrderSummary table..

SELECT [OrderNumber]
,[OrderID]
,[OrderDate]
,[OrderType]
,[DocID]
,[OrderCenterType]
,[CenterId]
,[CustPONumber]
,[ShipMethod]
,[ShipToName]
,[PickupDate]
,[Address1]
,[Address2]
,[Address3]
,[County]
,[City]
,[State]
,[ZipCode]
,[Country]
,[Phone]
,[Status]
,[UserType]
,[OrderedByAssistant]
,[OrderedBy]
,[AxAddressID]
,[IsNewAddress]
,[CreateBy]
,[CreateDate]
,[ModifyBy]
,[ModifyDate]
FROM [OrderSummary]

below is the Center table..

SELECT [CenterID]
,[Center]
,[InstructorID]
,[Insurer]
,[InsureAgentName]
,[InsuranceNo]
,[InsureExpiryDate]
,[RentalAmt]
,[FAssistAmt]
,[FAssistStartDate]
,[FAssistExpiryDate]
,[FAssistAcctCode]
,[LeaseSignDate]
,[LeaseExpiryDate]
,[FedTaxNumber]
,[OpenDate]
,[CloseDate]
,[Latitude]
,[Longitude]
,[ReassignStatus]
,[ReassignCenterID]
,[SMSCenterID]
,[Status]
,[CMS2]
,[NCLB]
,[Web]
,[DistrictID]
,[MapQuestLatitude]
,[MapQuestLongitude]
,[WebNotes]
,[CreateBy]
,[CreateDate]
,[ModifyBy]
,[ModifyDate]
,[FranchiseGroupID]
,[CompanyName]
,[CenterGUID]
,[RentSpaceTypeID]
,[RentSpaceSize]
,[AccountCode]
,[FAssistAmt2Y]
FROM [Center]

I need to display following fields...

[InstructorId]
,[CenterId]
,[Address1]
,[Address2]
,[Address3]
,[City]
,[State]
,[ZipCode]
,[Country]

hope this helps...

gvmk27
Starting Member

44 Posts

Posted - 2011-09-15 : 11:43:02

here we can consider createdate column as shipping date column..

gvmk27
Starting Member

44 Posts

Posted - 2011-09-15 : 11:56:40

Hi Visakh

Thanks for your time....any update on this ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-15 : 12:55:17

[code]SELECT c.InstructorId
,c.CenterId
,os.Address1
,os.[Address2]
,os.[Address3]
,os.[City]
,os.[State]
,os.[ZipCode]
,os.[Country]
FROM OrderSummary os
INNER JOIN Center c
ON c.CenterID = os.CenterId
INNER JOIN (SELECT CenterID,MAX(CreateDate) AS Latest
FROM OrderSummary
GROUP BY CenterID)os1
ON os1.CenterID =os.CenterID
AND os1.Latest = os.CreateDate
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

gvmk27
Starting Member

44 Posts

Posted - 2011-09-15 : 14:01:43

Oh you achieved this without cursors !

Thanks a lot Visakh ! Really appreciate your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-15 : 14:22:39

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

gvmk27
Starting Member

44 Posts

Posted - 2011-09-15 : 15:32:21

hey ! This logic is missing

If a instructor has multiple centers, for all his centers we need to get last placed order shipping address among his centers.

lets say X Instructor has 2 centers A & B.

X Instructor, Center A address is Fords, NJ - Last Placed order Sep 15th 2011
X Instructor, Center B address is Edison, NJ - Last Placed order Sep 10th 2011

Result should be

X Instructor, Center A - Fords, NJ
X Instructor, Center B - Fords, NJ (Edison, NJ should not come as last placed order by the instructor is on Fords, NJ)

Thanks in advance.

gvmk27
Starting Member

44 Posts

Posted - 2011-09-16 : 10:44:37

Hi Visakh

Appreciate your help, if you can let me know can this be achieved wihtout using cursor ?

Thanks in advance...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-16 : 12:34:20

[code]
SELECT c.InstructorId
,c.CenterId
,os.Address1
,os.[Address2]
,os.[Address3]
,os.[City]
,os.[State]
,os.[ZipCode]
,os.[Country]
FROM Center c
CROSS APPLY (SELECT TOP 1 Address1
,[Address2]
,[Address3]
,[City]
,[State]
,[ZipCode]
,[Country]
FROM OrderSummary
WHERE InstructorId=c.InstructorId
ORDER BY CreateDate DESC)os
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

gvmk27
Starting Member

44 Posts

Posted - 2011-09-19 : 11:39:11

Thanks for your help Visakh.

I tried the above SQL, but for all center's it is displaying same address only, I mean first address is getting repeated for all the centers.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-19 : 11:43:06

but that was what your sample suggested rite?

X Instructor, Center A address is Fords, NJ - Last Placed order Sep 15th 2011
X Instructor, Center B address is Edison, NJ - Last Placed order Sep 10th 2011

Result should be

X Instructor, Center A - Fords, NJ
X Instructor, Center B - Fords, NJ (Edison, NJ should not come as last placed order by the instructor is on Fords, NJ)

its always same address (Fords, NJ)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

gvmk27
Starting Member

44 Posts

Posted - 2011-09-19 : 12:10:30

yes, but each Instructor should get his last placed order center address. I mean to say..

X Instructor, Center A address is Fords, NJ - Last Placed order Sep 15th 2011
X Instructor, Center B address is Edison, NJ - Last Placed order Sep 10th 2011
Y Instructor, Center C address is Iselin, NJ - Last Placed order Sep 14th 2011
Y Instructor, Center D address is Jersey City, NJ - Last Placed order Sep 11th 2011
Z Instructor, Center E address is Princeton, NJ - Last Placed order Sep 11th 2011

Result should be..

1.X Instructor, Center A - Fords, NJ
2.X Instructor, Center B - Fords, NJ (Edison, NJ should not come as last placed order by the instructor is on Fords, NJ)
3.Y Instructor, Center C address is Iselin, NJ
4.Y Instructor, Center D address is Iselin, NJ
5.Z Instructor, Center E address is Princeton, NJ

but if I use cross apply, I'm getting below result which is wrong.

1.X Instructor, Center A address is Fords, NJ
2.X Instructor, Center A address is Fords, NJ
3.Y Instructor, Center A address is Fords, NJ
4.Y Instructor, Center A address is Fords, NJ
5.Z Instructor, Center A address is Fords, NJ

Thanks for your time Visakh.

gvmk27
Starting Member

44 Posts

Posted - 2011-09-19 : 12:19:27

visakh, moreover there is no InstructorId column in my OrderSummary table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-19 : 13:09:31

[code];With CTE(Rn,InstructorID,CenterID)
AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY c.InstructorID ORDER BY os.CreateDate DESC) AS Rn,c.InstructorID,os.CenterID
FROM center c
INNER JOIN OrderSummary os
ON os.CenterID =c.CenterID
)

SELECT c.IntructorID,c.Center,ct.ddress1
,ct.[Address2]
,ct.[Address3]
,ct.[City]
,ct.[State]
,ct.[ZipCode]
,ct.[Country]
FROM Center c
INNER JOIN CTE ct
ON ct.InstructorID = c.InstructorID
AND ct.Rn=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

gvmk27
Starting Member

44 Posts

Posted - 2011-09-20 : 16:37:34

Thanks Visakh, above SQL worked perfectly for my requirement.

Thanks once again !!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-20 : 22:06:57

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources