SQL Date formulas to be able to use Query to mine data every 6 Months
Posted on 2016-09-15
Your assistance would be truly appreciated with this formula.
The query below is used to extract customer data for a specific time range.
I would like to set up a job that runs every six months using a query to generate a file with the current data.
In this query I am seeking to run I would like to use "b.docdate>='1/01/2016' and b.docdate<='6/30/2016' " but in a formula format that would run a report 6 Months back.
I would then fire the query up on 1/1 (for time period 7/1 to 12/31) and 7/1 (for 1/1 to 6/30) to generate the two yearly reports needed.
Your help with building a workable query is greatly appreciated.
Current Full Query:
'' as SpaceHolder,
(c.totinvc-B.DocBal) As payments,
(c.totinvc-c.TotTax) AS subtotal,
c.TotMerch as RebateAmount,
(c.TotMerch*.05) as RebatePaid
from Customer a, ARDoc b, soshipheader c
where a.custid=b.custid and b.refnbr=c.invcnbr and
a.SlsperId like '%114'and
b.docdate>='1/01/2016' and b.docdate<='12/31/2016' -----> here is the line I would like to use a formula in place
order by a.CustId,b.RefNbr