Solved

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

Posted on 2016-09-15
6
93 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 41

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 29

Assisted Solution

by:Pawan Kumar
Pawan Kumar 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 29

Accepted Solution

by:
Pawan Kumar earned 500 total points
ID: 41800835
@Author - Please let me know if you need more help on this.
0
Stressed Out?

Watch some penguins on the livecam!

 
LVL 69

Expert Comment

by:Scott Pletcher
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

728 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