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?
>
>
>

help me out for a simple query

I am sending a small scenario
i have a table
CREATE TABLE STUDENT_ANSWERS(EXAMID INT, MARKS INT);
INSERT INTO VALUES (1, 20)
INSERT INTO VALUES (1, 10)
INSERT INTO VALUES (1, 30)
INSERT INTO VALUES (1, 50)
INSERT INTO VALUES (2, 50)
INSERT INTO VALUES (2, 70)
INSERT INTO VALUES (2, 20)
INSERT INTO VALUES (2, 40)
INSERT INTO VALUES (2, 90)
i need the output like
examid marksstring
----
1 20,10,30,50
2 50,70,20,40,90
----
thx for ur help
*** Sent via Developersdex http://www.examnotes.net ***Here's one way to achieve this using a function:
CREATE FUNCTION dbo.fn_ConcatMarks(@.id INT) RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @.marks VARCHAR(8000)
SET @.marks = ''
SELECT @.marks = @.marks + CAST(MARKS AS VARCHAR(10)) + ',' FROM
STUDENT_ANSWERS
WHERE EXAMID = @.id
RETURN LEFT(@.marks, LEN(@.marks) - 1)
END
GO
SELECT EXAMID, dbo.fn_ConcatMarks(EXAMID) AS marks
FROM STUDENT_ANSWERS
GROUP BY EXAMID
EXAMID marks
-- --
1 20,10,30,50
2 50,70,20,40,90
You can find several other solutions in previous threads if you look for the
keywords PIVOT, crosstab.
BG, SQL Server MVP
www.SolidQualityLearning.com
"kamal hussain" <skkamalh@.rediffmail.com> wrote in message
news:OzYlXrmRFHA.1208@.TK2MSFTNGP10.phx.gbl...
>
> I am sending a small scenario
> i have a table
> CREATE TABLE STUDENT_ANSWERS(EXAMID INT, MARKS INT);
> INSERT INTO VALUES (1, 20)
> INSERT INTO VALUES (1, 10)
> INSERT INTO VALUES (1, 30)
> INSERT INTO VALUES (1, 50)
> INSERT INTO VALUES (2, 50)
> INSERT INTO VALUES (2, 70)
> INSERT INTO VALUES (2, 20)
> INSERT INTO VALUES (2, 40)
> INSERT INTO VALUES (2, 90)
>
>
> i need the output like
> examid marksstring
> ----
> 1 20,10,30,50
> 2 50,70,20,40,90
> ----
>
> thx for ur help
>
>
> *** Sent via Developersdex http://www.examnotes.net ***