|
Hi Cocoonwis,
You need to change the GROUP BY clause so it does not include the Amount fields.
Try this instead...
SELECT
t1.AID,
t1.CODE,
MAX(distinct(t1.TYPE) as t1Type),
SUM(t1.Amount) as t1Amount,
COALESCE(SUM(t2.Amount),0)
FROM t1
left outer join t2
on t1.AID = t2.ID
group by t1.AID,t1.CODE
Good luck,
Kevin
On Apr 9, 2009, at 9:17 AM, The Code Project forums wrote:
Hi Kevin,
Thanks for your help.I got it right now 
But i have another question about it, if the records are duplicated in t1, and also t2. How could i sum the amount in t1.Amount and t2.Amount. Example there are 2 record A1 in t1, then i would like to sum them.Also in t2, i would like to sum the amount which have the same id.
I have try in my database, it dosen't SUM for me if i write like :
SELECT
t1.AID,
t1.CODE,
MAX(distinct(t1.TYPE) as t1Type),
SUM(t1.Amount) as t1Amount,
COALESCE(SUM(t2.Amount),0)
FROM t1
left outer join t2
on t1.AID = t2.ID
group by t1.AID,t1.CODE,t1.Amount,t2.Amount
thanks in advance
cocoonwls
|
|
|
|
|
Hi kevin,
First of all,thanks for your help
I am facing another problem of the sql.How about if i need to filter also of the date?For example,
in t1 have 3 record as below:
Table : T1 Table : T2
====================================== ===============================
AID | Code | Type | Amount1 | Date ID | Code | Amount2 | Date
====================================== ===============================
A 123 IA 200 1/15/2009 A 223 12 1/16/2009
B 223 IP 100 1/24/2009 B 323 29 2/13/2009
C 323 MR 55 2/11/2009 E 444 30 2/13/2009
If i want the data which are between 1/1 to 1/30.
The result will be:
Table : TableResult
==================================================
AID | Code | Type | Amount1 | Amount2 | Date
==================================================
A 123 IA 200 12 1/15/2009
B 223 IP 100 0 1/24/2009
Note that the T2.ID for B is not include in.
any ideas are welcome 
Thanks in advance
regards
cocoonwls
|
|
|
|
|
Hi cocoonwls,
You will need to expand the query to include a WHERE clause. So something like this for example...
SELECT
t1.AID,
t1.CODE,
MAX(distinct(t1.TYPE) as t1Type),
SUM(t1.Amount) as t1Amount,
COALESCE(SUM(t2.Amount),0)
FROM t1
left outer join t2
on t1.AID = t2.ID
WHERE t1.DATE >= '20090101' AND t1.DATE <= '20090130'
group by t1.AID,t1.CODE
Cheers,
Kevin
|
|
|
|
|
Hi friend
How to create encrypted trigger in sql 2005
plz help me
Thanks and Regards
Azad yadav
|
|
|
|
|
You already asked this question, just a few posts down. Please don't do that, it is considered rude. People have already answered your original post.
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
The first service pack for SQL Server 2008 has been released. Service Pack 1 (SP1) contains all the cumulative updates and fixes issued since SQL Server 2008 was released. It also makes it easier to deploy SQL Server 2008. A few specific features have been added:
- A feature called Slipstream allows users to install the database and service pack at once, easing the process of loading the software onto hundreds or thousands of servers.
- Report Builder 2.0 Click Once eases deployment.
- Service Pack Uninstall is designed to allow administrators to uninstall the service pack separately from the database release.
You can get the download here[^].
|
|
|
|
|
I am working with Microsoft Access MDBs (2003 Format) on a project and have a situation where two threads can be inserting records in the same table at the same time. I am using OleDB to open the Access MDB, create commands and transactions, and insert the records.
The connection string to open the MDB is essentially the following:
MyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Engine Type=5;" & _
"Jet OLEDB:Database Locking Mode=1;Mode=Share Deny None;Data Source = SOMENAME.MDB"
Even though I am trying to use record locking with this connection string, and the MDB is certainly shared, we consistently get "Could not update. Currently locked." errors when the two threads are writing to the same table at the same time (and the two threads are using separate OleDBConnections). I have tried using and not using transactions, and the error is the same.
Can anyone tell me what else I may need to do to avoid the "Could not update" problem I am experiencing?
Thank you...
modified on Wednesday, April 8, 2009 4:38 PM
|
|
|
|
|
AFAIK Access has PAGE locking, not record locking. I don't know if it still applies but MS used to state that Access is NOT a multiuser database and therefore record locking is not supported. Obviously the page is bigger than 1 record and your multithreaded app is screwing it up.
I used earlier versions with multiple users connected to the same data database with no real locking problems, but that is very different to a threaded application (which was not available on the desktop in those days).
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks for the reply. FWIW, the earliest versions of Access only supported page locking, but the Access 2003 format, at least in theory, is supposed to support record-level locking. If you happen to have such a version of an Access MDB, under its Tools/Options/Advanced tab you can see that there is an option to open databases using record-level locking. And, in the connection string I am using the parameter "Jet OLEDB:Database Locking Mode=1" is supposed to be for record level locking (Mode=0 would be page locking).
I agree that the multi-threading seems to be using page locking, so I am curious if anyone has had any success with Access MDBs under a similar, but certainly not recommended, scenario. Unfortunately using SQL Express is not currently an option on this project; users may have to suffer with slower non-threaded performance if I can't figure something out.
|
|
|
|
|
I presume you have jumped MS directly with the problem and got no respose?
It's been has 15 yrs since I worked with Access. 
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: I presume you have jumped MS directly with the problem and got no respose?
We don't have direct MS support; and their knowledge base (and other groups) has not yet shed any light on what I am seeing. However, I continue to hold out hope that I am missing something painfully obvious that will soon become clear. OTOH, I may just have to travel down another road.
Mycroft Holmes wrote: It's been has 15 yrs since I worked with Access.
Lucky you.
|
|
|
|
|
Assuming the two update threads belong to a single process, I would delegate all DB modifications to one specialized thread, and probably give it an input queue. Or choose another database (SQL Server Express).
The alternative, assuming DB load is low, is providing retry loops for every modification, with an appropriate sleep value of course.
Luc Pattyn [Forum Guidelines] [My Articles]
- before you ask a question here, search CodeProject, then Google
- the quality and detail of your question reflects on the effectiveness of the help you are likely to get
- use the code block button (PRE tags) to preserve formatting when showing multi-line code snippets
|
|
|
|
|
Luc Pattyn wrote: I would delegate all DB modifications to one specialized thread, and probably give it an input queue
Thanks for the reply. Delegating all DB modifications to one thread with an input queue is an interesting thought. I'll probably give that a try if I can not figure out why this version of an Access MDB, which is supposed to support record-level locking, does not seem to at least in this multi-threaded app. Unfortunately SQL Server Express is not an option, and a retry loop could get real ugly in this app.
|
|
|
|
|
Hi,
How can I run (or call) more than one store procedure at the same time?
For example I have a 3 store procedure;
First is sp_GetResult
Second is sp_Allotment
Third is sp_Price
How can I run sp_Allotment and sp_Price stored procedures at the same time (like thread) in sp_GetResult stored procedure.
Note: I use MS SQL Server 2005
Best Regards...
|
|
|
|
|
Don't think you can. Sql Server determines its concurrency automatically and steps through stored procs iteratively.
Regards,
Rob Philpott.
|
|
|
|
|
hi friend
How to hide trigger so that othe user can not modify or view
plz help me
Thank and Regards
Azad yadav
|
|
|
|
|
u have to set the permission for object by using the managaement option and set the permission for other user.
let me know if need any things
|
|
|
|
|
sorry for the above go the database and then security and there u can set the deny or grant for alter or view
|
|
|
|
|
hi experts.
i have a several special characters in my column
like example
goes here
empno emp name
1 ve?u.r
2 raj
3 venki
4 Stáció
5 gái
6 Vecsés
7 Mosonmagyaróvár
8 sachin
i want to list out the names with only special characters plz help me out
regards
venu
|
|
|
|
|
|
use patindex function to find out the column with special character .
|
|
|
|
|
i have 10000 rows in that different different special characters r there how can i list with patindex plz specify
|
|
|
|
|
ex a-zA-Z0-9
the above pateern will accecpt only value in betwwn them for other they will return for presence of special character .
plz check it
|
|
|
|
|
hi friend
How to view trigger so that we can modify, we donnot name of trigger.
plz help me
Thank and Regards
Azad Yadav
|
|
|
|
|
The management studio lets you view and modify triggers easily.
If you want to scripts, use exec sp_helptext "Your_Trigger_Name" -- actually, any database object to view the trigger. And use Alter to modify the trigger.
Eslam Afifi
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
