SQLTeam.com | Weblogs | Forums

SQL procedure


#1

I have 3 tables - student , settings, subsettings
student table includes studid, name, admissionno, class
settings table includes feesetid, studid , heads
subsettings include - feesubid, feesetid, fromdate, todate,
feeamount, finefromdate1 , finetodate1 , fineamount1 , finefromdate2,
finetodate2, fineamount2
i want to get today's date and check whether today's date falls in
which fine date so that i can add feeamount with fine amount and display
the FeeAmount
How to write procedure for that?

Regards
neethu


#2

select feeamount + case when getdate() between finefromdate1 and finetodate1 then fineamount1 else 0 end,

  • case when getdate() between finefromdate2 and finetodate2 then fineamount2 else 0 end
    from student s
    inner join settings s1
    on s.studid = s1.studid
    inner join subsettings ss
    on s1.feesetid = ss.feesetid

#3

Thanks much. But i want to get the sum of these Feeamount and fine sum too.
Could you please help me?

5000.00 0.00
7500.00 0.00

Instead of this , sum of the fee amount i should get.