SQLTeam.com | Weblogs | Forums

Status product?

sql2008

#1

Hi all,
I have a product table which include following fields : Id,Status,Price,CreatedTime,LastModifyTime

When i create new product : Status Field in product table is "new"
When i update information product such as price : Status Field in product table is "updated"

My problem:
Product can not be always new status , I want to write query after 5 days status product will be set normal. When status product is updated , it can not set "normal".Mean that we have 3 status : "new","updated","normal"
How can i do that ?
Thank you very much ! Have a good day !


#2

you can consider using a computed column for Status

Status as
case when datediff(day, CreatedTime, getdate()) <= 5 
     and  LastModifyTime is null then 'new'
     when LastModifyTime is null then 'normal'
     else 'updated'
     end

#3

Thank you khtan so much.
This is my sql statement in sql server 2008.

CREATE TABLE [dbo].[Product](
[Id] [int] NOT NULL,
[Status] nvarchar NULL,
[Name] nvarchar NULL,
[Price] [real] NULL,
[CreatedTime] [datetime] NULL,
[LastModifyTime] [datetime] NULL,
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Product] ADD CONSTRAINT [DF_Product_CreatedTime] DEFAULT (getdate()) FOR [CreatedTime]
GO

ALTER TABLE [dbo].[Product] ADD CONSTRAINT [DF_Product_LastModifyTime] DEFAULT (getdate()) FOR [LastModifyTime]
GO

Value in LastModify Time is always be same in createtime when i create a new record in my application.So it never be nulled.

Can you modify your guide ?(Im new in sql)

Thank you in advance ! :heart:


#4
CREATE TABLE [dbo].[Product](
[Id] [int] NOT NULL,
[Status] AS
  case when datediff(day, CreatedTime, getdate()) <= 5 
  and  LastModifyTime is null then 'new'
  when LastModifyTime is null then 'normal'
  else 'updated'
  end,
[Name] [nvarchar](50) NULL,
[Price] [real] NULL,
[CreatedTime] [datetime] NULL,
[LastModifyTime] [datetime] NULL,

#5

Hi Khtan,

Thank you so much for your reply !
But can you review your sql , i run it but error !


#6

what is the error message ?

can you post your query ?