Help optimising a stored proc

Hi I have the following procedure that accepts two CSV lists of values,
the first list contains primary key numbers, and the second values to
update.
When the list gets over about 200 items, I am getting intermittent
Timout errors.
Currently just over 500,000 records in the table.
Is there a way to optimise the performace of the update? Or is theer an
easier way if the input can be provided in a single list
e.g 2245=1,2257=2,3367=2 instead of
2245,2257,3367 and 1,2,2
Thanks!
CREATE Procedure dbo.UpdateResults
@.RegistrationIDs Varchar(8000),
@.Results Varchar(4000)
AS
UPDATE tblRegistrations
SET Result
= (SELECT A.Value FROM dbo.Split(@.Results,',') A
JOIN dbo.Split(@.RegistrationIDs,',') B ON A.id = B.id
WHERE RegistrationID=B.Value )
WHERE EXISTS (SELECT *
FROM dbo.Split(@.Results,',') A
JOIN dbo.Split(@.RegistrationIDs,',') B ON A.id = B.id
WHERE RegistrationID=B.Value)
CREATE FUNCTION dbo.Split
(
@.List varchar(8000),
@.SplitOn nvarchar(5)
)
RETURNS @.RtnValue table
(
Id int identity(1,1),
Value nvarchar(150)
)
AS
BEGIN
While (Charindex(@.SplitOn,@.List)>0)
Begin
Insert Into @.RtnValue (value)
Select
Value =
ltrim(rtrim(Substring(@.List,1,Charindex(
@.SplitOn,@.List)-1)))
Set @.List =
Substring(@.List,Charindex(@.SplitOn,@.List
)+len(@.SplitOn),len(@.List))
End
Insert Into @.RtnValue (Value)
Select Value = ltrim(rtrim(@.List))
Return
ENDI would try using temporary tables instead of table variables. I have seen
some strange stuff happen when trying to join two table variables together,
or joining a table-valued function together with a real base table.
Move the logic of the dbo.split funtion inside the stored procedure, take
the input csv strings and write them into a temporary table, then process
from that temp table.
"hals_left" wrote:

> Hi I have the following procedure that accepts two CSV lists of values,
> the first list contains primary key numbers, and the second values to
> update.
> When the list gets over about 200 items, I am getting intermittent
> Timout errors.
> Currently just over 500,000 records in the table.
> Is there a way to optimise the performace of the update? Or is theer an
> easier way if the input can be provided in a single list
> e.g 2245=1,2257=2,3367=2 instead of
> 2245,2257,3367 and 1,2,2
> Thanks!
>
> CREATE Procedure dbo.UpdateResults
> @.RegistrationIDs Varchar(8000),
> @.Results Varchar(4000)
> AS
> UPDATE tblRegistrations
> SET Result
> = (SELECT A.Value FROM dbo.Split(@.Results,',') A
> JOIN dbo.Split(@.RegistrationIDs,',') B ON A.id = B.id
> WHERE RegistrationID=B.Value )
> WHERE EXISTS (SELECT *
> FROM dbo.Split(@.Results,',') A
> JOIN dbo.Split(@.RegistrationIDs,',') B ON A.id = B.id
> WHERE RegistrationID=B.Value)
>
> CREATE FUNCTION dbo.Split
> (
> @.List varchar(8000),
> @.SplitOn nvarchar(5)
> )
> RETURNS @.RtnValue table
> (
> Id int identity(1,1),
> Value nvarchar(150)
> )
> AS
> BEGIN
> While (Charindex(@.SplitOn,@.List)>0)
> Begin
> Insert Into @.RtnValue (value)
> Select
> Value =
> ltrim(rtrim(Substring(@.List,1,Charindex(
@.SplitOn,@.List)-1)))
> Set @.List =
> Substring(@.List,Charindex(@.SplitOn,@.List
)+len(@.SplitOn),len(@.List))
> End
> Insert Into @.RtnValue (Value)
> Select Value = ltrim(rtrim(@.List))
> Return
> END
>|||Hi There,
What Mark suggested is right but you may like to try this .
UPDATE T1
SET Result = A.Value
>From FROM dbo.Split(@.Results,',') A
JOIN dbo.Split(@.RegistrationIDs,',') B ON A.id = B.id
Join tblRegistrations T1 On T1.RegistrationID=B.Value
and remove the split function If possible .
With Warm regards
Jatinder Singh
Mark Williams wrote:
> I would try using temporary tables instead of table variables. I have seen
> some strange stuff happen when trying to join two table variables together
,
> or joining a table-valued function together with a real base table.
> Move the logic of the dbo.split funtion inside the stored procedure, take
> the input csv strings and write them into a temporary table, then process
> from that temp table.
> --
> "hals_left" wrote:
>|||Thanks Jatinder, that works nice on a small test and is much simpler
SQL.
I'l test in on the real database and see how it compares to the
original
I dont really see how can I remove the split function, unless I pass
the data 1 record at a time in a loop ...

Help needed with Primary Key and Identity

I have some code in my ASP.NET page which uses a SQL 2000 Database that was created before creating the ASP Page. The problem I'm having is using an insert statement such as the following example from the DATAGRID example on the Matrix Product. I want the option to create new rows but my Primary Key doesn't allow Nulls and when I hard code a number in the first field of my table for my ID...it's not automatically generated. I've looked through this forum but I'm having some problems understanding what others have done with Identity or GUID's...etc...:

Sub AddNew_Click(Sender As Object, E As EventArgs)

' add a new row to the end of the data, and set editing mode 'on'

CheckIsEditing("")

If Not isEditing = True Then

' set the flag so we know to do an insert at Update time
AddingNew = True

' add new row to the end of the dataset after binding

' first get the data
Dim myConnection As New SqlConnection(ConnectionString)
Dim myCommand As New SqlDataAdapter(SelectCommand, myConnection)

Dim ds As New DataSet()
myCommand.Fill(ds)

' add a new blank row to the end of the data
Dim rowValues As Object() = {"", "", ""}
ds.Tables(0).Rows.Add(rowValues)

' figure out the EditItemIndex, last record on last page
Dim recordCount As Integer = ds.Tables(0).Rows.Count

If recordCount > 1 Then

recordCount -= 1
DataGrid1.CurrentPageIndex = recordCount \ DataGrid1.PageSize
DataGrid1.EditItemIndex = recordCount Mod DataGrid1.PageSize

End If

' databind
DataGrid1.DataSource = ds
DataGrid1.DataBind()

End If

End Subds.Tables(0).Columns("YourPrimaryKey").IncrementSeed = 1
ds.Tables(0).Columns("YourPrimaryKey")... other properties you need to set to make it an identity.|||Right now I'm using an "ID" field as the primary key and it is setup in SQL as an identity but when I try to add a row...it says..

System.Data.SqlClient.SqlException: Cannot insert explicit value for identity column in table 'CustomerInfo' when IDENTITY_INSERT is set to OFF. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at ASP.OrderEdit_aspx.DataGrid_Update(Object Sender, DataGridCommandEventArgs E

Would I need to remove the identity setting in SQL and create the identity through my code or is there a way to specify that my "ID" field is a primary key and it needs to be incremented by 1 whenever a new row is added?

Thanks for the help!!

Help needed on select and insert query

a bit of a begginer's request here.
I have a table called t_user in which username is the primary key and one of
the fields is a decimal called bsl.
I'm trying to write a stored procedure that selects the right record based
on the username (input from a cookie) and then inserts the bsl value based o
n
the users input into a textbox on the asp.net page
the stored proc code is
CREATE PROCEDURE addbsl
@.bsl decimal,
@.username varchar(20)
AS
SELECT username FROM t_user
WHERE username = @.username
insert into t_user (bsl) values (@.bsl)
GO
and when i run it in the query analyser it tells me:
Server: Msg 515, Level 16, State 2, Procedure addbsl, Line 8
Cannot insert the value NULL into column 'username', table
'FYProj.dbo.t_user'; column does not allow nulls. INSERT fails.
The statement has been terminated.
(1 row(s) affected)
Stored Procedure: FYProj.dbo.addbsl
Return Code = -6
I understand that it is trying to insert a username as well but cannot
because you cannot enter a null value but I don't want it to input a sername
i just want it to insert data into the record with the specified username
Is there a way to change the sproc to get it to work or am i just taking the
wrong approach?You will have to use an UPDATE statement to update an existing row. INSERT
always insert a new row.
Hello neil_pat,

> a bit of a begginer's request here.
<snip>
Lasse Vgsther Karlsen
http://www.vkarlsen.no/
mailto:lasse@.vkarlsen.no
PGP KeyID: 0x0270466B

Help needed in Table partition

Hi,
I'm doing horizontal partition with partition key as an
integer datatype.It's a part of primary key also.When i
search for a particular value, it's fetching correct
results ,but if we examine execution plan all the
partitions are searched for a particular search.
Please help me to fetch the correct result in execution
plan
Thanks in advance,
Sunish
is your primary key clustered? Are you saying the the execution plan does a
table scan?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Sunish" <sunish_007@.hotmail> wrote in message
news:732801c4761d$d3435170$a501280a@.phx.gbl...
> Hi,
> I'm doing horizontal partition with partition key as an
> integer datatype.It's a part of primary key also.When i
> search for a particular value, it's fetching correct
> results ,but if we examine execution plan all the
> partitions are searched for a particular search.
> Please help me to fetch the correct result in execution
> plan
> Thanks in advance,
> Sunish
|||hi,
yea, the primary key is a clustered one.When i checked
the execution p[lan it shows Clustered Index Scan for
each partition ......also instead of compute saclar it's
showing Concatenation ,Cost=2%.
Thanks in advance
Sunish

>--Original Message--
>is your primary key clustered? Are you saying the the
execution plan does a
>table scan?
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"Sunish" <sunish_007@.hotmail> wrote in message
>news:732801c4761d$d3435170$a501280a@.phx.gbl...
>
>.
>
|||hi,
yea, the primary key is a clustered one.When i check
the execution plan it shows Clustered Index Scan for
each partition ......also instead of compute saclar it's
showing Concatenation ,Cost=2%.
Thanks in advance
Sunish

>--Original Message--
>is your primary key clustered? Are you saying the the
execution plan does a
>table scan?
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"Sunish" <sunish_007@.hotmail> wrote in message
>news:732801c4761d$d3435170$a501280a@.phx.gbl...
>
>.
>
|||hi,
yea, the primary key is a clustered one.When i check
the execution plan it shows Clustered Index Scan for
each partition ......also instead of compute saclar it's
showing Concatenation ,Cost=2%.
Thanks in advance
Sunish

>--Original Message--
>is your primary key clustered? Are you saying the the
execution plan does a
>table scan?
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"Sunish" <sunish_007@.hotmail> wrote in message
>news:732801c4761d$d3435170$a501280a@.phx.gbl...
>
>.
>
|||hi,
yea, the primary key is a clustered one.When i check
the execution plan it shows Clustered Index Scan for
each partition ......also instead of compute saclar it's
showing Concatenation ,Cost=2%.
Thanks in advance
Sunish

>--Original Message--
>is your primary key clustered? Are you saying the the
execution plan does a
>table scan?
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"Sunish" <sunish_007@.hotmail> wrote in message
>news:732801c4761d$d3435170$a501280a@.phx.gbl...
>
>.
>
|||I think this is normal.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
<anonymous@.discussions.microsoft.com> wrote in message
news:873601c47855$f7adc3d0$a601280a@.phx.gbl...[vbcol=seagreen]
> hi,
> yea, the primary key is a clustered one.When i checked
> the execution p[lan it shows Clustered Index Scan for
> each partition ......also instead of compute saclar it's
> showing Concatenation ,Cost=2%.
> Thanks in advance
> Sunish
>
> execution plan does a
|||hi,
Since it has to search for the corresponding partition
only rather than searching the entire partition views ,i
think i am wrong some where .
also in the execution plan ,it has to show compute scalar
instead of concatenation, as i referred to articles which
i collected from the net.
please inform if there is any other way i need to perform
this .
my query is like this only
select * from detailstable where programcode =222
[vbcol=seagreen]
>--Original Message--
>I think this is normal.
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:873601c47855$f7adc3d0$a601280a@.phx.gbl...
it's[vbcol=seagreen]
as an[vbcol=seagreen]
also.When i[vbcol=seagreen]
execution
>
>.
>

Help Needed in Approach for Error Output In SSIS

Hi i have a issue i am loading data from a flat file into a relational Database and i am loading the data without dropping the Primary and Foreign Key constraints and i am sending the error rows into a error table for each table. This is becoming large over head is there a way to Load the entire error Row as a single Column in one Error Table.

This is occuring coz the input is being parsed and its dividing into columns,

One approach would be to used Derived Column Transformation and Substring all columns but it makes to writing long substring statement as some tables have 80 columns ,

is there a better way to handle these errors so that they can be looked at and Changes can be made to the Data ,

Also one more question is is there a way to Load in case of Duplicates load only the Recent Row By Date Column.

and send the old row according to Date into Error output.

Please suggest me what approach should i go for

Not really

You are best to create individual error tables. It means its easier for the user to see what the data is because it will be correctly split up.

|||

For your first question - why not use a error table with "row number" and "table name" columns and then rather than dumping the whole row into the error table -- log the row number and table name so that you can go back to the exact row and find the data you are having problem with. As the error table now has a standard structure (2 columns), you can use the same error table for logging errors for all the tables you are loading.

For you second question - You are trying to clean up the data (De-Duplication) so its better to stage the tables you are having duplicates in and then load it into the database after deleting the duplicate rows. This is hard to be done in the data-flow task because the order of the data coming in might not be based on date.

|||Another approach to de-duping rows is to use the Rank function - You can find an example using T-SQL here: http://rafael-salas.blogspot.com/2007/04/remove-duplicates-using-t-sql-rank.html and one with a custom component here: http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Rank-Transform.aspx

On the single column to capture an error, I have seen this implemented by using a text or xml column and generically creating the string to insert based on the row data. However, as Simon noted, this isn't typically very readable, and may require a seperate application to support reviewing the errors.

|||

is there way to log the record number in the Flatfile where the error is occuring and is there a way to log the Error Code and Error Description . Can u please guide me regarding this.

|||I need to capture the Database, Table, Input File Name, Input File Record Number; Key Data (does not need to be entire record), Error Code, Error Desc, Date and Time, SSIS Package/Program|||Basically you need to generate a rowNumber column in the dataflow. There are some samples:

http://support.microsoft.com/kb/908460

http://www.sqlis.com/93.aspx|||Thanks for the information it was very helpful i was thinking to capture the sorce filename and Destination table name too into the error table is there a way to capture this information.|||If you store those values in variables, you can use a derived column transform to bring them into the error flow.|||I am aware of this approach but is there way to store these values into variables , my connection managers are Dynamic i.e from a configurations table in sql server. is there way to capture the src and output names atleast in variable s to write it to error table|||

Dev2624 wrote:

Thanks for the information it was very helpful i was thinking to capture the sorce filename and Destination table name too into the error table is there a way to capture this information.

Why are you creating new threads on this topic? http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2195648&SiteID=1|||Phil's suggestion in the other thread is the same thing I was going to recommend.

Help Needed in Approach for Error Output In SSIS

Hi i have a issue i am loading data from a flat file into a relational Database and i am loading the data without dropping the Primary and Foreign Key constraints and i am sending the error rows into a error table for each table. This is becoming large over head is there a way to Load the entire error Row as a single Column in one Error Table.

This is occuring coz the input is being parsed and its dividing into columns,

One approach would be to used Derived Column Transformation and Substring all columns but it makes to writing long substring statement as some tables have 80 columns ,

is there a better way to handle these errors so that they can be looked at and Changes can be made to the Data ,

Also one more question is is there a way to Load in case of Duplicates load only the Recent Row By Date Column.

and send the old row according to Date into Error output.

Please suggest me what approach should i go for

Not really

You are best to create individual error tables. It means its easier for the user to see what the data is because it will be correctly split up.

|||

For your first question - why not use a error table with "row number" and "table name" columns and then rather than dumping the whole row into the error table -- log the row number and table name so that you can go back to the exact row and find the data you are having problem with. As the error table now has a standard structure (2 columns), you can use the same error table for logging errors for all the tables you are loading.

For you second question - You are trying to clean up the data (De-Duplication) so its better to stage the tables you are having duplicates in and then load it into the database after deleting the duplicate rows. This is hard to be done in the data-flow task because the order of the data coming in might not be based on date.

|||Another approach to de-duping rows is to use the Rank function - You can find an example using T-SQL here: http://rafael-salas.blogspot.com/2007/04/remove-duplicates-using-t-sql-rank.html and one with a custom component here: http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Rank-Transform.aspx

On the single column to capture an error, I have seen this implemented by using a text or xml column and generically creating the string to insert based on the row data. However, as Simon noted, this isn't typically very readable, and may require a seperate application to support reviewing the errors.

|||

is there way to log the record number in the Flatfile where the error is occuring and is there a way to log the Error Code and Error Description . Can u please guide me regarding this.

|||I need to capture the Database, Table, Input File Name, Input File Record Number; Key Data (does not need to be entire record), Error Code, Error Desc, Date and Time, SSIS Package/Program|||Basically you need to generate a rowNumber column in the dataflow. There are some samples:

http://support.microsoft.com/kb/908460

http://www.sqlis.com/93.aspx|||Thanks for the information it was very helpful i was thinking to capture the sorce filename and Destination table name too into the error table is there a way to capture this information.|||If you store those values in variables, you can use a derived column transform to bring them into the error flow.|||I am aware of this approach but is there way to store these values into variables , my connection managers are Dynamic i.e from a configurations table in sql server. is there way to capture the src and output names atleast in variable s to write it to error table|||

Dev2624 wrote:

Thanks for the information it was very helpful i was thinking to capture the sorce filename and Destination table name too into the error table is there a way to capture this information.

Why are you creating new threads on this topic? http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2195648&SiteID=1|||Phil's suggestion in the other thread is the same thing I was going to recommend.

HELP ME...........

It my ddl table:
CREATE TABLE [dbo].[TTEMP_BC] (
[RecID] [int] IDENTITY (1, 1) Primary Key,
[FDATE] [smalldatetime] NULL ,
[FTIME] [smalldatetime] NULL ,
[NOID] [nvarchar] (6) COLLATE Latin1_General_CI_AS NULL ,
[FSTATUS] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL
)
and my data like it:
noid fdate ftime fstatus
---
1 1/1/2005 1/1/2005 6:30:00 1
1 1/1/2005 1/1/2005 6:30:00 1
1 1/1/2005 1/1/2005 6:31:00 1
1 1/1/2005 1/1/2005 16:30:00 1
1 1/1/2005 1/1/2005 16:30:00 0
1 1/1/2005 1/1/2005 16:33:00 0
1 1/1/2005 1/1/2005 16:33:00 0
2 1/1/2005 1/1/2005 6:27:00 1
2 1/1/2005 1/1/2005 6:28:00 1
2 1/1/2005 1/1/2005 6:32:00 1
2 1/1/2005 1/1/2005 16:30:00 0
2 1/1/2005 1/1/2005 16:31:00 0
2 1/1/2005 1/1/2005 16:45:00 0
2 1/1/2005 1/1/2005 16:45:00 0
I want to delete. if fstatus =1 so fisrt record (min(Ftime)) of group
noid,fdate is not deleted. but if fstatus =0 so last record (max(Ftime)) of
group noid,fdate is not deleted. So its data will be;
noid fdate ftime fstatus
---
1 1/1/2005 1/1/2005 6:30:00 1
1 1/1/2005 1/1/2005 16:33:00 0
2 1/1/2005 1/1/2005 6:27:00 1
2 1/1/2005 1/1/2005 16:45:00 0
Can u help me? How sintax sql? Can it be solved with one statement?Hi
I think Steve Kass has already provided solution for you .
Would you mind to post a sample data when you ask for help or at least to
fix your current DDL
This is one of the many options that others provided
CREATE TABLE [dbo].[TTEMP_BC] (
[RecID] [int] IDENTITY (1, 1) Primary Key,
[NOID] [INT],
[FDATE] [smalldatetime] NULL ,
[FTIME] [smalldatetime] NULL ,
[FSTATUS] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL
)
INSERT INTO [dbo].[TTEMP_BC] VALUES (1,' 1/1/2005','1/1/2005 06:30:00',1)
INSERT INTO [dbo].[TTEMP_BC] VALUES (1,' 1/1/2005','1/1/2005 06:30:00',1)
INSERT INTO [dbo].[TTEMP_BC] VALUES (1,' 1/1/2005','1/1/2005 06:31:00',1)
INSERT INTO [dbo].[TTEMP_BC] VALUES (1,' 1/1/2005','1/1/2005 16:30:00',1)
INSERT INTO [dbo].[TTEMP_BC] VALUES (1,' 1/1/2005','1/1/2005 16:30:00',0)
INSERT INTO [dbo].[TTEMP_BC] VALUES (1,' 1/1/2005','1/1/2005 16:33:00',0)
INSERT INTO [dbo].[TTEMP_BC] VALUES (1,' 1/1/2005','1/1/2005 16:33:00',0)
INSERT INTO [dbo].[TTEMP_BC] VALUES (2,' 1/1/2005','1/1/2005 06:27:00',1)
INSERT INTO [dbo].[TTEMP_BC] VALUES (2,' 1/1/2005','1/1/2005 06:28:00',1)
INSERT INTO [dbo].[TTEMP_BC] VALUES (2,' 1/1/2005','1/1/2005 06:32:00',1)
INSERT INTO [dbo].[TTEMP_BC] VALUES (2,' 1/1/2005','1/1/2005 16:30:00',0)
INSERT INTO [dbo].[TTEMP_BC] VALUES (2,' 1/1/2005','1/1/2005 16:31:00',0)
INSERT INTO [dbo].[TTEMP_BC] VALUES (2,' 1/1/2005','1/1/2005 16:45:00',0)
INSERT INTO [dbo].[TTEMP_BC] VALUES (2,' 1/1/2005','1/1/2005 16:45:00',0)
SELECT * FROM
(
SELECT noid,MIN(ftime)as ftime
FROM TTEMP_BC WHERE fstatus=1
GROUP BY noid
UNION ALL
SELECT noid,MIN(ftime)as ftime
FROM TTEMP_BC WHERE fstatus=0
GROUP BY noid
) AS Der
ORDER BY noid
"Bpk. Adi Wira Kusuma" <adi_wira_kusuma@.yahoo.com.sg> wrote in message
news:uK2KgVQkFHA.3336@.tk2msftngp13.phx.gbl...
> It my ddl table:
> CREATE TABLE [dbo].[TTEMP_BC] (
> [RecID] [int] IDENTITY (1, 1) Primary Key,
> [FDATE] [smalldatetime] NULL ,
> [FTIME] [smalldatetime] NULL ,
> [NOID] [nvarchar] (6) COLLATE Latin1_General_CI_AS NULL ,
> [FSTATUS] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL
> )
> and my data like it:
> noid fdate ftime fstatus
> ---
> 1 1/1/2005 1/1/2005 6:30:00 1
> 1 1/1/2005 1/1/2005 6:30:00 1
> 1 1/1/2005 1/1/2005 6:31:00 1
> 1 1/1/2005 1/1/2005 16:30:00 1
> 1 1/1/2005 1/1/2005 16:30:00 0
> 1 1/1/2005 1/1/2005 16:33:00 0
> 1 1/1/2005 1/1/2005 16:33:00 0
> 2 1/1/2005 1/1/2005 6:27:00 1
> 2 1/1/2005 1/1/2005 6:28:00 1
> 2 1/1/2005 1/1/2005 6:32:00 1
> 2 1/1/2005 1/1/2005 16:30:00 0
> 2 1/1/2005 1/1/2005 16:31:00 0
> 2 1/1/2005 1/1/2005 16:45:00 0
> 2 1/1/2005 1/1/2005 16:45:00 0
> I want to delete. if fstatus =1 so fisrt record (min(Ftime)) of group
> noid,fdate is not deleted. but if fstatus =0 so last record (max(Ftime))
of
> group noid,fdate is not deleted. So its data will be;
> noid fdate ftime fstatus
> ---
> 1 1/1/2005 1/1/2005 6:30:00 1
> 1 1/1/2005 1/1/2005 16:33:00 0
> 2 1/1/2005 1/1/2005 6:27:00 1
> 2 1/1/2005 1/1/2005 16:45:00 0
> Can u help me? How sintax sql? Can it be solved with one statement?
>
>
>|||Try,
delete t1
where
recid !=
case
when fstatus = 1 then (select min(a.recid) from t1 as a where a.fstatus = 1
and a.noid = t1.noid and a.fdate = t1.fdate and a.ftime = (select
min(b.ftime) from t1 as b where b.fstatus = 1 and b.noid = t1.noid and
b.fdate = t1.fdate))
when fstatus = 0 then (select max(a.recid) from t1 as a where a.fstatus = 0
and a.noid = t1.noid and a.fdate = t1.fdate and a.ftime = (select
max(b.ftime) from t1 as b where b.fstatus = 0 and b.noid = t1.noid and
b.fdate = t1.fdate))
end
go
AMB
"Bpk. Adi Wira Kusuma" wrote:

> It my ddl table:
> CREATE TABLE [dbo].[TTEMP_BC] (
> [RecID] [int] IDENTITY (1, 1) Primary Key,
> [FDATE] [smalldatetime] NULL ,
> [FTIME] [smalldatetime] NULL ,
> [NOID] [nvarchar] (6) COLLATE Latin1_General_CI_AS NULL ,
> [FSTATUS] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL
> )
> and my data like it:
> noid fdate ftime fstatus
> ---
> 1 1/1/2005 1/1/2005 6:30:00 1
> 1 1/1/2005 1/1/2005 6:30:00 1
> 1 1/1/2005 1/1/2005 6:31:00 1
> 1 1/1/2005 1/1/2005 16:30:00 1
> 1 1/1/2005 1/1/2005 16:30:00 0
> 1 1/1/2005 1/1/2005 16:33:00 0
> 1 1/1/2005 1/1/2005 16:33:00 0
> 2 1/1/2005 1/1/2005 6:27:00 1
> 2 1/1/2005 1/1/2005 6:28:00 1
> 2 1/1/2005 1/1/2005 6:32:00 1
> 2 1/1/2005 1/1/2005 16:30:00 0
> 2 1/1/2005 1/1/2005 16:31:00 0
> 2 1/1/2005 1/1/2005 16:45:00 0
> 2 1/1/2005 1/1/2005 16:45:00 0
> I want to delete. if fstatus =1 so fisrt record (min(Ftime)) of group
> noid,fdate is not deleted. but if fstatus =0 so last record (max(Ftime)) o
f
> group noid,fdate is not deleted. So its data will be;
> noid fdate ftime fstatus
> ---
> 1 1/1/2005 1/1/2005 6:30:00 1
> 1 1/1/2005 1/1/2005 16:33:00 0
> 2 1/1/2005 1/1/2005 6:27:00 1
> 2 1/1/2005 1/1/2005 16:45:00 0
> Can u help me? How sintax sql? Can it be solved with one statement?
>
>
>