OpenData Base Queries

The queries listed here are designed to help you get basic information out of OpenData and to help you understand the data structure better. You can use these as base queries and modify them to suit your specific needs.

Asset Based Queries

Lost Assets

Purpose

To find a list of all assets that are currently marked as Lost.

Other Uses

Change the WHERE clause to 'Active' to find all currently active assets.

SELECT 
'Category' = assetcatname,
'Group' = assetgroupname,
'Type' = assettypename,
'ProductCode' = productcodename,
'Barcode' = tracknum,
'SerialNumber' = crossref,
'Ownership' = ownershipname
FROM opendata.assets
WHERE status = 'Lost'
ORDER BY assetcatname,assetgroupname,assettypename,productcodename,tracknum

Customer Based Queries

SELECT h.[HolderId] 'TA Customer ID'
      ,h.[ParentId] 'TA Parent ID'
	  ,h.holderstr 'Customer Number'
	  ,h.holdername 'Customer Name'
	  ,p.[holderstr] 'Parent Number'
	  ,p.[holdername] 'Parent Name'
      ,h.[HolderStr]
      ,h.[HolderName]
	  ,ciSeg.OptionValue 'Segment'
	  ,ciQuota.StringValue 'Quota'
      ,h.[TimeZoneId]
      ,h.[IsActive]
      ,a.Address1
	  ,a.Address2
	  ,a.Address3
	  ,a.City
	  ,a.Country

	 
  FROM [opendata].[Holders] h
  LEFT JOIN opendata.holders p on h.ParentId = p.holderid and h.ParentId > 0
  LEFT JOIN opendata.Addresses a on h.billtoaddress = a.AddressId  -- select top 100 * from opendata.addresses
  LEFT JOIN opendata.CustomInfo ciSeg on ciSeg.ItemId = h.holderid and ciSeg.TypeId = 290
  LEFT JOIN opendata.CustomInfo ciQuota on ciQuota.ItemId = h.holderid and ciQuota.TypeId = 291