All geek questions in one place

Need help counting request

I have users with corrections of 28 CheckList elements, where every day the user must enter the value infront of each element of the checklist.

Users sometimes partially fill out the list or leave it incomplete, so I need to show them that you left the INCOMPLETE checklist or PARTIALLY filled, therefore supporting the trans_status field, which by default enters the database with 0, means Incomplete and once the user fills the data, the value gets 1, and it intervenes in 28 records 1, and 0 means PARTIAL, and if all 28 values ​​have 0, then 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 are the data examples

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')

, , - , , trans_status

Date.............Status
30-12-12..........Partial (can be complete, incomplete)
+3
2
SELECT  vtrRespDate,
        CASE COUNT(CASE trans_status WHEN 1 THEN 1 END)
        WHEN 0 THEN
                'Incomplete'
        WHEN COUNT(*) THEN
                'Complete'
        ELSE
                'Partial'
        END AS Status
FROM    VTRCheckListDetails
GROUP BY
        vtrRespDate
+2
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
+3

Source: https://habr.com/ru/post/1782815/

More articles:


All Articles