Avatar of armgon
armgon
Flag for United States of America asked on

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
Microsoft SQL ServerMicrosoft SQL Server 2008SQL

Avatar of undefined
Last Comment
armgon

8/22/2022 - Mon
Sharath S

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

SOLUTION
Pawan Kumar

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Pawan Kumar

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Scott Pletcher

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)
...
armgon

ASKER
Thank you for all of your input.
Your help has saved me hundreds of hours of internet surfing.
fblack61
armgon

ASKER
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.