Post

2 followers Follow
1
Avatar

Strom Structures With / Without Any Work Orders

Here is a filter which will pull all Storm Structures that have never had a Work Order. It functions by using the Work Order Asset Table to link Structures (by SN_ID) with Work Orders. It also limits results to Storm Structure type assets only based on the WKCATINV (Table list here on page 12). You can use these codes and other assets to replicate this workflow.  

 

Raw SQL Server Query:

SELECT * FROM YourStormDB.dbo.SMVSTRUC S

WHERE S.SN_ID NOT IN (SELECT AS_INV_ID FROM WKWOASSET
WHERE AS_CAT_INV = 25 AND AS_INV_ID IS NOT NULL)

ORDER BY S.SN_ID

Equivalent Lucity Web Filter No Work Orders:

SMVSTRUC WHERE (SMVSTRUC.SN_ID 
NOT IN (
SELECT WKWOASSET.AS_INV_ID FROM WKWOASSET
WHERE AS_CAT_INV = 25 AND AS_INV_ID IS NOT NULL
))


Originally I joined the Storm Structures to Work Order Asset tables, and then filtered only those features where the join was not successful (meaning there were no matches and therefore no Work Orders). This was a LEFT JOIN and a WHERE the table_b_key field IS NULL. Unfortunately joins are not supported in the web filter so this had to be converted to a NOT IN clause (Thank you Jonathan Semones). 

...AND AS_INV_ID IS NOT NULL)

 portion is critical or the result will be zero records. Read this article for details on LEFT JOIN / NULL vs NOT IN vs NOT EXISTS.

To show Structures WITH Work Orders, simply change NOT IN to IN:

Raw Query:

SELECT * FROM YourStormDB.dbo.SMVSTRUC S

WHERE S.SN_ID IN (SELECT AS_INV_ID FROM WKWOASSET
WHERE AS_CAT_INV = 25 AND AS_INV_ID IS NOT NULL)

ORDER BY S.SN_ID

 

Lucity Filter WITH Work Orders:

SMVSTRUC WHERE (SMVSTRUC.SN_ID IN 
(SELECT WKWOASSET.AS_INV_ID
FROM WKWOASSET
WHERE AS_CAT_INV = 25 AND AS_INV_ID IS NOT NULL))

 

 

Langdon Sanders

Please sign in to leave a comment.

1 comment