Check the Following Queries...
-------------------------------------------------------------
First Creating 2 tables..
CREATE TABLE T1 (A INT, B1 INT, B2 INT)
CREATE TABLE T2 (A INT, B INT)
Insert Values In T1 Table..
insert into T1 values(11,65,55)
insert into T1 values(45,45,6)
insert into T1 values(14,9,98)
insert into T1 values(45,24,31)
insert into T1 values(133,5,4)
SELECT * FROM T1
Insert Values In T2 Table..
insert into T2 values(11,55)
insert into T2 values(45,6)
insert into T2 values(14,9)
insert into T2 values(24,31)
insert into T2 values(133,4)
SELECT * FROM T2
-----------------------------------------------------
- Subquery Using BETWEEN Statement
SELECT *
FROM T1
WHERE (SELECT SUM(T2.B) FROM T2 WHERE T2.A = T1.A) BETWEEN T1.B1 AND T1.B2
SELECT *
FROM T1
WHERE (SELECT SUM(T2.B) FROM T2 WHERE T2.A = T1.A) >= T1.B1 AND
(SELECT SUM(T2.B) FROM T2 WHERE T2.A = T1.A) <= T1.B2
SELECT Q.A, Q.B1, Q.B2
FROM
(
SELECT *, (SELECT SUM(T2.B) FROM T2 WHERE T2.A = T1.A) SUM_B
FROM T1
) Q
WHERE SUM_B BETWEEN Q.B1 AND Q.B2
SELECT T1.*
FROM T1 CROSS APPLY (SELECT SUM(T2.B) SUM_B FROM T2 WHERE T2.A = T1.A) Q
WHERE Q.SUM_B BETWEEN T1.B1 AND T1.B2
SELECT T1.*
FROM T1, (SELECT T2.A, SUM(T2.B) SUM_B FROM T2 GROUP BY T2.A) Q
WHERE T1.A = Q.A AND Q.SUM_B BETWEEN T1.B1 AND T1.B2
All Queries has Same Following Output..
----------------------------------------------------------------
- Subquery Using CASE Statement
SELECT *,
CASE (SELECT SUM(T2.B) FROM T2 WHERE T2.A = T1.A)
WHEN T1.B1 THEN 'B1'
WHEN T1.B2 THEN 'B2'
ELSE NULL
END CASE_B
FROM T1
SELECT Q.A, Q.B1, Q.B2,
CASE Q.SUM_B
WHEN Q.B1 THEN 'B1'
WHEN Q.B2 THEN 'B2'
ELSE NULL
END CASE_B
FROM
(
SELECT *, (SELECT SUM(T2.B) FROM T2 WHERE T2.A = T1.A) SUM_B
FROM T1
) Q
SELECT T1.*,
CASE Q.SUM_B
WHEN T1.B1 THEN 'B1'
WHEN T1.B2 THEN 'B2'
ELSE NULL
END CASE_B
FROM T1 CROSS APPLY (SELECT SUM(T2.B) SUM_B FROM T2 WHERE T2.A = T1.A) Q
Output :
----------------------------------------------------
SELECT T1.*,
CASE Q.SUM_B
WHEN T1.B1 THEN 'B1'
WHEN T1.B2 THEN 'B2'
ELSE NULL
END CASE_B
FROM T1, (SELECT T2.A, SUM(T2.B) SUM_B FROM T2 GROUP BY T2.A) Q
WHERE T1.A = Q.A
------------------------------------------
Sub-queries in CASE and BETWEEN Statements in SQL SERVER 2008
