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 2008 Forums
 Transact-SQL (2008)
 need help to built SQL

Author  Topic 

Idyana
Yak Posting Veteran

96 Posts

Posted - 2011-07-06 : 03:28:02
I've table as following,
declare @t1 table
(myYear varchar(4), noOfBook int);
insert into @t1 values('2010',15000);
insert into @t1 values('2011',10000);


if myYear='2010' and noOfBook='15000',

my resultset is
noOfBook        | serialStart         | serialEnd
-----------------------------------------------------
2010/00001 000001 000050
2010/00002 000051 000100
2010/00003 000101 000150
.....
.....
.....
.....
2010/15000 749951 750000


Looking for help to built SQL statement

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-07-06 : 05:26:27
Check It once....



declare @t1 table
(myYear varchar(4), noOfBook int);

insert into @t1 values('2010',15000);
insert into @t1 values('2011',10000);

with cte as
(
select 1 as num,myYear,noOfBook,1 as serialStart ,50 as serialEnd from @t1
union all
select c.num+1, a.myYear,a.noOfBook ,c.serialStart+50,c.serialEnd+50 from @t1 a inner join cte c on a.myYear=c.myYear
where c.num<a.noOfBook
)
select
convert(varchar(4),myYear)+'/'+RIGHT('00000'+convert(varchar(10),num),5) as noOfBook
,RIGHT('00000'+convert(varchar(10),serialStart),6) as serialStart
,RIGHT('00000'+convert(varchar(10),serialEnd),6) as serialEnd
from cte
order by myYear
option (maxrecursion 0)




--Ranjit
Go to Top of Page

Idyana
Yak Posting Veteran

96 Posts

Posted - 2011-07-09 : 04:49:16
tq sir
Go to Top of Page

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-07-11 : 00:26:56
Welcome Idyana


--Ranjit
Go to Top of Page
   

- Advertisement -