Select SUM(Case flag1 When 1 Then 1 Else 0 End) As flag1, SUM(Case flag2 When 2 Then 1 Else 0 End) As flag2, SUM(Case flag3 When 3 Then 1 Else 0 End) As flag3 From [table] Where date1 Between '2006-01-01' And '2006-08-01'
--??select count(*) from [table] where flag1=1 and flag2=2 and flag3=3 and date1 between TimeStart and TimeEnd
Create Table TEST (id Int , flag1 Int, flag2 Int, flag3 Int, date1 DateTime) Insert TEST Select 1,1,2,3,'2006-05-01' Union All Select 1,1,3,2,'2006-05-01' Union All Select 1,1,1,3,'2006-05-01' GO Select SUM(Case flag1 When 1 Then 1 Else 0 End) As flag1, SUM(Case flag2 When 2 Then 1 Else 0 End) As flag2, SUM(Case flag3 When 3 Then 1 Else 0 End) As flag3 From [TEST] Where date1 Between '2006-01-01' And '2006-08-01' GO Drop Table TEST --Result /* flag1 flag2 flag3 3 1 2 */
OrCreate Table TEST (id Int , flag1 Int, flag2 Int, flag3 Int, date1 DateTime) Insert TEST Select 1,1,2,3,'2006-05-01' Union All Select 1,1,3,2,'2006-05-01' Union All Select 1,1,1,3,'2006-05-01' GO Select Count(*) As Count From [TEST] Where flag1=1 And flag2=2 And flag3=3 And date1 Between '2006-01-01' And '2006-08-01' GO Drop Table TEST --Result /* Count 1 */
Select
SUM(Case flag1 When 1 Then 1 Else 0 End) As flag1,
SUM(Case flag2 When 2 Then 1 Else 0 End) As flag2,
SUM(Case flag3 When 3 Then 1 Else 0 End) As flag3
From [table]
Where date1 Between '2006-01-01' And '2006-08-01'
from [table]
where flag1=1 and flag2=2 and flag3=3 and date1 between TimeStart and TimeEnd
Create Table TEST
(id Int ,
flag1 Int,
flag2 Int,
flag3 Int,
date1 DateTime)
Insert TEST Select 1,1,2,3,'2006-05-01'
Union All Select 1,1,3,2,'2006-05-01'
Union All Select 1,1,1,3,'2006-05-01'
GO
Select
SUM(Case flag1 When 1 Then 1 Else 0 End) As flag1,
SUM(Case flag2 When 2 Then 1 Else 0 End) As flag2,
SUM(Case flag3 When 3 Then 1 Else 0 End) As flag3
From [TEST]
Where date1 Between '2006-01-01' And '2006-08-01'
GO
Drop Table TEST
--Result
/*
flag1 flag2 flag3
3 1 2
*/
(id Int ,
flag1 Int,
flag2 Int,
flag3 Int,
date1 DateTime)
Insert TEST Select 1,1,2,3,'2006-05-01'
Union All Select 1,1,3,2,'2006-05-01'
Union All Select 1,1,1,3,'2006-05-01'
GO
Select
Count(*) As Count
From [TEST]
Where flag1=1 And flag2=2 And flag3=3 And date1 Between '2006-01-01' And '2006-08-01'
GO
Drop Table TEST
--Result
/*
Count
1
*/