SQL Server - selecting multiple row records based on the column data has to be same

Asked By praveen pubby on 07-May-13 07:33 AM

hi guys,

here i am trying to fetch the multiple row records based on same column data has to get matched

Table_Fetch

sno filename fileid status settletime settledate settlesession
1 1.txt 123 30 16:00:00 5/7/2013 1

2 2.txt 173 30 16:00:00 5/7/2013 1

3  3.txt    124  30     16:00:00 5/7/2013    1
4  4.txt    523  30     14:00:00 5/7/2013    1
5  5.txt    723  30     15:00:00 5/7/2013    1
6  6.txt    163  30     15:00:00 5/7/2013    1

if i run a select query (select * from table_fetch where status = 30)
result - 6 rows will effect which is correct

Now i wanted to select the rows with condition called status = 30 and settletime and settledate and settlesession

first set :

1  1.txt    123  30     16:00:00 5/7/2013    1

2 2.txt 173 30 16:00:00 5/7/2013 1

3  3.txt    124  30     16:00:00 5/7/2013    1

second set

4  4.txt    523  30     14:00:00 5/7/2013    1

third set:

5  5.txt    723  30     15:00:00 5/7/2013    1
6  6.txt    163  30     15:00:00 5/7/2013    1

how can i achieve this ? can any one please guide me

john sat replied to praveen pubby on 07-May-13 08:31 AM

Hi,

Why don't you use union operation in the select statement.

Sandeep Mittal replied to praveen pubby on 13-May-13 04:09 AM

Requirement is to either generate output as a single resultset in the given format or multiple resultsets on the basis of some grouping?

Sandeep Mittal replied to praveen pubby on 13-May-13 08:04 AM

declare @tab table(sno int, filename varchar(10), fileid int, status int, settletime time, settledate date, settlesession int)

insert into @tab

select 1,'1.txt',123,30,'16:00:00','5/7/2026',1 union all

select 2,'2.txt',173,30,'16:00:00','5/7/2026',1 union all

select 3,'3.txt',124,30,'16:00:00','5/7/2026',1 union all

select 4,'4.txt',523,30,'14:00:00','5/7/2026',1 union all

select 5,'5.txt',723,30,'15:00:00','5/7/2026',1 union all

select 6,'6.txt',163,30,'15:00:00','5/7/2026',1

declare @settletime time, @settledate date, @settlesession int

declare cur1 cursor for select distinct settletime, settledate, settlesession from @tab

open cur1

fetch next from cur1 into @settletime, @settledate, @settlesession

while @@FETCH_STATUS = 0

begin

select * from @tab where settletime = @settletime and settledate = @settledate and settlesession = @settlesession

fetch next from cur1 into @settletime, @settledate, @settlesession

end

close cur1

deallocate cur1