Merging together in Stored Proc

[chrisssyp82]

SQL Server 2008

Hi

I have an issue where i have two of the same accounts which i would like to merge together, the list looks like below:

AcctID AcctType AcctSubType Curr TransType Amount
1 CCY SET EUR Opening 1000
1 CCY SET EUR BUY -100
1 CCY SET EUR SEL 100
1 CCY SET EUR Closing 1000
2 CCY SET EUR Opening 2000
2 CCY SET EUR SEL 100
2 CCY SET EUR Closing 2100

So basically i would like to merge it so it looks like this:

AcctID AcctType AcctSubType Curr TransType Amount
1 CCY SET EUR Opening 3000
1 CCY SET EUR BUY -100
1 CCY SET EUR SEL 100
2 CCY SET EUR SEL 100
1 CCY SET EUR Closing 3100

However i can't think of the best way to code this in SQL in my stored proc. A point in the right direction would be much appreciated.

Thanks

 

[gmmastros]

It looks like you want to sum the opening and closing, but leave the buy and sel alone. Is this correct?

Also, I'm a little confused about the AcctID column. It looks like you're dealing with different accounts here. Is it just a typo?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[chrisssyp82]

Basically in an ideal world you have one capital account per currency, but as we all know this isn't an ideal world and on occassions we have two capital accounts per currency. So basically as a step in my stored proc i want to be able to check for more than one capital account, and if there is more than one then merge the opening and closing lines together and have the transactions that make up them together.

Basically this is a reconcilliation procedure so the opening and closing lines are from a positions table, and the transactions between are from a transaction table. Later on in the proc i do a running total of the opening and transactions to see if there is a reconcilliation break with the closing balance.

Hope this makes sense, i am quite happy to supply more info if required.

Thanks

 

[chrisssyp82]

I'm guessing the code would start something like this:

IF(SELECT Count(distinct w.AcctID) 
FROM #Workbook w
WHERE w.AcctType = 'CCY'
AND w.AcctSubType = 'SET'
AND w.Curr = w.Curr) > 1

	BEGIN
        .................

?

 

[gmmastros]

Try this:

Select AcctType, AcctSubType, Curr, TransType, Sum(Amount) As Amount
From   [!]YourTableNameHere[/!]
Where  TransType In ('Opening','Closing')
Group By AcctType, AcctSubType, Curr, TransType

Union All 

Select AcctType, AcctSubType, Curr, TransType, Amount
From   [!]YourTableNameHere[/!]
Where  TransType Not In ('Opening','Closing')

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[chrisssyp82]

Unfortunately this seem to make no difference. I still have the same table as i started with. Would not only want to merge the records where certain fields met a certain criteria?

 

[gmmastros]

I'm afraid that I don't really understand your question. Sorry.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[chrisssyp82]

what i mean to say is only merge records where there are two capital accounts? ie where AcctType = 'CCY' AND w.AcctSubType = 'SET' and for the same currency? So add some more to the where clause before the union?

Sorry i think i'm gettin myself overly confused over this, basically my code at the moment looks like this:

CREATE TABLE #Workbook
	(	ID						INT
	,	PortfolioID				VARCHAR(20)
	,	PortfolioName			VARCHAR(255)		
	,	InstrumentID			VARCHAR(20)
	,	IssueID					VARCHAR(50)
	,	CashAccountInstrumentID VARCHAR(20)
	,	CashAccountName			VARCHAR(255)
	,	CashAccountType			CHAR(3)
	,	CashAccountSubType		CHAR(3)
	,	TransactionClass		VARCHAR(10)
	,	TransactionType			VARCHAR(20)
	,	EffPostDate 			DATETIME
	,	TradeDate				DATETIME
	,	SettleDate				DATETIME
	,	CcySettle				VARCHAR(3)
	,	DateItem				DATETIME
	,	RunningTotalDate		DATETIME
	,	Detail					VARCHAR(300)
	,	ProceedsLocal			FLOAT
	,	Total					FLOAT	
	,	RunningTotal			FLOAT
	,	ReconValue				FLOAT
	,	ReconNotes				VARCHAR(50)
	,	RecordType				VARCHAR(1)
	,	Sequence				INT
	,	RecordNum				INT
	,	TransactionID			VARCHAR(20)
	)

INSERT INTO #Workbook
	EXEC dbo.usp_Generic_ReconCashAccount_NEW '2010-10-01', '2010-10-07', '', 'OOGENHF', 'SS','','','','','' 

DELETE FROM #Workbook
WHERE TransactionType = 'BBA'
   OR (ProceedsLocal = 0 AND RecordType = 'T')	-- Do not show transactions with no proceeds

Select ID,PortfolioID,PortfolioName,InstrumentID,IssueID,CashAccountInstrumentID,CashAccountName,CashAccountType,CashAccountSubType,TransactionClass,TransactionType,EffPostDate,TradeDate,SettleDate
	,CcySettle,	DateItem,RunningTotalDate,Detail,SUM(ProceedsLocal),Sum(Total),RunningTotal,ReconValue,ReconNotes,RecordType,Sequence,RecordNum,TransactionID

From   #Workbook
Where  TransactionType In ('Opening','Closing')
AND CashAccountType = 'CCY'
AND CashAccountSubType = 'SET'
Group By ID,PortfolioID,PortfolioName,InstrumentID,IssueID,CashAccountInstrumentID,CashAccountName,CashAccountType,CashAccountSubType,TransactionClass,TransactionType,EffPostDate,TradeDate,SettleDate
	,CcySettle,	DateItem,RunningTotalDate,Detail,ProceedsLocal,Total,RunningTotal,ReconValue,ReconNotes,RecordType,Sequence,RecordNum,TransactionID

Union All 

Select ID,PortfolioID,PortfolioName,InstrumentID,IssueID,CashAccountInstrumentID,CashAccountName,CashAccountType,CashAccountSubType,TransactionClass,TransactionType,EffPostDate,TradeDate,SettleDate
	,CcySettle,	DateItem,RunningTotalDate,Detail,ProceedsLocal,Total,RunningTotal,ReconValue,ReconNotes,RecordType,Sequence,RecordNum,TransactionID
From   #Workbook
Where  TransactionType Not In ('Opening','Closing')

select * from #Workbook
ORDER By ID, PortfolioID, CashAccountType, CashAccountSubType, CcySettle, DateItem, Sequence

drop table #Workbook

and as i said above i want to merge the opening and closing balances and include all transactions between so that i have one capital account even though in reality there is two.

Sorry i can't explain this a little better.

Thanks

 

[chrisssyp82]

Right so now i have the following code which is pretty close, however the two opening and closing balances don't seem to be merging and removing the old opening and closing balances? Does anyone have any ideas? Am able to supply test data if required.

Thanks

CREATE TABLE #Workbook
( ID INT
, PortfolioID VARCHAR(20)
, PortfolioName VARCHAR(255)
, InstrumentID VARCHAR(20)
, IssueID VARCHAR(50)
, CashAccountInstrumentID VARCHAR(20)
, CashAccountName VARCHAR(255)
, CashAccountType CHAR(3)
, CashAccountSubType CHAR(3)
, TransactionClass VARCHAR(10)
, TransactionType VARCHAR(20)
, EffPostDate DATETIME
, TradeDate DATETIME
, SettleDate DATETIME
, CcySettle VARCHAR(3)
, DateItem DATETIME
, RunningTotalDate DATETIME
, Detail VARCHAR(300)
, ProceedsLocal FLOAT
, Total FLOAT
, RunningTotal FLOAT
, ReconValue FLOAT
, ReconNotes VARCHAR(50)
, RecordType VARCHAR(1)
, Sequence INT
, RecordNum INT
, TransactionID VARCHAR(20)
)

INSERT INTO #Workbook
EXEC dbo.usp_Generic_ReconCashAccount_NEW '2010-10-01', '2010-10-07', '', 'OOGENHF', 'SS','','','','',''

DELETE FROM #Workbook
WHERE TransactionType = 'BBA'
OR (ProceedsLocal = 0 AND RecordType = 'T') -- Do not show transactions with no proceeds

;;with needed as
(
select * from #Workbook w
WHERE w.CashAccountType = 'CCY'
AND w.CashAccountSubType = 'SET'
AND w.TransactionType In ('Opening','Closing')
AND w.CcySettle = w.CcySettle
--AND Count(distinct w.InstrumentID) > 1
)
select
min(ID)
, PortfolioID
, PortfolioName
, InstrumentID
, IssueID
, min(CashAccountInstrumentID) as CashAccountInstrumentID
, min(CashAccountName) as CashAccountName
, CashAccountType
, CashAccountSubType
, TransactionClass
, TransactionType
, EffPostDate
, TradeDate
, SettleDate
, CcySettle
, DateItem
, RunningTotalDate
, Detail
, sum(ProceedsLocal) as ProceedsLocal
, sum(Total) as Total
, sum(RunningTotal) as RunningTotal
, sum(ReconValue) as ReconValue
, ReconNotes
, RecordType
, Sequence
, RecordNum
, TransactionID
from needed
group by ID,PortfolioID,PortfolioName,InstrumentID,IssueID,CashAccountInstrumentID,CashAccountName,CashAccountType,CashAccountSubType,TransactionClass,TransactionType,EffPostDate,TradeDate,SettleDate
,CcySettle, DateItem,RunningTotalDate,Detail,ProceedsLocal,Total,RunningTotal,ReconValue,ReconNotes,RecordType,Sequence,RecordNum,TransactionID

UNION ALL
(
select * from #Workbook
except
select * from needed
)
drop table #Workbook