about sql query

Question

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 Trivedi · Answer

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 shinde · Answer

hi 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 Trivedi · Answer

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'012) insert into #tblLog values(3'2014-03-25'012) insert into #tblLog values(3'2014-03-25'112) insert into #tblLog values(3'2014-03-25'112) insert into #tblLog values(3'2014-03-25'012) 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.