How to write a case where operator when there are overlaps in T-SQL
3 answers
Try it like this ...
IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL
DROP TABLE #TestData;
CREATE TABLE #TestData (
ID INT NOT NULL PRIMARY KEY,
nCount int NOT NULL
);
INSERT #TestData (ID, nCount) VALUES
(1, 10), (2, 15), (3, 22), (4, 23),
(5, 25), (6, 27), (7, 30);
--=====================================
WITH
cte_Totals AS (
SELECT
Total = SUM(td.nCount),
Small = SUM(CASE WHEN td.nCount < 25 THEN td.nCount ELSE 0 END),
Large = SUM(CASE WHEN td.nCount >= 25 THEN td.nCount ELSE 0 END)
FROM
#TestData td
)
SELECT
x.[Group],
x.[Count]
FROM
cte_Totals t
CROSS APPLY (VALUES (1, 'Total', t.Total), (2, 'Small', t.Small), (3, 'Large', t.Large) ) x (SortBy, [Group],[Count])
ORDER BY
x.SortBy;
Results...
Group Count
----- -----------
Total 152
Small 70
Large 82
NTN, Jason
+4
source to share
Late answer ( keep accepted as ) but I would like to introduce a concept that might be more useful down the line.
I maintain a generic level table. Below is a simplified example, but you can deduce the aggregation levels from the code and put it in a table ... everything changes and you can serve multiple wizards.
Sample data
Declare @YourTable table (ID int,[Count] int)
Insert Into @YourTable values
(1, 10), (2, 15), (3, 22), (4, 23), (5, 25), (6, 27), (7, 30)
Declare @Tier table (Tier varchar(50),Seq int,Title varchar(50),R1 int,R2 int)
Insert Into @Tier values
('MyGroup',1,'Total',0,99999)
,('MyGroup',2,'Small',0,25)
,('MyGroup',3,'Large',25,99999)
Actual request
Select T.Title
,[Count] = sum(D.[Count])
From @Tier T
Join @YourTable D on (T.Tier='MyGroup' and D.Count >= T.R1 and D.Count<T.R2)
Group By T.Title,T.Seq
Order By T.Seq
Returns
Title Count
Total 152
Small 70
Large 82
EDIT. There are many ways to build this
Example
Declare @YourTable table (ID varchar(50),[Count] int)
Insert Into @YourTable values
('Tywin', 10), ('Tywin', 15), ('Tyrion', 22), ('Bran', 23), ('Ned', 25), ('John', 27), ('Robb', 30)
Declare @Tier table (Tier varchar(50),Seq int,Title varchar(50),R1 int,R2 int,C1 varchar(50),C2 varchar(50))
Insert Into @Tier values
('MyGroup',1,'Total' ,null,null,'a','z')
,('MyGroup',2,'Group 1',null,null,'Tywin,Tyrion',null)
,('MyGroup',3,'Group 2',null,null,'Bran,Ned,John,Robb',null)
Select T.Title
,[Count] = sum(D.[Count])
From @Tier T
Join @YourTable D on T.Tier='MyGroup' and (D.ID between C1 and C2 or patindex('%,'+D.ID+',%',','+C1+',')>0)
Group By T.Title,T.Seq
Order By T.Seq
Returns
Title Count
Total 152
Group 1 47
Group 2 105
+3
source to share