Click here to Skip to main content
15,566,394 members
Please Sign up or sign in to vote.
2.60/5 (2 votes)
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
Updated 29-Oct-13 18:46pm
v2
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

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
 
Share this answer
 
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'

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900