May 2, 2017 at 7:01 am
Hi guys, I am working on a requirement where I am not finding any efficient way to get the desired output.
I have a table called T_USERPROCESS_STATUS which contains processing status by users for few numbers on monthly basis. The table data looks like this:
user unit_id processdate userstatus userstatusdate
I need user and his status based on the latest status (can check with last column). Here are the rules: if for a user, status of at least one unitid is confirmed, then status should be partially confirmed. If for a user, if at least one status is not confirmed, then status should be Not confirmed. If for a user, at least one status is confirmed with caveat and nothing as not confirmed then status should be confirmed with caveat. If for a user, all the unit ids are confirmed then status of user should be confirmed.
May 2, 2017 at 7:25 am
I notice your dates in your data are in the format ddMMMMyyyy. Is this actually how they are stored; are you using a varchar to store them?
Can we have your data in a consumable format please, with the data types, so that we don't make any assumptions. If they really are stored as varchars in that format, this posses a hurdle to start with. See the link my signature on how to supply data.
Edit: It would also really help if you included an expected output for your sample data, and include all the scenarios for your logic. For example you have logic for not confirmed, but your sample data contains no users with this status.
Thanks.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 2, 2017 at 7:31 am
Hey Thom, these are dates only and no VARCHAR. I gave few records to show they type of data. All date related columns are in datetime data types only.
May 2, 2017 at 8:31 am
I think something along these lines:
SELECT user,
CASE WHEN confirmed_count = user_count THEN 'Confirmed'
WHEN not_confirmed_count > 0 THEN 'Not Confirmed'
WHEN confirmed_with_caveat_count > 0 THEN 'Confirmed With Caveat'
WHEN confirmed_count > 0 THEN 'Partially Confirmed'
ELSE '?' END AS user_status
FROM (
SELECT
user,
SUM(CASE WHEN userstatus = 'confirmed' THEN 1 ELSE 0 END) AS confirmed_count,
SUM(CASE WHEN userstatus = 'confirmed with caveat' THEN 1 ELSE 0 END) AS confirmed_with_caveat_count,
SUM(CASE WHEN userstatus NOT LIKE '%confirmed%' THEN 1 ELSE 0 END) AS not_confirmed_count,
COUNT(*) AS user_count
FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY user, unit_id ORDER BY userstatusdate DESC) AS row_num
FROM dbo.T_USERPROCESS_STATUS
) AS derived
WHERE row_num = 1 /* Edit: in huge hurry before, left out this obvious line */
GROUP BY user
) AS derived2
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
May 2, 2017 at 8:40 am
sqlenthu 89358 - Tuesday, May 2, 2017 7:01 AMI need user and his status based on the latest status (can check with last column). Here are the rules: if for a user, status of at least one unitid is confirmed, then status should be partially confirmed. If for a user, if at least one status is not confirmed, then status should be Not confirmed. If for a user, at least one status is confirmed with caveat and nothing as not confirmed then status should be confirmed with caveat. If for a user, all the unit ids are confirmed then status of user should be confirmed.
Ok, let's cover this logic, as it seems to not make sense. First thing you state is that a user that has at least one confirmed should be partially confirmed. You then go on to say if they have at least one not confirmed then not confirmed, and if all confirmed then confirmed. These all conflict with each other. Say a user has 2 confirmed status, which is it, confirmed or partially confirmed? What about a user with statuses confirmed and not confirmed. Are they partially confirmed or not confirmed?
Unfortunately, without expected output and full sample data, I've therefore had to guess a little (a lot), but does this meet your needs? If not, please see my first post and your questions will be a lot easier to answer 🙂USE DevTestDB;
GO
--Create sample table
CREATE TABLE UserStatus
( varchar(6),
unit_id int,
process_date date, --Guessing not datetime
user_status varchar(50),
user_status_date date);
GO
--Insert Sample data
INSERT INTO UserStatus
VALUES
('User1',121234,'20170421','confirmed','20150425'),
('User1',121234,'20170426','confirmed with caveat','20170427'),
('User1',121212,'20170426','confirmed','20170427'),
('User2',202134,'20170424','confirmed','20170427'),
('User3.',303241,'20170423','confirmed','20170425'),
('User3.',303241,'20170426','not confirmed','20170427'),
('User4',456421,'20170422','confirmed','20170424'),
('User5',565654,'20170426','confirmed with caveat','20170427');
GO
--View our current sample data
SELECT *
FROM UserStatus;
GO
--Attempt at expected output
WITH Statuses AS (
SELECT ,
MAX(user_status_date) AS Last_user_status_date,
SUM(CASE WHEN user_status = 'confirmed' THEN 1 ELSE 0 END) AS confirmed,
SUM(CASE WHEN user_status = 'confirmed with caveat' THEN 1 ELSE 0 END) AS caveat,
SUM(CASE WHEN user_status = 'not confirmed' THEN 1 ELSE 0 END) AS not_confirmed,
COUNT() AS Statuses
FROM UserStatus
GROUP BY )
SELECT ,
Last_user_status_date,
CASE WHEN not_confirmed > 0 THEN 'not confirmed'
WHEN caveat > 0 AND confirmed = 0 THEN 'confirmed with caveat'
WHEN confirmed > 0 AND confirmed <= statuses THEN 'partially confirmed' --Total guess on this logic, see my post
WHEN confirmed > 0 AND confirmed = statuses THEN 'confirmed'
ELSE 'Unknown status'
END AS user_status
FROM Statuses
ORDER BY ;
GO
--Clean up
DROP TABLE UserStatus;
GO
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 2, 2017 at 1:10 pm
I'm thinking the statuses have a priority, not necessarily that the logic conflicts. Here's my attempt, with results similar to Scott's:
CREATE TABLE T_USERPROCESS_STATUS
( varchar(6),
unit_id int,
processdate date, --Guessing not datetime
userstatus varchar(50),
userstatusdate date);GO
INSERT INTO T_USERPROCESS_STATUS
VALUES
('User1',121234,'20170421','confirmed','20150425'),
('User1',121234,'20170426','confirmed with caveat','20170427'),
('User1',121212,'20170426','confirmed','20170427'),
('User2',202134,'20170424','confirmed','20170427'),
('User3.',303241,'20170423','confirmed','20170425'),
('User3.',303241,'20170426','not confirmed','20170427'),
('User4',456421,'20170422','confirmed','20170424'),
('User5',565654,'20170426','confirmed with caveat','20170427');
WITH LastStatus AS
(SELECT , unit_id, processdate, userstatus, userstatusdate,
CASE userstatus WHEN 'confirmed' THEN 1 WHEN 'confirmed with caveat' THEN 2 WHEN 'not confirmed' THEN 3 END AS statuspriority,
ROW_NUMBER() OVER(PARTITION BY user, unit_id ORDER BY userstatusdate DESC) AS recentness
FROM dbo.T_USERPROCESS_STATUS),
CalcStatus AS
(SELECT ls., MAX(ls.statuspriority) AS max_priority,
SUM(CASE WHEN statuspriority = 1 THEN 1 ELSE 0 END) AS confirmed_cnt,
SUM(CASE WHEN statuspriority > 1 THEN 1 ELSE 0 END) AS other_cnt
FROM LastStatus ls
WHERE ls.recentness = 1
GROUP BY ls.)
SELECT cs.,
CASE WHEN cs.max_priority = 1 AND (confirmed_cnt > 1 OR other_cnt = 0) THEN 'confirmed'
WHEN cs.max_priority = 1 AND confirmed_cnt = 1 THEN 'partially confirmed'
WHEN cs.max_priority = 2 THEN 'confirmed with caveat'
WHEN cs.max_priority = 3 THEN 'not confirmed'
END AS user_status
FROM CalcStatus cs;
May 3, 2017 at 2:10 am
Apologies for late response. So here is the complete picture:
Every user is mapped with one or more unit ids. Every month when our batch is processed, all entries get populated in this table I.e. user ID and related unit ids with userstatus and userstatusdate as NULL. In our UI, it only shows all users and their status which initially will be blank. Now as users start confirming all the unitids within their authority based on certain parameters, the status on UI will change. The different status we have are blank (means user not started confirming), "partially confirmed" (at least one unit is confirmed and rest are blank), "confirmed with caveat" if there is atleast one unitid with confirmed with caveat for that user but not a single "Not Confirmed". And at last the status will be "Not confirmed if there is atleast one unitid for that user with "not confirmed" status.
Now that batch may run multiple times because of any production changes, so there might be multiple entries for a userid unitid mapping for a month. In that case, we have to select latest status entry based on userstatusdate.
Hope that summarizes the requirement. I can try giving the scenario with test data but it will be lengthy to describe each scenario and what output should look like. However the above clarifies everything. All the dates are datetime showing the time as well.
May 3, 2017 at 10:34 pm
Chris Harshman - Tuesday, May 2, 2017 1:10 PMI'm thinking the statuses have a priority, not necessarily that the logic conflicts. Here's my attempt, with results similar to Scott's:
CREATE TABLE T_USERPROCESS_STATUS
( varchar(6),
unit_id int,
processdate date, --Guessing not datetime
userstatus varchar(50),
userstatusdate date);GOINSERT INTO T_USERPROCESS_STATUS
VALUES
('User1',121234,'20170421','confirmed','20150425'),
('User1',121234,'20170426','confirmed with caveat','20170427'),
('User1',121212,'20170426','confirmed','20170427'),
('User2',202134,'20170424','confirmed','20170427'),
('User3.',303241,'20170423','confirmed','20170425'),
('User3.',303241,'20170426','not confirmed','20170427'),
('User4',456421,'20170422','confirmed','20170424'),
('User5',565654,'20170426','confirmed with caveat','20170427');WITH LastStatus AS
(SELECT , unit_id, processdate, userstatus, userstatusdate,
CASE userstatus WHEN 'confirmed' THEN 1 WHEN 'confirmed with caveat' THEN 2 WHEN 'not confirmed' THEN 3 END AS statuspriority,
ROW_NUMBER() OVER(PARTITION BY user, unit_id ORDER BY userstatusdate DESC) AS recentness
FROM dbo.T_USERPROCESS_STATUS),
CalcStatus AS
(SELECT ls., MAX(ls.statuspriority) AS max_priority,
SUM(CASE WHEN statuspriority = 1 THEN 1 ELSE 0 END) AS confirmed_cnt,
SUM(CASE WHEN statuspriority > 1 THEN 1 ELSE 0 END) AS other_cnt
FROM LastStatus ls
WHERE ls.recentness = 1
GROUP BY ls.)
SELECT cs.,
CASE WHEN cs.max_priority = 1 AND (confirmed_cnt > 1 OR other_cnt = 0) THEN 'confirmed'
WHEN cs.max_priority = 1 AND confirmed_cnt = 1 THEN 'partially confirmed'
WHEN cs.max_priority = 2 THEN 'confirmed with caveat'
WHEN cs.max_priority = 3 THEN 'not confirmed'
END AS user_status
FROM CalcStatus cs;
Thom and Chris, your query was good. Thanks a lot. I was thinking a lot on working on setting some priorities but was not sure how to proceed. This will certainly help me in future as well.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply