Need To Identify Student With Multiple Id Numbers
Jan 17, 2008
My working table:
CREATE TABLE [dbo].[Name_ID4](
[id_num] [decimal](18, 0) IDENTITY(1,1) NOT NULL,
[student_last_name] [varchar](30) NULL,
[student_first_name] [varchar](30) NULL,
[student_middle_init] [varchar](1) NULL
[local_student_id] [bigint] NULL,
I need to identify only the students with more than one local_student_id and the associated local_student_id's.
I can identify the students with two id's but not the associated id's
SELECT COUNT(*) AS Dup_Num,
rtrim(student_last_name),
rtrim(student_first_name),
rtrim(student_middle_init),
from Name_ID4
group by
student_last_name,
student_first_name,
student_middle_init,
having (count(*) > 1)
order by student_last_name, student_first_name, student_middle_init
Thanks
View 4 Replies
ADVERTISEMENT
Feb 1, 2007
I have a report with a column which contains either a string such as "N/A" or a number such as 12. A user exports the report to Excel. In Excel the numbers are formatted as text.
I already tried to set the value as CDbl which returns error for the cells containing a string.
The requirement is to export the column to Excel with the numbers formatted as numbers and the strings such as "N/A' in the same column as string.
Any suggestions?
View 1 Replies
View Related
Jun 20, 2007
I'm new to programming with the ReportViewer object and this issue has me stumped: it appears if you have some optional parameters in your report, and a way to refresh that report with different parameter values, the report "remembers" parameter values from previous calls to SetParameters() on subsequent renderings of the report. If a parameter is included in a call to ServerReport.SetParameters() on the first rendering, but not included in a subsequent call and the report is re-rendered, the previous value of the parameter (rather than the default value) appears to be used.
Here's a snippet of some test code I wrote within an ASP.NET 2.0 test application:
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
this.rptViewer.ServerReport.ReportServerUrl = new Uri(this.txtReportServerUrl.Text);
this.rptViewer.ServerReport.ReportPath = this.txtReportPath.Text;
}
}
protected void btnViewRpt_Click(object sender, EventArgs e)
{
ReportParameter[] rptParams = GetReportParameters();
this.rptViewer.ServerReport.SetParameters(rptParams);
this.rptViewer.ServerReport.Refresh();
}
private ReportParameter[] GetReportParameters()
{
int paramCount = 0;
ReportParameter[] retVal;
string emptyVal = null;
if (txtName.Text != "") paramCount++;
if (txtAddress.Text != "") paramCount++;
if (txtZip.Text != "") paramCount++;
retVal = new ReportParameter[paramCount];
paramCount = 0;
if (txtName.Text != "")
retVal[paramCount++] = new ReportParameter("Name", txtName.Text);
if (txtAddress.Text != "")
retVal[paramCount++] = new ReportParameter("Address", txtAddress.Text);
if (txtZip.Text != "")
retVal[paramCount++] = new ReportParameter("Zip", txtZip.Text);
return retVal;
}
The test report was written to simply echo back the values of the parameters that are specified. The report definition allows NULL to be specified for the parameters.
The test app was written so if I enter a blank value for Name, Address or Zip, the corresponding parameter does not get created in C# and does not get sent to the report server. If I view the report with all three values (parameters) filled in, I see the parameters echoed back to me in my simple report as expected. If I clear the parameter values the first time the report is rendered, none are sent to the report server and I get no values echoed back in my report, also as expected. I can change the values and click on the View Report button and see the new values for the parameters as expected. However, if I clear any previously-specified parameters and click on View Report, the previously-specified values for the ones that are now cleared are still displayed by the report.
So my question is: once a parameter has been sent to the report, how does one "unsend" it on subsequent refreshes? I know I can create the parameter and set its value to null...but I have a situation here where that can cause errors. It'd be better if I could simply leave out the unspecified parameters and have the report refresh and render as if I were rendering it for the first time.
Any suggestions?
View 7 Replies
View Related
Jan 15, 2004
Hi!
Want 2 store Multiple room numbers of a hotel in a single column using SQL Server2k.
Can anybody tell me how to do it??
Thanx.
View 2 Replies
View Related
Jan 10, 2005
i'm currently a student in computer information sciences, and i wanted to give my studying some more focus thus choosing dba. now i'd like to dba but be as broad as possible when i first start out my career. i know some programming languages and recently finished a C# book for doing .net development and a ASP.net book for scripting. but where should i go from here? should i just start studying ms sql server? what about a microsoft certification? i'm very curious to know where the next step in my studying should be, so anyone who was in my shoes, i'd love to hear any information you have to offer. TIA
View 14 Replies
View Related
Jul 20, 2005
Why does M$ Query Analyzer display all numbers as positive, no matterwhether they are truly positive or negative ?I am having to cast each column to varchar to find out if there areany negative numbers being hidden from me :(I tried checking Tools/Options/Connections/Use Regional Settings bothon and off, stopping and restarting M$ Query Analyer in betwixt, butno improvement.Am I missing some other option somewhere ?
View 7 Replies
View Related
Mar 11, 2008
I have a table with a column ID of ContentID. The ID in that column is all NULLs. I need a way to change those nulls to a number. It does not matter what type of number it is as long as they are different. Can someone point me somewhere with a piece of T-SQL that I could use to do that. There are over 24000 rows so cursor change will not be very efficient.
Thanks for any help
View 6 Replies
View Related
Jul 23, 2005
Hi,I'm finishing up a beginning SQL class where we learned on an Oracledatabase and the transition to working on SQL Server is easy. The next moreadvanced course will be in PL/SQL, but I know I will be working on SQLServer in the workplace, so my question is if I should take this course.Will I benefit from the basic philosophies that will be covered, or will itjust make a transition for me more difficult? Will it be partly a waste oftime and money and I'd be better served getting a book and self teachingmyself? I know that in a greater sense learning something isn't necessarilya waste, but I mean from the perspective of my goal of being able to use SqlServer, will this course be useful?thanks.
View 10 Replies
View Related
Oct 18, 2007
I am starting a class on SQL Server, and my school wants me to builddb's either on campus or remotely at my home connecting to their SQLServer Enterprise.Will I be okay to use Developer Edition at home to connect with anEnterprise-licensed SQL Server on campus, to build databases,sprocs, .net apps that connect to the database stored on my school'sserver?I would rather avoid the Express edition.thanks
View 1 Replies
View Related
Jan 18, 2008
I'm a kid and I'd like to know what kind of work a database administrator does. It would be great to learn from a real database administrator how you spend your typical day at the office. What are some of your main duties? Thanks so much in advance.
View 1 Replies
View Related
Feb 12, 2013
I want to calculate average of grades of each student and get the highest one with SQL command.
I have 2 tables:
Students:
*StudentId
*StudentName
___________
Grades:
*StudentId
*Grade
___________
I need to calculate average of each student and then get the highest.
My try:
Code:
SELECT Students.StudentId,Students.StudentName,AVG(Grades.Grade) AS avg_grade FROM Students s JOIN Grades g ON Grades.StudentId =Students.StudentId
GROUP BY Students.StudentId, Students.StudentName
ORDER BY avg_grade
LIMIT 1 FROM Students;
I encounter problem with this code, maybe it's Completely wrong...
View 5 Replies
View Related
Aug 4, 2013
I have created a table:
CREATE TABLE [dbo].[Student](
[StudentNumber] [varchar](50) NOT NULL,
[Name] [char](50) NOT NULL,
[Contact] [int] NOT NULL,
[Address] [char](50) NOT NULL,
[DateOfBirth] [datetime] NOT NULL,
[YearEnrolled] [int] NOT NULL,
[Year] [int] NOT NULL;
And insert the following data:
INSERT INTO Student VALUES('IT123456X', 'Ahmad Adam','05-18-1997', '33 Mangis Rod', 19970518, 2013, 1);
INSERT INTO Student VALUES('IT334455U', 'Mary Tan', '01-23-1996', '51 Koon Seng Road', 23-01-1996, 2012, 1);
INSERT INTO Student VALUES('BS123456X', 'Samuel Lee', '03-30-1997', '2 Joo Chiat Lane', 30-03-1997, 2013, 1);
INSERT INTO Student VALUES('BS234234Z', 'Nathaniel Koh', '12-08-1997', '5 Stll Road', 08-12-1997, 2013, 1);
INSERT INTO Student VALUES('BS987987F', 'Siti Faridah', '07-04-1995', '3 Duku Road', 04-07-1995, 2011, 3)
How do i calculate the age and how can i group by the StudentNumber like 'IT%'
View 3 Replies
View Related
Jun 9, 2015
We have SQL database now a days we are planing to update our students ID Records but i don't know the exact query for this. Table format is below
StudentID Name
500132253/Prep Aslam
112344883/Prep Ali
451132537/Prep Ahmed
Now i want to update only the grade from prep to KG-1 without update the registration number only prep to kg-1. in one column there is student register number and after slash the grade.
View 6 Replies
View Related
May 25, 2015
I have a table which shows data in below format
Now I need that only the max date record should be shown for every student as shown below...
View 8 Replies
View Related
Sep 15, 2006
I am stumped on a set-based approach for this one.
A cursor approach is straightforward enough, but i want to avoid that.
Here's my table:
create table StudentScores
(
id int primary key identity(1,1),
student_id int not null,
score int not null
)
with some sample data:
insert into StudentScores (student_id, score)
select 1, 10 union all
select 1, 29 union all
select 1, 50 union all
select 1, 53 union all
select 1, 45 union all
select 1, 10 union all
select 1, 29 union all
select 1, 50 union all
select 1, 53 union all
select 1, 45 union all
select 1, 88 union all
select 2, 23 union all
select 2, 54 union all
select 2, 55 union all
select 2, 34 union all
select 2, 56 union all
select 2, 78 union all
select 2, 23 union all
select 2, 54 union all
select 2, 55 union all
select 2, 34 union all
select 2, 56 union all
select 2, 78 union all
select 2, 23 union all
select 2, 54 union all
select 2, 55 union all
select 2, 34 union all
select 2, 56 union all
select 2, 78 union all
select 2, 98
What I want is, for each student, what is their 90th percentile score?
For a given single student, one possibility would be:
declare @studentid int
set @studentid = 2
select top 1 @studentid as student_id, a.score as [90th percentile score]
from
(
select top 90 percent score from StudentScores
where student_id = @studentid order by score asc
) as a
order by a.score desc
But I want this for all students, and not use a cursor.
Any ideas?
Thanks!
View 6 Replies
View Related
Feb 20, 2012
I am trying to find out the the Average number of contact hours per student. in Reporting Services 2005. The contact hours is the in the Totaltime field
Is this formula correct
=Sum(Fields!TotalTime.Value)/Avg(Fields!TotalTime.Value) is in the =Fields!StateServices.Value Group
My groups are
=Fields!Student_ID.Value
=Fields!StateCategory.Value
=Fields!StateServices.Value
Code:
SELECT Student_ind.[Student ID], ParticipantActivity.ActivityDate, School_tbl.[Studentschool Id], School_tbl.schoolID, ParticipantActivity.TotalTime,
ParticipantActivity.Services, ParticipantActivity.Activity, Student_ind.SSID, ParticipantActivity.StateCategory, ParticipantActivity.StateServices
FROM ParticipantActivity INNER JOIN
School_tbl INNER JOIN
Student_ind ON School_tbl.[Student ID] = Student_ind.[Student ID] ON ParticipantActivity.[Student ID] = Student_ind.[Student ID]
[code]....
View 6 Replies
View Related
Apr 4, 2006
Dose MS sell SQlSerever with student rate? If so, which website do Ibuy from?
View 10 Replies
View Related
Apr 5, 2012
I have a csv that contains attendance records that I get daily from a 3rd party grade book solution. I need to import directly into the attend table in our student database.
Code:
Attend File-"1112","0021","404550","20120402","ABU","2300000","06","05"
The file is setup as follows, School Year, school number, student_id, absence date, absence code, course number, section number.
I need to check the student schedule to see if they are scheduled for that class when the import runs. So if they had a schedule change in the middle of the day it won't post attend to a dropped class.
I have done something similar to this before with the way I export teachers out to our grade book. I have it check the master schedule to see if the teacher is teaching at least one class, that way it won't export tutors and office staff to the grade book. I used the script below to do that but not sure who to apply it to a bulk insert.
Code:
Script Used to export teachers note last four lines, checks master
USE [GSchool]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[Code] ....
Secondly I need to check the date of the record and overwrite a record if one already exists for that exact course and section for that student, I need this because if they make changes to a previous day from absence un excused to excused I need to get rid of the unexcused by overwriting it.
One more thing that would be nice but is optional, is there a way to send log of errors on the import via email?
View 3 Replies
View Related
Nov 21, 2015
i am having problem putting this query to calculate students grade using the condition and legend bellow.
WHEN EXAMS BETWEEN 75 AND 100 THEN 'A'
WHEN EXAMS BETWEEN 70 AND 74 THEN 'AB'
WHEN EXAMS BETWEEN 65 AND 69 THEN 'B'
WHEN EXAMS BETWEEN 60 AND 64 THEN 'BC'
[code]...
View 7 Replies
View Related
Oct 9, 2007
Hi Everybody
I've one table named Student. Here is data
Name
Subject
Mraks
Prasad
English
80
Tushar
English
79
Sunil
English
78
Prasad
Geometry
80
Tushar
Geometry
81
Sunil
Geometry
79
Prasad
History
82
Tushar
History
81
Sunil
History
80
Now I want to write a query that displays student with subjects and marks who secured highest marks in each subject.
I want to output like this
Name
Subject
Mraks
Prasad
English
80
Tushar
Geometry
81
Prasad
History
82
So will anybody help me to write a sql query that acheive the same output
View 5 Replies
View Related
May 14, 2015
In my query, I am showing Student Record and also want to retrieve the last class attended by the student using JOIN to the table (ClassHistory).
Master
ID Student_Name
1 Arindam
2 Brenda
3 Callie
(ClassHistory)
ID Class Year
1 4 2003
1 5 2004
1 6 2005
2 4 2003
2 5 2004
3 4 2003
DESIRED RESULTSET
ID Student_Name Class Record
1 Arindam 6
2 Brenda 5
3 Callie 4
The result is like - Arindam last class was Class 6, while Brenda was in Class 5 and Callie was in class 4.
Please note that the query will b part of a larger query and the Class record to be retrieved from ClassHistory if possible need to be retrieved through JOIN.
View 7 Replies
View Related
Feb 21, 2007
I have an 'ID' column. I'm up to about ID number 40000, but not all are in use, so ID 4354 might not be in any row. I want a list of all numbers which aren't in use. I want to write something like this:
select [numbers from 0 to 40000] where <number> not in (select distinct id from mytable)
but don't know how. Any clues?
View 1 Replies
View Related
Mar 27, 2007
I'm trying to write data to excel from an ssis component to a excel destination.
Even thought I'm writing numerics, every cell gets this error with a green tag:
Convert numbers stored as text to numbers
Excel Cells were all pre-formated to accounting 2 decimal, and if i manually type the exact data Im sending it formats just fine.
I'm hearing this a common problem -
On another project I was able to find a workaround for the web based version of excel, by writing this to the top of the file:
<style>.text { mso-number-format:@; } </style>
is there anything I can pre-set in excel (cells are already formated) or write to my file so that numerics are seen as numerics and not text.
Maybe some setting in my write drivers - using sql servers excel destination.
So close.. Thanks for any help or information.
View 1 Replies
View Related
Jul 2, 2015
I have to calculate the Total number of days present and absent for a singel student.
AS of now i have 3 tables.
1.Daily attendance - Columns -[Guid][,AcademyId],[StudentId],[Date],[Status],[Reason]
2.Student details - Columns - [Guid],[FullName],[DOB],[Address]
3.Class Details - Columns - [Guid],[AcademyId],[Class],[Section],[Startdate],Enddate]
So now i have loaded all the data into the table.
I can fetch the counts for total present and absent
Query i have tried is
Declare
@StudentId Uniqueidentifier ='0B2D4D41-8D33-4D79-A981-03E0F093F458'
Begin
select A.StudentId ,A.Date,Count(Date)Total,B.Guid,
[Code] ....
AS result of this query i get the data.Present count and Absent count from date inserted in Dailyattendance tables.
SO my problem is if the student have promoted to next class then by this query it will count the before year also how do i need to calculate the count according to the Class StartDate and Enddate as i mention in the Class Details table what will be the query.
View 7 Replies
View Related
Jan 18, 2008
Has microsoft issued an update so I can install SQL 2005 DE on Windows Vista? Thank You!
View 3 Replies
View Related
Apr 17, 2008
Hi,
How would I know that a dll component I built is in .NET framework 1.0 or 1.1??
annej
View 4 Replies
View Related
May 7, 2003
How do l select a negative value from a column and set it to 'C' and if its a positive value set it to 'D' for debit
l've written it in layman's terms .Is it wise to use substring or they is a better method......
select
case
when amount is (negative) then 'C'
when amount is (positive) then 'D'
else
end
from table_Tran
View 2 Replies
View Related
Oct 11, 2007
Greetings.
I'm currently on a company with an ms sql server 2000. I'm looking into the indexes and tables to see if there are some bottlenecks there but the LogicalFramentation is very low in the index I have searched.
However, this table has a logicalFragmentation of 99,9215698242188 which I get when I do DBCC SHOWCONTIG ([TInsurance]) WITH TABLERESULTS. Is that a value to be trusted or not to be trusted since this does not check an index? If it is, how do I defrag a table? I know only how to defrag an index. (example: DBCC INDEXDEFRAG (MFSSEK,[TInsurance], PK_InsuranceID) )
Tipps, suggestions, help, all is very wellcome! :-)
DBCC SHOWCONTIG scanning 'TInsurance' table...
Table: 'TInsurance' (2051694557); index ID: 0, database ID: 17
TABLE level scan performed.
- Pages Scanned................................: 1275
- Extents Scanned..............................: 225
- Extent Switches..............................: 224
- Avg. Pages per Extent........................: 5.7
- Scan Density [Best Count:Actual Count].......: 71.11% [160:225]
- Extent Scan Fragmentation ...................: 74.67%
- Avg. Bytes Free per Page.....................: 520.0
- Avg. Page Density (full).....................: 93.58%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
View 9 Replies
View Related
Nov 20, 2006
I have seen several examples explaining the fact that a tablecontaining a field for each day of the week is for the most part anarray. An specific example is where data representing worked hours isstored in a table.CREATE TABLE [hoursWorked] ([id] [int] NOT NULL ,[location_id] [tinyint] NOT NULL,[sunday] [int] NULL ,[monday] [int] NULL ,[tuesday] [int] NULL ,[wednesday] [int] NULL ,[thursday] [int] NULL ,[friday] [int] NULL ,[saturday] [int] NULL)I had to work with a table with a similar structure about 7 years agoand I remember that writing code against the table was pretty close toHell on earth.I am now looking at a table that is similar in nature - but different.CREATE TABLE [blah] ([concat_1_id] [int] NOT NULL ,[concat_2_id] [int] NOT NULL ,[code_1] [varchar] (30) NOT NULL ,[code_2] [varchar] (20) NULL ,[code_3] [varchar] (20) NULL ,[some_flg] [char] (1) NOT NULL) ON [PRIMARY]The value for code_2 and code_3 will be dependently null and they willrepresent similar data in both records (i.e. the value "abc" can existin both fields) . For example if code_2 contains data then code_3 willprobably not contain data.I do not think that this is an array. But with so many rows wherecode_2 and code_3 will be NULL something just does not feel right.I will appreciate your input.
View 1 Replies
View Related
Jul 20, 2005
Does anyone know how to identify the hottest, most active tables in adatabase?We have hundreds of users hitting a PeopleSoft database with hundredsof tables. We are I/O bound on our SAN, and are thinking of puttingthe hottest tables on a solid state (RAM) drive for improvedperformance. Problem is: which are the hottest tables? Would like todo this based on hard data instead of developer/vendor guesses.Any suggestions are much appreciated.
View 2 Replies
View Related
Jul 21, 2004
hi,
for sql server 2000, how can we find the fixpack(service pack) level installed on this sql server?
is there any command, or any gui tool to identify the level?
tnks
View 3 Replies
View Related
Jan 20, 2006
hi,
I have a huge db with many services ,users and applications hitting the db.
Suddenly one of our column is nullified , we are not able track who /how it is done,
Can any one tell be whatz the best way to identify this????
trace(what events to select ), trigger or what????
Thanks,
srini
View 2 Replies
View Related
Sep 24, 2012
there is a Crystal Report run from the Front end Application. The DB used here is SQL Server 2008. I need to know the Stored Procedure used by the report that is been run from the front end. . How shall I do it?
View 6 Replies
View Related
