December 11, 2006 at 12:00 pm
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
December 11, 2006 at 12:06 pm
SELECT DISTINCT POLICYNUMBER WHERE STATUS='A' AND MonthReported < DATEADD(year,EffectiveDate,10)
Lowell
December 11, 2006 at 12:13 pm
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.
December 11, 2006 at 12:17 pm
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply