SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Exact interval query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Hime
Starting Member

6 Posts

Posted - 09/30/2012 :  09:39:09  Show Profile  Reply with Quote
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.

Edited by - Hime on 09/30/2012 09:39:36

HenryFulmer
Posting Yak Master

USA
110 Posts

Posted - 09/30/2012 :  10:43:24  Show Profile  Reply with Quote
What datatype is column [Date]?

WHERE [Date]>='200201' AND [Date]<='201201'
Go to Top of Page

Hime
Starting Member

6 Posts

Posted - 09/30/2012 :  10:50:40  Show Profile  Reply with Quote
Date is in integer
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/30/2012 :  18:15:06  Show Profile  Reply with Quote
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?
Go to Top of Page

Hime
Starting Member

6 Posts

Posted - 10/01/2012 :  07:55:37  Show Profile  Reply with Quote
Yes, 200201 represents January 2002. It is inserted as an integer in my database.

I mean, all, exactly all values from 200201 to 201201.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/01/2012 :  08:30:47  Show Profile  Reply with Quote
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
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/01/2012 :  09:39:06  Show Profile  Reply with Quote
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))
Go to Top of Page

Hime
Starting Member

6 Posts

Posted - 10/01/2012 :  09:47:02  Show Profile  Reply with Quote
The code runs, but I get no result, which is strange considering there are 114 programs who should be available with the constraints given.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/01/2012 :  09:55:22  Show Profile  Reply with Quote
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
Go to Top of Page

Hime
Starting Member

6 Posts

Posted - 10/02/2012 :  10:11:34  Show Profile  Reply with Quote
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?

Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/02/2012 :  10:43:59  Show Profile  Reply with Quote
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))
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000