Difference between revisions of "Helpful Queries"
Jump to navigation
Jump to search
m |
m |
||
| (7 intermediate revisions by the same user not shown) | |||
| Line 9: | Line 9: | ||
{| class = "wikitable" | {| class = "wikitable" | ||
|- | |- | ||
| − | | | + | |Start and End Lease total Inventories. |
| − | | | + | |<syntaxhighlight lang="SQL"> |
| − | + | #TOTAL 5 6 | |
| + | ---------------------------- | ||
| + | --Change these parameters: | ||
| + | #DEFINE :AGR='The Lease #' | ||
| + | #DEFINE :PDT='mm/dd/yyyy' | ||
| + | ---------------------------- | ||
| + | SELECT s.user_ref, p.name,md.prod_date, md.start_bal, md.end_bal | ||
| + | FROM md_product md | ||
| + | LEFT JOIN site s ON md.site_id = s.site_id | ||
| + | LEFT JOIN product p On md.product_id = p.product_id | ||
| + | |||
| + | WHERE md.product_id IN | ||
| + | (SELECT product_id FROM product | ||
| + | WHERE site_id = md.site_id AND [type] = 'OIL' AND name = 'OIL' ) | ||
| + | AND s.[site_type] IN ('WEL') | ||
| + | AND md.prod_date >= :PDT | ||
| + | AND s.site_id IN (SELECT site_id FROM site_agree WHERE [value] = :AGR) | ||
| + | |||
| + | ORDER BY s.user_ref, prod_date | ||
| + | </syntaxhighlight> | ||
|- | |- | ||
| + | |Tank Strappings | ||
| + | |<syntaxhighlight lang="SQL"> | ||
| + | SELECT | ||
| + | w.state AS 'State', | ||
| + | w.user_ref As 'User Ref', | ||
| + | RTRIM(w.name+' '+ISNULL(w.number,'')) As 'SiteName', | ||
| + | t.name As 'Tank Name', | ||
| + | s.increment As 'Increments', | ||
| + | CAST(s.increment/48 As VARCHAR) +''', ' | ||
| + | + CAST((s.increment-((s.increment/48)*48))/4 AS VARCHAR)+ '", ' | ||
| + | + CAST(s.increment%48%4 As VARCHAR) As 'Ft, In, Qtr', | ||
| + | s.volume As 'Strap Volume' | ||
| + | |||
| + | FROM strap s | ||
| + | LEFT JOIN tank t ON s.tank_id = t.tank_id | ||
| + | LEFT JOIN site w ON t.site_id = w.site_id | ||
| + | --WHERE user_ref = 'WELL1' | ||
| + | -- AND t.name = '123' | ||
| + | ORDER BY w.state, w.name, w.number,t.name, s.increment | ||
| + | </syntaxhighlight> | ||
| + | |- | ||
| + | <!-- | ||
| + | Template | ||
| + | | Query Name | ||
| + | |<syntaxhighlight lang="SQL"> code </syntaxhighlight> | ||
| + | |- | ||
| + | --> | ||
|} | |} | ||
| + | |||
Latest revision as of 17:44, 15 May 2020
This page contains queries that have been found useful for Prams users.
◄ QueryX2
To use one of the queries below, copy all the data and paste into QueryX2, change the parameters, then run the query.
If you are having difficulties running a query or need a new query built, call Prams at 303-904-6855.
| Start and End Lease total Inventories. | #TOTAL 5 6
----------------------------
--Change these parameters:
#DEFINE :AGR='The Lease #'
#DEFINE :PDT='mm/dd/yyyy'
----------------------------
SELECT s.user_ref, p.name,md.prod_date, md.start_bal, md.end_bal
FROM md_product md
LEFT JOIN site s ON md.site_id = s.site_id
LEFT JOIN product p On md.product_id = p.product_id
WHERE md.product_id IN
(SELECT product_id FROM product
WHERE site_id = md.site_id AND [type] = 'OIL' AND name = 'OIL' )
AND s.[site_type] IN ('WEL')
AND md.prod_date >= :PDT
AND s.site_id IN (SELECT site_id FROM site_agree WHERE [value] = :AGR)
ORDER BY s.user_ref, prod_date |
| Tank Strappings | SELECT
w.state AS 'State',
w.user_ref As 'User Ref',
RTRIM(w.name+' '+ISNULL(w.number,'')) As 'SiteName',
t.name As 'Tank Name',
s.increment As 'Increments',
CAST(s.increment/48 As VARCHAR) +''', '
+ CAST((s.increment-((s.increment/48)*48))/4 AS VARCHAR)+ '", '
+ CAST(s.increment%48%4 As VARCHAR) As 'Ft, In, Qtr',
s.volume As 'Strap Volume'
FROM strap s
LEFT JOIN tank t ON s.tank_id = t.tank_id
LEFT JOIN site w ON t.site_id = w.site_id
--WHERE user_ref = 'WELL1'
-- AND t.name = '123'
ORDER BY w.state, w.name, w.number,t.name, s.increment |
Copyright © 2024 by PRAMS Plus LLC. All Rights Reserved.