SQL query problem for Reporting [SOLVED]

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 2005 Forums

Transact-SQL (2005)

SQL query problem for Reporting [SOLVED]

Author

Topic

buellart
Starting Member

3 Posts

Posted - 2008-05-30 : 03:48:20

hi guys, im having a sql problem for reporting here. here is the table

CREATE TABLE `tblasset` (
`AssetID` int(11) NOT NULL,
`AssetName` varchar(50) NOT NULL,
PRIMARY KEY (`AssetID`)
);

INSERT INTO `tblasset` (`AssetID`, `AssetName`) VALUES
(1, 'IPOD'),
(2, 'Laptop'),
(3, 'Sony PS3'),
(4, 'Wooden Cupboard'),
(5, 'Fridge');

CREATE TABLE `tblassetplacement` (
`PlacementID` int(11) NOT NULL,
`LocationID` int(11) NOT NULL,
`AssetID` int(11) NOT NULL,
`PlacementDate` bigint(20) NOT NULL,
`OfficerInChargeID` varchar(50) DEFAULT NULL,
PRIMARY KEY (`PlacementID`)
);

INSERT INTO `tblassetplacement` (`PlacementID`, `LocationID`, `AssetID`, `PlacementDate`, `OfficerInChargeID`) VALUES
(1, 3, 1, 1209628156, 'John'),
(2, 6, 1, 1209800956, 'Susan'),
(3, 3, 3, 1209714556, 'Erik'),
(4, 4, 3, 1210405756, 'Albert'),
(5, 5, 3, 1211096956, 'Fred');

CREATE TABLE `tbllocation` (
`LocationID` int(11) NOT NULL,
`LocationName` varchar(50) NOT NULL,
`ParentID` int(11) DEFAULT NULL,
PRIMARY KEY (`LocationID`),
KEY `ParentID` (`ParentID`)
);

INSERT INTO `tbllocation` (`LocationID`, `LocationName`, `ParentID`) VALUES
(1, 'Building A', NULL),
(2, 'Building B', NULL),
(3, 'Room 1', 1),
(4, 'Room 2', 1),
(5, 'Partition 1', 4),
(6, 'Room 1', 2);

basically the location looks like this

Location Tree View
---
.___ Building A
. .___ Room 1
. .___ Room 2
. .___ Partition 1
.
.___ Building B
. .___ Room 1

on tblAssetPlacement, the column PlacementDate is a unix timestamp stored as bigint.
the higher of its value means it is the latest placement.

so i had created a sql to pull the data like this:

SELECT
tblasset.AssetName,
tblassetplacement.OfficerInChargeID,
tblassetplacement.Locationid,
tbllocation.LocationName
FROM
tblasset,tblassetplacement, tbllocation,
(select tblassetplacement.AssetID,tblassetplacement.LocationID,MAX(tblassetplacement.PlacementDate) AS LatestDate from tblassetplacement group by tblassetplacement.AssetID) temptbl
where
tblasset.AssetID = tblassetplacement.AssetID
and tblassetplacement.PlacementDate = temptbl.LatestDate
and tblassetplacement.AssetID = temptbl.AssetID
and tbllocation.LocationID=tblassetplacement.LocationID
;

this will output:
---
AssetName OfficerInChargeID LocationID LocationName
IPOD Susan 6 Room 1
Sony PS3 Fred 5 Partition 1

i would need to have a column ParentLocationName that directly shows the name of the parent's location as below:
---
AssetName OfficerInChargeID LocationID LocationName ParentLocationName
IPOD Susan 6 Room 1 Building B
Sony PS3 Fred 5 Partition 1 Building A

is there a way to pull the parent's location name in one sql syntax?

eralper
Yak Posting Veteran

66 Posts

Posted - 2008-05-30 : 04:49:58

Hello,

If you have parent - child relation ships on the same table, you can use Recursive Common Table Expressions (Recursive CTE's)

There exists a sample at [url]http://www.kodyaz.com/articles/article.aspx?articleid=18[/url] but you find more on this topic.

I hope this approach may help you

-------------
Eralper
http://www.kodyaz.com

buellart
Starting Member

3 Posts

Posted - 2008-05-30 : 05:05:11

thanx, btw what language is this
http://www.kodyaz.com/articles/article.aspx?articleid=18
post is using

ibeckett
Starting Member

12 Posts

Posted - 2008-05-30 : 12:25:51

English tutorial for CTE's:

[url]http://www.databasejournal.com/features/mssql/article.php/3502676[/url]

Ian Beckett
ibeckett at gmail dot com
www.sqlblog.ibeckett.com

buellart
Starting Member

3 Posts

Posted - 2008-05-31 : 10:06:02

hi folks, got this problem solved, here is the solution:

 
SELECT 
tblasset.AssetName, 
tblassetplacement.OfficerInChargeID, 
tblassetplacement.Locationid, 
tbllocation.LocationName,
COALESCE(GrGrGrandParentL.LocationName 
,GrGrandParentL.LocationName 
,GrandParentL.LocationName 
,ParentL.LocationName ) AS TopLocationName 
 
FROM 
tblasset,tblassetplacement, tbllocation, 
(SELECT tblassetplacement.AssetID,tblassetplacement.LocationID,MAX(tblassetplacement.PlacementDate) AS LatestDate 
FROM tblassetplacement GROUP BY tblassetplacement.AssetID) temptbl 
 
INNER JOIN tblasset AS A 
ON A.AssetID = temptbl.AssetID 
 
INNER JOIN tbllocation AS L
ON L.LocationID = temptbl.LocationID
 
LEFT OUTER JOIN tbllocation AS ParentL
ON ParentL.LocationID = L.ParentID
 
LEFT OUTER JOIN tbllocation AS GrandParentL
ON GrandParentL.LocationID = ParentL.ParentID
 
LEFT OUTER JOIN tbllocation AS GrGrandParentL
ON GrGrandParentL.LocationID = GrandParentL.ParentID
 
LEFT OUTER JOIN tbllocation AS GrGrGrandParentL
ON GrGrGrandParentL.LocationID = GrGrandParentL.ParentID
 
WHERE
tblasset.AssetID = tblassetplacement.AssetID 
AND tblassetplacement.PlacementDate = temptbl.LatestDate 
AND tblassetplacement.AssetID = temptbl.AssetID 
AND tbllocation.LocationID=tblassetplacement.LocationID 
;

re-check the result using this query

 
SELECT A.AssetName
,AP.OfficerInChargeID
,AP.PlacementDate
,L.Locationid
,L.LocationName
,COALESCE(GrGrGrandParentL.LocationName 
,GrGrandParentL.LocationName 
,GrandParentL.LocationName 
,ParentL.LocationName ) AS TopLocationName
 
FROM ( SELECT AssetID
,LocationID
,MAX(PlacementDate) AS LatestDate 
FROM tblassetplacement 
GROUP BY AssetID,LocationID ) AS temptbl
 
INNER JOIN tblassetplacement AS AP
ON AP.AssetID = temptbl.AssetID
AND AP.LocationID = temptbl.LocationID
AND AP.PlacementDate = temptbl.LatestDate 
 
INNER JOIN tblasset AS A
ON A.AssetID = temptbl.AssetID 
 
INNER JOIN tbllocation AS L
ON L.LocationID = temptbl.LocationID
 
LEFT OUTER JOIN tbllocation AS ParentL
ON ParentL.LocationID = L.ParentID
 
LEFT OUTER JOIN tbllocation AS GrandParentL
ON GrandParentL.LocationID = ParentL.ParentID
 
LEFT OUTER JOIN tbllocation AS GrGrandParentL
ON GrGrandParentL.LocationID = GrandParentL.ParentID
 
LEFT OUTER JOIN tbllocation AS GrGrGrandParentL
ON GrGrGrandParentL.LocationID = GrGrandParentL.ParentID
 
ORDER BY assetname,placementdate
;

credit to all from:

quote:

http://www.sqlteam.com
http://www.devnetwork.net
http://www.xtremevbtalk.com
http://www.devshed.com
http://www.vbcity.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources