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
