Help me?

I've data like this
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?Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.
Let's try to get rid of redundancy and get some real DDL instead of
pesonal pseudo-code. You will also want to start using 'yyyy-mm-dd'
date formats.
CREATE TABLE Foobar
(grp_id INTEGER NOT NULL
CHECK ((grp_id > 0),
foo_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
foo_status INTEGER DEFAULT 0 NOT NULL
CHECK(foo_status IN (0,1)),
PRIMARY KEY (grp_id, foo_date));
You can get what you want with a VIEW.
CREATE VIEW TrimmedFoobar (F1.grp_id, F1.foo_time, F1.foo_status)
AS
SELECT F1.grp_id, F1.foo_time, F1.foo_status
FROM Foobar AS F1
WHERE (F1.status = 1
AND foo_time
= (SELECT MIN(foo_time)
FROM Foobar AS F2
WHERE F1.grp_id = F2.grp_id
AND F2.status = 1))
OR (F1.status = 0
AND foo_time
= (SELECT MAX(foo_time)
FROM Foobar AS F3
WHERE F1.grp_id = F3.grp_id
AND F3.status = 0));
This is untested, but try this:
DELETE FROM Foobar
WHERE (Foobar.status = 1
AND Foobar.foo_time
<> (SELECT MIN(F2.foo_time)
FROM Foobar AS F2
WHERE Foobar.grp_id = F2.grp_id
AND F2.status = 1))
OR (Foobar.status = 0
AND Foobar.foo_time
<> (SELECT MAX(foo_time)
FROM Foobar AS F3
WHERE Foobar.grp_id = F3.grp_id
AND F3.status = 0));|||You can't remove one, but not another, identical row with
one DELETE, because there is no way to write a WHERE
clause that one row satisfies and an identical row does not
satisfy. You can remove all the unwanted rows, leaving what
you want with possible duplicates, this way:
delete from t
from t as t1
where ftime = (
select
case when t1.fstatus = 0 then max(t2.ftime) else min(t2.ftime) end
from t as t2
where t2.noid = t1.noid
and t2.fdate = t1.fdate
)
go
I assume that the min(ftime) for the group already has fstatus = 1,
and the max(ftime) already has fstatus = 0. If that is not true, you
must change the query.
You should try not to get into this situation in the first place, if
possible.
Steve Kass
Drew University
Bpk. Adi Wira Kusuma wrote:

>I've data like this
>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?
>
>|||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?|||This is not a table at all. You have no key, all your columns are
NULL-able, and fdate is redundant. I also missed the fact that you
have redundant dupicate rows. Start over and use something like the
DDL I gave you. You shoudl never have something like this in a schema.