469,579 Members | 1,214 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,579 developers. It's quick & easy.

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_ID

Jul 23 '05 #1
3 926
The 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.

Jul 23 '05 #2
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" )
;

Jul 23 '05 #3
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

Jul 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

By using this site, you agree to our Privacy Policy and Terms of Use.