Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 436
  • Last Modified:

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)
0
rwheeler23
Asked:
rwheeler23
  • 7
  • 7
1 Solution
 
PaulCommented:
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
 
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

0
 
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)
0
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
PaulCommented:
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
 
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.
0
 
PaulCommented:
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
 
rwheeler23Author Commented:
That makes sense. I will make this change.
0
 
PaulCommented:
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
 
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.
0
 
PaulCommented:
>>"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
 
rwheeler23Author Commented:
I was taught in SQL 101 many moons ago to never use BETWEEN so that was never a consideration.
0
 
PaulCommented:
:) 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
 
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.
0
 
rwheeler23Author Commented:
Thanks again!
0
 
PaulCommented:
a pleasure! all the best for 2014. Cheers, Paul
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 7
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now