|
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
|
|
|
|
|
I cannot get more than two columns using this simple SELECT query.
m_FDRecordset = new FDRecordset();
m_FDRecordset->Open(AFX_DB_USE_DEFAULT_TYPE,
"SELECT [Column1],[Column2],[Column3] FROM [Table] WHERE [Column1] LIKE '" + text + "%'", CRecordset::readOnly );
All I get is "Error retrieving record".
Being a total greenhorn in SQL I am lost.
Could someone give me a hint what is missing.
The query works fine with just two columns selections.
I am using Access as a database.
Here is an addendum to my dilema.
I am getting this error message in my trace.
Invalid character value for cast specification on column number 4
I have copied working column #2 to columns 3 and 4.
They are "text" in Access database.
As soon as I add data to these columns I get this error.
If my columns data are same why I get this error?
PS I am stuck with Access.
Thanks for reading.
Vaclav
The "problem" was in Access column data type - it does not accept
"3.5 MHz" either as text or number. The SELECT than fails.
But "1A" is accepted as text - no problem.
Live and learn!
modified on Wednesday, May 21, 2008 10:54 AM
|
|
|
|
|
Try using Select * from table and inspect the results to make sure the cols are named correctly.
Recommendation - move from Access to SQL Expres, URGENTLY
See this lounge discussion - save yourself some nightmares!
clickety[^]
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
No good, same error message.
What is puzzling is that it returns one or two columns correctly.
Vaclav
|
|
|
|
|
Dear all,
I have a 'Games' table in testxyz db following:
create database testxyz
go
use testxyz
go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Games]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Games]
GO
CREATE TABLE [dbo].[Games] (
[UserID] [int] IDENTITY (1, 1) NOT NULL ,
[UserName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Point] [int] NULL ,
[Dates] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Games] ADD
CONSTRAINT [DF_Users_Dates] DEFAULT (getdate()) FOR [Dates],
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[UserID]
) ON [PRIMARY]
GO
--Insert data:
DELETE FROM [Games]
GO
SET IDENTITY_INSERT[Games] ON
GO
INSERT INTO [dbo].[Games](UserID,UserName,Point,Dates)
VALUES(1,'C.John',10,'5/19/2008 7:20:59 PM')
GO
INSERT INTO [dbo].[Games](UserID,UserName,Point,Dates)
VALUES(2,'C.John',12,'5/18/2008 7:10:59 PM')
GO
INSERT INTO [dbo].[Games](UserID,UserName,Point,Dates)
VALUES(3,'C.John',6,'5/17/2008 7:22:59 PM')
GO
INSERT INTO [dbo].[Games](UserID,UserName,Point,Dates)
VALUES(4,'C.John',20,'5/19/2008 7:45:59 PM')
GO
INSERT INTO [dbo].[Games](UserID,UserName,Point,Dates)
VALUES(5,'A.Jerry',4,'5/19/2008 7:28:59 PM')
GO
INSERT INTO [dbo].[Games](UserID,UserName,Point,Dates)
VALUES(6,'A.Jerry',8,'5/19/2008 7:19:59 PM')
GO
INSERT INTO [dbo].[Games](UserID,UserName,Point,Dates)
VALUES(7,'A.Jerry',10,'4/1/2008 7:19:59 PM')
GO
INSERT INTO [dbo].[Games](UserID,UserName,Point,Dates)
VALUES(8,'A.Jerry',67,'4/1/2008 7:19:59 PM')
GO
INSERT INTO [dbo].[Games](UserID,UserName,Point,Dates)
VALUES(9,'Nancy',50,'5/11/2008 7:19:59 PM')
GO
INSERT INTO [dbo].[Games](UserID,UserName,Point,Dates)
VALUES(10,'Nancy',10,'5/21/2008 7:19:59 PM')
GO
INSERT INTO [dbo].[Games](UserID,UserName,Point,Dates)
VALUES(11,'Nancy',78,'5/25/2008 7:19:59 PM')
GO
INSERT INTO [dbo].[Games](UserID,UserName,Point,Dates)
VALUES(12,'A.Jerry',78,'5/26/2008 7:19:59 PM')
SET IDENTITY_INSERT[Games] OFF
GO
Now, I want to get rows which have maximum 'Point' and minimum 'Dates'.
If Point of user is the same. it gets a row with minimum 'Dates' condition. Note that result will sort by Point 'DESC'. UserName do not repeat in the result.
In data above. Expected result is:
UserID | UserName | Point | Dates
11 | Nancy |78 | 5/25/2008 7:19:59 PM
12 | A.Jerry |78 | 5/26/2008 7:19:59 PM
4 | C.John |20 | 5/19/2008 7:45:59 PM
Please help me!
Thanks a lot
mangrovecm
(-,-)am from VietNamese.
modified on Tuesday, May 20, 2008 9:13 PM
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
