June 17, 2005 at 3:07 am
Hi ...
I have a Addresses table with the following structure and columns:
AddressId,
CustomerCode,
AddressType,
DateReceived,
Line1,
Line2,
Line3,
Line4,
ZipCode
The table contains both current and historical data. AddressId is the Primary Key which is unique for each row. The column AddressType specifies the type of address eg Home, Work, Postal etc. There are approx 20 types.
Please would you reccomend the SQL code I could use to select the three most recent (top 3) addresses FOR EACH customer and thereafter pivot the data into columns as follows:
CustomerCode,
Addr1AddressType,
Addr1DateReceived,
Addr1Line1,
Addr1Line2,
Addr1Line3,
Addr1Line4,
Addr1ZipCode,
Addr2AddressType,
Addr2DateReceived,
Addr2Line1,
Addr2Line2,
Addr2Line3,
Addr2Line4,
Addr2ZipCode,
Addr3AddressType,
Addr3DateReceived,
Addr3Line1,
Addr3Line2,
Addr3Line3,
Addr3Line4,
Addr3ZipCode
Many thanks,
Soahc
June 17, 2005 at 7:29 am
Bit long winded but might work
CREATE TABLE #temp (rowid int IDENTITY(1,1), colid int,
CustomerCode, AddressId, CustomerCode, a.AddressType, a.DateReceived, Line1, Line2, Line3, Line4, ZipCode)
INSERT INTO #temp
(colid, CustomerCode, AddressId, CustomerCode, a.AddressType, a.DateReceived, Line1, Line2, Line3, Line4, ZipCode)
SELECT 0, a.CustomerCode,a.AddressId,a.CustomerCode, a.AddressType, a.DateReceived, a.Line1, a.Line2, a.Line3, a.Line4, a.ZipCode
FROM [Customers] c
INNER JOIN Addresses a ON a.CustomerCode = c.CustomerCode
WHERE a.AddressId IN
(SELECT TOP 3 a2.AddressId
FROM Addresses a2
WHERE a2.CustomerCode = c.CustomerCode
ORDER BY DateReceived DESC)
ORDER BY a.CustomerCode ASC, a.DateReceived DESC
UPDATE t
SET t.colid = (t.rowid - m.rowid) + 1
FROM #temp t
INNER JOIN (SELECT m.CustomerCode, MIN(DateReceived) AS [DateReceived] FROM #temp m GROUP BY CustomerCode)
SELECT CustomerCode,
MAX(CASE WHEN colid=1 THEN AddressType ELSE '' END) AS [Addr1AddressType],
MAX(CASE WHEN colid=1 THEN DateReceived ELSE '' END) AS [Addr1DateReceived],
MAX(CASE WHEN colid=1 THEN Line1 ELSE '' END) AS [Addr1Line1],
MAX(CASE WHEN colid=1 THEN Line2 ELSE '' END) AS [Addr1Line2],
MAX(CASE WHEN colid=1 THEN Line3 ELSE '' END) AS [Addr1Line3],
MAX(CASE WHEN colid=1 THEN Line4 ELSE '' END) AS [Addr1Line4],
MAX(CASE WHEN colid=1 THEN ZipCode ELSE '' END) AS [Addr1ZipCode],
MAX(CASE WHEN colid=2 THEN AddressType ELSE '' END) AS [Addr2AddressType],
MAX(CASE WHEN colid=2 THEN DateReceived ELSE '' END) AS [Addr2DateReceived],
MAX(CASE WHEN colid=2 THEN Line1 ELSE '' END) AS [Addr2Line1],
MAX(CASE WHEN colid=2 THEN Line2 ELSE '' END) AS [Addr2Line2],
MAX(CASE WHEN colid=2 THEN Line3 ELSE '' END) AS [Addr2Line3],
MAX(CASE WHEN colid=2 THEN Line4 ELSE '' END) AS [Addr2Line4],
MAX(CASE WHEN colid=2 THEN ZipCode ELSE '' END) AS [Addr2ZipCode],
MAX(CASE WHEN colid=3 THEN AddressType ELSE '' END) AS [Addr3AddressType],
MAX(CASE WHEN colid=3 THEN DateReceived ELSE '' END) AS [Addr3DateReceived],
MAX(CASE WHEN colid=3 THEN Line1 ELSE '' END) AS [Addr3Line1],
MAX(CASE WHEN colid=3 THEN Line2 ELSE '' END) AS [Addr3Line2],
MAX(CASE WHEN colid=3 THEN Line3 ELSE '' END) AS [Addr3Line3],
MAX(CASE WHEN colid=3 THEN Line4 ELSE '' END) AS [Addr3Line4],
MAX(CASE WHEN colid=3 THEN ZipCode ELSE '' END) AS [Addr3ZipCode]
FROM #temp
GROUP BY CustomerCode
DROP TABLE #temp
Far away is close at hand in the images of elsewhere.
Anon.
June 17, 2005 at 7:41 am
Improved plan:
SET NOCOUNT ON
Declare @tbl Table (
CustomerCode varchar(50),
AddressType varchar(50),
DateReceived datetime,
Line1 varchar(50),
Line2 varchar(50),
Line3 varchar(50),
Line4 varchar(50),
ZipCode varchar(50),
AddressId int identity(1,1))
INSERT INTO @tbl
SELECT
'CustomerCode', 'AddressType-5', getdate()-5,'Line1-5','Line2-5','Line3-5', 'Line4-5','ZipCode-5'
UNION ALL
SELECT
'CustomerCode', 'AddressType-4', getdate()-4,'Line1-4','Line2-4','Line3-4', 'Line4-4','ZipCode-4'
UNION ALL
SELECT
'CustomerCode', 'AddressType-3', getdate()-3,'Line1-3','Line2-3','Line3-3', 'Line4-3','ZipCode-3'
UNION ALL
SELECT
'CustomerCode', 'AddressType-2', getdate()-2,'Line1-2','Line2-2','Line3-2', 'Line4-2','ZipCode-2'
UNION ALL
SELECT
'CustomerCode', 'AddressType-1', getdate()-1,'Line1-1','Line2-1','Line3-1', 'Line4-1','ZipCode-1'
UNION ALL
SELECT
'CustomerCode1', 'AddressType-4', getdate()-4,'Line1-4','Line2-4','Line3-4', 'Line4-4','ZipCode-4'
UNION ALL
SELECT
'CustomerCode1', 'AddressType-3', getdate()-3,'Line1-3','Line2-3','Line3-3', 'Line4-3','ZipCode-3'
UNION ALL
SELECT
'CustomerCode1', 'AddressType-2', getdate()-2,'Line1-2','Line2-2','Line3-2', 'Line4-2','ZipCode-2'
UNION ALL
SELECT
'CustomerCode1', 'AddressType-1', getdate()-1,'Line1-1','Line2-1','Line3-1', 'Line4-1','ZipCode-1'
SELECT * FROM @tbl
SELECT CustomerCode,
MAX(CASE WHEN colid=1 THEN AddressType ELSE '' END) AS [Addr1AddressType],
MAX(CASE WHEN colid=1 THEN DateReceived ELSE '' END) AS [Addr1DateReceived],
MAX(CASE WHEN colid=1 THEN Line1 ELSE '' END) AS [Addr1Line1],
MAX(CASE WHEN colid=1 THEN Line2 ELSE '' END) AS [Addr1Line2],
MAX(CASE WHEN colid=1 THEN Line3 ELSE '' END) AS [Addr1Line3],
MAX(CASE WHEN colid=1 THEN Line4 ELSE '' END) AS [Addr1Line4],
MAX(CASE WHEN colid=1 THEN ZipCode ELSE '' END) AS [Addr1ZipCode],
MAX(CASE WHEN colid=2 THEN AddressType ELSE '' END) AS [Addr2AddressType],
MAX(CASE WHEN colid=2 THEN DateReceived ELSE '' END) AS [Addr2DateReceived],
MAX(CASE WHEN colid=2 THEN Line1 ELSE '' END) AS [Addr2Line1],
MAX(CASE WHEN colid=2 THEN Line2 ELSE '' END) AS [Addr2Line2],
MAX(CASE WHEN colid=2 THEN Line3 ELSE '' END) AS [Addr2Line3],
MAX(CASE WHEN colid=2 THEN Line4 ELSE '' END) AS [Addr2Line4],
MAX(CASE WHEN colid=2 THEN ZipCode ELSE '' END) AS [Addr2ZipCode],
MAX(CASE WHEN colid=3 THEN AddressType ELSE '' END) AS [Addr3AddressType],
MAX(CASE WHEN colid=3 THEN DateReceived ELSE '' END) AS [Addr3DateReceived],
MAX(CASE WHEN colid=3 THEN Line1 ELSE '' END) AS [Addr3Line1],
MAX(CASE WHEN colid=3 THEN Line2 ELSE '' END) AS [Addr3Line2],
MAX(CASE WHEN colid=3 THEN Line3 ELSE '' END) AS [Addr3Line3],
MAX(CASE WHEN colid=3 THEN Line4 ELSE '' END) AS [Addr3Line4],
MAX(CASE WHEN colid=3 THEN ZipCode ELSE '' END) AS [Addr3ZipCode]
FROM
(SELECT COUNT(*) colid,a.CustomerCode,a.AddressType,a.DateReceived,a.addressid,a.line1,a.line2,a.line3,a.line4,a.zipcode
FROM @tbl a JOIN @tbl b
on a.CustomerCode=b.CustomerCOde AND
a.DateReceived<=b.DateReceived
GROUP BY a.addressid,a.CustomerCode,a.AddressType,a.DateReceived,a.line1,a.line2,a.line3,a.line4,a.zipcode) aa
WHERE colid<=3
group by CustomerCode
Vasc
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply