|
yes i did, here is the stored procedure:
i create it by command builder
CREATE PROCEDURE dbo.InsertQueryoperation
(
@Award_num int,
@patient_name varchar(40),
@Operation_date smalldatetime,
@Duration smalldatetime,
@Surgean_name varchar(40),
@Type_of_operation varchar(20),
@Anaesthiologist_name varchar(40),
@Type_of_anaesthesia varchar(20),
@ID_num int
)
AS
SET NOCOUNT OFF
INSERT INTO Operation_details
(Award_num, patient_name, Operation_date, Duration, Surgean_name, Type_of_operation, Anaesthiologist_name, Type_of_anaesthesia, ID_num)
VALUES (@Award_num,@patient_name,@Operation_date,@Duration,@Surgean_name,@Type_of_operation,@Anaesthiologist_name,@Type_of_anaesthesia,@ID_num);
SELECT Operation_ID, Award_num, patient_name, Operation_date, Duration, Surgean_name, Type_of_operation, Anaesthiologist_name, Type_of_anaesthesia, surgean_ID, Anaesthiologist_ID, ID_num FROM Operation_details WHERE (Operation_ID = SCOPE_IDENTITY())
|
|
|
|
|
CREATE PROCEDURE dbo.InsertQueryoperation
(
@Award_num int output,
@patient_name varchar(40),
@Operation_date smalldatetime,
@Duration smalldatetime,
@Surgean_name varchar(40),
@Type_of_operation varchar(20),
@Anaesthiologist_name varchar(40),
@Type_of_anaesthesia varchar(20),
@ID_num int
)
AS
SET NOCOUNT OFF
INSERT INTO Operation_details
(Award_num, patient_name, Operation_date, Duration, Surgean_name, Type_of_operation, Anaesthiologist_name, Type_of_anaesthesia, ID_num)
VALUES (@Award_num,@patient_name,@Operation_date,@Duration,@Surgean_name,@Type_of_operation,@Anaesthiologist_name,@Type_of_anaesthesia,@ID_num);
SELECT Operation_ID, Award_num, patient_name, Operation_date, Duration, Surgean_name, Type_of_operation, Anaesthiologist_name, Type_of_anaesthesia, surgean_ID, Anaesthiologist_ID, ID_num FROM Operation_details WHERE (Operation_ID = SCOPE_IDENTITY())
i hv change ur parameter @award_num int output
pest this code to ur store procedure.
try this
no knowledge in .net
|
|
|
|
|
|
Hey! Im an sql beginner and i have got a small problem with a query:
select avg(dbo.GetProcessingTime(InterchangeId)) from Messages
i want to call the function only once per InterchangeId
like
select distinct InterchangeId from Messages -- Run the avg(Function(...)) on this result only
Any suggestions?
betonglasermur.FeedDwarf(pur_is, 17);
ProcessStartupInfo.AintNotCreateNoWindow = (false && !true) != (true || false) ? false == true ? true : false : (true != false && false);
Morgonen är tröttmans mecka
|
|
|
|
|
select distinct avg(dbo.GetProcessingTime(InterchangeId)) from Messages
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
Hm yeah but that selects the avg(.. only once?
and avg(distinct functioncall(... gets the result of the functioncall distinct(what if it is the same for two different Ids?)
if I understand it correctly 
betonglasermur.FeedDwarf(pur_is, 17);
ProcessStartupInfo.AintNotCreateNoWindow = (false && !true) != (true || false) ? false == true ? true : false : (true != false && false);
Morgonen är tröttmans mecka
|
|
|
|
|
Can you post here data how are stored on table, and write result which you want to get.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
I also have a function to get the processing time for an interchange, it looks like this:
ALTER function [dbo].[GetProcessingTime] ( @interchangeId uniqueidentifier )
returns int
AS
BEGIN
DECLARE @recDate DateTime
DECLARE @sendDate DateTime
DECLARE @return int
-- Get receivetime
select top 1 @recDate = mf.[Event/Timestamp] from dbo.Ports p INNER JOIN
BizTalkDTADb.dbo.dtav_MessageFacts mf
INNER JOIN
dbo.Messages m ON mf.[MessageInstance/InstanceID] = m.MessageId ON
p.Name = mf.[Event/Port] INNER JOIN
dbo.PortTypes pt ON p.FK_PortType = pt.Id
WHERE pt.Description='ReceivePort' and m.[InterchangeId] = @interchangeId
order by mf.[Event/Timestamp] asc --first
select top 1 @sendDate = mf.[Event/Timestamp] from dbo.Ports p INNER JOIN
BizTalkDTADb.dbo.dtav_MessageFacts mf
INNER JOIN
dbo.Messages m ON mf.[MessageInstance/InstanceID] = m.MessageId ON
p.Name = mf.[Event/Port] INNER JOIN
dbo.PortTypes pt ON p.FK_PortType = pt.Id
WHERE pt.Description='SendPort' and m.[InterchangeId] = @interchangeId
order by mf.[Event/Timestamp] desc -- latest
set @return = DATEDIFF(millisecond ,@recDate,@sendDate)
return @return
END
It gets the time difference between the first and the last message in the interchange in milliseconds.
Now what i want to do is:
get the average processing time for each unique interchange
hence:
ALTER proc [dbo].[GetAverageProcessingTime](@startDate datetime, @endDate datetime)
as
select avg(distinct dbo.GetProcessingTime(InterchangeId)) from Messages where WrittenDate between @startDate and @endDate am confused about the distinct in the last procedure, it feels.. wrong 
betonglasermur.FeedDwarf(pur_is, 17);
ProcessStartupInfo.AintNotCreateNoWindow = (false && !true) != (true || false) ? false == true ? true : false : (true != false && false);
Morgonen är tröttmans mecka
|
|
|
|
|
try to group by InterchangeId like:
select distinct avg(dbo.GetProcessingTime(InterchangeId)) from Messages<br />
Group By InterchangeId <br />
Having WrittenDate <br />
between @startDate and @endDate
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
I think i solved it! 
ALTER proc [dbo].[GetAverageProcessingTime](@startDate datetime, @endDate datetime)
as
begin
DECLARE @InterchangeIds TABLE
(
InterchangeId uniqueIdentifier
)
insert into @InterchangeIds (InterchangeId)
select distinct m.InterchangeId from Messages m where WrittenDate between @startDate and @endDate
select avg(dbo.GetProcessingTime(InterchangeId)) from @InterchangeIds
end
thanks
betonglasermur.FeedDwarf(pur_is, 17);
ProcessStartupInfo.AintNotCreateNoWindow = (false && !true) != (true || false) ? false == true ? true : false : (true != false && false);
Morgonen är tröttmans mecka
|
|
|
|
|
Cool,sorry for my unusefull answers 
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
Can someone please tell me the commands for the following:
Deleting a Primary Key
Adding a Primary Key
Turning a Primary Key off and On for Nulls, duplicates, etc.
Thanks,
Michael
|
|
|
|
|
MAW30 wrote: Deleting a Primary Key
Adding a Primary Key
To either delete the field altogether or add a field, look up the ALTER TABLE SQL command.
MAW30 wrote: Turning a Primary Key off and On for Nulls, duplicates, etc.
You can't do this - Primary keys can never be NULL nor does it allow duplicates.
|
|
|
|
|
So.
I'm trying to match multiple codes (in one record) to their descriptions as listed in another table.
So I have...
TABLE 1 with columns: Key, Code1, Code2
TABLE 2 with columns: Code, Description
...which are tables I have no control over.
I want to do an inner join so that my new table will have columns...
Key, Code1, Code1Description, Code2, Code2Description.
Its easy enought to match one
SELECT Table1.*, Table2.description AS Code1Description<br />
FROM Table1 INNER JOIN<br />
Table2 ON Table1.Code1=Table2.Code
How do i expand this to 2 or 3 or 4 different codes???
Please?
|
|
|
|
|
The same way you did it for the first column. ie: adding a second would give:
SELECT t1.*, c1.description AS Code1Description, c2.description AS Code2Description
FROM Table1 t1
INNER JOIN Table2 c1 ON t1.Code1=c1.Code
INNER JOIN Table2 c2 ON t1.Code2=c2.Code With this you should be able to add as many code you would like.
Wout Louwers
|
|
|
|
|
Thank you very much.
Never would have got that on my own.
But on some servers the description table is empty, and i'm not getting any return rows. How can i make it optional, to say
Return row for key='123' and add the descriptions IF they exist in the descriptions table?
|
|
|
|
|
Hi;
my english is bad sorry 
I have a table
review (note, #code_matiere, #num_inscription)
I created a pivot table from it.
and I showed it in a gridview
I add column moyennemodule which is the average of 4 matter and column avggeneral average of moduls.
I mean General sql following:
<code>
SELECT Num_Inscription, [1] AS '1', [2] AS '2', [3] AS '3' , (([ 1 ]*(select COEFF from MATIERE where code_mat=1))+<b>([2]*(select COEFF from MATIERE where code_mat=2)))/ ((select COEFF from MATIERE where code_mat=1)+(select COEFF from MATIERE where code_mat=2))AS moymod1 ,........as moymod2,..............as moymod3,...........asmoymod5</b>
FROM
(SELECT Code_mat, Num_Inscription, Note
FROM Examen ) p
PIVOT
(
SUM(Note)
FOR Code_Mat IN
( [1], [2], [3])
) AS pvt</code>
i have 5 module I must repeat the part in bold and 5 times ,and the overall average which is sum (moymod) / 8 I have to redo the code in bold 8 times so de sql is very long.
Do you have a solution.
thanks
|
|
|
|
|
Hi;
I have to leave the as it no solution? I am using sql server2005.
Thanks
|
|
|
|
|
Hi,
I have training Database which contains 3 tables, STUDENTS,COURSES and STUDENT_COURSE which contains the F.k for the Students and Courses tables. The STUDENT_COURSE Contains StudentID,CourseID as Primary Key
I need to write Stored Procedure that Return All Students attends 'MCSE' and 'MCSD' Courses.
The following are The Attributes of all 3 tables
1- Students
A. StudentID P.K
B. StudentName
C. Mobile
D.Address
2- Courses
A. CourseID P.k
B. CourseName
3- Student_Course
A.StudentID P.k, F.k
B.CourseID P.k, F.k
regards
|
|
|
|
|
select students.*,Courses.*,Student_course.* <br />
from students,Courses,Student_Course<br />
where Student_Course.CourseID = Courses.CourseID and <br />
(Courses.CourseName = 'MCSE' or Courses.CourseName='MCSD')
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
When i tried to update the contents of a table in my database i am getting the "Could not save; currently locked by another user" exception.
I tried closing the connection other places where ever it is used.
But still the same problem is coming? what should i do? Please help me?
|
|
|
|
|
Hi,
on the pc hosting my sqlserver I have a file, i.e C:\temp\myimage.jpg.
On my sql I have a table like this:
Images{data (verbinary(max)};
I need my sql server to load c:\temp\myimage.jpg and store it in table Images
Somebody knows a way to do this?
Life is not short... the problem is only how you organize yourself
|
|
|
|
|
Dear All,
Iam new to Sql server 2005 reporting services.I have a small problem in that.
I have a column which consist of the flowing values
1
1.1
1.1.1
1.1.2
1.2
1.2.1
1.2.2
1.3
1.3.1
1.3.2
2
2.1
2.1.1
2.1.2
2.2
2.2.1
2.2.2
2.3
3
3.1
3.12
3.13
3.2
3.21
3.22
So I want to make toggle like this.
Means initially it should display
1,2,3 in a collapsid mode
when i click one it should display
1.1,1.2,1.3 when i click 1.1
then it should display 1.1.1,1.1.2
Like this. Please help me on this
1
1.1
1.1.1
1.1.2
1.2
1.2.1
1.2.2
1.3
1.3.1
1.3.2
2
2.1
2.1.1
2.1.2
2.2
2.2.1
2.2.2
2.3
2.3.1
2.3.2
3
3.1
3.1.1
3.1.2
3.2
3.2.1
3.2.2
Regard's
Veeresh
i want to join this group
|
|
|
|
|
Double post. Has one just below.
|
|
|
|
|
Dear All,
Iam new to Sql server 2005 reporting services.I have a small problem in that.
I have a column which consist of the flowing values
1
1.1
1.1.1
1.1.2
1.2
1.2.1
1.2.2
1.3
1.3.1
1.3.2
2
2.1
2.1.1
2.1.2
2.2
2.2.1
2.2.2
2.3
3
3.1
3.12
3.13
3.2
3.21
3.22
So I want to make dril down like this please help me on this..
1
1.1
1.1.1
1.1.2
1.2
1.2.1
1.2.2
1.3
1.3.1
1.3.2
2
2.1
2.1.1
2.1.2
2.2
2.2.1
2.2.2
2.3
2.3.1
2.3.2
3
3.1
3.1.1
3.1.2
3.2
3.2.1
3.2.2
i want to join this group
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
