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 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.
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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:...
|
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.
|
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...
|
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
-...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
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....
| |