[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1656
  • Last Modified:

creating a countdown column in sql server

Hi everybody:

I am developing an asp.net application that will log in a complaint record and keep track of the amount of time it takes for the complaint to be resolved. I need to have a column that keeps track of the number of days that the record as been active. The record should only be active 30 days. What I want to know is if when the record enters the database is there a way in SQL SERVER to have the column count down everyday the record is still open without depending on the asp.net script to do it. I am using sql server 2000.

 example:
on 1/1/2007
Case    name    daysleft    status
 1           tim          30           Open
next day
Case   name    daysleft     status
  1         tim         29            Open
4 days later
Case    name    daysleft    status
 1           tim          25          closed

i would like to know how I can get sql server to handle taking care of the countdown automatically instead of having to use asp.net to do it. I have a column that marks when the record is opened I have a column also that shows the date that the case should be closed by.  I want a column that counts the days remaining for the case to be active. I would like sql server to calculate the column itself without me having to do it with the asp.net.  I need the data to already be present when I display it.  I hope I made this clear enough to understand. Thanks for any assistance you can give
0
RegWood79
Asked:
RegWood79
  • 3
  • 3
  • 2
  • +3
2 Solutions
 
Kevin3NFCommented:
look into SQL Server computed columns:

From SQL 2000 BOL:

computed_column_expression

Is an expression defining the value of a computed column. A computed column is a virtual column not physically stored in the table. It is computed from an expression using other columns in the same table. For example, a computed column can have the definition: cost AS price * qty. The expression can be a noncomputed column name, constant, function, variable, and any combination of these connected by one or more operators. The expression cannot be a subquery.

Computed columns can be used in select lists, WHERE clauses, ORDER BY clauses, or any other locations in which regular expressions can be used, with the following exceptions:

A computed column cannot be used as a DEFAULT or FOREIGN KEY constraint definition or with a NOT NULL constraint definition. However, a computed column can be used as a key column in an index or as part of any PRIMARY KEY or UNIQUE constraint, if the computed column value is defined by a deterministic expression and the data type of the result is allowed in index columns.
For example, if the table has integer columns a and b, the computed column a+b may be indexed, but computed column a+DATEPART(dd, GETDATE()) cannot be indexed because the value may change in subsequent invocations.

A computed column cannot be the target of an INSERT or UPDATE statement.


Note  Each row in a table can have different values for columns involved in a computed column, therefore the computed column may not have the same value for each row.

The nullability of computed columns is determined automatically by SQL Server based on the expressions used. The result of most expressions is considered nullable even if only non-nullable columns are present because possible underflows or overflows will produce NULL results as well. Use the COLUMNPROPERTY function (AllowsNull property) to investigate the nullability of any computed column in a table. An expression expr that is nullable can be turned into a non-nullable one by specifying ISNULL(check_expression, constant) where the constant is a non-NULL value substituted for any NULL result.





I have never used one, as I believe the application layer should do the calculatnig, not the RDBMS.

Kevin3NF
0
 
TSmoothCommented:
Aside from using a computed column. You could also just use a View, stored procedure, or inline SQL Statement that displays the "Days Left" as a column in the result set. As long as your other queries  select from the View (generally a good practice) instead of directly from the table, this practice should work well and does not require you to explicity store the days left value. Look into using the DateDiff function. It would look something like this but I may have my parameters mixed up:

SELECT *, 30 - DateDiff(d, dateEnteredColumn, GetDate()) As [Days Left]
FROM tablename
0
 
dbbishopCommented:
I agree with Kevin3NF's last sentence. In fact, all you need is a single column in your table, DateOpened. You can determine the rest either through T-SQL or on the client.

SELECT DateToBeClosed = DateAdd(dd, 30, DateOpened)
SELECT DaysRemaining = DateDiff(dd, GETDATE(), DateAdd(dd, 30, DateOpened))

One good thing about this method is, if you decide to change the days a case can be open, you do not need to make changes to your DDL. If fact, if you do this as a stored procedure (which it should be), you could declare @DaysToRemainOpen and set it to a value at the start of the procedures (or better yet,  make it a row in a configuration table) and then change it there.
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
RegWood79Author Commented:
So you guys think that i should not have a column in my table that will store the day remaining value?  Because my thinking was that have this column to automatically update everyday without having to run a query on it.  
0
 
TSmoothCommented:
What is your reasoning for having the column update every day on it's own? You only need the value when something accesses it. That's why a computed column or a view will work nicely.
0
 
dbbishopCommented:
Anything you want to do can be done with just the DateOpened. If you want to identify rows that have less than 5 days remaining (assuming 30 day period), run the query:

SELECT [Case], [Name], Status, DateDiff(dd, GETDATE(), DateAdd(dd, 30, DateOpened)) AS DaysRemaining
FROM myTable
WHERE DateDiff(dd, GETDATE(), DateAdd(dd, 30, DateOpened)) < 5

If you do not want the complexity exposed to users, do as TSmooth suggests and create a view:

CREATE VIEW myView
AS
SELECT [Case], [Name], Status, DateOpened,
             DateToBeClosed = DateAdd(dd, 30, DateOpened),
             DaysRemaining = DateDiff(dd, GETDATE(), DateAdd(dd, 30, DateOpened))
FROM myTable

Then SELECT * FROM myView will return the columns Case, Name, Status, DateOpened, DateToBeClosed, DaysRemaining

0
 
ZberteocCommented:
Use this select statement to retrieve your recordset:

SELECT
      [Case],
      [Name],
--      datediff(dd,entry_date,getdate())            AS DaysOpen, -- this is for days from entered if needed
      30 - datediff(dd,entry_date,getdate())      AS DaysLeft
      CASE datediff(dd,entry_date,getdate())
            WHEN <=30 THEN 'Open'
            ELSE 'Close'
      END                                                            AS Status
FROM
      YourTable
0
 
dbbishopCommented:
Zberteoc: I think Status is an actual entered value and is not based on how long the case has been opened.
If you check his example, the last row shows a status of closed with 25 days remaining.
0
 
ZberteocCommented:
I thought that was just a bad example because contradicts what he said.
0
 
RejojohnyCommented:
there is no automatic countdown column .. if you are not happy with quering the value as all have mentioned above .. you will need to write a SQL job which will udpate the value at the start of each day .. create a job which calls a stored procedure at the start of each day, maybe "12:00 am" .. this SP will update the "countdown" column  ..

Rejo
0
 
RegWood79Author Commented:
Rejojohny,

I heard of about creating a job to accomplish this, can you tell me about how to go about doing that.

Once again I really appreciate everyones response to this matter.  
0
 
RegWood79Author Commented:
thanks guys for your help your suggestions help me greatly
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 3
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now