December 30, 2010 at 6:47 am
I have the following table with this 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] [date] NULL
) ON [PRIMARY]
with the following 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')
I want to check if user has entered data Completely, Partially or didnt entered which is Incomplete in any date
Output desired.
Date ..............Status
30-12-2010......Complete/Incomplete/Partial (One status would show) based on
1. If all trans_status value = 0 status would be "Incomplete'
2. If all trans_status value = 1 status would be "Complete'
3. if trans_Status value ha some 0's and 1 then 'Partial'
how to do in one query
December 30, 2010 at 6:54 am
joshtheflame (12/30/2010)
1. If all trans_status value = 0 status would be "Incomplete'2. If all trans_status value = 1 status would be "Complete'
3. if trans_Status value ha some 0's and 1 then 'Partial'
All for a userID? All for a UserID, BranchID combo? All for a UserID, Date combo? All for something else?
p.s. Thank you for the nice sample data. Makes things so much easier.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 30, 2010 at 10:39 am
well actually its a daily checklist users has to fill in every day so as much as they will fill partially or incomplete i will have to show in the bucket that on following dates user has filled it partially or left it incomplete so it will be for all the dates but GROUP BY 🙂
December 30, 2010 at 11:44 am
So it's per user, per date?
If I understand you correctly...
SELECT UserID, vtrRespDate,
CASE
WHEN EXISTS (SELECT 1 FROM VTRCheckListDetails sub WHERE sub.UserID = v.UserID AND sub.vtrRespDate = v.vtrRespDate AND trans_status = 0)
AND NOT EXISTS (SELECT 1 FROM VTRCheckListDetails sub WHERE sub.UserID = v.UserID AND sub.vtrRespDate = v.vtrRespDate AND trans_status = 1)
THEN 'Incomplete'
WHEN EXISTS (SELECT 1 FROM VTRCheckListDetails sub WHERE sub.UserID = v.UserID AND sub.vtrRespDate = v.vtrRespDate AND trans_status = 1)
AND NOT EXISTS (SELECT 1 FROM VTRCheckListDetails sub WHERE sub.UserID = v.UserID AND sub.vtrRespDate = v.vtrRespDate AND trans_status = 0)
THEN 'Complete'
ELSE 'Partial'
END as Status
FROM VTRCheckListDetails v
GROUP BY UserID, vtrRespDate
p.s. your sample data didn't insert correctly. '0' can't be converted to a date.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 30, 2010 at 11:07 pm
Thank you all of you who helped me here..both the queries worked great 🙂 Appreciate support.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply