?
Solved

Set a negative column value to 0 in SQL Server

Posted on 2010-08-19
1
Medium Priority
?
361 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 2000 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

777 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