Solved

Set a negative column value to 0 in SQL Server

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

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

Suggested Solutions

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
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…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

739 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