August 5, 2015 at 4:27 pm
DECLARE @IssueData TABLE
(
IssueID INT,
IssueTypeID INT,
Value INT
)
INSERT INTO @IssueData VALUES (1, 1, 20), (1,2,50), (1,3,1), (1,4,2), (2,2, 50), (3,1,20), (3,3,1)
DECLARE @IssueStatus TABLE
(
IssueTypeID INT,
Value INT,
IssueTitle VARCHAR(24)
)
INSERT INTO @IssueStatus VALUES (1, 0, 'Very Low'), (1, 20, 'Low'), (2, 40, 'Medium'), (3,1, 'High'), (4,2, 'Critical')
As the data shows,
I have IssueID's 1,2 and 3. By default every Issue starts at 0 (Very Low) Status. As the inputs are received data is logged in the table.
IssueID 1 has a StatusID of 1 with 20 as Value, so it has reached 'Low' Status. After that value of Status ID 1 can go upto any number and that doesn't matter. After sometime, IssueID 1 has recieved another Status 2 with a Value of 50. Since for StatusID "2" 40 is the threshold value and it crossed that it is now considered as "Medium".
IssueID 2 doesn't have the very first status reached but status 2 has reached the threshold limit. Even in that case, it should be considered as "Very Low" only because it hasn't met the qualifying criteria.
IssueID 3 can only be given "Low" because it didn't reach the Status ID of 2 of 40, but already reached for the 3rd one. so the 3rd one will not be considered without the second status.
So basically I am planning to go through the flow of the Issue from where it started to where it is currently.
So after the query, now my output should look like
SELECT 1 AS IssueID, 'Very Low' AS IssueStatus
UNION ALL
SELECT 1, 'Low'
UNION ALL
SELECT 1, 'Medium'
UNION ALL
SELECT 1, 'High'
UNION ALL
SELECT 1, 'Critical'
UNION ALL
SELECT 2, 'Low'
UNION ALL
SELECT 3, 'Very Low'
UNION ALL
SELECT 3, 'Low'
I tried to do this using a join but it won't work because it should go to the next status only if the other status criteria have met...
Thank you in advance for any inputs.
August 5, 2015 at 5:38 pm
ilovedata (8/5/2015)
DECLARE @IssueData TABLE
(
IssueID INT,
IssueTypeID INT,
Value INT
)
INSERT INTO @IssueData VALUES (1, 1, 20), (1,2,50), (1,3,1), (1,4,2), (2,2, 50), (3,1,20), (3,3,1)
DECLARE @IssueStatus TABLE
(
IssueTypeID INT,
Value INT,
IssueTitle VARCHAR(24)
)
INSERT INTO @IssueStatus VALUES (1, 0, 'Very Low'), (1, 20, 'Low'), (2, 40, 'Medium'), (3,1, 'High'), (4,2, 'Critical')
As the data shows,
I have IssueID's 1,2 and 3. By default every Issue starts at 0 (Very Low) Status. As the inputs are received data is logged in the table.
IssueID 1 has a StatusID of 1 with 20 as Value, so it has reached 'Low' Status. After that value of Status ID 1 can go upto any number and that doesn't matter. After sometime, IssueID 1 has recieved another Status 2 with a Value of 50. Since for StatusID "2" 40 is the threshold value and it crossed that it is now considered as "Medium".
IssueID 2 doesn't have the very first status reached but status 2 has reached the threshold limit. Even in that case, it should be considered as "Very Low" only because it hasn't met the qualifying criteria.
IssueID 3 can only be given "Low" because it didn't reach the Status ID of 2 of 40, but already reached for the 3rd one. so the 3rd one will not be considered without the second status.
So basically I am planning to go through the flow of the Issue from where it started to where it is currently.
So after the query, now my output should look like
SELECT 1 AS IssueID, 'Very Low' AS IssueStatus
UNION ALL
SELECT 1, 'Low'
UNION ALL
SELECT 1, 'Medium'
UNION ALL
SELECT 1, 'High'
UNION ALL
SELECT 1, 'Critical'
UNION ALL
SELECT 2, 'Low'
UNION ALL
SELECT 3, 'Very Low'
UNION ALL
SELECT 3, 'Low'
I tried to do this using a join but it won't work because it should go to the next status only if the other status criteria have met...
Thank you in advance for any inputs.
1. In your code, you have an IssueTypeID. In your description, you mention StatusID. Are these the same?
2. When explaining IssueID 1, you talk about StatusID 1 & 2. Assuming that StatusID = IssueTypeID, what about the values for 3 and 4?
Based upon the data that your have provided, can you provide the expected results for your query? All columns and values please.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 5, 2015 at 6:12 pm
Thank you for your quick reply. Sorry about the confusion. Yes, you are right when I mean status Id I was referring to issuetypeid.
I provided the output in the form of select statement in the post. Let me know if I can clarify anything else.
August 6, 2015 at 8:35 am
Let me work on this a bit and see what I can come up with.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 6, 2015 at 8:58 am
Thank you wayne, I will wait for your answer.
August 6, 2015 at 12:11 pm
How does this work?
WITH cte AS
(
SELECT d.IssueID,
s.IssueTypeID,
s.IssueTitle,
-- get the previous IssueTypeID for this IssueID
LAG(d.IssueTypeID, 1, d.IssueTypeID) OVER (PARTITION BY d.IssueID ORDER BY d.IssueTypeID, d.Value) LagIssueTypeID,
-- get the first IssueTypeID for this IssueID
FIRST_VALUE(d.IssueTypeID) OVER (PARTITION BY d.IssueID ORDER BY d.IssueTypeID, d.Value) FirstIssueTypeID
FROM @IssueData d
JOIN @IssueStatus s ON d.IssueTypeID = s.IssueTypeID
)
SELECT cte.IssueID,
-- if this IssueID didn't start off with an issue type of 1, change status to value 20 (low)
CASE WHEN cte.FirstIssueTypeID = 1 THEN cte.IssueTitle ELSE s.IssueTitle END AS IssueStatus
FROM cte
JOIN @IssueStatus s ON s.IssueTypeID = 1 AND s.Value = 20
-- skip those IssueTypeIDs that aren't sequential
WHERE cte.LagIssueTypeID+1 >= cte.IssueTypeID
ORDER BY IssueID, cte.IssueTypeID;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 6, 2015 at 4:05 pm
I am sorry Wayne, I gave you the wrong output values.
SELECT 1 AS IssueID, 'Very Low' AS IssueStatus
UNION ALL
SELECT 1, 'Low'
UNION ALL
SELECT 1, 'Medium'
UNION ALL
SELECT 1, 'High'
UNION ALL
SELECT 1, 'Critical'
UNION ALL
SELECT 2, 'Very Low'
UNION ALL
SELECT 3, 'Very Low'
UNION ALL
SELECT 3, 'Low'
The only change was with IssueID 2, its IssueStatus in the output should show only "Very Low" because it hasn't met the first requirement. I made a small change to your by making the value = 0 instead of 20.
It worked fine for the input I provided... but when I change the input to
DECLARE @IssueData TABLE
(
IssueID INT,
IssueTypeID INT,
Value INT
)
INSERT INTO @IssueData VALUES (1, 1, 19), (1,3,1), (1,4,2), (2,2, 50), (3,1,20), (3,3,1)
DECLARE @IssueStatus TABLE
(
IssueTypeID INT,
Value INT,
IssueTitle VARCHAR(24)
)
INSERT INTO @IssueStatus VALUES (1, 20, 'Low'), (2, 40, 'Medium'), (3,1, 'High'), (4,2, 'Critical')
;WITH cte AS
(
SELECT d.IssueID,
s.IssueTypeID,
s.IssueTitle,
-- get the previous IssueTypeID for this IssueID
LAG(d.IssueTypeID, 1, d.IssueTypeID) OVER (PARTITION BY d.IssueID ORDER BY d.IssueTypeID, d.Value) LagIssueTypeID,
-- get the first IssueTypeID for this IssueID
FIRST_VALUE(d.IssueTypeID) OVER (PARTITION BY d.IssueID ORDER BY d.IssueTypeID, d.Value) FirstIssueTypeID
FROM @IssueData d
JOIN @IssueStatus s ON d.IssueTypeID = s.IssueTypeID
)
SELECT cte.IssueID,
-- if this IssueID didn't start off with an issue type of 1, change status to value 20 (low)
CASE WHEN cte.FirstIssueTypeID = 1 THEN cte.IssueTitle ELSE s.IssueTitle END AS IssueStatus
FROM cte
JOIN @IssueStatus s ON s.IssueTypeID = 1 AND s.Value = 20
-- skip those IssueTypeIDs that aren't sequential
WHERE cte.LagIssueTypeID+1 >= cte.IssueTypeID
ORDER BY IssueID, cte.IssueTypeID;
your code didn't work.
In this input for IssueID 1, the issuetypeID 1 value is 19, issuetypeid 2 value is not present, issuetypeid 3 value is 1 and issuetypeid 4 value is 2. Since It hasn't even crossed the limit for IssueTypeID 1 that is 20 in the IssueStatus table. So it should be in "Very Low" Status only.
for IssueID 2, issuetypeid 1 value is not present, issuetypeid 2 value is 50. Even though it crossed the limit 40 for issuetypeid 2, it should have a status of "Very Low" because it didn't fulfill the first requirement that is Issuetypeid 1 being >= 20.
for IssueID 3, issuetypeid 1 value is 20, issuetypeid 2 value is not present and issuetypeid 3 is 1. The status of this issue is "Low" because it hasn't crossed the limit for issuetypeid 2.
so the output will look like below,
SELECT 1 AS IssueID, 'Very Low' AS IssueStatus
UNION ALL
SELECT 2, 'Very Low'
UNION ALL
SELECT 3, 'Very Low'
UNION ALL
SELECT 4, 'Low'
Thank you for your time.
August 6, 2015 at 7:49 pm
ilovedata (8/6/2015)
I am sorry Wayne, I gave you the wrong output values.
SELECT 1 AS IssueID, 'Very Low' AS IssueStatus
UNION ALL
SELECT 1, 'Low'
UNION ALL
SELECT 1, 'Medium'
UNION ALL
SELECT 1, 'High'
UNION ALL
SELECT 1, 'Critical'
UNION ALL
SELECT 2, 'Very Low'
UNION ALL
SELECT 3, 'Very Low'
UNION ALL
SELECT 3, 'Low'
The only change was with IssueID 2, its IssueStatus in the output should show only "Very Low" because it hasn't met the first requirement. I made a small change to your by making the value = 0 instead of 20.
It worked fine for the input I provided... but when I change the input to
DECLARE @IssueData TABLE
(
IssueID INT,
IssueTypeID INT,
Value INT
)
INSERT INTO @IssueData VALUES (1, 1, 19), (1,3,1), (1,4,2), (2,2, 50), (3,1,20), (3,3,1)
DECLARE @IssueStatus TABLE
(
IssueTypeID INT,
Value INT,
IssueTitle VARCHAR(24)
)
INSERT INTO @IssueStatus VALUES (1, 20, 'Low'), (2, 40, 'Medium'), (3,1, 'High'), (4,2, 'Critical')
;WITH cte AS
(
SELECT d.IssueID,
s.IssueTypeID,
s.IssueTitle,
-- get the previous IssueTypeID for this IssueID
LAG(d.IssueTypeID, 1, d.IssueTypeID) OVER (PARTITION BY d.IssueID ORDER BY d.IssueTypeID, d.Value) LagIssueTypeID,
-- get the first IssueTypeID for this IssueID
FIRST_VALUE(d.IssueTypeID) OVER (PARTITION BY d.IssueID ORDER BY d.IssueTypeID, d.Value) FirstIssueTypeID
FROM @IssueData d
JOIN @IssueStatus s ON d.IssueTypeID = s.IssueTypeID
)
SELECT cte.IssueID,
-- if this IssueID didn't start off with an issue type of 1, change status to value 20 (low)
CASE WHEN cte.FirstIssueTypeID = 1 THEN cte.IssueTitle ELSE s.IssueTitle END AS IssueStatus
FROM cte
JOIN @IssueStatus s ON s.IssueTypeID = 1 AND s.Value = 20
-- skip those IssueTypeIDs that aren't sequential
WHERE cte.LagIssueTypeID+1 >= cte.IssueTypeID
ORDER BY IssueID, cte.IssueTypeID;
your code didn't work.
In this input for IssueID 1, the issuetypeID 1 value is 19, issuetypeid 2 value is not present, issuetypeid 3 value is 1 and issuetypeid 4 value is 2. Since It hasn't even crossed the limit for IssueTypeID 1 that is 20 in the IssueStatus table. So it should be in "Very Low" Status only.
for IssueID 2, issuetypeid 1 value is not present, issuetypeid 2 value is 50. Even though it crossed the limit 40 for issuetypeid 2, it should have a status of "Very Low" because it didn't fulfill the first requirement that is Issuetypeid 1 being >= 20.
for IssueID 3, issuetypeid 1 value is 20, issuetypeid 2 value is not present and issuetypeid 3 is 1. The status of this issue is "Low" because it hasn't crossed the limit for issuetypeid 2.
so the output will look like below,
SELECT 1 AS IssueID, 'Very Low' AS IssueStatus
UNION ALL
SELECT 2, 'Very Low'
UNION ALL
SELECT 3, 'Very Low'
UNION ALL
SELECT 4, 'Low'
Thank you for your time.
Is @IssueStatus supposed to have a "Very Low" entry? It's removed from what you just posted, which makes it impossible to ever get this status.
Also, for your new test data:
INSERT INTO @IssueData VALUES (1, 1, 19), (1,3,1), (1,4,2), (2,2, 50), (3,1,20), (3,3,1)
How do you get your expected results from this? Specifically:
SELECT 4, 'Low'
(Your new test data doesn't have an IssueID = 4...)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 6, 2015 at 8:02 pm
For the sample data that you just provided, and including the "Very Low" status, this query:
DECLARE @IssueData TABLE
(
IssueID INT,
IssueTypeID INT,
Value INT
)
INSERT INTO @IssueData VALUES (1, 1, 19), (1,3,1), (1,4,2), (2,2, 50), (3,1,20), (3,3,1)
DECLARE @IssueStatus TABLE
(
IssueTypeID INT,
Value INT,
IssueTitle VARCHAR(24)
)
--INSERT INTO @IssueStatus VALUES (1, 20, 'Low'), (2, 40, 'Medium'), (3,1, 'High'), (4,2, 'Critical')
INSERT INTO @IssueStatus VALUES (1, 0, 'Very Low'), (1, 20, 'Low'), (2, 40, 'Medium'), (3,1, 'High'), (4,2, 'Critical')
;WITH cte AS
(
SELECT d.IssueID,
s.IssueTypeID,
s.IssueTitle,
d.Value,
s.Value AS StatusValue,
-- get the previous IssueTypeID for this IssueID
LAG(d.IssueTypeID, 1, d.IssueTypeID) OVER (PARTITION BY d.IssueID ORDER BY d.IssueTypeID, d.Value) LagIssueTypeID,
-- get the first IssueTypeID for this IssueID
FIRST_VALUE(d.IssueTypeID) OVER (PARTITION BY d.IssueID ORDER BY d.IssueTypeID, d.Value) FirstIssueTypeID
FROM @IssueData d
JOIN @IssueStatus s ON d.IssueTypeID = s.IssueTypeID
)
SELECT cte.IssueID,
--cte.IssueTypeID, cte.IssueTitle, cte.LagIssueTypeID, cte.FirstIssueTypeID, cte.Value, cte.StatusValue,
-- if this IssueID didn't start off with an issue type of 1, change status to value 0 (Very Low)
CASE WHEN cte.FirstIssueTypeID = 1 THEN cte.IssueTitle ELSE s.IssueTitle END AS IssueStatus
FROM cte
JOIN @IssueStatus s ON s.IssueTypeID = 1 AND s.Value = 0
-- skip those IssueTypeIDs that aren't sequential
WHERE cte.LagIssueTypeID+1 >= cte.IssueTypeID
AND cte.Value > cte.StatusValue
ORDER BY IssueID, cte.IssueTypeID;
Produces the following results:
IssueID IssueStatus
----------- ------------------------
1 Very Low
2 Very Low
3 Very Low
Does this work for you?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply