Computed Coloun

• Sarfaraj Ahmed

  Old Hand

  Points: 397

  More actions

  November 9, 2007 at 8:39 pm

  #183771

  CREATE TABLE [dbo].[Borrowers008](

  [AUTOID] [int] IDENTITY(10001,1) NOT NULL,

  [MemberID] [varchar](12) NOT NULL,

  [ItemID] [varchar](12) NOT NULL,

  [DateTaken] [datetime] NULL DEFAULT (getdate()),

  [Duedate] [datetime] NULL DEFAULT (getdate()+(7)),

  [SubmitDate] [datetime] NULL,

  [DaysDelay] AS (case when datediff(day,[DueDate],isnull([SubmitDate],(0)))<(0) then (0)

  else datediff(day,[Duedate],isnull([SubmitDate],getdate())) end),

  [FinePerDay] [money] NULL DEFAULT ((0.500)),

  [TotalFine] AS (case when datediff(day,[DueDate],isnull([SubmitDate],(0)))>(0)

  then datediff(day,[DueDate],isnull([SubmitDate],(0))) else (0) end*[fineperday]),

  [Paid] [tinyint] NULL,

  [PaidDate] [datetime] NULL

  ) ON [PRIMARY]

  please run this query.

  problem is if submitDate is null then cant calculate total fine. i want something which will be automated check the duedate and put value on taotalfine column.

  thanks in advance.

• mrpolecat

  SSCertifiable

  Points: 6964

  More actions

  November 10, 2007 at 5:19 pm

  #750069

  [TotalFine] AS (case when datediff(day,[DueDate],isnull([SubmitDate],[DueDate]))>(0)

  then datediff(day,[DueDate],isnull([SubmitDate],[DueDate])) else (0) end*[fineperday])

  ---

• Sarfaraj Ahmed

  Old Hand

  Points: 397

  More actions

  November 10, 2007 at 8:13 pm

  #750074

  mrpolecat (11/10/2007)

  ---

  [TotalFine] AS (case when datediff(day,[DueDate],isnull([SubmitDate],[DueDate]))>(0)

  then datediff(day,[DueDate],isnull([SubmitDate],[DueDate])) else (0) end*[fineperday])

  thanks for ur reply

  still same problem, if submited date is NULL then can't calculate total fine.

  code i want like this

  if submitedDate is Null then

  check dueDate and check todayDate and put daysDelay value then calculate total fine

  else

  check submitedDate and dueDate and put daysDelay value then calculate total vale

  end if

  thanks in advance

• Carl Federl

  One Orange Chip

  Points: 25384

  More actions

  November 11, 2007 at 5:57 am

  #750087

  Here is a simple solution:

  , DaysDelay as (CASE WHEN SubmitDate IS NULL OR Duedate IS NULL OR Duedate <= SubmitDate then 0

  else DATEDIFF(dd,SubmitDate,Duedate) END )

  , TotalFines as (FinePerDay * (CASE WHEN SubmitDate IS NULL OR Duedate IS NULL OR Duedate <= SubmitDate then 0

  else DATEDIFF(dd,SubmitDate,Duedate) END ))

  SQL = Scarcely Qualifies as a Language

• mrpolecat

  SSCertifiable

  Points: 6964

  More actions

  November 11, 2007 at 7:36 am

  #750095

  [TotalFine] AS (case when datediff(day,[DueDate],isnull([SubmitDate],getdate()))>(0)

  then datediff(day,[DueDate],isnull([SubmitDate],getdate())) else (0) end*[fineperday])

  I don't have access to a server to test this.

  ---

• SQL_ABD

  SSCertifiable

  Points: 5832

  More actions

  November 11, 2007 at 11:23 am

  #750106

  Hi,

  Just as a hint when you execute select isnull(submitdate,0) as submitdate from Borrowers008 and submitdate is null

  the result will be 1900-01-01 00:00:00.000 instead of 0.

  So I suggest to put a default date (like 1900-01-01 00:00:00) for null date

  Regards

  Ahmed

• Sarfaraj Ahmed

  Old Hand

  Points: 397

  More actions

  November 12, 2007 at 2:23 pm

  #750379

  thanks everybody

  i have done my task, code is below

  [font="Verdana"]DaysDelay AS (CASE WHEN SubmitDate IS NULL THEN DATEDIFF(DD, DUEDATE, GETDATE())

  ELSE DATEDIFF(DD,Duedate,SUBMITDATE) END ),

  FINED AS (CASE WHEN SUBMITDATE IS NULL AND DATEDIFF(DD, DUEDATE, GETDATE()) > (0)

  THEN DATEDIFF(DD, DUEDATE, GETDATE()) * FINEPERDAY

  WHEN SUBMITDATE IS NOT NULL AND DATEDIFF (DD,DUEDATE,SUBMITDATE) > (0)

  THEN DATEDIFF(DD,Duedate,SUBMITDATE) * FINEPERDAY

  ELSE 0

  END)[/font]