Pls help - SQL Server Forums

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)

Pls help

Author

Topic

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2012-08-04 : 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

379 Posts

Posted - 2012-08-04 : 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)

17689 Posts

Posted - 2012-08-04 : 08:49:21

[code]
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[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources