|
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
|
|
|
|
|
Their are multiple solutions for that. You could try:
select g.*
from games g
inner join (
select point=max(point), date=min(dates)
from games
) d on g.point = d.point or g.dates = d.date
order by g.point desc
or:
select *
from games
where point = (select max(point) from games)
or dates = (select min(dates) from games)
order by point desc
Both give the same results.
Wout Louwers
|
|
|
|
|
Dear WoutL,
The result did not expected. Because 'A.Jerry' UserName appear in 3 rows.
The result must be not repeat the same UserName. That mean the result will be:
row 1: 11 | Nancy |78 | 5/25/2008 7:19:59 PM
row 2 12 | A.Jerry |78 | 5/26/2008 7:19:59 PM
row 3: | C.John |20 | 5/19/2008 7:45:59 PM
Could you help me!
Thanks a lot
mangrovecm
(-,-)am from VietNamese.
|
|
|
|
|
Without the UserID it would be simple:
select UserName, points=max(point), date=min(dates)
from games
Group by UserName But the problem is thath a singel user has more than one userid. So which userid should be returned? I think it is better to redesign the database and split the games table in a user and games table.
Wout Louwers
|
|
|
|
|
Hello,
I have the following query working well, however; I need to augment it and what seemed simple has really stumoed me. Maybe some of you can guide me in the right direction.
use db1
select distinct v.userid, v.firstname, v.lastname, c.[name]
as 'credential field', q.address, value from import3
inner join vulture v on v.userid = external
inner join credentialfielddef c on c.id = fieldid
inner join vulture_commo o on o.vultureid = v.id
inner join Commo q on q.id = o.vultureid where q.commotypeid like '4'
order by userid
Results look like this:
UserId Firstname Lastname Crednetial Field Value
GUID Jim Roberts Field1 Test1
GUID Jim Roberts Field2 Test2
GUID Jim Roberts Field3 Test3
GUID Jim Roberts Field4 Test4
Desired Result set:
UserId Firstname Lastname Field1 Field2 field3 field4
GUID Jim Roberts Test1 Test2 Test3 Test4
The schema is nightmare and it took me a while just to get where I am now but I am stuck and need a little insight as to the best approach. I have tried using varibles and considered case statements but I am truely lost.
Any help would be appreciated on the best method.
Regards,
Hulicat
|
|
|
|
|
How are stored your data on table, can you post here data on your table?
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
