my table and data as following,declare @paymentH table (idx int not null,paymentDte date not null, receiptNo varchar(16) not null, isCancel bit not null, amt decimal(10,2));/*idx is a primary keyreceiptNo is a unique key*/insert into @paymentH values(1, '20120919', '009899', 'false', 4000.00);insert into @paymentH values(2, '20120919', '009900', 'false', 390.90);insert into @paymentH values(3, '20120919', '009901', 'true', 400.50);insert into @paymentH values(4, '20120919', '009904', 'false', 100.30);insert into @paymentH values(5, '20120920', '009910', 'false', 500.00);insert into @paymentH values(6, '20120920', '009920', 'false', 90.90);insert into @paymentH values(7, '20120921', '009950', 'false', 120.00);insert into @paymentH values(8, '20120921', '009960', 'false', 900.00);insert into @paymentH values(9, '20120922', '009970', 'true', 1500.00);insert into @paymentH values(10, '20120922', '009971', 'false', 700.50);
How to get resultset as following,paymentDte | amt | noOfReceipt | noOfCancel------------------------------------------------------------------------2012-09-19 4891.70 4 12012-09-20 590.90 2 02012-09-21 1020.00 2 02012-09-22 2200.50 2 1
I hope someone can help me to built the SQL statement