Solved

Set a negative column value to 0 in SQL Server

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to show the last employee that updated a record 5 44
SQL HELP 2 87
Inserting a column in a table that creates an ID and row number 4 58
Query 14 55
This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

776 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