Databases
StackOverflow2013
Postsdbo
POPostgresql Average Time it takes to go 5000 miles to calc next oil change
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POPostgresql Average Time it takes to go 5000 miles to calc next oil change
    text
    copied!<p>Lets say I have two tables.</p> <p><strong>Table 1:</strong> service_sales_closed</p> <p><strong>Table 2:</strong> service_sales_details</p> <p><strong>service_sales_closed:</strong></p> <pre><code>ronumber - Repair Order Number custno - Customer Number closedate - Closed RO Date mileage - Mileage vehid - VIN </code></pre> <p><strong>service_sales_details:</strong></p> <pre><code>ronumber opcode - Operation Code (What type of repair) </code></pre> <p><strong>What I would like to do is:</strong></p> <p>Per vehicle on a specific ronumber (lets say its oilchange) <strong>how much time does it take that vehicle to go about 5000 miles</strong>. Then if possible on todays date (or yesterdays) be able to <strong>determine if that vehicle should go in for an oilchange in the next week</strong> or so based on how long it takes that vehicle to drive 5000 miles. I do need to make sure they <strong>haven't come in recently for an oilchange</strong>, although that maybe negated because of the intelligence of the selection.</p> <p>I have the following to determine an oil change based on 23-30days since a vehicle has been in for an oilchange:</p> <pre><code> SELECT &lt;*noted fields*&gt; FROM service_sales_closed LEFT JOIN service_sales_details ON service_sales_closed.ronumber = service_sales_details.ronumber LEFT JOIN vehicle ON servicesalesclosed.vehid = vehicle.vehid WHERE service_sales_closed.closedate &gt;= current_date - integer '90' AND service_sales_closed.closedate &lt;= current_date - integer '83' AND service_sales_details.opcode = '04DOZ1' AND service_sales_closed.vehid NOT IN (SELECT service_sales_closed.vehid FROM service_sales_closed LEFT JOIN service_sales_details ON service_sales_closed.ronumber = service_sales_details.ronumber WHERE service_sales_closed.closedate &gt; current_date - integer '83' AND service_sales_details.opcode = 'oilchange') </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload