Join two records - SQL Server Forums

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'

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources