CODEWITHSUNDEEP
sqlsql-serverdatebetween
phpPhil
phpPhil

Reputation: 926

filter date records

On a SQL Server 2008 I have a view revenue with the following schema:                                

+----------------------------+ 
| id | year | month | amount | 
+----------------------------+ 
|  1 | 2014 |    11 |    100 | 
|  2 | 2014 |    12 |   3500 | 
|  3 | 2014 |    12 |     90 | 
|  4 | 2015 |     1 |   1000 | 
|  5 | 2015 |     2 |   6000 | 
|  6 | 2015 |     2 |    600 | 
|  7 | 2015 |     3 |     70 | 
|  8 | 2015 |     3 |    340 | 
+----------------------------+ 

The schema and data above is simplified and the view is very big with millions of rows. I have no control over the schema, so cannot change the fields to DATE or similar. The year and month fields are INT.

I'm looking for a SELECT statement that returns me x months worth of data starting from an arbitrary month. For example rolling 3 months, rolling 5 months, etc.

What I came up with is this:

SELECT
rolling_date,
amount

FROM (SELECT CAST('01/' + RIGHT('00' + CONVERT(VARCHAR(2), month), 2) + '/' + CAST(year AS VARCHAR(4)) AS DATE) AS rolling_date,
         amount
    FROM [revenue]
) date_revenue

WHERE rolling_date BETWEEN CAST('01/12/2014' AS DATE) AND CAST('31/02/2015' AS DATE)

However, ...

  1. This doesn't work and throws Error line 1: Conversion failed when converting date and/or time from character string.. which seems to be referring to the BETWEEN clause
  2. This seems a terribly awkward way of doing it and a waste of resources. What is an efficient way to write this query?

Upvotes: 1

Views: 173

Answers (4)

Jon Tirjan
Jon Tirjan

Reputation: 3694

First off, the conversion error happens because there is no February 31st. I changed it to February 28th in the sample below.

Since your table contains millions of rows, you're best off avoiding any conversions or calculations on the data in the table. Instead, convert the input to a format which matches your table. That way you can take advantage of indexes.

The following example will be very efficient, especially if you can create a nonclustered index on Year, Month.

declare @start datetime = '2014-12-01'
declare @end datetime = '2015-02-28'

declare @startyear int = datepart(year, @start)
declare @startmonth int = datepart(month, @start)

declare @endyear int = datepart(year, @end)
declare @endmonth int = datepart(month, @end)

select * from revenue
where (Year > @startyear OR (Year = @startyear AND Month >= @startmonth))
AND (Year < @endyear OR (Year = @endyear AND Month <= @endmonth))

Edit: The following example is identical from a processing standpoint, and does not declare any new variables:

select * from revenue
where (Year > datepart(year, @start)
  OR (Year = datepart(year, @start) AND Month >= datepart(month, @start)))
AND (Year < datepart(year, @end)
  OR (Year = datepart(year, @end) AND Month <= datepart(month, @end)))

Edit 2: If you're able to pass in the Year & Month individually, you can run this:

select * from revenue r
where (Year > 2014 OR (Year = 2014 AND Month >= 12))
AND (Year < 2015 OR (Year = 2015 AND Month <= 2))

Upvotes: 3

r12
r12

Reputation: 1732

I can't check my syntax right now on this machine, but I would try something like this. Basically, I took your start month and year and made it a datetime with 01 as the day, then I used that to compare it to the casting in the same manner for each row, using the technique in this answer.

declare @n integer
set @n = 3

declare @startmonth as integer
set @startmonth = 12

declare @startyear as integer
set @startyear = 2014

declare @startdatetime as datetime
set @startdatetime = 
      CAST(@startyear AS VARCHAR(4)) +
      RIGHT('0' + CAST(@startmonth AS VARCHAR(2)), 2) +
      '01'
   AS DATETIME)

select
*
from revenue
where
CAST(
      CAST(year AS VARCHAR(4)) +
      RIGHT('0' + CAST(month AS VARCHAR(2)), 2) +
      '01'
   AS DATETIME) >= @startdatetime
and
CAST(
      CAST(year AS VARCHAR(4)) +
      RIGHT('0' + CAST(month AS VARCHAR(2)), 2) +
      '01'
   AS DATETIME)
<
dateadd(month, @n, @startdatetime)

Upvotes: 0

TavoloPerUno
TavoloPerUno

Reputation: 579

It the SQL server version is 2012 and above, you could use DATEFROMPARTS method to construct dates from year and month. If not, modify your query to this :

SELECT
rolling_date,
amount

FROM (SELECT CAST(
  CAST(year AS VARCHAR(4)) +
  RIGHT('0' + CAST(month AS VARCHAR(2)), 2) +
  RIGHT('0' + CAST('01' AS VARCHAR(2)), 2) 
 AS DATE) AS rolling_date,
     amount
FROM [revenue]
) date_revenue

WHERE rolling_date BETWEEN CAST('2014/02/01' AS DATE) AND CAST('2015/12/31' AS DATE)

Upvotes: 0

druidicwyrm
druidicwyrm

Reputation: 500

You can do an integer comparison for your year month:

SELECT
 id, yr, month, amount
FROM
  Magazines
WHERE yr*100 + month >= 201412 AND yr*100 + month <= 201503

This will not return your year month as a date however. Is this a requirement?

Upvotes: 2

Related Questions