Alex Alex - 2 months ago 9
SQL Question

SQL TOP 10 Order by Count in Sub Query with IF

I have a query with some parameters and based on the parameters I need to run a different query each time.

For example:

declare @daterangeint
set @daterange = '7'
If @date range !=0 (Select TOP 10 value,Count * from a where columna = ab group by value)
ELSE (Select TOP 10 value,Count * from a where columnb = abcd group by value)


I need the top 10 counts in descending order for each query otherwise there will be too many rows returned. I understand you can't use order by within the query and order by doesn't seem to work outside of it so how would I tackle this.

This is my actual query

declare @assignedto nvarchar(256)
declare @createdrange int
set @assignedto = 'user'
set @createdrange = '7'

IF (@createdrange != 0) (
SELECT TOP 10
COUNT(sr.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C) AS 'wicount',
(u.Department_312201FE_C1B3_E95A_01DF_E132E9BD3EC9, 'No Department') as Department

FROM MTV_System$WorkItem$Incident sr
LEFT JOIN RelationshipView r ON r.SourceEntityId = sr.BaseManagedEntityId AND r.RelationshipTypeId = 'DFF9BE66-38B0-B6D6-6144-A412A3EBD4CE' AND r.IsDeleted ='0'
LEFT JOIN MTV_System$Domain$User u ON r.TargetEntityId = u.BaseManagedEntityId
Inner JOIN RelationshipView assr ON assr.SourceEntityId = sr.BaseManagedEntityId AND assr.RelationshipTypeId = '15E577A3-6BF9-6713-4EAC-BA5A5B7C4722' AND assr.IsDeleted ='0'
Inner JOIN MTV_System$Domain$User assu ON assr.TargetEntityId = assu.BaseManagedEntityId AND assu.UPN_7641DFF7_7A20_DC04_FC1C_B6FA8715DA02 = @assignedto
WHERE sr.ResolvedDate_D2A4C73F_01B8_29C5_895B_5BE4C3DFAC4E >=dateadd(day,datediff(day,0,GetutcDate())-@createdrange,0)
GROUP BY u.Department_312201FE_C1B3_E95A_01DF_E132E9BD3EC9
) ELSE (
SELECT TOP 10
COUNT(sr.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C) AS 'wicount',
COALESCE(u.Department_312201FE_C1B3_E95A_01DF_E132E9BD3EC9, 'No Department') as Department

FROM MTV_System$WorkItem$Incident sr
LEFT JOIN RelationshipView r ON r.SourceEntityId = sr.BaseManagedEntityId AND r.RelationshipTypeId = 'DFF9BE66-38B0-B6D6-6144-A412A3EBD4CE' AND r.IsDeleted ='0'
LEFT JOIN MTV_System$Domain$User u ON r.TargetEntityId = u.BaseManagedEntityId
Inner JOIN RelationshipView assr ON assr.SourceEntityId = sr.BaseManagedEntityId AND assr.RelationshipTypeId = '15E577A3-6BF9-6713-4EAC-BA5A5B7C4722' AND assr.IsDeleted ='0'
Inner JOIN MTV_System$Domain$User assu ON assr.TargetEntityId = assu.BaseManagedEntityId AND assu.UPN_7641DFF7_7A20_DC04_FC1C_B6FA8715DA02 = @assignedto
WHERE sr.Status_785407A9_729D_3A74_A383_575DB0CD50ED NOT IN ('2B8830B6-59F0-F574-9C2A-F4B4682F1681','BD0AE7C4-3315-2EB3-7933-82DFC482DBAF')
Group By u.Department_312201FE_C1B3_E95A_01DF_E132E9BD3EC9
)

Answer

Just add the switching criteria into the Where clause and use only one query:

SELECT TOP 10 
  COUNT(sr.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C) wicount,
       (u.Department_312201FE_C1B3_E95A_01DF_E132E9BD3EC9, 'No Department') Department 
FROM MTV_System$WorkItem$Incident sr 
LEFT JOIN RelationshipView r 
   ON r.SourceEntityId = sr.BaseManagedEntityId 
     AND r.RelationshipTypeId = 'DFF9BE66-38B0-B6D6-6144-A412A3EBD4CE' 
     AND  r.IsDeleted ='0'
LEFT JOIN  MTV_System$Domain$User u 
   ON r.TargetEntityId = u.BaseManagedEntityId
JOIN RelationshipView assr 
   ON assr.SourceEntityId = sr.BaseManagedEntityId 
      AND assr.RelationshipTypeId = '15E577A3-6BF9-6713-4EAC-BA5A5B7C4722'
      AND  assr.IsDeleted ='0'
JOIN MTV_System$Domain$User assu 
   ON assr.TargetEntityId = assu.BaseManagedEntityId 
     AND assu.UPN_7641DFF7_7A20_DC04_FC1C_B6FA8715DA02 = @assignedto        
WHERE (@createdrange = '7' And
      sr.ResolvedDate_D2A4C73F_01B8_29C5_895B_5BE4C3DFAC4E 
         >= dateadd(day,datediff(day,0,GetutcDate())-@createdrange,0)) Or
      (@createdrange != '7' And
      sr.Status_785407A9_729D_3A74_A383_575DB0CD50ED 
         NOT IN ('2B8830B6-59F0-F574-9C2A-F4B4682F1681',
                 'BD0AE7C4-3315-2EB3-7933-82DFC482DBAF'))
GROUP BY u.Department_312201FE_C1B3_E95A_01DF_E132E9BD3EC9
Order By [Whatever ordering you want to determine top 10]
Comments