Subqueries in CASE and BETWEEN Statements in SQL SERVER 2008

Hi Frendz...
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