Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Join two records

Author  Topic 

eagle16
Starting Member

1 Post

Posted - 2012-09-30 : 16:35:41
Hi Guys,

I hope you can help me with this problem. I've been working on the below for some time & can't seem to make any progress.

The problem is that there may not be a 'cashvalue2' record for some accounts. In that case, a record is not inserted yet I need the existing 'cashvalue1' record to be inserted into 'total cash'in those instances. How can I rewrite this to have 'cashvalue1' inserted when 'cashvalue2' does not exist?

INSERT INTO accountvalues
(account,
type,
date,
cashreturn,
cashvalue)
SELECT a.account,
'totalcash' AS type,
a.date,
a.cashreturn,
SUM(a.cashvalue + b.cashvalue) AS cashvalue

FROM accountvalues a
LEFT JOIN accountvalues b
ON a.account = b.account
AND a.date = b.date
WHERE a.type = 'cashvalue1'
AND b.type = 'cashvalue2'

Any help is greatly appreciated!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-30 : 18:08:16
Move the where clause on cashvalue2 to the join. Also, account for cashvalue2 being null - see changes in red below.
INSERT INTO accountvalues
(
account,
TYPE,
date,
cashreturn,
cashvalue
)
SELECT a.account,
'totalcash' AS TYPE,
a.date,
a.cashreturn,
SUM(a.cashvalue + COALESCE(b.cashvalue,0)) AS cashvalue
FROM accountvalues a
LEFT JOIN accountvalues b
ON a.account = b.account
AND a.date = b.date
AND b.type = 'cashvalue2'

WHERE a.type = 'cashvalue1'
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-01 : 11:21:00
see below to understand the reason

http://weblogs.sqlteam.com/jeffs/archive/2007/05/14/criteria-on-outer-joined-tables.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -