Auto Column Check

Question

Auto Column Check

Sarfaraj Ahmed

SSC-Addicted

Points: 417
More actions
October 18, 2007 at 4:50 am

#75796

Hello Everybody
I am new in SQL SERVER 2005 and ASP.NET 2.0 , Im doing a proper Online Library Management Database , I know you are expert and please help me.
On Borrower Table
MemberID, ItemID, DateTaken, Duedate, SubmitDate, Delay
On Penalty Table
MemberID, ItemID, Fine, Paid, DatePaid
(B as Borrower and P as Penalty) and a Member borrow a Item and Borrower below
M.MemberID = SCOM1001
M.ItemID=EBEG100302
M.DateTaken = 14/10/2007
M.DueDate= 16/10/2007
M.Submitdate= NULL
M.Delay=NULL
right now CurrentDate is 18/10/2007 and M.MemberID = SCOM1001 did not return ItemID = EBEG100302 .
Per day penalty is 1.500 and Member already delayed 2 days and Tomorrow it will be 3 days then 4 days then 5 days............
Just think Penalty Table is Empty, If you delay 1 day then Penalty Table will automatic add
P.MemberID=SCOM1001
P.ItemID = EBEG100302
P.Fine=1.500
P.Paid=NO
P.PaidDate=NULL
Dealy day 2
Penalty Table will automati update P.Fine, Value will be 3.000
Delay day 3, day4....................... UpdateP.Fine
My Question is it possible to write some code inside my SQL SERVER datbase, Which will automatic check the B.DateDue column and (add or update Penaly Table where necessary)
I can write some function inside ASP.NET which will create a report for my needs but It will be taken more time.
I want my DataBase will check column status and put value to other table or same table where necessary.
Please tell me how can I do it? Any instruction will be really helpful.
Thanks
Sarfaraj Ahmed

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

Why do you want a cumulative update on fine each day?

It's easier to just query for that information, and you don't have to mess around with permanently storing a derived value.

All you need is in the borrowers table (except the amount of the actual fine per day)

You *could* make the fine column a computed column though, I guess.

Haven't looke that close what the formula would look like, though, the thought just struck me as a possible 'auto-update' feature...

/Kenneth

Lowell SSC Guru Points: 323504 More actions · Answer 2

i don't think you can use a calculated column in this case, because the penalty amount resides in a different table(P as Penalty Table) than the items that determine the penalty, right?(Borrower TAble)

if the penalty is getdate - duedate where submit date is null, the penalty amount could be calculated in a view, or updated as a proc or trigger, but not inline in the borrower table.

Also would penalties accrue EVERY day, or only business days? if the business is closed saturday/sunday, wouldn't you need to skip adding tot eh penalties on those days? what about holidays?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 3

Yea, thought there might be something preventing that with the current looks of tables.

Though, it seems like this is in the build-process, so perhaps some schema changes still is an option..

If it's going to be 'proper', then changing schema according to needs and functionality should be a valid option worth considering...

/Kenneth

Sergiy SSC Guru Points: 110206 More actions · Answer 4

Lowell (10/18/2007)
i don't think you can use a calculated column in this case, because the penalty amount resides in a different table

You can declare computed column using a function.

And function can query as many tables as you wish.

_____________
Code for TallyGenerator

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 5

Another idea... The penalty is only applied if the Delay is > 0.

So, why not make Delay a computed column?

Then any penalties in the Borrower table will be dynamic, and easily found, and easily calculated, since the penalty would be delay * ..

create table dbo.borrower

(MemberID varchar(10) not null, ItemID varchar(10) not null, DateTaken datetime not null,

Duedate datetime not null, SubmitDate datetime null,

Delay as (DATEDIFF(day, Duedate, ISNULL(SubmitDate, GETDATE()))) )

/Kenneth

Sarfaraj Ahmed SSC-Addicted Points: 417 More actions · Answer 6

Thanks For Your Reply

I am not going to use Fine Table. I will use only Borrowers Table and columns are...

MemberID, ItemID, DateTaken, DateDue, DateSubmit, FinePerDay, DaysDelay, TotalFine, Paid, PaidDate

IF DateSubmit IS NOT NULL THEN

IF currentDate > DateDue then

DaysDelay = CurrentDate – DateDue

TotalFine = DaysDelay * FinePerDay

UPDATE Borrowers

SET DaysDelay, TotalFine

End If

ELSE

DO NOTHING

END IF

Please, can you write a function inside SQL DATABE which will be executed automaticaly.

Thanks

Sarfaraj Ahmed SSC-Addicted Points: 417 More actions · Answer 7

Dear Kenneth

Thanks for your reply, I want to set FinePerDay .50 and TotalFine= FinePerDay * Delay

How Can I do it

Sarfaraj Ahmed SSC-Addicted Points: 417 More actions · Answer 8

Kenneth Wilhelmsson (10/18/2007)
Another idea... The penalty is only applied if the Delay is > 0.
So, why not make Delay a computed column?
Then any penalties in the Borrower table will be dynamic, and easily found, and easily calculated, since the penalty would be delay * ..
create table dbo.borrower
(MemberID varchar(10) not null, ItemID varchar(10) not null, DateTaken datetime not null,
Duedate datetime not null, SubmitDate datetime null,
Delay as (DATEDIFF(day, Duedate, ISNULL(SubmitDate, GETDATE()))) )
/Kenneth

Dear Kenneth

Thanks for your help, I want to set FinePerDay .50 and TotalFine= FinePerDay * Delay

could you please tell me the formula is?

Sarfaraj Ahmed SSC-Addicted Points: 417 More actions · Answer 9

Lowell (10/18/2007)
i don't think you can use a calculated column in this case, because the penalty amount resides in a different table(P as Penalty Table) than the items that determine the penalty, right?(Borrower TAble)
if the penalty is getdate - duedate where submit date is null, the penalty amount could be calculated in a view, or updated as a proc or trigger, but not inline in the borrower table.
Also would penalties accrue EVERY day, or only business days? if the business is closed saturday/sunday, wouldn't you need to skip adding tot eh penalties on those days? what about holidays?

Dear Lowell

Could please tell me how can I set FinePerDay .50 and TotalFine= FinePerDay * Delay

could you please tell me the formula is?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 10

Computed column formula would be

case when datediff(dd,datedue,isnull(datesubmit,0))>0 then datediff(dd,datedue,isnull(datesubmit,0)) else 0 end *fineperday

DateDue and fineperday are assumed not ever to be null.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Lowell SSC Guru Points: 323504 More actions · Answer 11

something like this might get you closer to where you want to go... the fines are calculated, as well as the days delayed.

create table Borrowers(

MemberID int,

ItemID int,

DateTaken datetime,

Duedate datetime,

SubmitDate datetime,

FinePerDay money default 0.50, --fifty cents per day

DaysDelay as (DATEDIFF(day, Duedate, ISNULL(SubmitDate, GETDATE()))) ,

--TotalFine As FinePerDay * DaysDelay, --can't have a calculated field use a calculated field.

TotalFine As FinePerDay * DATEDIFF(day, Duedate, ISNULL(SubmitDate, GETDATE())), --can't have a calculuated field use a calculated field.

Paid int, --1/0 for true/False?

PaidDate datetime)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sarfaraj Ahmed SSC-Addicted Points: 417 More actions · Answer 12

Lowell (10/18/2007)
something like this might get you closer to where you want to go... the fines are calculated, as well as the days delayed.
create table Borrowers(
MemberID int,
ItemID int,
DateTaken datetime,
Duedate datetime,
SubmitDate datetime,
FinePerDay money default 0.50, --fifty cents per day
DaysDelay as (DATEDIFF(day, Duedate, ISNULL(SubmitDate, GETDATE()))) ,
--TotalFine As FinePerDay * DaysDelay, --can't have a calculated field use a calculated field.
TotalFine As FinePerDay * DATEDIFF(day, Duedate, ISNULL(SubmitDate, GETDATE())), --can't have a calculuated field use a calculated field.
Paid int, --1/0 for true/False?
PaidDate datetime)

Many Thanks

But problem is if a member return book ontime or before due date then getting output like Daysdelay = -1,-2,-3 and total fine is minus as well. how can we get ouput 0 and 0

Waiting For Your Reply

Sarfaraj Ahmed SSC-Addicted Points: 417 More actions · Answer 13

Lowell (10/18/2007)
something like this might get you closer to where you want to go... the fines are calculated, as well as the days delayed.
create table Borrowers(
MemberID int,
ItemID int,
DateTaken datetime,
Duedate datetime,
SubmitDate datetime,
FinePerDay money default 0.50, --fifty cents per day
DaysDelay as (DATEDIFF(day, Duedate, ISNULL(SubmitDate, GETDATE()))) ,
--TotalFine As FinePerDay * DaysDelay, --can't have a calculated field use a calculated field.
TotalFine As FinePerDay * DATEDIFF(day, Duedate, ISNULL(SubmitDate, GETDATE())), --can't have a calculuated field use a calculated field.
Paid int, --1/0 for true/False?
PaidDate datetime)

Many Thanks

But problem is if a member return book ontime or before due date then getting output like Daysdelay = -1,-2,-3 and total fine is minus as well. how can we get ouput 0 and 0

Waiting For Your Reply

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 14

So... add a CASE to the calculated column and you're done!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)