Difference between revisions of "Helpful Queries"

From PRAMS Plus Wiki
Jump to navigation Jump to search
(Created page with "''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, ch...")
 
m
 
(8 intermediate revisions by the same user not shown)
Line 9: Line 9:
 
{| class = "wikitable"  
 
{| class = "wikitable"  
 
|-
 
|-
−
|Query 1
+
|Start and End Lease total Inventories.
−
|Code
+
|<syntaxhighlight lang="SQL">
−
|Result
+
#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>
 +
|-
 +
-->
 
|}
 
|}
 +
 +
 +
 +
 +
 +
 +
 +
Copyright © {{CURRENTYEAR}} by PRAMS Plus LLC. All Rights Reserved.

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.

Retrieved from "https://wiki.pramsplus.com/w/index.php?title=Helpful_Queries&oldid=1586"