Count query help required

Question

I have users with fix 28 CheckList Items where on daily basis user has to enter value infront of every checklist item.

Users sometimes partially filled the list or leave it incomplete so i need to show them that you have left the checklist INCOMPLETE or filled PARTIALLY so therefore maintaining a field called "trans_status" which by default goes in database with 0 means Incomplete and once user fills the data the value gets 1 and it 28 entries has mixed 1 and 0's it means PARTIAL and if all 28 enties has 0 means INCOMPLETE and if all 1 means COMPLETED.

Here is the structure

CREATE TABLE [dbo].[VTRCheckListDetails](
    [userid] [int] NULL,
    [branchid] [int] NULL,
    [vtrRespDate] [date] NULL,
    [CLid] [int] NULL,
    [VtrValue] [varchar](5) NULL,
    [trans_status] [int] NULL,
    [last_updated] [int] NULL
 ) ON [PRIMARY]

And here is the sample data

INSERT VTRCheckListDetails(userid,branchid,vtrRespDate,CLid,VtrValue,trans_status,last_updated)       VALUES('144','14','2010-12-30','1','1','1','0')

INSERT VTRCheckListDetails(userid,branchid,vtrRespDate,CLid,VtrValue,trans_status,last_updated)       VALUES('144','14','2010-12-30','2','2','0','0')    

INSERT VTRCheckListDetails(userid,branchid,vtrRespDate,CLid,VtrValue,trans_status,last_updated)       VALUES('144','14','2010-12-30','3','3','0','0')      

INSERT VTRCheckListDetails(userid,branchid,vtrRespDate,CLid,VtrValue,trans_status,last_updated)       VALUES('144','14','2010-12-30','4','4','0','0')   

INSERT VTRCheckListDetails(userid,branchid,vtrRespDate,CLid,VtrValue,trans_status,last_updated)       VALUES('144','14','2010-12-30','5','5','0','0')     

INSERT VTRCheckListDetails(userid,branchid,vtrRespDate,CLid,VtrValue,trans_status,last_updated)       VALUES('144','14','2010-12-30','6','6','0','0')  

 INSERT VTRCheckListDetails(userid,branchid,vtrRespDate,CLid,VtrValue,trans_status,last_updated)       VALUES('144','14','2010-12-30','7','7','0','0')  

INSERT VTRCheckListDetails(userid,branchid,vtrRespDate,CLid,VtrValue,trans_status,last_updated)       VALUES('144','14','2010-12-30','8','8','0','0')   

INSERT VTRCheckListDetails(userid,branchid,vtrRespDate,CLid,VtrValue,trans_status,last_updated)       VALUES('144','14','2010-12-30','9','9','0','0')  

INSERT VTRCheckListDetails(userid,branchid,vtrRespDate,CLid,VtrValue,trans_status,last_updated)       VALUES('144','14','2010-12-30','10','10','0','0')  

INSERT VTRCheckListDetails(userid,branchid,vtrRespDate,CLid,VtrValue,trans_status,last_updated)       VALUES('144','14','2010-12-30','11','11','0','0')   

INSERT VTRCheckListDetails(userid,branchid,vtrRespDate,CLid,VtrValue,trans_status,last_updated)       VALUES('144','14','2010-12-30','12','12','0','0')   

INSERT VTRCheckListDetails(userid,branchid,vtrRespDate,CLid,VtrValue,trans_status,last_updated)       VALUES('144','14','2010-12-30','13','13','0','0')   

INSERT VTRCheckListDetails(userid,branchid,vtrRespDate,CLid,VtrValue,trans_status,last_updated)       VALUES('144','14','2010-12-30','14','14','0','0')   

INSERT VTRCheckListDetails(userid,branchid,vtrRespDate,CLid,VtrValue,trans_status,last_updated)       VALUES('144','14','2010-12-30','15','15','0','0')   

INSERT VTRCheckListDetails(userid,branchid,vtrRespDate,CLid,VtrValue,trans_status,last_updated)       VALUES('144','14','2010-12-30','16','16','0','0')  

INSERT VTRCheckListDetails(userid,branchid,vtrRespDate,CLid,VtrValue,trans_status,last_updated)       VALUES('144','14','2010-12-30','17','17','0','0')   

INSERT VTRCheckListDetails(userid,branchid,vtrRespDate,CLid,VtrValue,trans_status,last_updated)       VALUES('144','14','2010-12-30','18','18','0','0')  

INSERT VTRCheckListDetails(userid,branchid,vtrRespDate,CLid,VtrValue,trans_status,last_updated)       VALUES('144','14','2010-12-30','19','19','0','0')  

INSERT VTRCheckListDetails(userid,branchid,vtrRespDate,CLid,VtrValue,trans_status,last_updated)       VALUES('144','14','2010-12-30','20','20','0','0')  

INSERT VTRCheckListDetails(userid,branchid,vtrRespDate,CLid,VtrValue,trans_status,last_updated)       VALUES('144','14','2010-12-30','21','21','0','0')   

INSERT VTRCheckListDetails(userid,branchid,vtrRespDate,CLid,VtrValue,trans_status,last_updated)       VALUES('144','14','2010-12-30','22','22','0','0')   

INSERT VTRCheckListDetails(userid,branchid,vtrRespDate,CLid,VtrValue,trans_status,last_updated)       VALUES('144','14','2010-12-30','23','23','0','0')   

INSERT VTRCheckListDetails(userid,branchid,vtrRespDate,CLid,VtrValue,trans_status,last_updated)       VALUES('144','14','2010-12-30','24','24','0','0')   

INSERT VTRCheckListDetails(userid,branchid,vtrRespDate,CLid,VtrValue,trans_status,last_updated)       VALUES('144','14','2010-12-30','25','25','0','0')   

INSERT VTRCheckListDetails(userid,branchid,vtrRespDate,CLid,VtrValue,trans_status,last_updated)       VALUES('144','14','2010-12-30','26','26','0','0')   

INSERT VTRCheckListDetails(userid,branchid,vtrRespDate,CLid,VtrValue,trans_status,last_updated)       VALUES('144','14','2010-12-30','27','27','0','0')   

INSERT VTRCheckListDetails(userid,branchid,vtrRespDate,CLid,VtrValue,trans_status,last_updated)       VALUES('144','14','2010-12-30','28','28','0','0')   

What i want is when i run the query it should group on dates and show the data like this based on trans_status

Date.............Status
30-12-12..........Partial (can be complete, incomplete)

Answer 1

SELECT   userid     ,
         vtrRespDate,
         CASE
                  WHEN MAX(trans_status) = 0
                  THEN 'InComplete'
                  WHEN MIN(trans_status)=1
                  THEN 'Complete'
                  ELSE 'Partial'
         END AS status
FROM     VTRCheckListDetails
GROUP BY userid,
         vtrRespDate