| Author |
 Topic  |
|
|
jafrywilson
Constraint Violating Yak Guru
India
379 Posts |
 Posted - 08/04/2012 : 03:19:27
|
My table is like this
Fullname UID invoice paymode chequeno amount amountpaid outstanding date
History Record - Hilary Ang Hui Min 1 29440 Cheque UOB 173631 - 3593004129 0.00 360 0 2012-03-02 00:00:00.000
History Record - Hilary Ang Hui Min 1 29700 Cheque UOB 173643 - 3593004129 0.00 360 0 2012-05-04 00:00:00.000
Chan Zi Jian 2 249513 Cash 0 95.00 94.5 0 2012-07-18 16:04:39.890
Cheng Kei Teng Joey 3 29409 Cheque UOB 420311 - 9513492651 0.00 360 0 2012-02-24 00:00:00.000
Cheng Kei Teng Joey 3 29754 Cheque UOB 420334 - 9513482651 0.00 360 0 2012-05-11 00:00:00.000
Cheng Kei Teng Joey 3 249524 Cash 360.00 360 0 2012-08-03 15:46:32.373
Chew Ke Qin 4 29611 Cash - 0.00 200 0 2012-04-13 00:00:00.000
Chew Ke Qin 4 249512 Cash 0 270.00 270 0 2012-07-16 20:05:02.873
Chew Ke Qin 4 249523 Cash 225.00 225 0 2012-08-03 15:16:52.797
Chua Wen Xin 5 29415 Cash - 0.00 200 0 2012-02-24 00:00:00.000
now i need to select data as like this,
Fullname UID invoice paymode chequeno amount amountpaid outstanding date cashpayment(total of all records) chequepayment (total of all records)
|
|
|
jafrywilson
Constraint Violating Yak Guru
India
379 Posts |
Posted - 08/04/2012 : 03:36:35
|
I have tried this query
select *,sum(PaymentAmount) CashAmount from(
select distinct FullName,LessonPayment.StudentID as UID,InvoiceNo,
--Categories.categoryName,
case Paymode when '1' then 'Cash' when '2' then 'Cheque' when '3' then 'Visa' when '4' then 'Master' End as PayMode,
ISNULL(ChequeNo,0) ChequeNo,ISNULL(PaymentAmount,0)PaymentAmount,ISNULL(AmountPaid ,0) AmountPaid,
ISNULL(Outstanding ,0) Outstanding,LessonPayment.CreatedDate ,
(select sum(PaymentAmount) CashAmount where PayMode='Cash')
from LessonPayment
join LessonPaymentHistory on LessonPaymentHistory.HistoryId=LessonPayment.InvoiceNo
join TuitionClassMasterDetails on TuitionClassMasterDetails.UID=LessonPaymentHistory.LessonId
--join Categories on Categories.categoryID=TuitionClassMasterDetails.SubjectId
join student on student.UID=LessonPayment.studentID
) a
where PayMode='Cash'
group by FullName,UID,InvoiceNo,Paymode,PaymentAmount,Outstanding,ChequeNo,AmountPaid,CreatedDate
while grouping it groups the amount by fullname
but i need the total of all records
|
 |
|
|
khtan
In (Som, Ni, Yak)
Singapore
16769 Posts |
Posted - 08/04/2012 : 08:49:21
|
SELECT Fullname, UID, invoice, paymode, chequeno, amount, amountpaid, outstanding, date,
cashpayment = sum(case when paymode = '1' then AmountPaid end) over(),
chequepayment = sum(case when paymode = '2' then AmountPaid end) over()
from yourtable
KH
Time is always against us
|
Edited by - khtan on 08/04/2012 08:58:46 |
|
|
| |
Topic |
|
|
|
Pls help
