The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.
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
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
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get this solution by purchasing an Individual license!
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
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
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trial
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)
...
...
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)
...
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.
Thanks all.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008
From novice to tech pro — start learning today.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get this solution by purchasing an Individual license!
Start your 7-day free trial.
Open in new window