how to get one row at a time - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

how to get one row at a time

Author

Topic

azharrahi
Starting Member

44 Posts

Posted - 2007-02-13 : 02:13:23

Hope u all will be fine
Here is the scenario.
I have a table named PremiumReceived It has columns
PremiumReceivingID (int,pk, autogenerated)
IID (int,FOREIGN KEY)
ReceivingDate (datetime)
PeriodFrom (datetime)
PeriodTo (datetime)

now this table has hundreds, thousand rows.
some sample date is here

PremiumReceivingID IID ReceivingDate PeriodFrom PeriodTo
1 1 1/1/1999 1/1/1999 1/1/2000
2 1 1/1/2000 1/1/2000 1/1/2002
3 2 4/2/2001 5/1/2001 5/1/2002

I want that only one row is retrieved at a time, do manipulation on that row and then it goes to second row (esp when IID is specified e.g. IID = 1)
The manipualtion is that i want to get the DateDiff between the Periodfrom And PeriodTo in years and then separate each year in a temporary table.
e.g in 2nd row
the datediff between PeriodFrom and PeriodTo is 2 years
then I want to insert Each Period using this difference in a atemporary table i.e.
PeriodFrom PeriodTo
1/1/2000 1/1/2001
1/1/2001 1/1/2002
I am waiting for frequent reply.
thanks.

Azhar Rahi
Software Engineer
Experts Desk Pvt Ltd,Lahore
Pakistan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-13 : 02:38:07

T-SQL is a database language that performs best when doing set-based thing, as it was developed for.

set dateformat mdy

-- initialize input parameter
declare	@iid int

set	@iid = 1

-- prepare sample data
declare @t table (PremiumReceivingID int, IID int, ReceivingDate datetime, PeriodFrom datetime, PeriodTo datetime)

insert	@t
select	1, 1, '1/1/1999', '1/1/1999', '1/1/2000' union all
select	2, 1, '1/1/2000', '1/1/2000', '1/1/2002' union all
select	3, 2, '4/2/2001', '5/1/2001', '5/1/2002'

-- do some preparation stuff
declare	@mindt datetime,
	@maxdt datetime

select	@mindt = dateadd(month, datediff(month, 0, min(dt)), 0),
	@maxdt = dateadd(month, datediff(month, 0, max(dt)), 0)
from	(
	    select min(periodfrom) as dt from @t where iid = @iid union all
	    select max(periodfrom) from @t where iid = @iid union all
	    select min(periodto) from @t where iid = @iid union all
	    select max(periodto) from @t where iid = @iid
	) as d

-- prepare staging table
declare	@stage table (periodfrom datetime, periodto datetime)

while @mindt < @maxdt
    begin
	insert	@stage (periodfrom, periodto)
	select	dateadd(year, datediff(year, 0, @mindt), 0),
		dateadd(year, 1 + datediff(year, 0, @mindt), 0)

	select	@mindt = dateadd(year, 1, @mindt)
    end

-- show the output
select		periodfrom,
		periodto
from		@stage
order by	periodfrom

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-13 : 02:39:31

why do you want to process one row at a time when you can process all rows at once ?

SQL Server is design to process data as a set (set based) not record by record. You have to use cursor or some sort of while loop if you need to process record by record.

Maybe you can explain what are the manipulation / processing you required. Provide the related table structure, sample data and the result that you want

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-13 : 02:42:47

Replace this code

while @mindt < @maxdt
    begin
	insert	@stage (periodfrom, periodto)
	select	dateadd(year, datediff(year, 0, @mindt), 0),
		dateadd(year, 1 + datediff(year, 0, @mindt), 0)

	select	@mindt = dateadd(year, 1, @mindt)
    end

with this better code to handle more cases

while @mindt < @maxdt
    begin
	insert	@stage (periodfrom, periodto)
	select	dateadd(month, datediff(month, 0, @mindt), 0),
		dateadd(month, 12 + datediff(month, 0, @mindt), 0)

	select	@mindt = dateadd(month, 12, @mindt)
    end

Peter Larsson
Helsingborg, Sweden

azharrahi
Starting Member

44 Posts

Posted - 2007-02-13 : 04:10:15

Actually khtan. I want to retrieve the separate Periods for which an insurant pays the premium. and also i want to retrieve that periods for which insurant does not pay.
and an insurant an have payment modes for monthly, quarterly, half-yearly or yearly basis and i have to show each insurant report on the basis of payment mode.
an insurant having yearly payment mode must be shown on report on yearly basis and also he can pay in advance.i.e he might pay not only the current year but also for next 2,3,4.... years and at entry time only one value will be inserted in PeriodFrom and PeriodTo and if it is paid for 3 years then I must want to separate each year to show on reports so i want row by row retrieving for manipulation.

Azhar Rahi
Software Engineer
Experts Desk Pvt Ltd,Lahore
Pakistan

arfanbaig
Starting Member

1 Post

Posted - 2011-05-20 : 08:20:28

Following may help:

declare @RecordID int
declare @counter int
set @counter = 1;
declare @max int
set @max = (select count(*) from MyTable

while @counter< @max
begin
set @RecordID = (select top 1 x.RecordID from
(select top (@counter) RecordID from MyTable order by RecordID asc)
as x
order by leaveid desc
)

select * from MyTable where RecordID = @RecordID
end

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources