Using multiple Left Outer Joins to calculate a column

Question

I have two tables CustomerStatementSetup in which data is maintained that this customer has entered financials for 2 years or 3 years or so on. Another table is CustomerStatementSetup which has all the information against a year and a coacoade; the user has entered this value.

CustomerStatementSetup:

 CSSCode  CustomerCode   FinancialYear   CreatedOn
 -------------------------------------------------
  80349     42151          2019          Date
  80350     42151          2020          //
  71131     42120          2018          //
  71132     42120          2017          //

CustomerStatement:

 CSTCode   CSSCODE  COACode  COAValue       CustomerCode
 -------------------------------------------------------
   1      80349   10700          50            42151
   2      80349   10701          20            42151
   3      80350   10700          15            42151
   4      80350   10701          45            42151
   5      71131   10700          20            42120
   6      71131   10701          25            42120
   7      71132   10700          150           42120
   8      71132   10701          200           42120

I want to get the current and previous COAValue against a customercode and COAcode and then perform some calculations on it so it returns only the calculated value against that customer.

E.g.: Against CustomerCode 42151 two financials have been performed, 2020 and 2019. Against 2020 there are two entries in customerstatementsetup against coacode. I want to calculate a column like ((Current Year Value -last Year Value)/Last Year Value)*100 against customercode=42151 and coacode=10700. So it will be ((15-50)/50)*100

My sample query is this but this is not returning the required result.

-- perform calculation against current and previous
select csst.CSSCode,csst.FinancialYear,S.COACode,S.COAValue 
from CustomerStatementSetup csst
left join CustomerStatement S on S.CSSCode =  csst.CSSCode 

-- get coavalue against customercode and coacode
left outer join (
    select  top 1 cst.COAValue,css.CSSCode from  CustomerStatementSetup css
    left join CustomerStatement cst on cst.CSSCode =css.CSSCode 
    where cst.CustomerCode=42151  cst.COACode=10700 ORDER  BY css.financialyear DESC
) C1 
on C1.CSSCode =csst.CSSCode 

-- get previous value against customercode and coacode
left outer join (
    select * from (
        select row_number() OVER (ORDER BY css.FinancialYear desc) as rowNo, cst.COAValue,css.CSSCode
        from  CustomerStatementSetup css
        inner join CustomerStatement cst on cst.CSSCode =css.CSSCode 
        where cst.CustomerCode = 42151  and cst.COACode=10700 
    ) as tbl
    where tbl.rowNo = 2
) P1
on P1.CSSCode =csst.CSSCode

I am having trouble understanding the full outer join.

I want to get the current year and previous yearin the same row so I can further use this for my formula. My sample result:

  CustomerCode   COACode   CurrentYearValue   PreviousYearValue
 --------------------------------------------------------------
     42151           10700       15                 50

Can you provide an example expected results? Also, for understanding joins, I'd recommend searching google for "sql joins venn diagram". It's a great visual representation of how joins work. — jw11432, Aug 22 '22 at 18:33
Debug questions require a [mre]--cut & paste & runnable code including initialization; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For SQL include DDL & tabular initialization code. For debug that includes the least code you can give that is code that you show is OK extended by code that you show is not OK. [ask] [Help] When you get a result you don't expect, pause your overall goal, chop to the 1st subexpression with unexpected result & say what you expected & why, justified by documentation. (Debugging fundamental.) — philipxy, Aug 23 '22 at 00:09
@jw11432 Those diagrams are unhelpful & misleading, especially for how inner join works. [Venn Diagram for Natural Join](https://stackoverflow.com/a/55642928/3404097) "All you have to do to see this is to identify what exactly are the elements of the sets represented by the circles." "SQL involves bags & nulls"--not sets. — philipxy, Aug 23 '22 at 00:13
LEFT JOIN returns INNER JOIN rows UNION ALL unmatched left table rows extended by NULLs. Always know what INNER JOIN you want as part of an OUTER JOIN. After a LEFT JOIN a WHERE, INNER JOIN or HAVING that requires a right [sic] table column to be not NULL removes any rows with introduced NULLs, ie leaves only INNER JOIN rows, ie "turns OUTER JOIN into INNER JOIN". You have that. PS This is a [faq](https://stackoverflow.com/q/4752455/3404097). But one must pin down via a [mre] & write many clear, concise & precise phrasings of one's question/problem/goal to search reasonably. — philipxy, Aug 23 '22 at 00:17
@philipxy In reading the link, it goes on to explain that in greater detail it has failings when more complexity is added. But in principle, I see no issue in using Venn diagrams to explain the fundamental workings of joins, up to and including inner joins. There are exceptions, as with most things, but in general, an inner join is perfectly characterized with a venn diagram. Only the rows in which both tables have a shared key will show up. It's a dead simple concept. — jw11432, Aug 23 '22 at 15:05
@jw11432 "All you have to do to see this is to identify what exactly are the elements of the sets represented by the circles." I'm done. — philipxy, Aug 24 '22 at 10:19

jw11432 · Answer 1 · 2022-08-22T19:52:07.647

Without further info, I'm assuming that the current and previous is determined by the FinancialYear and/or CSSCode. Also, without sample data to populate a test case, I didn't validate, but this should work in getting the results you described.

    create table #CustomerStatementSetup (CSSCode int, CustomerCode int, FinancialYear int)
insert into #CustomerStatementSetup values 
(80349,    42151,         2019),
(80350,    42151,         2020)  

create table #CustomerStatement (CSTCode int,  CSSCODE int, COACode int, COAValue int,      CustomerCode int)
 insert into #CustomerStatement values
   (1 ,     80349 ,  10700  ,        50      ,      42151),
   (3  ,    80350 ,  10700   ,       15      ,      42151)

select CustomerCode
    , COACode
    , CurrentYearValue = max(CurrentYearValue)
    , PreviousYearValue = max(PreviousYearValue)
    , (cast (max(CurrentYearValue) - max(PreviousYearValue) as float)/max(PreviousYearValue))*100
from (
    select Data.CustomerCode
        , Data.COACode
        , CASE WHEN RN = 1 THEN COAValue end as CurrentYearValue 
        , CASE WHEN RN = 2 THEN COAValue end as PreviousYearValue 
    from (
        select css.CustomerCode
            , cs.COACode
            , cs.COAValue
            , RN = ROW_NUMBER() OVER (PARTITION BY css.CustomerCode ORDER BY css.FinancialYear desc)
        from #CustomerStatementSetup css
        inner join #CustomerStatement cs on css.CustomerCode = cs.CustomerCode
            and css.CSSCode = cs.CSSCode
         ) Data
            ) Final
group by CustomerCode
    , COACode

I suppose my question is why you're asking about the FULL JOIN? Was it on the premise that you may have needed to use it and didn't realize it?

Using multiple Left Outer Joins to calculate a column

1 Answers1