SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 looking for help to built SQL
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Idyana
Yak Posting Veteran

95 Posts

Posted - 11/26/2012 :  05:49:07  Show Profile  Reply with Quote
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

Edited by - Idyana on 11/26/2012 05:50:28

stepson
Constraint Violating Yak Guru

Romania
428 Posts

Posted - 11/26/2012 :  06:10:11  Show Profile  Reply with Quote
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

95 Posts

Posted - 11/26/2012 :  09:05:06  Show Profile  Reply with Quote
tq sir
Go to Top of Page

stepson
Constraint Violating Yak Guru

Romania
428 Posts

Posted - 11/27/2012 :  01:22:12  Show Profile  Reply with Quote
with welcome

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000