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

QUERY BY USER

I have a datagrid with rows that pertain to multiple users. I want to
write a query statement so that whomever the CURRENT logged-in
user is, he can go to the datagrid page and it will show ONLY rows
that pertain to him (programmatic security). To accomplish this,
I have a column in the datatable that references each user's
username. So I need a query statement that will compare the table's
'username' column to the current logged-in user. Something like...

SELECT... FROM [VendorEquipment]
WHERE [UserName] = Context.Current.User.Name

...or something like this. Question is, can someone help on how to
syntax this. Also, do I need anything special in the login form or in
web.config to 'pave the way' for this query to work.

Thx much.
Reid C.after a successfull login,you can store the username in sessions...|||Thanks for the reply...but I am a novice and will probably need more to clear up
the picture...can you suggest how I can syntax my SELECT command to accomplish
the above-stated objective and is there something I need to do via sessions to
enable this--and where would I do that?

Thx much.
Reid C.|||i believe you are checking for user authorization when the user logs in right ?

just put the username into session after the validation is successfull

session("username")=username

and frm any page
you can get it back as

dim uname as string
uname=session("username")

so when you query the db for the records you can do something like

strsql=select * From table where username='" & uname & "'"

and bind the datagrid.

HTH|||Thanks again...this sounds like what I'm looking for...just two
more questions due to my relative inexperience:

1) the statement: session("username")=username
you suggested...does that need to be in the login form
like in the "sub loginBtn_click" subroutine? and if so where in
the subroutine does it need to go...is the placement of the
statement critical?

2) is "username" interchangeable in this operation with "userid" ?
because I have written my registration form and login with a
"userid" instead of a "username"...but I will change this if it is
important to have "username" for forms authentication.

Thx.
Reid C.|||yep you can have any variable in session you want..username,userid...doesnt matter.
right after you check the userid with the password and if the query returns as a valid user, store the userid in sessions...and you should be able to access it frm any page...

HTH|||Thank you ...do I place the session stmt in global.asax?

like this, for instance:

Sub Session_OnStart()
 Session("userId")=userId
End Sub

?

Thx.
Reid C.|||no...you put it in ur login.aspx where ever you validate the authenticity of the user...|||o.k., thanks again, ...but I am a complete novice and have been building pages
using WebMatrix, so sorry to be dense...but do I place the "session..." statement
in the "Function GetUser..." or in my "Sub LoginBtn_click..." subroutine, or after
that and right before the </script> tag ? I know placement of this statement is
important, I just don't know where?

Thx again.
Reid C.|||if you post your code for the login page..i can tell you where to place it...|||Thanks again for all your help...here is the code for the login (minus the html) and
I've got the statement placed right before the end </script> tag...tell me if you see
a glaring case of needing to move it to a more appropriate space.

<script runat="server">
Function GetUser(ByVal userId As String, ByVal userPassword As String) As System.Data.DataSet
 Dim connectionString As String = "server='(local)'; trusted_connection=true; database='VSdatastore'"
 Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
 Dim queryString As String = "SELECT [Users].* FROM [Users] WHERE (([Users].[UserId] = @.UserId) AND ([Users].[U"& _
 "serPassword] = @.UserPassword))"
 Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
 dbCommand.CommandText = queryString
 dbCommand.Connection = dbConnection
 Dim dbParam_userId As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_userId.ParameterName = "@.UserId"
 dbParam_userId.Value = userId
 dbParam_userId.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_userId)
 Dim dbParam_userPassword As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_userPassword.ParameterName = "@.UserPassword"
 dbParam_userPassword.Value = userPassword
 dbParam_userPassword.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_userPassword)
 Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter
 dataAdapter.SelectCommand = dbCommand
 Dim dataSet As System.Data.DataSet = New System.Data.DataSet
 dataAdapter.Fill(dataSet)
 Return dataSet
End Function
Sub LoginBtn_Click(Sender As Object, E As EventArgs)
 If Page.IsValid Then
 Dim userDS As New System.Data.DataSet
 userDS = GetUser(UserId.Text, UserPassword.Text)
 If userDS.Tables(0).Rows.Count = 1 Then
 FormsAuthentication.RedirectFromLoginPage(UserId.Text, false)
 Else
 Msg.Text = "Invalid UserId or Password: Please try again"
 End If
 End If
End Sub
session("UserId")=UserId
</script

Thx again.
Reid C.|||i think this should work...

If Page.IsValid Then
 Dim userDS As New System.Data.DataSet
 userDS = GetUser(UserId.Text, UserPassword.Text)
 If userDS.Tables(0).Rows.Count = 1 Then
 'valid user...
 session("UserId")=UserId
 FormsAuthentication.RedirectFromLoginPage(UserId.Text, false)
 Else
 Msg.Text = "Invalid UserId or Password: Please try again"
 End If
 End If
End Sub
session("UserId")=UserId

|||In your revision you have the "session..." statement right after
the "If userDS.Tables(0).Rows.Count = 1 Then" statement which
made sense as soon as I saw it. But you also have the same
"session..." statement again after the "End Sub" statement. Is
this just a typo on your part from copying and pasting what I
sent you, or does it need to be there?

I am assuming it is a copy&paste oversight...unless you answer
back that it needs to be repeated...

Thx much again for your help.
Reid C.|||See if you can review the code for my datagrid page and see where I'm going wrong.
One thing, the "UserId" I need to compare is in the datatable as "PropUserId" because
I also have a column in the table "VendorUserId" and need to reference both. So I
need to compare the session "UserId" with the table's "PropUserId" column and I've
added a statement in the "Function GetVendorEquipment..." that says the "PropUserId"
column is equivalent to "UserId"...but I am getting a blank table on the page when I
run it.

<script runat="server"
 Function GetVendorEquipment() As System.Data.DataSet
 Dim connectionString As String = "server='(local)'; trusted_connection=true; database='VSdatastore'"
 Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
 dim UserId as String
 dim PropUserId as String
 PropUserId=session("UserId")
 Dim queryString As String = "SELECT [VendorEquipment].* FROM [VendorEquipment] WHERE [PropUserId]='" & UserId & "'"
 Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
 dbCommand.CommandText = queryString
 dbCommand.Connection = dbConnection
 Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter
 dataAdapter.SelectCommand = dbCommand
 Dim dataSet As System.Data.DataSet = New System.Data.DataSet
 dataAdapter.Fill(dataSet)
 Return dataSet
 End Function
 Sub Page_Load(sender As Object, e As EventArgs)
 DataGrid1.DataBind()
 End Sub
</script>

Thx again for your help.
Reid C.|||ur userid seems to be a number...
so you have to use

Dim queryString As String = "SELECT [VendorEquipment].* FROM [VendorEquipment] WHERE [PropUserId]=" & PropUserId

also one other suggestion...bind your datagrid within if not ispostback...loop

Sub Page_Load(sender As Object, e As EventArgs)
 if not ispostback then
 DataGrid1.DataBind()
 end if
End Sub

HTH