Same table twice left join issue

  • Hi All ,

    I have table with data like below

    Namemarksmonthsubject

    Kumar801maths

    Guna701science

    Kumar502physics

    Guna402science

    Query

    select t1.name,t1.marks-t2.marks as Marks,CONVERT(VARCHAR(19),

    t1.month)+'~'+CONVERT(VARCHAR(19),

    t2.month) as Month,

    t1.subject from #tmp_h t1

    left join #tmp_h t2 on t1.name=t2.name and t1.subject=t2.subject

    where t1.month<>t2.month

    Actual output

    nameMarksMonthsubject

    Guna301~2science

    Guna-302~1science

    I need output like below.

    nameMarksMonthsubject

    Kumar801~2maths

    Kumar402~1science

    Guna301~2science

    Guna-302~1science

    Kumar801~2maths

    Kumar402~1science

    these rows not added , I need these details also .. Kindly advise what need to change in the query

  • My psychic abilities are failing me... could you explain what your goal is? Looks like you're trying to calculate a change in grades for a single (student,course) between two time periods.

  • Hi pietlinden ,

    I need to find the variance between month 1 and 2

    For Name Guna in month 1 70 marks in science subject

    For Name Guna in month 2 40 marks in science subject

    so variance about month 1 and 2 is

    Guna301~2science

    so variance about month 2 and 1 is

    Guna-302~1science

    here is issue

    For Name Kumar in month 1 80 marks in maths subject

    For Name Kumar in month 2 have no maths subject

    but i need to calculate 2 month marks as 0

    80-0 =80 Marks

    so variance about month 1 and 2 is

    Kumar 80 1~2Maths

    Can you understand now ..I need to find the variance between the month of marks

    Thanks,

    Chandrahasan S

  • You have used left join for table x and you are applying the WHERE clause.

    Even your query gets the data, it will not meet the WHERE clause condition, so those rows will be removed from the result.

  • Try with this code

    WITH DATA (Name,marks,month,subject) AS

    (SELECT 'Kumar',80,1,'maths' UNION

    SELECT 'Guna',70,1,'science' UNION

    SELECT 'Kumar',40,2,'physics' UNION

    SELECT 'Kumar',70,3,'physics' UNION

    SELECT 'Guna',40,2,'science')

    select t1.name,ISNULL(t1.marks,0)-ISNULL(t2.marks, 0) as Marks,

    CONVERT(VARCHAR(19),ISNULL(t1.month, '')

    )+'~'+CONVERT(VARCHAR(19),

    case when t2.month is null and t1.month=2 then t1.month - 1

    when t2.month is null and t1.month=1 then 2 else t2.month end) as Month,

    t1.subject from DATA t1

    left join DATA t2 on t1.name=t2.name and t1.subject=t2.subject

    AND t1.month<>t2.month

  • HI parulprabu ,

    Thanks for your reply .

    Almost correct output

    nameMarksMonthsubject

    Kumar801~0maths

    Guna301~2science

    Kumar502~0physics

    Guna-302~1science

    BUT, Is possible to show the actual month 1~2 instead of 1~0

    I have to validate with Month column after words

    Thanks,

  • Assuming that, month 2 is having data and 1 is not having data for 2~1

    and month 1 is having data and month 2 is not having data for 1~2

  • Hi,

    I have edited the query based on the assumption mentioned above.

  • Hi parulprabu ,

    Thanks for reply again ,

    Actually in month column does not has in order 1,2,3,4...

    It may be some time month column M1,M2,M3,M4 .etc

    so case condition not suitable for this

    case when t2.month is null and t1.month=2 then t1.month - 1

    when t2.month is null and t1.month=1 then 2 else t2.month end)

    Is possible without hard coding ?

  • For your data containing 2 months

    ;WITH

    m (Month1, Month2)

    AS (

    SELECT MIN(Month), MAX(Month)

    FROM #tmp_h

    UNION ALL

    SELECT MAX(Month),MIN(Month)

    FROM #tmp_h

    ),

    n (name, subject)

    AS (

    SELECT name, subject

    FROM #tmp_h

    GROUP BY name, subject

    )

    SELECTn.name,

    t1.Marks-ISNULL(t2.Marks,0) AS [Marks],

    CAST(m.Month1 as varchar(2))+'~'+CAST(m.Month2 as varchar(2)) AS [month],

    n.subject

    FROMn

    CROSS JOIN m

    JOIN #tmp_h t1 ON t1.name = n.name AND t1.subject = n.subject AND t1.Month = m.Month1

    LEFT JOIN #tmp_h t2 ON t2.name = n.name AND t2.subject = n.subject AND t2.Month = m.Month2

    ORDERBY n.name ASC, m.Month1 ASC

    How many months are you catering for? and are they consecutive?

    Far away is close at hand in the images of elsewhere.
    Anon.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply