Query Critique

I just finished a new query where I summarized detail information. I'm
wondering if I did this really awkwardly or is this a common way to
write SQL? I've cross referenced the end results and the data seems
consistant, so I am happy with the results.
TIA

SELECT
SESSION_ID,
CAMPUS_ID,
SUM(STUDENT_COUNT) AS STUDENT_COUNT,
SUM(NEW_STUDENT) AS NEW_STUDENT_COUNT
FROM (
SELECT
SESSION_ID,
STUDENT_ID,
CAMPUS_ID ,
STUDENT_COUNT ,
STUDENT_STARTING_SESSION_ID,
NEW_STUDENT = CASE WHEN SESSION_ID=STUDENT_STARTING_SESSION_ID
THEN (1) ELSE (0) END
FROM (
select
SESSION_ID,
STUDENT_ID,
CAMPUS_ID ,
STUDENT_COUNT ,
STUDENT_STARTING_SESSION_ID
FROM
(
select
SESSION_ID,
STUDENT_ID,
CAMPUS_ID = (SELECT STUDENT_CAMPUS_ID FROM
D_BI_STUDENT WHERE A.STUDENT_SKEY=D_BI_STUDENT.STUDENT_SKEY) ,
STUDENT_COUNT = DAY0_CLASS_COUNT,
(select student_starting_session_id from
f_bi_student_statistics where A.student_id =
f_bi_student_statistics.student_id) as 'STUDENT_STARTING_SESSION_ID'
from f_bi_registration_tracking_summary A
) AS X
WHERE STUDENT_COUNT > 0
GROUP BY SESSION_ID, STUDENT_ID, CAMPUS_ID, STUDENT_COUNT,
STUDENT_STARTING_SESSION_ID
) AS Y
) AS Z
GROUP BY SESSION_ID, CAMPUS_IDThe only base tables in this query are "f_bi_student_statistics" and
"f_bi_registration_tracking_summary" and we have no idea what they look
like. I would think that this can be made much easier. Please post
DDL, so that people do not have to guess what the keys, constraints,
Declarative Referential Integrity, data types, etc. in your schema are.
Sample data is also a good idea, along with clear specifications. It
is very hard to debug code when you do not let us see it.|||The code works, just wonding if all the nesting is 'normal' in SQL eez.

CREATE TABLE "dbo"."F_BI_Student_Statistics"
(
"STUDENT_ID" VARCHAR(20) NULL,
"STUDENT_SKEY" INTEGER NULL,
"STUDENT_STARTING_SESSION_ID" VARCHAR(10) NULL,
"STUDENT_LAST_ATTENDED_SESSION_ID" VARCHAR(10) NULL,
"STUDENT_NEXT_REG_SESSION_ID" VARCHAR(10) NULL,
"STUDENT_NEXT2_REG_SESSION_ID" VARCHAR(10) NULL,
"STUDENT_CURRENT_REG_SESSION_ID" VARCHAR(10) NULL,
"STUDENT_LATEST_REG_SESSION_ID" VARCHAR(10) NULL,
"STUDENT_STARTING_SESSION_SKEY" INTEGER NULL,
"STUDENT_LAST_ATTENDED_SESS_SKEY" INTEGER NULL,
"STUDENT_NEXT_REG_SESSION_SKEY" INTEGER NULL,
"STUDENT_NEXT2_REG_SESSION_SKEY" INTEGER NULL,
"STUDENT_CURRENT_REG_SESSION_SKEY" INTEGER NULL,
"STUDENT_LATEST_REG_SESSION_SKEY" INTEGER NULL
)
;

CREATE TABLE "dbo"."F_BI_Registration_Tracking_Summary"
(
"STUDENT_ID" VARCHAR(20) NULL,
"SESSION_ID" VARCHAR(6) NULL,
"FULL_CLASS_ID" VARCHAR(15) NULL,
"CAMPUS_ID" VARCHAR(10) NULL,
"ACTIVITY_DT" DATETIME NULL,
"ACTIVITY_CODE" VARCHAR(1) NULL,
"ACTIVITY_COUNT" INTEGER NULL,
"BEFORE_D0_CLASS_COUNT" INTEGER NULL,
"DAY0_CLASS_COUNT" INTEGER NULL,
"AFTER_D0_CLASS_COUNT" INTEGER NULL,
"BEFORE_D0_ONLINE_CLASS_COUNT" INTEGER NULL,
"ALL_CLASS_COUNT" INTEGER NULL,
"DAY0_ONLINE_CLASS_COUNT" INTEGER NULL,
"AFTER_D0_ONLINE_CLASS_COUNT" INTEGER NULL,
"ALL_ONLINE_CLASS_COUNT" INTEGER NULL,
"CLASS_DROP_DT" DATETIME NULL,
"CLASS_DROP_COUNT" INTEGER NULL,
"CLASS_ADD_DT" DATETIME NULL,
"CLASS_ADD_COUNT" INTEGER NULL,
"BANDED_ID" NUMERIC(19,0) NULL,
"CLASS_ID" VARCHAR(15) NULL,
"SESSION_SKEY" INTEGER NULL,
"CLASS_CAMPUS_SKEY" INTEGER NULL,
"STUDENT_SKEY" INTEGER NULL
)
;
CREATE INDEX STUDENT_ID ON "dbo"."F_BI_Registration_Tracking_Summary" (
"STUDENT_ID" )
;

CREATE INDEX SESSION_ID ON "dbo"."F_BI_Registration_Tracking_Summary" (
"SESSION_ID" )
;

CREATE INDEX FULL_CLASS_ID ON
"dbo"."F_BI_Registration_Tracking_Summary" ( "FULL_CLASS_ID" )
;

CREATE INDEX CAMPUS_ID ON "dbo"."F_BI_Registration_Tracking_Summary" (
"CAMPUS_ID" )
;

CREATE INDEX CLASS_ID ON "dbo"."F_BI_Registration_Tracking_Summary" (
"CLASS_ID" )
;

CREATE INDEX SESSION_SKEY ON "dbo"."F_BI_Registration_Tracking_Summary"
( "SESSION_SKEY" )
;

CREATE INDEX CLASS_CAMPUS_SKEY ON
"dbo"."F_BI_Registration_Tracking_Summary" ( "CLASS_CAMPUS_SKEY" )
;

CREATE INDEX STUDENT_ID ON "dbo"."F_BI_Student_Statistics" (
"STUDENT_ID" )
;

CREATE INDEX STUDENT_SKEY ON "dbo"."F_BI_Student_Statistics" (
"STUDENT_SKEY" )
;

CREATE INDEX STARTING_SESSION_ID ON "dbo"."F_BI_Student_Statistics" (
"STUDENT_STARTING_SESSION_ID" )
;

CREATE INDEX LAST_ATTENDED_SESSION_ID ON
"dbo"."F_BI_Student_Statistics" ( "STUDENT_LAST_ATTENDED_SESSION_ID" )
;

CREATE INDEX NEXT_REG_SESSION_ID ON "dbo"."F_BI_Student_Statistics" (
"STUDENT_NEXT_REG_SESSION_ID" )
;

CREATE INDEX NEXT2_REG_SESSION_ID ON "dbo"."F_BI_Student_Statistics" (
"STUDENT_NEXT2_REG_SESSION_ID" )
;

CREATE INDEX CURRENT_REG_SESSION_ID ON "dbo"."F_BI_Student_Statistics"
( "STUDENT_CURRENT_REG_SESSION_ID" )
;

CREATE INDEX LATEST_REG_SESSION_ID ON "dbo"."F_BI_Student_Statistics" (
"STUDENT_LATEST_REG_SESSION_ID" )
;

CREATE INDEX STARTING_SESSION_SKEY ON "dbo"."F_BI_Student_Statistics" (
"STUDENT_STARTING_SESSION_SKEY" )
;

CREATE INDEX LAST_ATTENDED_SESS_SKEY ON "dbo"."F_BI_Student_Statistics"
( "STUDENT_LAST_ATTENDED_SESS_SKEY" )
;

CREATE INDEX NEXT_REG_SESSION_SKEY ON "dbo"."F_BI_Student_Statistics" (
"STUDENT_NEXT_REG_SESSION_SKEY" )
;

CREATE INDEX NEXT2_REG_SESSION_SKEY ON "dbo"."F_BI_Student_Statistics"
( "STUDENT_NEXT2_REG_SESSION_SKEY" )
;

CREATE INDEX CURRENT_REG_SESSION_SKEY ON
"dbo"."F_BI_Student_Statistics" ( "STUDENT_CURRENT_REG_SESSION_SKEY" )
;

CREATE INDEX LATEST_REG_SESSION_SKEY ON "dbo"."F_BI_Student_Statistics"
( "STUDENT_LATEST_REG_SESSION_SKEY" )
;|||Although I don't have any idea about what this query is supposed to
return, I think your query can be safely rewritten as:

SELECT
session_id,
campus_id,
SUM(student_count) AS student_count,
SUM(CASE WHEN session_id=student_starting_session_id
THEN 1 ELSE 0 END) AS new_student_count
FROM (
SELECT DISTINCT
session_id,
student_id,
campus_id = (
SELECT student_campus_id
FROM d_bi_student
WHERE a.student_skey=d_bi_student.student_skey) ,
student_count = day0_class_count,
student_starting_session_id = (
SELECT student_starting_session_id
FROM f_bi_student_statistics
WHERE a.student_id = f_bi_student_statistics.student_id
)
FROM f_bi_registration_tracking_summary a
WHERE day0_class_count > 0
) AS z
GROUP BY session_id, campus_id

There may be further improvements, but without understanding the
meaning of your tables, that's the best I can do.

Razvan