Solved

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

Posted on 2016-09-15
6
60 Views
Last Modified: 2016-09-17
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
Comment
Question by:armgon
6 Comments
 
LVL 40

Expert Comment

by:Sharath
ID: 41800740
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
 
LVL 17

Assisted Solution

by:Pawan Kumar Khowal
Pawan Kumar Khowal earned 500 total points
ID: 41800834
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
 
LVL 17

Accepted Solution

by:
Pawan Kumar Khowal earned 500 total points
ID: 41800835
@Author - Please let me know if you need more help on this.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 69

Expert Comment

by:ScottPletcher
ID: 41801657
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
 

Author Comment

by:armgon
ID: 41803053
Thank you for all of your input.
0
 

Author Closing Comment

by:armgon
ID: 41803054
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
MS SQL export CSV & schedule It 9 41
SQL Select Query help 3 31
sql calculate averages 18 24
MySQL left join performance 4 7
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now