December 3, 2013 at 1:40 am
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
December 3, 2013 at 2:20 am
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.
December 3, 2013 at 2:29 am
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
December 3, 2013 at 2:33 am
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.
December 3, 2013 at 2:35 am
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
December 3, 2013 at 2:45 am
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,
December 3, 2013 at 2:46 am
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
December 3, 2013 at 2:49 am
Hi,
I have edited the query based on the assumption mentioned above.
December 3, 2013 at 3:00 am
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 ?
December 3, 2013 at 7:13 am
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