WARNING: The TrackAbout MetaWiki has been deprecated and is no longer being updated.
Please visit our new TrackAbout Knowledge Base at https://supportkb.trackabout.com for the most-up-to-date documentation on TrackAbout and TrackAbout Mobile.
Difference between revisions of "OpenData Base Queries"
Jump to navigation
Jump to search
Jgullickson (talk | contribs) m |
Jgullickson (talk | contribs) |
||
| Line 32: | Line 32: | ||
<syntaxhighlight lang="tsql"> | <syntaxhighlight lang="tsql"> | ||
| + | -- Report to pull basic Customer Information -- | ||
| + | |||
SELECT h.[HolderId] 'TA Customer ID' | SELECT h.[HolderId] 'TA Customer ID' | ||
,h.[ParentId] 'TA Parent ID' | ,h.[ParentId] 'TA Parent ID' | ||
| Line 40: | Line 42: | ||
,h.[HolderStr] | ,h.[HolderStr] | ||
,h.[HolderName] | ,h.[HolderName] | ||
| − | |||
| − | |||
,h.[TimeZoneId] | ,h.[TimeZoneId] | ||
,h.[IsActive] | ,h.[IsActive] | ||
| Line 50: | Line 50: | ||
,a.Country | ,a.Country | ||
| − | |||
FROM [opendata].[Holders] h | FROM [opendata].[Holders] h | ||
LEFT JOIN opendata.holders p on h.ParentId = p.holderid and h.ParentId > 0 | 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.Addresses a on h.billtoaddress = a.AddressId -- select top 100 * from opendata.addresses | ||
| − | |||
| − | |||
</syntaxhighlight> | </syntaxhighlight> | ||
Revision as of 14:04, 8 January 2021
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
Customer Based Queries
-- Report to pull basic Customer Information --
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]
,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