Microsoft SQL Server forum

Author

Query Critique

rcamarda

2005-07-19, 8:24 pm

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_SES
SION_ID,
NEW_STUDENT = CASE WHEN SESSION_ID=STUDENT_S
TARTING_SESSION_ID
THEN (1) ELSE (0) END
FROM (
select
SESSION_ID,
STUDENT_ID,
CAMPUS_ID ,
STUDENT_COUNT ,
STUDENT_STARTING_SES
SION_ID
FROM
(
select
SESSION_ID,
STUDENT_ID,
CAMPUS_ID = (SELECT STUDENT_CAMPUS_ID FROM
D_BI_STUDENT WHERE A. STUDENT_SKEY=D_BI_ST
UDENT.STUDENT_SKEY) ,
STUDENT_COUNT = DAY0_CLASS_COUNT,
(select student_starting_ses
sion_id from
f_bi_student_statist
ics where A.student_id =
f_bi_student_statist
ics.student_id) as 'STUDENT_STARTING_SE
SSION_ID'
from f_bi_registration_tr
acking_summary A
) AS X
WHERE STUDENT_COUNT > 0
GROUP BY SESSION_ID, STUDENT_ID, CAMPUS_ID, STUDENT_COUNT,
STUDENT_STARTING_SES
SION_ID
) AS Y
) AS Z
GROUP BY SESSION_ID, CAMPUS_ID

--CELKO--

2005-07-20, 1:23 pm

The only base tables in this query are " f_bi_student_statist
ics" and
" f_bi_registration_tr
acking_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.

rcamarda

2005-07-20, 1:23 pm

The code works, just wonding if all the nesting is 'normal' in SQL eez.

CREATE TABLE "dbo"." F_BI_Student_Statist
ics"
(
"STUDENT_ID" VARCHAR(20) NULL,
"STUDENT_SKEY" INTEGER NULL,
" STUDENT_STARTING_SES
SION_ID" VARCHAR(10) NULL,
" STUDENT_LAST_ATTENDE
D_SESSION_ID" VARCHAR(10) NULL,
" STUDENT_NEXT_REG_SES
SION_ID" VARCHAR(10) NULL,
" STUDENT_NEXT2_REG_SE
SSION_ID" VARCHAR(10) NULL,
" STUDENT_CURRENT_REG_
SESSION_ID" VARCHAR(10) NULL,
" STUDENT_LATEST_REG_S
ESSION_ID" VARCHAR(10) NULL,
" STUDENT_STARTING_SES
SION_SKEY" INTEGER NULL,
" STUDENT_LAST_ATTENDE
D_SESS_SKEY" INTEGER NULL,
" STUDENT_NEXT_REG_SES
SION_SKEY" INTEGER NULL,
" STUDENT_NEXT2_REG_SE
SSION_SKEY" INTEGER NULL,
" STUDENT_CURRENT_REG_
SESSION_SKEY" INTEGER NULL,
" STUDENT_LATEST_REG_S
ESSION_SKEY" INTEGER NULL
)
;

CREATE TABLE "dbo"." F_BI_Registration_Tr
acking_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_COUN
T" INTEGER NULL,
"DAY0_CLASS_COUNT" INTEGER NULL,
" AFTER_D0_CLASS_COUNT
" INTEGER NULL,
" BEFORE_D0_ONLINE_CLA
SS_COUNT" INTEGER NULL,
"ALL_CLASS_COUNT" INTEGER NULL,
" DAY0_ONLINE_CLASS_CO
UNT" INTEGER NULL,
" AFTER_D0_ONLINE_CLAS
S_COUNT" INTEGER NULL,
" ALL_ONLINE_CLASS_COU
NT" 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_Tr
acking_Summary" (
"STUDENT_ID" )
;

CREATE INDEX SESSION_ID ON "dbo"." F_BI_Registration_Tr
acking_Summary" (
"SESSION_ID" )
;

CREATE INDEX FULL_CLASS_ID ON
"dbo"." F_BI_Registration_Tr
acking_Summary" ( "FULL_CLASS_ID" )
;

CREATE INDEX CAMPUS_ID ON "dbo"." F_BI_Registration_Tr
acking_Summary" (
"CAMPUS_ID" )
;

CREATE INDEX CLASS_ID ON "dbo"." F_BI_Registration_Tr
acking_Summary" (
"CLASS_ID" )
;

CREATE INDEX SESSION_SKEY ON "dbo"." F_BI_Registration_Tr
acking_Summary"
( "SESSION_SKEY" )
;

CREATE INDEX CLASS_CAMPUS_SKEY ON
"dbo"." F_BI_Registration_Tr
acking_Summary" ( "CLASS_CAMPUS_SKEY" )
;

CREATE INDEX STUDENT_ID ON "dbo"." F_BI_Student_Statist
ics" (
"STUDENT_ID" )
;

CREATE INDEX STUDENT_SKEY ON "dbo"." F_BI_Student_Statist
ics" (
"STUDENT_SKEY" )
;

CREATE INDEX STARTING_SESSION_ID ON "dbo"." F_BI_Student_Statist
ics" (
" STUDENT_STARTING_SES
SION_ID" )
;

CREATE INDEX LAST_ATTENDED_SESSIO
N_ID ON
"dbo"." F_BI_Student_Statist
ics" ( " STUDENT_LAST_ATTENDE
D_SESSION_ID" )
;

CREATE INDEX NEXT_REG_SESSION_ID ON "dbo"." F_BI_Student_Statist
ics" (
" STUDENT_NEXT_REG_SES
SION_ID" )
;

CREATE INDEX NEXT2_REG_SESSION_ID
ON "dbo"." F_BI_Student_Statist
ics" (
" STUDENT_NEXT2_REG_SE
SSION_ID" )
;

CREATE INDEX CURRENT_REG_SESSION_
ID ON "dbo"." F_BI_Student_Statist
ics"
( " STUDENT_CURRENT_REG_
SESSION_ID" )
;

CREATE INDEX LATEST_REG_SESSION_I
D ON "dbo"." F_BI_Student_Statist
ics" (
" STUDENT_LATEST_REG_S
ESSION_ID" )
;

CREATE INDEX STARTING_SESSION_SKE
Y ON "dbo"." F_BI_Student_Statist
ics" (
" STUDENT_STARTING_SES
SION_SKEY" )
;

CREATE INDEX LAST_ATTENDED_SESS_S
KEY ON "dbo"." F_BI_Student_Statist
ics"
( " STUDENT_LAST_ATTENDE
D_SESS_SKEY" )
;

CREATE INDEX NEXT_REG_SESSION_SKE
Y ON "dbo"." F_BI_Student_Statist
ics" (
" STUDENT_NEXT_REG_SES
SION_SKEY" )
;

CREATE INDEX NEXT2_REG_SESSION_SK
EY ON "dbo"." F_BI_Student_Statist
ics"
( " STUDENT_NEXT2_REG_SE
SSION_SKEY" )
;

CREATE INDEX CURRENT_REG_SESSION_
SKEY ON
"dbo"." F_BI_Student_Statist
ics" ( " STUDENT_CURRENT_REG_
SESSION_SKEY" )
;

CREATE INDEX LATEST_REG_SESSION_S
KEY ON "dbo"." F_BI_Student_Statist
ics"
( " STUDENT_LATEST_REG_S
ESSION_SKEY" )
;

Razvan Socol

2005-07-20, 8:23 pm

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_s
tarting_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_st
udent.student_skey) ,
student_count = day0_class_count,
student_starting_ses
sion_id = (
SELECT student_starting_ses
sion_id
FROM f_bi_student_statist
ics
WHERE a.student_id = f_bi_student_statist
ics.student_id
)
FROM f_bi_registration_tr
acking_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

Sponsored Links