how ro get category to be in a certain order

Question

i want to be able to have the Category order as Payroll,PTO andthen OT.
Not sure if it is posoble.
![DECLARE @dtMonth datetime = '2022-09-01'
DECLARE @intProp INT = 8005

SELECT  
CONCAT(tblProperties.intProp, ' - ', tblProperties.strAlias)Title,  
qryFinal.strTemplate,  
qryFinal.intGroup,  
qryFinal.strGroup,  
qryFinal.intOrder,  
qryFinal.intCategory,  
Case qryFinal.strCategory  
  
  When 'Payroll' then  'Payroll'  
  when 'Payroll - Overtime' then 'OT'  
  when 'Payroll PTO' then 'PTO'  
  end as strCategory,  
  
qryFinal.intCategoryOrder,  
qryFinal.dtMonth,  
qryFinal.intProp,  
qryFinal.mnyMonthActual,  
qryFinal.mnyMTDActual,  
qryFinal.mnyMonthBudget,  
qryFinal.mnyMTDBudget,  
tblProperties.strAlias  
FROM  
(  
SELECT   
tblGLRowTemplate.strTemplate,   
tblGLGroup.intGroup,  
tblGLGroup.strGroup,   
tblGLRowItem.intOrder,  
tblGLCategory.intCategory,  
tblGLCategory.strCategory,  
  
  
tblGLCategory.intOrder AS intCategoryOrder,  
qryCategoryData.dtMonth,  
qryCategoryData.intProp,  
SUM(CASE   
  WHEN tblGLGroup.fNegate = 1 THEN ISNULL(qryCategoryData.mnyMonthActual, 0) * -1  
  ELSE ISNULL(qryCategoryData.mnyMonthActual, 0)  
END) AS mnyMonthActual,  
SUM(CASE  
  WHEN tblGLGroup.fNegate = 1 THEN ISNULL(qryCategoryData.mnyMTDActual, 0) * -1  
  ELSE ISNULL(qryCategoryData.mnyMTDActual, 0)  
END) AS mnyMTDActual,  
SUM(ISNULL(qryCategoryData.mnyMonthBudget, 0)) AS mnyMonthBudget,  
SUM(ISNULL(qryCategoryData.mnyMTDBudget, 0)) AS mnyMTDBudget  
FROM  
tblGLRowItem  
INNER JOIN tblGLRowTemplate ON tblGLRowItem.intTemplate = tblGLRowTemplate.intTemplate  
INNER JOIN tblGLGroup ON tblGLRowItem.intGroup = tblGLGroup.intGroup  
INNER JOIN tblGLCategory ON tblGLRowItem.intItem = tblGLCategory.intItem  
LEFT JOIN  
(  
SELECT   
qryPreExclude.intCategory,  
qryPreExclude.dtMonth,  
qryPreExclude.intProp,  
qryPreExclude.mnyMonthActual,  
qryPreExclude.mnyMTDActual,  
qryPreExclude.mnyMonthBudget,  
qryPreExclude.mnyMTDBudget  
FROM   
(  
SELECT   
tblGLCategory.intCategory,  
tblGLCategory.strCategory,  
tblGLCategory.intItem,  
tblGLCategory.intOrder,  
tblGLAccount.intAccount,  
tblGLAccount.strAcctCode AS strAccountAcctCode,  
tblGLAccount.strSegment AS strAccountSegment,  
tblGLAccount.fStoresOnly,  
tblGLAccount.fExclude,  
qryBudgetAndActual.*  
FROM tblGLCategory WITH (NOLOCK)  
INNER JOIN tblGLAccount WITH (NOLOCK) ON tblGLCategory.intCategory = tblGLAccount.intCategory  
CROSS JOIN   
(  
SELECT  
CASE WHEN qryActual.intProp IS NOT NULL THEN qryActual.intProp ELSE qryBudget.intProp END AS intProp,  
CASE WHEN qryActual.strAcctCode IS NOT NULL THEN qryActual.strAcctCode ELSE qryBudget.strAcctCode END AS strAcctCode,  
CASE WHEN qryActual.strSegment IS NOT NULL THEN qryActual.strSegment ELSE qryBudget.strSegment END AS strSegment,  
CASE WHEN qryActual.dtMonth IS NOT NULL THEN qryActual.dtMonth ELSE qryBudget.dtMonth END AS dtMonth,  
qryActual.fStore,  
qryBudget.mnyMonthBudget,  
qryBudget.mnyMTDBudget,  
qryActual.mnyMonthActual,  
qryActual.mnyMTDActual  
FROM  
(  
SELECT  
intProp,  
dtMonth,  
NULL AS strSegment,  
strAcctCode,  
SUM(CASE WHEN dtMonth = @dtMonth THEN mnyBudget ELSE 0 END) AS mnyMonthBudget,  
SUM(mnyBudget) AS mnyMTDBudget  
FROM   
tblAcumaticaBudgets WITH (NOLOCK)  
WHERE   
intProp In(@intProp) AND   
dtMonth BETWEEN DATEFROMPARTS(DATEPART(yyyy, @dtMonth), 1, 1) AND @dtMonth  
GROUP BY intProp, dtMonth, strAcctCode  
)qryBudget  
FULL JOIN  
(  
SELECT  
intProp,  
dtMonth,  
strSegment,  
strAcctCode,  
fStore,  
SUM(CASE WHEN qryAct.dtMonth = @dtMonth THEN qryAct.mnyActual ELSE 0 END) AS mnyMonthActual,  
SUM(qryAct.mnyActual) AS mnyMTDActual  
FROM  
(  
SELECT  
intProp,  
dtMonth,  
RIGHT('0000' + CAST(intProp as varchar), 4) AS strSegment,  
strAcctCode,  
1 AS fStore,  
SUM(mnyDebit) - SUM(mnyCredit) AS mnyActual  
FROM  
tblAcumaticaGL WITH (NOLOCK)  
WHERE  
intProp IN(@intProp) and  
dtMonth BETWEEN DATEFROMPARTS(DATEPART(yyyy, @dtMonth), 1, 1) AND @dtMonth  
GROUP BY intProp, dtMonth, strAcctCode  
)qryAct  
GROUP BY intProp, dtMonth, strSegment, strAcctCode, fStore  
)qryActual ON qryBudget.dtMonth = qryActual.dtMonth AND qryBudget.strSegment = qryActual.strSegment AND qryBudget.strAcctCode = qryActual.strAcctCode  
)qryBudgetAndActual  
WHERE  
tblGLCategory.fActive = 1 AND tblGLAccount.fExclude = 0 AND  
CASE  
  WHEN tblGLAccount.fStoresOnly = 0 AND tblGLAccount.strSegment IS NULL AND tblGLAccount.strAcctCode = qryBudgetAndActual.strAcctCode THEN 1  
  WHEN tblGLAccount.fStoresOnly = 0 AND tblGLAccount.strSegment IS NOT NULL AND tblGLAccount.strAcctCode = qryBudgetAndActual.strAcctCode AND tblGLAccount.strSegment = qryBudgetAndActual.strSegment THEN 1  
  WHEN tblGLAccount.fStoresOnly = 1 AND tblGLAccount.strAcctCode = qryBudgetAndActual.strAcctCode AND qryBudgetAndActual.fStore = 1 THEN 1  
  ELSE 0  
END = 1  
)qryPreExclude  
LEFT JOIN tblGLAccount tblGLAccountExclude ON qryPreExclude.intCategory = tblGLAccountExclude.intCategory AND qryPreExclude.strAcctCode = tblGLAccountExclude.strAcctCode AND qryPreExclude.strSegment = tblGLAccountExclude.strSegment AND tblGLAccountExclude.fExclude = 1  
WHERE tblGLAccountExclude.intAccount IS NULL  
)qryCategoryData ON tblGLCategory.intCategory = qryCategoryData.intCategory  
WHERE  tblGLRowItem.fActive = 1 AND tblGLCategory.fActive = 1  
and tblGLCategory.strCategory in('Payroll', 'Payroll - Overtime','Payroll PTO')  
and tblGLRowTemplate.intTemplate IN (22)  
  
GROUP BY   
tblGLRowTemplate.strTemplate,   
tblGLGroup.intGroup,  
tblGLGroup.strGroup,   
tblGLRowItem.intOrder,  
tblGLCategory.intCategory,  
tblGLCategory.strCategory,  
tblGLCategory.intOrder,  
qryCategoryData.dtMonth,  
qryCategoryData.intProp  
  
)qryFinal  
INNER JOIN tblProperties ON qryFinal.intProp = tblProperties.intProp  
WHERE  
qryFinal.dtMonth = @dtMonth   
  
  
  
ORDER BY qryFinal.intProp, qryFinal.dtMonth, qryFinal.intCategoryOrder,strCategory ASC][1]  

Answer 1

Order By Case  
  When strCategory = 'Payroll' Then 1  
  When strCategory = 'PTO' Then 2  
  When strCategory = 'OT' Then 3 End  
  

Tom

Answer 2

select ...

From yourtable

Order By Len(strCategory) DESC

Answer 3

Hi @Jannette Jones ,
I did a test in SSRS and when I query with the following statement, I get a table without custom sorting.

Now I use 'CASE WHEN' to add sorting to the dataset:

SELECT        [Order], Name, Total  
FROM            Order_A  
ORDER BY  
CASE WHEN Name = 'Orange' THEN 1   
           WHEN Name = 'Grape' THEN 2  
           WHEN Name = 'Watermelon' THEN 3   
           WHEN Name = 'Apple' THEN 4  
           WHEN Name = 'Mango' THEN 5   
           ELSE 6   
END  

In the end, I get the table sorted as expected.
Design：


Preview:

If I misunderstand what you mean, please feel free to correct me.
Best Regards,
Joy

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.