August 31, 2018 at 9:59 am
Hello everyone! First time posting and i'm sure this is going to be an easy one as i'm not a SQL dev but just do it for periodic requests.
I'm trying to report on the status of something and am having some difficulties. Basically, a customers status may have many results returned for the else condition in the below query but I want to prioritize. The order of the data would be Not Installed which they could have multiple entries for, then Pending and then finally completed. Here is the SQL
select sc.companyname, case when sc.szProblemDescription like '%v:\%' and sc.bClosed = '0' then 'Pending'
when sc.szProblemDescription like '%v:\%' and sc.bClosed = '1' then 'Completed'
else 'Not installed' END
from supportcase sc where sc.nCompanyId = '1019179'
What I need is basically if the Completed condition is returned, I don't want any of the other conditions returned per ncompanyid. If the Pending condition is returned, i don't want the Not Installed condition returned. Currently, it will return the following:
Company A Not installed
Company A Completed
Company A Not installed
Company A Not installed
Company A Not installed
Company A Not installed
Company A Not installed
What I would like is
Company A Completed
Another scenario:
Company A Not installed
Company A Not installed
Company A Not installed
Would like
Company A Not installed
Hopefully this makes sense and i'm just not using the right function. Let me know if I need to provide any table structure or anything. Thanks!
August 31, 2018 at 10:18 am
cory.hedstrom - Friday, August 31, 2018 9:59 AMHello everyone! First time posting and i'm sure this is going to be an easy one as i'm not a SQL dev but just do it for periodic requests.I'm trying to report on the status of something and am having some difficulties. Basically, a customers status may have many results returned for the else condition in the below query but I want to prioritize. The order of the data would be Not Installed which they could have multiple entries for, then Pending and then finally completed. Here is the SQL
select sc.companyname, case when sc.szProblemDescription like '%v:\%' and sc.bClosed = '0' then 'Pending'
when sc.szProblemDescription like '%v:\%' and sc.bClosed = '1' then 'Completed'
else 'Not installed' END
from supportcase sc where sc.nCompanyId = '1019179'What I need is basically if the Completed condition is returned, I don't want any of the other conditions returned per ncompanyid. If the Pending condition is returned, i don't want the Not Installed condition returned. Currently, it will return the following:
Company A Not installed
Company A Completed
Company A Not installed
Company A Not installed
Company A Not installed
Company A Not installed
Company A Not installedWhat I would like is
Company A CompletedAnother scenario:
Company A Not installed
Company A Not installed
Company A Not installedWould like
Company A Not installedHopefully this makes sense and i'm just not using the right function. Let me know if I need to provide any table structure or anything. Thanks!
This help?
SELECT TOP (1)
[sc].[companyname]
, CASE
WHEN [sc].[szProblemDescription] LIKE '%v:\%'
AND [sc].[bClosed] = '0'
THEN 'Pending'
WHEN [sc].[szProblemDescription] LIKE '%v:\%'
AND [sc].[bClosed] = '1'
THEN 'Completed'
ELSE 'Not installed'
END
FROM
[supportcase] [sc]
WHERE
[sc].[nCompanyId] = '1019179'
ORDER BY
CASE
WHEN [sc].[szProblemDescription] LIKE '%v:\%'
AND [sc].[bClosed] = '0'
THEN 1 --'Pending'
WHEN [sc].[szProblemDescription] LIKE '%v:\%'
AND [sc].[bClosed] = '1'
THEN 2 --'Completed'
ELSE 0 --'Not installed'
END DESC;
GO
August 31, 2018 at 10:50 am
YAY! Case statement in the order by clause!
Perfect! Thank you. Let me validate it holds true when I plug it into the rest of it.
August 31, 2018 at 11:23 am
I'm attempting to apply this to all companies but was specifying the company to try and fix in a smaller data set. So when I remove the where clause it only will return the top 1. Here is the entire:
SELECT CASE WHEN cast(sc.szProblemDescription as nvarchar(200)) LIKE '%v:\%'
AND [sc].[bClosed] = '0'
THEN 'Pending'
WHEN cast(sc.szProblemDescription as nvarchar(200)) LIKE '%v:\%'
AND [sc].[bClosed] = '1'
THEN 'Completed'
ELSE 'Not installed'
END ,c.szcustomernum, s.dtClose, c.szCompanyName FROM StatusItems s , company c, supportcase sc
WHERE (c.nid = s.nCompanyId) and s.dtclose > dateadd(m, -6,getdate()) and s.szStatusName = 'ActivationDate' and sc.nCompanyId = c.nId GROUP BY cast(sc.szProblemDescription AS nvarchar(200)), szCustomerNum, dtClose, szCompanyName,sc.bClosed
ORDER BY
CASE
WHEN cast(sc.szProblemDescription as nvarchar(200)) LIKE '%v:\%'
AND [sc].[bClosed] = '0'
THEN 1 --'Pending'
WHEN cast(sc.szProblemDescription as nvarchar(200)) LIKE '%v:\%'
AND [sc].[bClosed] = '1'
THEN 2 --'Completed'
ELSE 0 --'Not installed'
END DESC;
Sorry, guess I should've given the entirety of it up front.
August 31, 2018 at 12:39 pm
This is also not the best approach anyhow. The best approach is either to use a CROSS APPLY/TOP(1) or to use a CTE/ROW_NUMBER. The CROSS APPLY/TOP(1) tends to perform better in most circumstances, but you should test both.
Since you did not supply sample data and expected results, this is the closest that I could come up with based on your original query.
SELECT
sc.SupportCaseStatus,
c.szCustomerNum,
s.dtClose,
c.szCompanyName
FROM Company c
INNER JOIN StatusItems s
ON c.nid = s.nCompanyID
CROSS APPLY
(
SELECT TOP(1) CASE WHEN s.Sort = 1 THEN 'Completed' WHEN s.Sort = 0 THEN 'Pending' ELSE 'Not Installed' END AS SupportCaseStatus
FROM SupportCase sc
CROSS APPLY(
VALUES(
CASE
WHEN sc.szProblemDescription NOT LIKE '%v: \%' THEN -1
ELSE CAST(sc.bClosed AS SMALLINT)
END
)
) s(sort)
WHERE sc.nCompanyID = c.nID
ORDER BY s.sort DESC
) sc
WHERE s.dtClose > DATEADD(M, -6, GETDATE()
AND s.szStatusName = 'ActivationDate'
Also, the old-style joins (table1, table2, table3, etc.) have been deprecated FOREVER. You should be using the standard INNER/LEFT OUTER/RIGHT OUTER/CROSS joins.
Drew
PS: Don't let Joe C. see your column names. You really don't want to get his lecture on tibbles.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 31, 2018 at 1:13 pm
Hello Drew,
First off, thanks for the help as that worked perfectly. Secondly, i appreciate the insight to the correct use... I started a long time ago but don't get to use it all too often. Looks like that might be changing in the coming months so i'll take any and all help I can get 🙂
P.S. Don't tell Joe C.
September 13, 2018 at 2:59 pm
cory.hedstrom - Friday, August 31, 2018 9:59 AM
you might want to look at this article
https://www.red-gate.com/simple-talk/sql/t-sql-programming/state-transition-constraints/
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply