SQL Date Range Question

How can I restructure this query to always returns the proper sums based on a date range? In this case a new year always begins on 05/01 so as time goes by I need the sums based on
05/01/13-05/31/13 then 05/01/13-06/30/13 ... 05/01/13-12/31/13 and then 05/01/13-01/31/14 and finally 05/01/13-04/30/14 and then each subsequent year the year would advance by one. My where clause needs to change to accommodate this. What is the proper way to select the date range?

select
'$'+ CONVERT(VARCHAR,(CONVERT(MONEY,coalesce(sum(case soptype when 3 then subtotal else subtotal*(-1) end),0))),1) AS SUBTOTAL_YTD,
'$'+ CONVERT(VARCHAR,(CONVERT(MONEY,coalesce(SUM(case soptype when 3 then FRTAMNT else FRTAMNT*(-1) END),0))),1) AS FRTAMNT_YTD,
'$'+ CONVERT(VARCHAR,(CONVERT(MONEY,coalesce(SUM(case soptype when 3 then TAXAMNT else TAXAMNT*(-1) END),0))),1) AS TAXAMNT_YTD,
'$'+ CONVERT(VARCHAR,(CONVERT(MONEY,coalesce(SUM(case soptype when 3 then TRDISAMT else TRDISAMT*(-1) END),0))),1) AS DISCAMNT_YTD,
'$'+ CONVERT(VARCHAR,(CONVERT(MONEY,coalesce(SUM(case soptype when 3 then DOCAMNT else DOCAMNT*(-1) END),0))),1) AS DOCAMNT_YTD
from powmt..sop30200
where DOCDATE >='05/01/'+ CONVERT(CHAR(4),YEAR(GETDATE())) AND DOCDATE <= FLOOR( CAST(GETDATE() AS FLOAT)) AND SOPTYPE IN(3,4)
LVL 1
rwheeler23Asked:
Who is Participating?
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.

PortletPaulEE Topic AdvisorCommented:
Could you  provide some sample data please?
Also can you tell what the data type is for field [docdate] ?
(e.g. datetime/date/varchar)
Guy Hengel [angelIII / a3]Billing EngineerCommented:
if would do like this:
select convert(varchar(7), docdate - 5, 120) yyyy_mm
  , sum(...)
  , sum(...)
  , sum(...)
 from powmt..sop30200
where SOPTYPE IN(3,4) 
  and DOCDATE >= convert(datetime, convert( varchar(8) , getdate() - 5, 120) + '01', 120)
  and DOCDATE < dateadd(year, 1, convert(datetime, convert( varchar(8) , getdate() - 5, 120) + '01', 120))
group by convert(varchar(7), docdate - 5, 120)
order by convert(varchar(7), docdate - 5, 120) 

Open in new window

rwheeler23Author Commented:
This is part of an email alert that goes out each day and report YTD sales figures. It takes invoice amounts and subtracts returns for total sales. So as each day goes by the YTD totals increase by that day's sales. I think I finally found something that works. Let me know what you think. The DB is 9gb and this table is too big to put in here.  The query reports total for SUBTOTAL, Freight, Tax and discounts.

select
'$'+ CONVERT(VARCHAR,(CONVERT(MONEY,coalesce(sum(case soptype when 3 then subtotal else subtotal*(-1) end),0))),1) AS SUBTOTAL_YTD,
'$'+ CONVERT(VARCHAR,(CONVERT(MONEY,coalesce(SUM(case soptype when 3 then FRTAMNT else FRTAMNT*(-1) END),0))),1) AS FRTAMNT_YTD,
'$'+ CONVERT(VARCHAR,(CONVERT(MONEY,coalesce(SUM(case soptype when 3 then TAXAMNT else TAXAMNT*(-1) END),0))),1) AS TAXAMNT_YTD,
'$'+ CONVERT(VARCHAR,(CONVERT(MONEY,coalesce(SUM(case soptype when 3 then TRDISAMT else TRDISAMT*(-1) END),0))),1) AS DISCAMNT_YTD,
'$'+ CONVERT(VARCHAR,(CONVERT(MONEY,coalesce(SUM(case soptype when 3 then DOCAMNT else DOCAMNT*(-1) END),0))),1) AS DOCAMNT_YTD
from powmt..sop30200
where SOPTYPE IN(3,4) AND DOCDATE >='05/01/'+
CASE WHEN MONTH(GETDATE()) < 5 THEN CONVERT(CHAR(4),YEAR(GETDATE())-1) ELSE CONVERT(CHAR(4),YEAR(GETDATE())) END
AND DOCDATE <= CONVERT(char(10),GETDATE(),101)
Your Guide to Achieving IT Business Success

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.

PortletPaulEE Topic AdvisorCommented:
I believe what you propose above would work, my concern however is that you are comparing a date/datetime to a varchar (and that varchar is in the MM/DD/YYYY format which isn't the best format to use) ; and this is done twice. I would prefer to avoid the implicit conversion from varchar to date or datetime by matching the calculated data type to the data type of [docdate].

I'd really like to know precisely what data type [docdate] is. date? datetime? smalldatetime?
also what version of SQL Server are you using?

If you are using SQL Server 2012 then datefromparts() might be used, e.g
SELECT
...
FROM sop30200
WHERE SOPTYPE IN(3,4)
AND DOCDATE >= (CASE WHEN month(getdate()) < 5
                           THEN datefromparts( YEAR(getdate()) - 1, 5, 1)
                           ELSE datefromparts( YEAR(getdate())    , 5, 1)
                  END)
AND DOCDATE < dateadd(dd, datediff(dd,0, getDate()), 0)
;

Open in new window

also note line 9 above omits the equal sign deliberately, if you are attempting to exclude today from results then don't use "less than or equal to", just use "less than".
rwheeler23Author Commented:
DOCDATE is a datetime field and I do not have the option of changing that. In this case it is SQL 2008 R2 and SQL 2012.
PortletPaulEE Topic AdvisorCommented:
Datetime is just fine.
I'm not suggesting you need to change the field's data type :)

What I am suggesting is that you compare the datetime field to a datetime value instead of a varchar value e.g.

CONVERT(char(10),GETDATE(),101)
this results in a varchar value representing "today" without a time element
You are also using format 101, a safer format to use is 112 (YYYYMMDD)

DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)
results in datetime value representing "today" with a time of 00:00:00+0000
Altenatives for this is:
SELECT
      CONVERT(char(10), GETDATE(), 101) --<< below are better ways
    , DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)
    , CONVERT(datetime, (CONVERT(date, GETDATE())))
    , CAST(CAST(GETDATE() AS date) AS datetime)

Open in new window

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
rwheeler23Author Commented:
That makes sense. I will make this change.
PortletPaulEE Topic AdvisorCommented:
Great, the change should be in 2 places

AND DOCDATE >= ... 1. change so it returns a datetime value
AND DOCDATE <   ... 2. change so it returns a datetime value

and I believe you only want "less than" in that last condition
rwheeler23Author Commented:
This alert kicks off at the end of every day so it does need to include that day's sales. I hope to have this change made by the end of today.
PortletPaulEE Topic AdvisorCommented:
>>"so it does need to include that day's sales"

That's fine, I'll have to leave this up to you as you are familiar with the data. Just note that you have built a time range that is the equivalent of "between" if you use: >= and <=

see: "Beware of Between"
rwheeler23Author Commented:
I was taught in SQL 101 many moons ago to never use BETWEEN so that was never a consideration.
PortletPaulEE Topic AdvisorCommented:
:) and pleased to hear it!
however, as I said, you have inadvertently created the same conditions as between

BETWEEN is the direct equivalent of:

( somefield >= someLowValue and somefield   <=   someHighValue )

typically, for date ranges, the overlap that can cause is dealt with by using:

( somefield >= someLowValue and somefield   <   someHighValue )

sorry if this is clearly obvious to you, I promise to stop now.
rwheeler23Author Commented:
As with everything else it all depends on what is needed. In my case, I am dealing with dates in an accounting program where ranges of dates are always inclusive of the entire range. You can never provide too much information. It is up to the receiver to decide what to do with it. Thanks again for all of your help.
rwheeler23Author Commented:
Thanks again!
PortletPaulEE Topic AdvisorCommented:
a pleasure! all the best for 2014. Cheers, Paul
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

From novice to tech pro — start learning today.