How can Automaticaly Update Column then Insert data in sql server

Question

2.60/5 (2 votes)

See more:

SQL-Server-2008

SQL

USE [ddrr]
GO

/****** Object:  Table [dbo].[ssse]    Script Date: 10/30/2013 08:45:43 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ssse](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[StartDte] [date] NULL,
	[Enddate] [date] NULL,
	[Statu] [nvarchar](50) NULL,
 CONSTRAINT [PK_ssse] 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

I am Inserting the Data and Again Inserting data through Query then Check the previous record Status Column Name Is Active or Inactive, then Status Column Name is Inactive then Insert data other wise not insert data

Automatically Update Status Column check the end Date and Current date ,
End date can less then Current date then Status Column Update Show in Inactive

Posted 29-Oct-13 17:30pm

krishna97

Updated 29-Oct-13 17:46pm

coded007

v2

Add a Solution

Comments

_Maxxx_ 29-Oct-13 23:38pm

I think it might be better to show an example of what you are trying to achieve - e.g. show the rows in the table at each step of what you want to achieve - as it is hard to follow your question.
Alternatively, writing the steps as simple bullet points might help?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

coded007 · Accepted Answer · 2013-10-29T17:55:00

Solution 1

SQL

DECLARE @previousstatus nvarchar(50)
DECLARE @presentstatus nvarchar(50)
DECLARE @Startdate nvarchar(50)
DECLARE @Enddate nvarchar(50)

SET @Startdate = getdate() - 7
SET @Enddate = getdate() - 2

CREATE TABLE #Temp(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [StartDte] [date] NULL,
    [Enddate] [date] NULL,
    [Statu] [nvarchar](50) NULL,
 CONSTRAINT [PK_ssse] 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]

Select @previousstatus = statu FROM #Temp where ID = (Select max(id) From #Temp)

IF @Enddate < cast(getdate() as date) or ISNULL(@previousstatus,'') = ''
SET @presentstatus = 'Inactive'
ELSE
SET @presentstatus = 'Active'



IF @previousstatus = 'Inactive' or ISNULL(@previousstatus,'') = ''
BEGIN
    Insert INTO #Temp(StartDte,enddate,statu) VALUES(@Startdate,@Enddate,@presentstatus)
END

SELECT * FROM #Temp

Drop TABLE #Temp

Posted 29-Oct-13 17:55pm

coded007

Updated 30-Oct-13 20:30pm

v4

Comments

krishna97 30-Oct-13 0:57am

if '2013-08-08'< CAST(getdate() as enddate)
then
error given "Incorrect syntax near the keyword 'then'."

krishna97 30-Oct-13 1:07am

DECLARE @previousstatus nvarchar(50)
DECLARE @presentstatus nvarchar(50)

Select @previousstatus = statu FROM ssse where ID = (Select max(id) From ssse)

IF Enddateparameter < cast(getdate() as date) then @presentstatus = 'Inactive'

IF @previousstatus = 'Inactive'
BEGIN
Insert INTO ssse(startdate,enddate,status) VALUES(startdateparameter,enddateparameter,@presentstatus)
END

error given
"Incorrect syntax near the keyword 'then'."

coded007 30-Oct-13 2:42am

Sorry it's mistaken I have updated the query in solution please take a look

krishna97 30-Oct-13 2:49am

this code also given error
Incorrect syntax near '@presentstatus'

krishna97 30-Oct-13 2:55am

DECLARE @previousstatus nvarchar(50)
DECLARE @presentstatus nvarchar(50)

Select @previousstatus = statu FROM ssse where ID = (Select max(id) From ssse)

IF '2014-08-08' < cast(getdate() as date)
--@presentstatus = 'Inactive'

IF @previousstatus = 'Inactive'
BEGIN
Insert INTO ssse(StartDte,Enddate,Statu) VALUES('2014-09-09','2014-09-08','Active')
END

I am used this One But not Update the Previous Record Status Column 'Inactive'

krishna97 30-Oct-13 3:10am

DECLARE @previousstatus nvarchar(50)
DECLARE @presentstatus nvarchar(50)

Select @previousstatus = statu FROM ssse where ID = (Select max(id) From ssse)

IF '2013-10-29' < cast(getdate() as date)
set @presentstatus = 'Inactive'

IF @previousstatus = 'Inactive'
BEGIN
Insert INTO ssse(StartDte,Enddate,Statu) VALUES('2014-09-09','2014-09-08','Active')
END

Used this One also but not Update Status Column

coded007 30-Oct-13 7:03am

Please check solution code section i have updated the complete query with temp table

krishna97 30-Oct-13 12:08pm

This Query not Proper Work
Status Column Not Update automatically End Date is Less then Current Date Only one data can insert then end date is less then current not update...??

coded007 31-Oct-13 1:53am

krishna i have provide a demo query you need to change it as you want

krishna97 31-Oct-13 2:00am

query given for my question sir pleas

coded007 31-Oct-13 2:30am

Ok again i have updated code section in solution please verify it

krishna97 31-Oct-13 2:33am

ok i am try it