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
 Date function question

Author  Topic 

SmirnoffKid
Starting Member

10 Posts
Posted - 2005-12-11 : 12:01:36
I am trying to compare Effective_Date, Expiration_Date and Cancellation_Date for particular Policies in a mock DB for class.

Is there a way that I can compare these three dates (which are defined as smalldatetime) then return the Num_Claims for the particular policy period. The database currently is hard-coded (w/ insert statements) and there are some Cancellation_Dates that are before the Expiration_Date.

Any help is greatly appreciated.

Select Policy_ID, Num_Claims, Claim_Date
	from Policy right join Claim
	on Claim.Policy_ID@ = Policy.Policy_ID
	where Policy.Num_Claims >='5'
	Between Effective_Date and Expiration_Date or
	Effective_Date and Cancellation_Date
	;

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2005-12-11 : 12:37:21
provide stuff indicated here:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

you'll get help faster

Go with the flow & have fun! Else fight the flow
Go to Top of Page

SmirnoffKid
Starting Member

10 Posts
Posted - 2005-12-11 : 12:40:41
Well, if you really want my Insert statements - I'll retrieve them.. 

 Create Table Policy (
	Policy_ID		varchar(20)	primary key,
	Policy_Premium		decimal(9,2)	not null,
	Effective_Date		smalldatetime	not null,
	Expiration_Date		smalldatetime	not null,
	Cancellation_Date	smalldatetime	not null,
	Num_Claims		tinyint,
	Underwriter_ID$		varchar(20)	null,
	Foreign key (Underwriter_ID$) references Underwriter (Underwriter_ID),
);

Insert into Policy values ('34732044580912123094', '2019.00', '04/24/04', '04/23/05', '', '0', 'U123458');
Insert into Policy values ('12234093438240812343', '1908.00', '02/21/03', '02/20/04', '01/21/04', '4', 'U123459');
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts
Posted - 2005-12-11 : 12:51:58
>>in a mock DB for class
I thought we don't do homework for people...
Be One with the Optimizer
TG
Go to Top of Page

SmirnoffKid
Starting Member

10 Posts
Posted - 2005-12-11 : 12:56:46
I never said it was for a homework assignment - I had a question about a class database and I wanted to see if I can do more functionality. But I can accept the fact if you dont want to help.
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2005-12-11 : 13:03:12
can you also provide expected results for the data you've given us?


Go with the flow & have fun! Else fight the flow
Go to Top of Page

SmirnoffKid
Starting Member

10 Posts
Posted - 2005-12-11 : 13:10:05
[code]Policy_ID Num_Claims
34732044580912123094 5
12234093438240812343 1[/code]

But this result must be within the policy period and the claim_date must be within the policy period as well. I wanted to compare the three Dates (listed above) so validate that the Claim_Date fell in the policy period. (Effective_Date -> Expiration_Date or Effective_Date -> Cancellation_Date) I was hoping to learn the datepart, dateadd functions and to see if I can compare dates
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

4184 Posts
Posted - 2005-12-11 : 13:30:12
Are you using Microsoft SQL Server or mySQL for this? You should be able to do this without using datepart or dateadd. It's just a simple AND (eff_date between date1 and date2 OR eff_date between date1 and date3). If you give us a little more sample data, we can validate the above. Give it a spin though, and we'll help you after you try it out.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

SmirnoffKid
Starting Member

10 Posts
Posted - 2005-12-11 : 13:53:48
Looks like I solved the question that I had asked - Using the help from Derrick I re-evaluated my code and made some slight modifications. It seems to work now.
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

4184 Posts
Posted - 2005-12-11 : 13:58:02
Good job then. Don't get bothered if we beat you up a little here. We get a lot of lazy people who don't want to even try and figure things out or want to cheat on homework. We do also help a lot of people out along the way though. Glad you figured it out.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site