Solved

Set a negative column value to 0 in SQL Server

Posted on 2010-08-19
1
351 Views
Last Modified: 2012-05-10
I have a view which computes the fines for a delayed library book. The logic being used is check the no of day between the DUE_DATE and the current date and multiplies it with a FINE_AMOUNT.

Now the issue i am having is one of the column is  negative since due date is greater than today's date. See my attached script.

My reference is this code in the view
(DATEDIFF(dd,DUE_DATE,getdate()) *  FINE_PRICE) AS FINE_AMOUNT,
--ALTER VIEW  [dbo].[LIBRARY_LOAN_REGISTRATION_VIEW]

--AS

SElECT

	LLR.ID,

	ISNULL(LLR.LIBRARY_ID,-1) AS LIBRARY_ID,

	ISNULL(LC.LIBRARY_NAME,'') AS LIBRARY_NAME,

	ISNULL(LC.FINE_PRICE,1) AS FINE_PRICE,

	ISNULL(LLR.ASSET_ID,-1) AS ASSET_ID,

	ISNULL(LR.TITLE,'') AS TITLE,

	ISNULL(LLR.LOANER_ID,-1) AS LOANER_ID,

	CASE LOANER_ID

         WHEN 1 THEN ISNULL(STUDENT_REG.FIRST_NAME,'') + ' ' + ISNULL(STUDENT_REG.LAST_NAME,'')

         WHEN 2 THEN ISNULL(TEACHER.FIRST_NAME,'') + ' ' + ISNULL(TEACHER.LAST_NAME,'')

         WHEN 3 THEN ISNULL(ADMINISTRATOR.FIRST_NAME,'') + ' ' + ISNULL(ADMINISTRATOR.LAST_NAME,'')

         WHEN 4 THEN ISNULL(PRINCIPAL.FIRST_NAME,'') + ' ' + ISNULL(PRINCIPAL.LAST_NAME,'')

		 WHEN 5 THEN ISNULL(PARENT.FIRST_NAME,'') + ' ' + ISNULL(PARENT.LAST_NAME,'')

    END AS LOANER_NAME,	

	ISNULL(LLR.LOANER_TYPE_ID,-1) AS LOANER_TYPE_ID,

	ISNULL(CONVERT(varchar, LLR.LOAN_DATE, 106), '') AS LOAN_DATE, 

	ISNULL(CONVERT(varchar, LLR.DUE_DATE, 106), '') AS DUE_DATE, 

	ISNULL(CONVERT(varchar, LLR.RETURN_DATE, 106), '') AS RETURN_DATE, 

	DATEDIFF(dd,DUE_DATE,getdate()) AS NUM_OF_DAY_LATE,

	ISNULL(LLR.LENDING_STATUS_ID,-1) AS LENDING_STATUS_ID,

	(DATEDIFF(dd,DUE_DATE,getdate()) *  FINE_PRICE) AS FINE_AMOUNT,

 	ISNULL(LKLS.NAME,'') AS LENDING_STATUS

	 

FROM

	dbo.LIBRARY_LOAN_REGISTRATION LLR LEFT OUTER JOIN

	dbo.LIBRARY_CONFIGURATION LC ON LLR.LIBRARY_ID=LC.ID  LEFT OUTER JOIN

	dbo.LIBRARY_REPOSITORY LR ON LLR.ASSET_ID=LR.ID LEFT OUTER JOIN

	dbo.LIBRARY_KEYWORD_LENDING_STATUS LKLS ON LLR.LENDING_STATUS_ID=LKLS.KEYWORD_ID  LEFT OUTER JOIN

	dbo.STUDENTS_REGISTRATION_VIEW AS STUDENT_REG ON LLR.LOANER_ID = STUDENT_REG.ID  AND LLR.LOANER_ID = 1 LEFT OUTER JOIN	

	dbo.STAFF_REGISTRATION_VIEW AS TEACHER ON LLR.LOANER_ID = TEACHER.ID AND LLR.LOANER_ID = 2 LEFT OUTER JOIN    

	dbo.STAFF_REGISTRATION_VIEW AS ADMINISTRATOR ON LLR.LOANER_ID = ADMINISTRATOR.ID AND LLR.LOANER_ID =3 LEFT OUTER JOIN    

	dbo.STAFF_REGISTRATION_VIEW AS PRINCIPAL ON LLR.LOANER_ID = PRINCIPAL.ID AND LLR.LOANER_ID = 4 LEFT OUTER JOIN    

	dbo.PARENT_REGISTRATION_VIEW AS PARENT ON LLR.LOANER_ID = PARENT.ID AND LLR.LOANER_ID = 5

GO



SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

Open in new window

0
Comment
Question by:TECH_NET
1 Comment
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
ID: 33482035
CASE WHEN DATEDIFF(dd,DUE_DATE,getdate()) < 0 Then 0 ELSE
(DATEDIFF(dd,DUE_DATE,getdate()) *  FINE_PRICE) END AS FINE_AMOUNT,
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now