Exact interval query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Exact interval query

Author

Topic

Hime
Starting Member

6 Posts

Posted - 2012-09-30 : 09:39:09

Hi folks,

I have asked the greatest minds I know, yet they have not found a solution to my problem. Nor has a tremendous amount of internet searching helped me either. So I am posting here in the vain hope of maybe, just maybe, obtaining a solution to a problem which has vexed me for quite some time. Here goes:

quote:
SELECT RoR, Date, program_id FROM dbo.RoR JOIN CTA_program ON dbo.CTA_program.program_id=dbo.RoR.ret_program_id
WHERE Active = 'true'
ORDER BY Date

For the code above, I need to choose rows in RoR where Date is in the exact interval 200201 and 201201, i.e. a period, but the values are not saved as the type Date.

I cannot use BETWEEN-AND obviously, because then I only get a range. Nor can I use LIKE. GROUP-BY-HAVING has failed me/my friends as well.

Please let me know if my uqestion is to vague, or if you need furtehr information.

HenryFulmer
Posting Yak Master

110 Posts

Posted - 2012-09-30 : 10:43:24

What datatype is column [Date]?


WHERE [Date]>='200201' AND [Date]<='201201'

Hime
Starting Member

6 Posts

Posted - 2012-09-30 : 10:50:40

Date is in integer

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-30 : 18:15:06

Given that date is an integer, what does 200201 represent? Is it February 20th of 2001 or January 1 of 2002 or something else?

Also, can you explain more what you meant by "where Date is in the exact interval 200201 and 201201"? For example, did it mean you want to get all rows where Date is January 1, 2002 or later, but less than 2012 January 1? Or something else?

Hime
Starting Member

6 Posts

Posted - 2012-10-01 : 07:55:37

Yes, 200201 represents January 2002. It is inserted as an integer in my database.

I mean, all, exactly all values from 200201 to 201201.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-01 : 08:30:47

See if this works

DECLARE @start INT = 200201;
DECLARE @end INT = 201201;


SELECT RoR,
       Date,
       program_id
FROM   dbo.RoR
       JOIN CTA_program
            ON  dbo.CTA_program.program_id = dbo.RoR.ret_program_id
WHERE  ACTIVE = 'true'
	AND Date >= DATEADD(MONTH, @start%100, DATEADD(YEAR, @start / 100 -1900, 0))
	AND Date < DATEADD(MONTH, @end%100, DATEADD(YEAR, @end / 100 -1900, 0))
ORDER BY
       Date

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-01 : 09:39:06

Sorry - correction:

WHERE ACTIVE = 'true' 
AND
	DATEADD(MONTH, Date%100-1, DATEADD(YEAR, Date / 100 -1900, 0)) 
	>= DATEADD(MONTH, @start%100-1, DATEADD(YEAR, @start / 100 -1900, 0))
AND 
	DATEADD(MONTH, Date%100-1, DATEADD(YEAR, Date / 100 -1900, 0)) 
	< DATEADD(MONTH, @end%100-1, DATEADD(YEAR, @end / 100 -1900, 0))

Hime
Starting Member

6 Posts

Posted - 2012-10-01 : 09:47:02

The code runs, but I get no result, which is strange considering there are 114 programs who should be available with the constraints given.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-01 : 09:55:22

My understanding of what the date column represents might be incorrect. Can you run this query and see what the last 3 columns show?

DECLARE @start INT = 200201;
DECLARE @end INT = 201201;

SELECT TOP 10 
       RoR,
       Date,
       program_id,
       DATEADD(MONTH, Date%100 -1, DATEADD(YEAR, Date / 100 -1900, 0)) [DateColumn],
       DATEADD(MONTH, @start%100 -1, DATEADD(YEAR, @start / 100 -1900, 0)) [StartDate],
       DATEADD(MONTH, @end%100 -1, DATEADD(YEAR, @end / 100 -1900, 0)) [EndDate]
FROM   dbo.RoR
       JOIN CTA_program
            ON  dbo.CTA_program.program_id = dbo.RoR.ret_program_id
WHERE  ACTIVE = 'true'
ORDER BY
       Date

Hime
Starting Member

6 Posts

Posted - 2012-10-02 : 10:11:34

Hmm, I am unsure od how to interpret the result:

<code>
0,11 197702 405 1977-02-01 00:00:00.000 2002-01-01 00:00:00.000 2012-01-01 00:00:00.000
2,56 197703 405 1977-03-01 00:00:00.000 2002-01-01 00:00:00.000 2012-01-01 00:00:00.000
6,54 197704 405 1977-04-01 00:00:00.000 2002-01-01 00:00:00.000 2012-01-01 00:00:00.000
-4,81 197705 405 1977-05-01 00:00:00.000 2002-01-01 00:00:00.000 2012-01-01 00:00:00.000
1,64 197706 405 1977-06-01 00:00:00.000 2002-01-01 00:00:00.000 2012-01-01 00:00:00.000
5,94 197707 405 1977-07-01 00:00:00.000 2002-01-01 00:00:00.000 2012-01-01 00:00:00.000
-2,86 197708 405 1977-08-01 00:00:00.000 2002-01-01 00:00:00.000 2012-01-01 00:00:00.000
-7,12 197709 405 1977-09-01 00:00:00.000 2002-01-01 00:00:00.000 2012-01-01 00:00:00.000
4,45 197710 405 1977-10-01 00:00:00.000 2002-01-01 00:00:00.000 2012-01-01 00:00:00.000
-2,1 197711 405 1977-11-01 00:00:00.000 2002-01-01 00:00:00.000 2012-01-01 00:00:00.000
<\code>

I this get new columns, and only one program is chosen, with a limited set of returns. What did your code do?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-02 : 10:43:59

I was simply trying to see if the data in your table is correctly interpreted by the query. For example, the first line of the results you posted has date column as February 1, 1977. That would not be within the date range you are interested in (2002-01-01 to 2012-01-01).

If your query with the where clause shown below did not return any rows, that means the table does not have any data that falls within the date range of 2002-01-01 to 2012-01-01 and ACTIVE column has a 'True'. Remove the where clauses one by one to see what is causing the data to be filtered out.

WHERE ACTIVE = 'true' 
AND
	DATEADD(MONTH, Date%100-1, DATEADD(YEAR, Date / 100 -1900, 0)) 
	>= DATEADD(MONTH, @start%100-1, DATEADD(YEAR, @start / 100 -1900, 0))
AND 
	DATEADD(MONTH, Date%100-1, DATEADD(YEAR, Date / 100 -1900, 0)) 
	< DATEADD(MONTH, @end%100-1, DATEADD(YEAR, @end / 100 -1900, 0))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources