April 24, 2012 at 4:40 am
Hi
I'm trying to write a query that combines two rows into one with specific columns.
I'm using SQL 2000
Here is the SQL
selectorig_branch_no,
acct_no,
amt,
description,
create_dt,
charge_code,
tran_code
from history
where tran_code in (262, 157)
and description like '%DDAC%'
and create_dt = '20110802'
and acct_no = '000000000'
Below are the results.
Branch---Acct #----------Amt--------Description--------------Charge_code---Tran_code
410------00000000000----9050.00-------DDACC KABILI-----------756------------262
410------00000000000----5.00----------FEE ON DDACC KABILI----756------------157
410------00000000000----7250.00-------DDACC LIMBIKANI--------756------------262
410------00000000000----7.5-----------FEE ON DDAC LIMBIKANI--756------------157
And after merging the results should look like this
Branch---Acct #----------Amt--------Description--------------Charge_code---Tran_code---Chargeamount
410------00000000000----9050.00-------DDACC KABILI------------157-------------262----------5.00
410------00000000000----7250.00-------DDACC LIMBIKANI---------157-------------262---------7.5
Instead of returning four rows it should only return two rows, is this possible?
Regards,
April 24, 2012 at 4:51 am
Sorry Guys i got it right
Here is the query
selectorig_branch_no,
acct_no,
amt,
description,
create_dt,
charge_code,
tran_code, (select t2.tran_code from history as t2
where t2.tran_code in (157)
and t2.description like '%DDAC%'
and t2.create_dt = '20110802'
and t2.acct_no = t1.acct_no) as 'Charge Code',
(select t3.amt from history as t3
where tran_code in (157)
and t3.description like '%DDAC%'
and t3.create_dt = '20110802'
and t3.acct_no = t1.acct_no) as 'Charge Amount'
from history as t1
where tran_code in (262)
and description like '%DDAC%'
and create_dt = '20110802'
and acct_no = 000000000
And how do I delete a post if I posted it by mistake?
April 24, 2012 at 6:28 am
shani19831 (4/24/2012)
Sorry Guys i got it rightHere is the query
selectorig_branch_no,acct_no,
amt,
description,
create_dt,
charge_code,
tran_code, (select t2.tran_code from history as t2
where t2.tran_code in (157)
and t2.description like '%DDAC%'
and t2.create_dt = '20110802'
and t2.acct_no = t1.acct_no) as 'Charge Code',
(select t3.amt from history as t3
where tran_code in (157)
and t3.description like '%DDAC%'
and t3.create_dt = '20110802'
and t3.acct_no = t1.acct_no) as 'Charge Amount'
from history as t1
where tran_code in (262)
and description like '%DDAC%'
and create_dt = '20110802'
and acct_no = 000000000
And how do I delete a post if I posted it by mistake?
You can click the "EDIT" button and edit your post.
Please post your results once more. I don't think what you posted was the right result.
April 24, 2012 at 6:42 am
Please post your results once more. I don't think what you posted was the right result.
Ya its giving me weird results but I'm working around it, i will also appreciate any kind of input in order to get this sorted out.
Here is my result at the moment..
orig_branch_no-acct_no-amt-description-charge_code-tran_code
410-010300333001-50000.000000-KASONKOMONA-756-157
April 24, 2012 at 6:45 am
shani19831 (4/24/2012)
Please post your results once more. I don't think what you posted was the right result.
Ya its giving me weird results but I'm working around it, i will also appreciate any kind of input in order to get this sorted out.
Can you post Create table statements and sample data for the tables involved. i would hazard a guess you dont need the 2 queries as columns but with out being able to see the data i cant say for sure.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 24, 2012 at 6:54 am
If you only have maximum one "Charge" record per transaction and description always the same prefix with "FEE ON ", you can use:
selecthA.orig_branch_no,
hA.acct_no,
hA.amt,
hA.description,
hA.create_dt,
hA.charge_code,
hA.tran_code,
hC.tran_code AS ChargeCode,
hC.amt AS ChargeAmount,
from history as hA
left join history as hC
on hC.tran_code = 157
and hC.orig_branch_no = hA.orig_branch_no
and hC.acct_no = hA.acct_no
and hC.create_dt = hA.create_dt
and hC.Description = 'FEE ON ' + hA.Description
where hA.tran_code = 262
hA.description like '%DDAC%'
hA.create_dt = '20110802'
hA.acct_no = '000000000'
However, it may be very slow and not very reliable query as transactions should be really joined on something better than description. May be you have some shared transaction id?
April 24, 2012 at 7:00 am
CREATE TABLE History
(
branch int,
acct_no int,
amt double,
description varchar(255),
Create_dt date,
charge_code int,
tran_code int
)
INSERT INTO History
VALUES (410,1234567890,6000000.00,'DDACC IFO KAWASAKI','2011-08-02 00:00:00',756,262)
INSERT INTO History
VALUES (410,1234567890,50000.00,FEE ON DDACC IFO KAWASAKI','2011-08-02 00:00:00',756,157)
April 24, 2012 at 7:05 am
selecthA.orig_branch_no,
hA.acct_no,
hA.amt,
hA.description,
hA.create_dt,
hA.charge_code,
hA.tran_code,
hC.tran_code AS ChargeCode,
hC.amt AS ChargeAmount,
from history as hA
left join history as hC
on hC.tran_code = 157
and hC.orig_branch_no = hA.orig_branch_no
and hC.acct_no = hA.acct_no
and hC.create_dt = hA.create_dt
and hC.Description LIKE '%' +hA.Description
where hA.tran_code = 262
hA.description like '%DDAC%'
hA.create_dt = '20110802'
hA.acct_no = '000000000'
April 24, 2012 at 7:48 am
Eugene Elutin,
Just out Curiosity, what’s the difference between the two queries as they return the exact same set of results?
selectt1.orig_branch_no,
t1.acct_no,
t1.amt as 'Transaction Amount',
RoutingNo = NULL,
t1.description,
t1.tran_code, (select t2.tran_code from history as t2
where t2.tran_code in (157)
and t2.description like '%COMM ON DDAC%'
and t2.create_dt = '20110802'
and t2.acct_no = t1.acct_no) as 'Charge Code',
(select t3.amt from history as t3
where tran_code in (157)
and t3.description like '%COMM ON DDAC%'
and t3.create_dt = '20110802'
and t3.acct_no = t1.acct_no) as 'Charge Amount'
from history as t1
where tran_code in (262,163)
and description like '%DDAC%'
and create_dt = '20110802'
--and acct_no = '010300333001'
--------------------------------------------------------------------
selecthA.orig_branch_no,
hA.acct_no,
hA.amt,
hA.description,
hA.create_dt,
hA.charge_code,
hA.tran_code,
hC.tran_code AS ChargeCode,
hC.amt AS ChargeAmount
from history as hA
left join history as hC
on hC.tran_code = 157
and hC.orig_branch_no = hA.orig_branch_no
and hC.acct_no = hA.acct_no
and hC.create_dt = hA.create_dt
and hC.Description LIKE '%' +hA.Description
where hA.tran_code in (262,163) and
hA.description like '%DDAC%' and
hA.create_dt = '20110802' and
--hA.acct_no = '010300333001'
April 24, 2012 at 8:39 am
Check the query plan (Ctrl + M)
April 24, 2012 at 8:55 am
Wow, you taught me something new, though its been there and never used it before.
But your query does not return all data, as my query returns a few records with charge amount while yours only the first transaction and it ignores the rest,
Should I provide you with an insert query of the two transactions and you will see that it only retrieves the first but not the second even though it meets the search criteria
April 24, 2012 at 9:14 am
...
But your query does not return all data, as my query returns a few records with charge amount while yours only the first transaction and it ignores the rest...
The query does only return records which satisfy WHERE conditions.
You can relax conditions under WHERE clause and it will start to return more records.
The join between "amount" and "charge" type historical records is based on the code. It may be wrong, but it's what you gave us.
As I said before, trying to match "charge" to "amount" record just based on description is not very good idea. You should have some sort of correlating transaction id.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply