Solved

SQL Date Range Question

Posted on 2014-01-03
15
416 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 143

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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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.

821 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