Daily income and expenditure

If object_id ('[TB]') is not null drop table [TB]
Go
Create Table [TB] (Name: varchar (10), amount: int, sign: varchar (2), date: varchar (30) -- Revenue 1 expenditure: 2
Insert [TB]
Select 'A', 100, '1', '2017-11-23 'Union all
Select 'B', 150, '2', '2017-11-22 'Union all
Select 'C', 190, '1', '2017-11-23 'Union all
Select 'D', 110, '2', '2017-11-23 'Union all
Select 'E', 145, '1', '2017-11-24'

Select * from TB

Select
Convert (varchar (10), date, 120) as date,
Sum (case indicates when 1 then amount else 0 end) as income,
Sum (case indicates when 2 then amount else 0 end) as expenditure
From
TB
Group
Convert (varchar (10), date, 120)

/*

Name amount flag date
-------------------------------------------------------
A 100 1
B 150 2
C 190 1
D 110 2
E 145 1

Date Income Expenditure
--------------------------------
2009-11-22 0 150
2009-11-23 290 110
145 0

*/

 

 

select batchid,max(message),min(SendTime), sum(case result1 when 0 then 1 else 0 end) as TotalSub, sum(case result2 when 0 then 1 else 0 end) as SuccSubNumber, sum(case result3 when 0 then 1 else 0 end) as AbsoluteSuccNumberfrom Send WHERE  CompanyID=1group by  batchid order by batchid desc