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

Transaction / Records Based Queries

select --List of assets that have been scanned on a delivery that has no associating accounting data (example: order / invoice from ERP)
	--r.RecordId,
	--ra.AssetId,
	r.Invoice OrderNumber,
	ra.TrackNum Barcode,
	ra.CrossRef SerialNumber,
	iif(ra.IsDeliver = 1, 'Delivered', 'Returned') [Delivered/Returned],
	ra.AssetTypeName, 
	ra.AssetCatName,
	ra.AssetGroupName,
	ra.ProductCodeName,
	ra.ProductCodeDescription
from opendata.Records r
join opendata.RecordDetails rd
	on rd.RecordId = r.RecordId
	and rd.IsControlStrKeyedIn = 1
join opendata.RecAssets ra
	on ra.RecordId = r.RecordId
where r.IsDeleted = 0 
and r.Invoice <> ''
and r.ActionId = 6		--Delivery
and not exists (
	select *
	from opendata.Orders o
	where o.OrderNumber = r.Invoice
)
order by r.RecordId

Customer Based Queries

select                                  -- Report to pull basic Customer Information 
     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],
     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

Asset Based Queries

Inventory

What kind of assets do I have and where are they?

select --top 100 * 
a.tracknum 'Barcode',
a.crossref 'Serial Number',
'Location Type' =
case when a.holderid > 0 then 'At Customer'
when a.holderid <= 0 then 'In-House'
else '' end,
'Location Name' =
case when a.holderid > 0 then (a.holderstr + ' - ' + h.holdername)
when a.holderid <= 0 then a.locationname
else '' end,
a.assetcatname,
a.assetgroupname,
a.assettypename,
a.productcodename,
a.usestate
from opendata.assets a
LEFT JOIN opendata.holders h on a.holderid = h.holderid

Days At Customer

--How long have my assets been out at customers? --

select distinct --top 100
	a.TrackNum'Barcode',
	a.CrossRef'Serial Number',
	IIF(a.holderid > 0, 'At Customer', 'In-House') 'Location Type',
	IIF(a.holderid > 0, (a.holderstr + ' - ' + h.holdername), a.locationname) 'Location Name',
	a.AssetCatName,
	a.AssetGroupName,
	a.AssetTypeName,
	a.ProductCodeName,
	(select top 1 Invoice FROM opendata.Records r2 JOIN opendata.RecAssets ra2 ON r2.RecordId = ra2.RecordId WHERE actionid = 6 AND ra2.AssetId = ra.AssetId ORDER BY r2.RecDate DESC) 'Invoice',
	(select top 1 r2.recdate FROM opendata.Records r2 JOIN opendata.RecAssets ra2 ON r2.RecordId = ra2.RecordId WHERE actionid = 6 AND ra2.AssetId = ra.AssetId ORDER BY r2.RecDate DESC) 'Invoice Date',
	datediff(d, (SELECT TOP 1 r2.recdate FROM Records r2 JOIN opendata.RecAssets ra2 ON r2.RecordId = ra2.RecordId WHERE actionid = 6 AND ra2.AssetId = ra.AssetId ORDER BY r2.RecDate DESC), getdate()) 'Days Held',
	a.UseState
from opendata.Assets a
left join opendata.Holders h 
	on h.holderid = a.holderid
join opendata.RecAssets ra 
	on ra.assetid = a.assetid
join opendata.Records r 
	on r.recordid = ra.recordid 
	and r.actionid = 6 
	and ra.isdeliver = 1 
where a.holderid > 0