Simple query

Question

Post reply

Simple query

Best-330874

SSCrazy

Points: 2350
More actions
December 11, 2006 at 12:00 pm

#114875

Hi,
   I want to run a query on the following table.
The Business rule for it is as follows.
Select Records with unique PolicyNumber where the Status is Active and the MonthReported is less than the EffectiveDate + 10 years
CREATE TABLE [GE_Transaction] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Month_of_file] [datetime] NULL ,
[CompanyCode] [varchar] (3) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[PolicyNumber] [varchar] (10) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[Status] [char] (1) COLLATE SQL_Latin1_General_CP850_BIN NULL ,
[EffectiveDate] [datetime] NULL ,
        [MonthReported] [smalldatetime] NULL ,

) ON [PRIMARY]
GO

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323482 More actions · Answer 1

SELECT DISTINCT POLICYNUMBER WHERE STATUS='A' AND MonthReported < DATEADD(year,EffectiveDate,10)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Best-330874 SSCrazy Points: 2350 More actions · Answer 2

SELECT DISTINCT PolicyNumber

from GE_Transaction

WHERE Status='A' AND MonthReported < DATEADD(year,EffectiveDate,10)

i get the following error

Server: Msg 8116, Level 16, State 1, Line 1

Argument data type datetime is invalid for argument 2 of dateadd function.

Lowell SSC Guru Points: 323482 More actions · Answer 3

SELECT DISTINCT PolicyNumber

from GE_Transaction

WHERE Status='A' AND MonthReported < DATEADD(year,10,EffectiveDate)

i reversed the argument;

i don't know that MonthReported is a valid date, you'll have to evaluate with data to see if this is valid.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!