Multiple row with calculation problem

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 2005 Forums

Transact-SQL (2005)

Multiple row with calculation problem

Author

Topic

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-03-13 : 09:29:12

Hello,
I have Course table with different courses.. as below
CourseId CourseName Fees Months
1 ABC 2000 2
2 XYZ 5000 4

while applying this course to student i need installment entries..
for that i need select Statement with help of months.. as below

output.. if user selects second(2) course then.. four installments as below

CourseId Coursename ToPayFees
1 XYZ 1250
1 XYZ 1250
1 XYZ 1250
1 XYZ 1250

Please help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-13 : 11:00:42

How did courseId change to 1 in output? Assuming thats a typo, the solution you need is

SELECT c.CourseId,c.CourseName,c.Fees/Months AS ToPayFees
FROM Course c
CROSS JOIN master..spt_values v
WHERE v.type='p'
AND v.number BETWEEN 1 AND c.Months
AND c.CourseId=@CourseID

pass required course number into parameter like 1,2,...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sachin.Nand

2937 Posts

Posted - 2010-03-13 : 11:29:54


;with cte
as
(
select courseid,coursename,months,fees,1 as cntmonths from @tbl
union all
select t1.courseid,t1.coursename,t1.months,t1.fees,cntmonths+1 from @tbl t1
inner join cte on cte.cntmonths+1<=t1.months
where cte.courseid=t1.courseid

)

select courseid,coursename,fees/months as ToPayfees from cte t2 order by courseid

PBUH

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-03-13 : 12:44:35

what is this table used for..?

master..spt_values v

didn't getting this ?

result is same as i need..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-13 : 14:21:27

quote:
Originally posted by keyursoni85

what is this table used for..?

master..spt_values v

didn't getting this ?

result is same as i need..

thats an internal count table which is available in sql server.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-03-14 : 00:39:00

Thanks..
visakh and idera

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-14 : 00:47:58

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources