0

Say I have the following schema: 

create table client (clientid int) create table mv (clientid int, fundid int, date datetime, value decimal) insert into client values(1) insert into mv values(1, 1, '1 may 2010', 35) insert into mv values (1,1, '1 may 2011', 434) insert into mv values (1, 2, '1 may 2011', 635)

The first table represents the client, and the second table represents their market value and the fund they are invested in.

I need to display their market value per fund at 2 dates, but display 0 for a date if I cant find a record for that date.

Here is my attempt:

select c.clientid, mvstart.fundid startfundid, mvend.fundid endfundid, mvstart.value startvalue, mvend.value endvalue from client c left join mv mvstart on c.clientid = mvstart.clientid and mvstart.date = '1 may 2010' left join mv mvend on c.clientid = mvend.clientid and mvend.date = '1 may 2011'

Which produces:

CLIENTID STARTFUNDID ENDFUNDID STARTVALUE ENDVALUE 1 1 1 35 434 1 1 2 35 635

I don't understand why the second row has a startvalue of 35.

I need to have the following output: 

CLIENTID FUNDID STARTVALUE ENDVALUE 1 1 35 434 1 2 0 635

Can anyone help me join correctly or explain why my query produces 35 as the startvalue for the 2nd row?

Heres a SQLFiddle

Improve this question
3
  • cannot test it right now, but at first glance is because you don't put any condition on the mvstart.fundid=mvend.fundid Commented Jun 24, 2014 at 6:42
  • thanks, I'll bear that in mind, but for now that would just exclude the 2nd record Commented Jun 24, 2014 at 6:46
  • The problem for your query is the line on c.clientid = mvstart.clientid and mvstart.date = '1 may 2010'. It only get the value 35 twice, that's why you see 35 twice there. Commented Jun 24, 2014 at 7:19

4 Answers 4

Reset to default
2

Try this query without joins:

SELECT mv.clientid, mv.fundid, MAX(CASE WHEN date = '1 may 2010' THEN value ELSE 0 END) as startvalue, MAX(CASE WHEN date = '1 may 2011' THEN value ELSE 0 END) as endtvalue FROM mv GROUP BY clientid,fundid

SQLFiddle demo

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

Again bear in mind that I have no access to a db right now :( This query should do something similar to what you want

SELECT X.CLIENTID, X.FUNDID, Y.STARTVAL, Y.ENDVAL FROM MV X, (SELECT B.CLIENTID, B.FUNDID, COALESCE(A.VALUE,0) STARTVAL, COALESCE(B.VALUE,0) ENDVAL FROM MV A FULL OUTER JOIN MV B ON A.CLIENTID=B.CLIENTID AND A.FUNDID=B.FUNDID AND A.DATE='1 may 2010' AND B.DATE='1 may 2011' ) Y WHERE X.CLIENTID=Y.CLIENTID AND X.FUNDID=Y.FUNDID AND EXISTS (SELECT 1 FROM CLIENT C WHERE C.CLIENTID=X.CLIENTID)

Probably you don't even need to use the client table, but I added it just to be sure

Improve this answer

Comments

0

Something like:

select base.clientid, base.fundid, mvs.value, mve.value from (select clientid, fundid, MIN(date) as startdate, MAX(date) as enddate from mv group by clientid, fundid ) base, mv as mvs, mv as mve where base.clientid = mvs.clientid and base.fundid = mvs.fundid and base.startdate = mvs.date and base.clientid = mve.clientid and base.fundid = mve.fundid and base.enddate = mve.date
Improve this answer

1 Comment

the startvalue for fund 2 is 635, where it should be 0
0

I think this will produce proper result: 

create table #client (clientid int) create table #mv (clientid int, fundid int, date datetime, value decimal) insert into #client values(1) insert into #mv values(1, 1, '1 may 2010', 35) insert into #mv values (1,1, '1 may 2011', 434) insert into #mv values (1, 2, '1 may 2011', 635) select c.clientid, mvstart.fundid startfundid, mvend.fundid endfundid, mvstart.value startvalue, mvend.value endvalue from #client c cross join (select distinct fundid, clientid from #mv mv) fId left join #mv mvstart on c.clientid = mvstart.clientid and mvstart.date = '1 may 2010' and mvstart.fundid = fId.fundid left join #mv mvend on c.clientid = mvend.clientid and mvend.date = '1 may 2011' and mvend.fundid = fId.fundid where fId.clientid = c.clientid
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.