I have 2 tables
tblLog and tbluser
tblLog
-UserId
-Date
-Validity(bit) ---0 for invalid 1-valid
-StatusId
tblUser
-UserId
-UserName
I want to getting datewise Valid and Invalid count data if i entered UserName
e.g
if i entered Username=3 and fromDate=25/03/2014 and toDate=27/03/2014
Date Valid Invalid
25/03/2014 0 1
26/03/2014 5 4
27/03/2014 1 2
how should I write query for showing above data ?
Jignesh TrivediPosted Mar 28, 2014, 2:25 AM
Hi,
No In this query, I am using case when statement and from this case when I am returning int 0 and 1 so it is possible to use SUM. try your self above T-SQL and revert back me if you have any problem
hope this will help you.
rd shindePosted Mar 28, 2014, 2:17 AM
thanks jignesh
I tried this but it can work instead of using sum() we can count() because datatype of Validity field is bit so it returns only 0 and 1..
Jignesh TrivediPosted Mar 27, 2014, 9:14 AM
hi,
try
Create table #tblLog
(
UserId int,
Date date,
Validity bit, ---0 for invalid 1-valid
StatusId int
)
insert into #tblLog values(3,'2014-03-25',0,12)
insert into #tblLog values(3,'2014-03-25',0,12)
insert into #tblLog values(3,'2014-03-25',1,12)
insert into #tblLog values(3,'2014-03-25',1,12)
insert into #tblLog values(3,'2014-03-25',0,12)
select Date, sum(case Validity when 0 then 1 else 0 end) as Invalid,
sum(case Validity when 1 then 1 else 0 end) as Invalid from #tblLog
where UserId =3 and DATE between '2014-03-25' and '2014-03-25'
group by date
hope this will help you.