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 2005 Forums
 Transact-SQL (2005)
 Join two records
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

eagle16
Starting Member

USA
1 Posts

Posted - 09/30/2012 :  16:35:41  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/30/2012 :  18:08:16  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 10/01/2012 :  11:21:00  Show Profile  Reply with Quote
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
  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.05 seconds. Powered By: Snitz Forums 2000