473,320 Members | 2,164 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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 975
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Saqib Ali | last post by:
Hello All, I m not sure if this is the right place to ask for a critique. If not please refer me to another group. Thanks. I would a criqtique of the following website:...
19
by: TC | last post by:
Are there any good sites or forums for a web critique? I went to alt.html.critique and it's pretty dead.
9
by: bowsayge | last post by:
Inspired by fb, Bowsayge decided to write a decimal integer to binary string converter. Perhaps some of the experienced C programmers here can critique it. It allocates probably way too much...
188
by: christopher diggins | last post by:
I have posted a C# critique at http://www.heron-language.com/c-sharp-critique.html. To summarize I bring up the following issues : - unsafe code - attributes - garbage collection -...
39
by: Eric | last post by:
There is a VB.NET critique on the following page: http://www.vb7-critique.741.com/ for those who are interested. Feel free to take a look and share your thoughts. Cheers, Eric. Ps: for those...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.