[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 103
  • Last Modified:

SQL Date formulas to be able to use Query to mine data every 6 Months

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:
Select
b.CustId,
a.Name,
b.RefNbr,
c.shipperid,
b.DocDate,
c.totinvc,
'' as SpaceHolder,
c.TotTax,
(c.totinvc-B.DocBal) As payments,
B.DocBal,
(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
0
armgon
Asked:
armgon
2 Solutions
 
SharathData EngineerCommented:
check this.
Select
b.CustId,
a.Name,
b.RefNbr,
c.shipperid,
b.DocDate,
c.totinvc,
'' as SpaceHolder,
c.TotTax,
(c.totinvc-B.DocBal) As payments,
B.DocBal,
(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 ((CONVERT(varchar(5),b.docdate,1) <= '06/30' and b.docdate>='1/01/2016' and b.docdate<='06/30/2016')
 or (CONVERT(varchar(5),b.docdate,1) > '06/30' and b.docdate>='07/01/2016' and b.docdate<='12/31/2016'))

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
Here is the dynamic logic to get data for last 6 months..

Select
b.CustId,
a.Name,
b.RefNbr,
c.shipperid,
b.DocDate,
c.totinvc,
'' as SpaceHolder,
c.TotTax,
(c.totinvc-B.DocBal) As payments,
B.DocBal,
(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>= dateadd(m, -6, getdate() - datepart(d, getdate()) + 1) and b.docdate<= CAST(GETDATE() AS DATE) -----> here is the line I would like to use a formula in place
order by a.CustId,b.RefNbr

Open in new window

1
 
Pawan KumarDatabase ExpertCommented:
@Author - Please let me know if you need more help on this.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Scott PletcherSenior DBACommented:
WHERE condition for dates is below.  Do not use a function on the column in the table to do the comparison, as that could very severely hurt performance.

...
WHERE ...
/* get dates in the full six-month period before the current month, no matter what day of month it currently is. */
/* for example, if run on *any* day in Jan, this would select dates from Jun 1 thru Dec 31.  
b.docdate>=DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 6, 0) and b.docdate<DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
...
1
 
armgonAuthor Commented:
Thank you for all of your input.
0
 
armgonAuthor Commented:
I was able to complete the query and set up an automated job that fires twice a year for the data I need.

Thanks all.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now