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 2008 Forums
 Transact-SQL (2008)
 looking for help to built SQL

Author  Topic 

Idyana
Yak Posting Veteran

96 Posts

Posted - 2012-11-26 : 05:49:07
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 key
receiptNo 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 1
2012-09-20 590.90 2 0
2012-09-21 1020.00 2 0
2012-09-22 2200.50 2 1
I hope someone can help me to built the SQL statement

stepson
Aged Yak Warrior

545 Posts

Posted - 2012-11-26 : 06:10:11
select paymentDte,sum(amt) as amt,count(receiptNo) as NoRecepient , sum(case when isCancel=1 then 1 else 0 end) as NoIsCancel
from paymentH
group by paymentDte

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb
Go to Top of Page

Idyana
Yak Posting Veteran

96 Posts

Posted - 2012-11-26 : 09:05:06
tq sir
Go to Top of Page

stepson
Aged Yak Warrior

545 Posts

Posted - 2012-11-27 : 01:22:12
with welcome

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb
Go to Top of Page
   

- Advertisement -