INSERT INTO studentchargeshistory (studentid,
chargesid,
chargemonth,
chargeyear,
generatedate,
chargesamount,
sessionid)
SELECT
StudentRoll# AS StudentID,
extracharges.chargesid AS ChargesID,
'August' AS ChargeMonth,
'2014' AS ChargeYear,
GETDATE() AS GenerateDate,
CASE
WHEN apply >= 1 THEN Amount
ELSE 0
END AS ChargeAmount,
6 AS SessionID
FROM ClassCharges
INNER JOIN extracharges
ON extracharges.chargesid = ClassCharges.ChargesID
INNER JOIN ClassInfo
ON ClassInfo.ClassID = ClassCharges.ClassID
INNER JOIN ClassSection
ON ClassSection.ClassID = ClassInfo.ClassID
INNER JOIN studentinfo
ON StudentInfo.ClassSectionID = ClassSection.ClassSectionID
WHERE campusid = 1
AND StudentInfo.ClassSectionID = 1
AND extracharges.type = 'Monthly'
AND NOT EXISTS (SELECT
ExtraCharges.ChargesID
FROM studentchargeshistory
INNER JOIN extracharges
ON extracharges.chargesid =
studentchargeshistory.chargesid
INNER JOIN studentinfo
ON studentinfo.studentroll# =
studentchargeshistory.studentid
WHERE chargemonth = 'August'
AND chargeyear = '2014'
AND sessionid = 6
AND type = 'Monthly'
AND studentinfo.campusid = 1
AND studentinfo.classsectionid = 1)
Now I want get sum of (if any rows affected) Charge Amount Column so that I can insert into my ledger table