|
I need to write a script that gets the first time an employee attended a live class, the script I wrote below works but I need to also display the name of the class, but the result set goes out of whack when I include the className field to the select statement. Can someone please tell me how I can display the class name in my result set. The className field is in tblClass. Thanks
SELECT tblAttendance.EmployeeID, vwEmployeeInformation.FullName, MIN(tblAttendance.AttendanceDate) AS FirstAttendanceDate
FROM tblAttendance
INNER JOIN tblClassInstance ON tblAttendance.ClassInstanceID = tblClassInstance.ClassInstanceID
INNER JOIN tblClass ON tblClassInstance.ClassID = tblClass.ClassID
INNER JOIN vwEmployeeInformation ON tblAttendance.EmployeeID = vwEmployeeInformation.EmployeeID
WHERE (tblClass.ClassFormat = 'On Demand') AND (tblAttendance.AttendanceDate BETWEEN '08/10/07' AND GetDate())
GROUP BY tblAttendance.EmployeeID, vwEmployeeInformation.FullName
modified on Friday, February 8, 2008 10:01 AM
|
|
|
|
|
It should go out-of-whack when you add class name since you would then be asking the server to give you the first attendance date of EACH class each student took. I have to assume that a student can take multiple classes in a day and that AttendanceDate does not respect the time of the class (only the date).
Here is a messy start that should work on SQL Server. I used your original query to aquire the necessary root data for finding the first class name. TOP 1 is used in case the student attends more than 1 class that day and time is not embedded in AttendanceDate.
SELECT
EmployeeID,
FullName,
FirstAttendanceDate,
(SELECT TOP 1
tblClass.className
FROM
tblAttendance
INNER JOIN
tblClassInstance
ON (tblAttendance.ClassInstanceID = tblClassInstance.ClassInstanceID)
INNER JOIN
tblClass
ON (tblClassInstance.ClassID = tblClass.ClassID)
WHERE
tblAttendance.EmployeeID = root.EmployeeId,
tblAttendance.AttendanceDate = root.FirstAttendanceDate) AS ClassName
FROM
(SELECT
tblAttendance.EmployeeID,
vwEmployeeInformation.FullName,
MIN(tblAttendance.AttendanceDate) AS FirstAttendanceDate
FROM
tblAttendance
INNER JOIN
tblClassInstance
ON (tblAttendance.ClassInstanceID = tblClassInstance.ClassInstanceID)
INNER JOIN
tblClass
ON (tblClassInstance.ClassID = tblClass.ClassID)
INNER JOIN
vwEmployeeInformation
ON (tblAttendance.EmployeeID = vwEmployeeInformation.EmployeeID)
WHERE
(tblClass.ClassFormat = 'On Demand') AND
(tblAttendance.AttendanceDate BETWEEN '08/10/07' AND GetDate())
GROUP BY
tblAttendance.EmployeeID,
vwEmployeeInformation.FullName) AS Root
|
|
|
|
|
Thanks a lot Michael, just what I needed... I've learnt something new..
|
|
|
|
|
how we can export table into text .csv files using sqlserver enterprise manager
|
|
|
|
|
Google not working for you? There are many results [^] available
|
|
|
|
|
if it i can find in google . iam not asking you .if you know just give me idea otherwise silent. in google i get solution use ems. but with out using ems i am asking
|
|
|
|
|
|
This is killing me...and not slowly.
I have a stored procedure (a) which calls another stored procedure (b).
Stored procedure (b) is a C# stored procedure which simply writes out to a file data in XML format. Internally, it calls...
select fld1, fld2, fld3, fld4, fld5from #tmptable for xml auto, elements .
If I call stored procedure (a) from Query Analyser / SQL Management Studio everything works fine. Perfect.
But....we need this all to run asynchronously. So we used the Service Broker, configured the queues and messages and off we went. All worked as planned except out XML files were empty.
Further investigation showed that if we call
select fld1, fld2, fld3, fld4, fld5from #tmptable - without the 'xml' bits, we got a resultset back. But if we call it with the for xml auto, elements, the reader was empty. No errors are visible in the profiles, but the XmlReader refuses to read.
The binary / extended stored procedure is the same pysical binary that is called from Query analyser that works, but via the Service Broker refuses to do anything XML based. Outputting the data as normal text is cool, but not what we want.
 Any ideas?
so you answer don't be scared of failure
The only failure is never to try
Things You've Never Done - Passenger -2008
|
|
|
|
|
How to Populate Crystal Report dynamically with database and tables?
|
|
|
|
|
Hi,
You can populate Crystal Report dynamically using ADO.Net. This is how you do it using OLEDB, and a table view in MSSQL. The Following code assumes that you have setup your report viewer with the wizard.
-------------------------------------------------------------------------
BEGIN CODE
dataSet21.Clear();
oleDbDataAdapter1.Fill(dataSet21,"your_table_view");
CrystalReport2 custReport = new CrystalReport2();
custReport.SetDataSource(dataSet21);
TableLogOnInfo logOnInfo = new TableLogOnInfo();
logOnInfo = custReport.Database.Tables["your_table_view"].LogOnInfo;
ConnectionInfo connectionInfo = new ConnectionInfo ();
connectionInfo = logOnInfo.ConnectionInfo;
// Set the Connection parameters.
connectionInfo.DatabaseName = yourcatalog;
connectionInfo.ServerName = workstationID;
MessageBox.Show(connectionInfo.ServerName);
//connectionInfo.Password = "yourpassword";
//connectionInfo.UserID = "youruserid";
custReport.Database.Tables ["Vw_Address_Label"].ApplyLogOnInfo(logOnInfo);
crystalReportViewer1.ReportSource = custReport;
crystalReportViewer1.Refresh();
crystalReportViewer1.RefreshReport();
END CODE
-------------------------------------------------------------------------
I hope this helps  .
Regards,
John Adams
ComponentOne LLC
|
|
|
|
|
In my case I am not connecting the database/table with the Crystal Report through the wizard, I want to coonnect the database and table with the crystal report at page_load.
in that case I need to get that code that connect with the database and table with the crystal report.
thanks
|
|
|
|
|
Hi i like to send email from sqlserver2000,
I dont have anyidea abt that,
plz anyone guide me regarding that,
Thanks & Regards,
Mageshh,
please don't forget to vote on the post
|
|
|
|
|
Hi Magesh!
Go Here[^]
May be it will give you some idea about sending mail from SQL Server 2000
Regards.
|
|
|
|
|
hi!
how would i display below rows in correct sequence
Level 1
Level 10
Level 11
Level 12
Level 13
Level 2
Level 3
|
|
|
|
|
uglyeyes wrote: how would i display below rows in correct sequence
Level 1
Level 10
Level 11
Level 12
Level 13
Level 2
Level 3
What do you mean by 'correct sequence'? What sequence do you want to display them in?
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
ORDER BY Cast(Replace(<colname>, 'Level ', '') AS Int)
this will extract only numbers ignoring the string 'Level ' and convert to integer
Regards
KP
|
|
|
|
|
Hi,
I am having problem. I want to sql query to insert a row, but before that i want update in the same table with a condition.
for eg: i am having a table, in that there is a field named default. this default value must true for only one row based on the foreign key. whn i insert a row for tht foreign key, before insert it should check is the default value is true before for that foreign key. if yes then i have to reset that old default value to false and set the new one to true.
id name isdefault fkey
1 aaa true 12
2 aas false 12
3 aer false 12
4 sdf true 17
5 ter false 32
6 zxc false 32
in the above example if i enter row for fkey 12, then the query should reset the previous all default value to false and then inset the new value to true(if the new row have true value) value to new row.
if there is no default true value thn it shold enter the true value to new row.
any one please suggest me a query.
thanks in advance.
Know is Drop, Unknown is Ocean
|
|
|
|
|
Run an UPDATE query against your table before the insert to set the value of 'isdefault' to false for all records with the same 'fkey' value as the record you are inserting. Then run your INSERT query to insert your new row with an 'isdefault' value of true.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
hello once again, haven't solved the tableName problem that i posted a few minutes ago but want to ask one more thing.. .is it possible to have different number of parameters for an sql stored procedure... wat i mean is that is it possible to specify the columns that needs to be selected at run time for a stored procedure...
if so plz provide help
haseeb
|
|
|
|
|
I don't think there is a way to have a variant number of parameters in a sql stored procedure. However you can pass a list via 'array', Check the following article. It might haelp you to get what you want.
http://www.sommarskog.se/arrays-in-sql.html
This may help you too:
http://www.sommarskog.se/share_data.html
|
|
|
|
|
hello
i wanted to make a single stored procedure which takes as parameter the table name from which all the rows should be selected ... in query analyzer i tried
declare @var varchar(30);
set @var='tableName';
select * from @var;
but it won't work... the variable declaration and assignment works fine, but the select statement doesnt work...
Please help in this regard... i also tried making
declare @col_name varchar(30);
set @col_name='studentName';
select @col_name from studentTable;
it works but the rows returned have col_name as it's value and the original value doesn't appear
Plz help
haseeb
|
|
|
|
|
You have to use Dynamic EXECUTE or sp_executesql (recommended). Check Books Online sp_executesql topic. It has a good example.
|
|
|
|
|
Hello everyone,
I'm working on a project that requires extensive searhing and I want to implement demand paging technique for displaying data in the gridview. I've read a few articles showing how to implement the retrieval of n to m rows from Oracle DB but I'm using SQL Server 2005. Can anyone plz tell me how I can implement this functionality.
Thank you
Rocky
Success is a ladder which you can't climb with your hands in your pockets.
|
|
|
|
|
I have not used what is in the ROW_NUMBER() topic of SQL2005 BOL but it should give you some ideas.
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/82fa9016-77db-4b42-b4c8-df6095b81906.htm
modified on Thursday, February 07, 2008 12:23:52 PM
|
|
|
|
|
I have a dataset/datatable that has a column 'name'. The data may have accents as in 'João'.
I need to search for Joao (no accent) and get rows including João.
row 1 - Joao Pedro
row 2 - Maria João
row 3 - João Antonio
datatable.Select("name LIKE '%Joao%'") should return all 3.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
