Solved

SQL Date Range Question

Posted on 2014-01-03
15
411 Views
Last Modified: 2014-01-07
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)
0
Comment
Question by:rwheeler23
  • 7
  • 7
15 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39755809
Could you  provide some sample data please?
Also can you tell what the data type is for field [docdate] ?
(e.g. datetime/date/varchar)
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39755862
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

0
 

Author Comment

by:rwheeler23
ID: 39756008
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)
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39758109
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".
0
 

Author Comment

by:rwheeler23
ID: 39758174
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.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39758259
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

0
 

Author Comment

by:rwheeler23
ID: 39758293
That makes sense. I will make this change.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 48

Expert Comment

by:PortletPaul
ID: 39758433
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
0
 

Author Comment

by:rwheeler23
ID: 39759023
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.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39761055
>>"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"
0
 

Author Comment

by:rwheeler23
ID: 39761059
I was taught in SQL 101 many moons ago to never use BETWEEN so that was never a consideration.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39761301
:) 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.
0
 

Author Comment

by:rwheeler23
ID: 39761800
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.
0
 

Author Closing Comment

by:rwheeler23
ID: 39761803
Thanks again!
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39763396
a pleasure! all the best for 2014. Cheers, Paul
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

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

17 Experts available now in Live!

Get 1:1 Help Now