Solved

Set a negative column value to 0 in SQL Server

Posted on 2010-08-19
1
354 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Compare data between two databases 16 109
Following an example - removing duplicate strings 4 61
Restrict result set 1 39
kill process lock Sql server 9 63
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 I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

840 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