SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Joint rows
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kshoberalb
Starting Member

2 Posts

Posted - 01/31/2014 :  15:57:48  Show Profile  Reply with Quote
I have a Transaction table view that returns transaction rows for each id summed to columns. Some transaction rows are joint transactions and usually split equally between two ids. Each joint row has the corresponding id number in the jointid column.

Id Summary1 Summary2 Jointid
A1 100.00 200.00 B2
B2 100.00 200.00 A1
C3 100.00 100.00 NULL

I need to sum the two joint row summary columns where the id = jointid but only return one row. It is not important which row is returned.

The easiest way would be to create a unique id common to both rows and use row_number() over (partition by) and select row 1, but I cannot find a common unique id.

Any suggestions would be appreciated.

kshoberalb

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1769 Posts

Posted - 01/31/2014 :  18:55:23  Show Profile  Reply with Quote
I'n not sure if I really understand your requirements but hopefully, this will get you started:
declare @SumTbl table (
   Id         varchar(2),
   Summary1   money,
   Summary2   money,
   Jointid    varchar(2)
   )

insert into @SumTbl (
   Id, Summary1, Summary2, Jointid
) values
   ('A1', 100.00, 200.00, 'B2'),
   ('B2', 100.00, 200.00, 'A1'),
   ('C3', 100.00, 100.00, NULL)

--/**/select * from @SumTbl

select --*
   t.Id,
   sum(t.Summary1) totSummary1,
   sum(t.Summary2) totSummary2
from (
   select
      a.id,
      a.Summary1,
      a.Summary2
   from
      @SumTbl a
   where
      a.Id < a.jointid
   or a.Jointid is null

   --------------------
   union all
   --------------------

   select
      a2.jointid,
      a2.Summary1,
      a2.Summary2
   from
      @SumTbl a1
   inner join
      @SumTbl a2
         on  a1.Id = a2.Jointid
         and a2.Id = a1.Jointid
         and a1.Id < a2.id
   ) t
group by
   t.Id
order by
   t.Id


===============================================================================
There are two kinds of light -- the glow that illuminates, and the glare that obscures. -James Thurber (1894-1961)
Go to Top of Page

kshoberalb
Starting Member

2 Posts

Posted - 02/01/2014 :  09:51:13  Show Profile  Reply with Quote
Thanks for the quick response. I noticed that you mentioned that you were not sure what I am looking for and you are correct. I did not do a very good job of explaining what I am attempting to do.

I would like to simply find the paired rows and return just one row from each pair. All rows with a Null in the jointid column are not paired and should remain as is.

The table contains thousands of paired rows with each row having the paired row's ID number in the jointid column. I am able to find each pair individually as I illustrated, but I cannot figure out how to eliminate one of the rows. The techniques needs to be applied to the entire table.

Id Jointid
A B
B A

Isolate all of the paired rows (should only be two each) and eliminate one of the rows.

Id Jointid
A B

or (the single row returned is not important)

B A

Regards


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/03/2014 :  04:20:25  Show Profile  Reply with Quote

SELECT *
FROM Table t
WHERE NOT EXISTS (SELECT 1
FROM Table 
WHERE Jointid = t.id
AND id = t.Jointid 
) 
OR t.id < t.Jointid 


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000