|
Hello, I have a sql (t-sql) question.
I have two tables Orders and Agents.
Each agent does a transaction (bying or selling) and transactions are recorded in Orders table.
Columns in table "Orders":
ItemId, bla, bla, bla, Seller, Buyer
Columns buyer and seller are ID of responsible agent.
Columns in table "Agents":
AgentName, AgentSecondName, ID, bla, bla, bla
NOW i want to display list of all agents alongside with numbers of sold and bought items, in a new table like this:
AGentName, AgentSecondName, AgentId, Bought, Sold
My query is
SELECT AgentName, AgentSecondName, ID
(SELECT COUNT(*)
FROM Orders
WHERE (Seller = Agents.ID)) AS SOLD,
(SELECT COUNT(*)
FROM Orders
WHERE (Buyer = Agents.ID)) AS BOUGHT
FROM Agents
but itąs damn slow with a few millions of records that i have (although its still milliseconds its too slow), any idea how to speed it up?
|
|
|
|
|
There are a number of ways to get this data, I would probably use two left joins and a sum on a case statement based on the id in the seller/buyer fields. You would need to create the alternative queries and run them through the profiler to evaluate the performance.
Performance is more likely to be improved by adding an index to the buyer and seller id fields on the orders table.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
How's about this?
SELECT ID=MAX(Agent.ID)
,FirstName=MAX(Agent.FirstName)
,LastName=MAX(Agent.SecondName)
,Sold=SUM(CASE Seller WHEN Agent.ID THEN 1 ELSE 0 END)
,Bought=SUM(CASE Buyer WHEN Agent.ID THEN 1 ELSE 0 END)
FROM Agent
LEFT OUTER JOIN [Order]
ON Agent.ID=[Order].Seller
OR Agent.ID=[Order].Buyer
GROUP BY Agent.ID
(And, yeah, be sure to index Buyer and Seller)
|
|
|
|
|
Have a data truncation problem at NULL byte, while trying to store binary array to BLOB
Table 'major' structure is:
(smallint) Frame_number|(longblob) ch1|(longblob) ch2|(longblob) ch3|(longblob) ch4|(smallint) frame_size
Connection to base is done, memory for all necessary handles is allocated.
1. Prepare statemt
SQLPrepare(sql_hStmt,_T("INSERT INTO major(Frame_number,CH1,CH2,CH3,CH4,frame_size) VALUES(?,?,?,?,?,?)"),SQL_NTS);
2. Binding params
SQLBindParameter(sql_hStmt, 1, SQL_PARAM_INPUT, SQL_INTEGER, SQL_INTEGER, 0,0, &frames_parsed, 0, 0);
SQLBindParameter(sql_hStmt, 2, SQL_PARAM_INPUT, SQL_C_BINARY, SQL_VARBINARY, 0,0, v, bufsz, 0);
SQLBindParameter(sql_hStmt, 3, SQL_PARAM_INPUT, SQL_C_BINARY, SQL_VARBINARY, 0,0, v, bufsz, 0);
SQLBindParameter(sql_hStmt, 4, SQL_PARAM_INPUT, SQL_C_BINARY, SQL_VARBINARY, 0,0, v, bufsz, 0);
SQLBindParameter(sql_hStmt, 5, SQL_PARAM_INPUT, SQL_C_BINARY, SQL_VARBINARY, 0,0, v, bufsz, 0);
SQLBindParameter(sql_hStmt, 6, SQL_PARAM_INPUT, SQL_INTEGER, SQL_INTEGER, 0,0, &datasize, 0, 0);
where v - array holding data to be stored to DB. bufsz - its size
3. Cycle execution
SQLExecute(sql_hStmt);
Ant suggestion would be great, thanks.
ver MySQL - 5.5, ver OBDC - Mysql ODBC 5.1
|
|
|
|
|
First check parameter value for null or empty content, if is null then send DBNull value (in .NET is DBNull.Value) , if is not null then send value of array.
Hope this will help you 
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
|
|
|
|
|
Hi! Thanks, that was helpful, but only partly, since passing SQL_NULL_DATA (-1) instead of 0x00, I get 0xFF value in the base field. if (v[i] == 0x00) v[i] = SQL_NULL_DATA; How do I later distingush real FF bytes from wraped null data?  Still many thanks for help
|
|
|
|
|
I have the fun job of increasing the field lengths of some of our SQL Table columns, Stored Proc parameters, Data Access Layer, GUI, etc. I am looking for a better way than the current way of having hard coded, or constants scattered throughout our code and SQL to define a field length of say 30.
Thinking big, wouldn't it be nice to define a field length or schema in one location that both SQL and code could use for validation? Anyone have any best practices for this problem they would like to share?
Thanks,
Bill
|
|
|
|
|
SQL Server already has all this meta data in the system views, these can be used for validation based on the existing tables.
Adding another layer of abstraction to manage all the data field lengths/formats sounds like a disaster looking for somewhere to happen. We use code generators so we can reproduce the relevant parts of the DAL/Model/ViewModel code, based on the database tables, this leaves us with the table and the UI/View to be refactored when a change is required.
A utility to validate the property based on the table would be trivial but you now need a property with a length attribute in your DAL/Model layer.
If you define a field standard lenght (eg name will always be 150 characters in length) there will always be exceptions.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
A solution to one part of the problem can be to define types in sql and then extract that information for code validation. I'd be interested in any tools to do the latter, but this is a nice way to define "constants" or types in sql.
/*Define INENTIFIER as varchar(50*/
IF NOT EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'IDENTIFIER' AND ss.name = N'dbo')
CREATE TYPE dbo.IDENTIFIER from varchar(50) null
/*Use the newly defined system type.*/
ALTER TABLE [dbo].[Customers] ALTER COLUMN [Id] IDENTIFIER /* varchar(50) */
You can use IDENTIFIER for your stored procs and have more control over hard coded lenghts, etc.
|
|
|
|
|
I have never actually needed to use custom types, you would then need to maintain the type in both SQL and your code as SQLDBType would not include your IDENTIFIER type. Not a big deal until someone forgets to sync a new type!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi,
I am having 3 tables... in the third table(Table C) i want to insert the values... which i am having datas( some datas not all) in the first(Table A) and second table(some vales not all) (Table B), by single insert....
how to do this??..
|
|
|
|
|
Try something like
INSERT INTO TableC SELECT TableA.Field, TableB.Field FROM TableA, TableB
|
|
|
|
|
INSERT INTO TABLEC
SELECT tableb.col1,tableb.col1 FROM tablea, tableb
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
|
|
|
|
|
if there no restrictions of this script,there must have some repeat results,like if the tableb have 3 rows,the tablec have 3 rows,there will be exists 9 rows,how to solve this? 
|
|
|
|
|
My previous post for this thread was only example how to do the insert from select statement. For more specified case then it must have other conditions on select statement and so. 
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
|
|
|
|
|
I have created sql function and stored procedure to calculate work hours of an employee.
timein,timeout,workingHours are the fields.
eg:
TimeIn TimeOut WorkingHours
9:20:00 AM 11:59:59 PM 14:39:59
00:01:00 AM 3:56:40 AM 3:55:40
I calculated workHours using datediff.I have to calculate the sum of WorkingHours also.What can I do for this??Really I m confused..
|
|
|
|
|
Did you try SUM function to get sum of working hours.
example:
select sum(workinghours) from mytable
Or post your query and we will see how concretely to add too sum of working hours.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
|
|
|
|
|
Hi,
I have CLR procedure which sends request to server and get the url, I need to open that url into browser. I am trying to do it in the application code by Process.Start("IExplore.exe",url). When I debug the code it runs fine but when it deploy to SQL Server to call from CLR procedure it returns error "Request Failed".
Hope someone can help to solve this.
Thanks
|
|
|
|
|
I have two Tables LIKE Emp(EmpNo,EmpName,DeptNo),Dept(No,Name).
Emp Table looks like:
1 xxx 1,2
2 yyy 2,3
3 zzz 1
Dept Table:
1 aaaa
2 bbbb
3 cccc
My Output will be:
1 xxx aaaa,bbbb
2 yyy bbbb,cccc
3 zzz aaaa
How can I do this in Sqlserver2005
|
|
|
|
|
Your database model is faulty IMAO.
How about:
Emp
EmpID Empname
1 xxx
2 yyy
3 ZZZ
Dept
DeptID Deptname
1 aaaa
2 bbbb
3 cccc
Emp_Dept
EmpID DeptID
1 1
1 2
2 2
2 3
3 1
then you can use
SELECT EmpName,DeptName
FROM Emp e
Join EmpDept ed
ON e.EmpID = ed.EmpID
JOIN Dept d
ON ed.Deptid = d.DeptID
And if you really need to get the deptnames as commaseparated values you can have a look in this thread[^] how to do that.
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
Ignore this response - it was wrong. Monday morning syndrome.
Do you work in 2 departments? I admit that some corporate structures work in this way, but for the purpose of the OP's question it was unecessary to add a many-to-many relationship between employees and departments. His original structure of 1 department hads many employees, ie a one-to-many relationship (or to put this another way each employee works in just 1 department) would have worked just fine, with just one join in the query.
modified on Monday, August 9, 2010 7:55 AM
|
|
|
|
|
No, if you look at the example data in the OP, it is a many-to-many relationship. Employee xxx works in two departments, aaaa and bbbb. Department aaaa has two employees, xxx and zzz.
|
|
|
|
|
My bad, I interpreted as the nameof the first employee to be "xxx 1" - I now see the deptNo field is a comma-separated list 
|
|
|
|
|
J4amieC wrote: Monday morning syndrome.
I know everything about that, except it usually happens to me any day of the week
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
Ignore this answer. I mis-interpreted your source data.
The above answer, although correct confuses your question somewhat.
All you need is to join employee table to department table using the foreign/primary key between employee and department.
SELECT EmpNo, EmpName, DeptName
FROM Emp
INNER JOIN Dept
ON Emp.DeptNo=Dept.No
modified on Monday, August 9, 2010 7:54 AM
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
