sql query self join to find sum of difference in date

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

I have a table Absent

Id	sid	Startdate	enddate	     Reason
id
1	1	1/1/2013	3/1/2013	1
2	1	4/1/2013	5/1/2013	2
3	2	12/12/2012	20/12/2012	1
4	2	5/1/2013	7/1/2013	3
5	1	8/1/2013	9/1/2013	1

I want to find total number of days that a student absent for a reason .i.e. find sum of difference of days for a reason.Out put look like

Sid	Reason1	Reason2	Reason3		
1	3	1	0		
2	8	0	2

Posted 10-Jan-13 17:58pm

rajin kp

Updated 10-Jan-13 18:08pm

Sandeep Mewara

v2

Add a Solution

Comments

Sandeep Mewara 11-Jan-13 0:08am

Tried anything so far?

rajin kp 11-Jan-13 0:35am

select AT.StudentID,AT.ArabicName as StudentAr,AT.EnglishName StudentEn,AT.ClassAr,AT.Description,
isnull (R1.Reason1,0) as Reason1,isnull(R2.Reason2,0) as Reason2,isnull(R3.Reason3,0) as Reason3 ,AT.SchoolAr
from
(select distinct A.StudentID,S.ArabicName,S.EnglishName,C.ArabicName as ClassAr,AY.Description,S.ArabicName as SchoolAr from
Attendance A inner join dbo.Student S on A.StudentID=S.StudentID
inner join Class C on A.ClassID=C.ClassID
inner Join AcademicYear AY on A.AcademicYearID=AY.AcademicYearID
inner Join School SC on SC.SchoolID= A.SchoolID
where (@StudentID=0 or A.StudentID=@StudentID)
and (@SchoolID=0 or A.SchoolID=@SchoolID)
and(@ClassID=0 or A.ClassID=@ClassID) )AT
left join
(select sum(DATEDIFF(dd,(StartDate+1),EndDate))as Reason1 ,A.StudentID from Attendance A inner join AbsentReason R
on A.AbsentReasonCode=R.AbsentReasonID
group by R.AbsentReasonCode,A.StudentID,A.AcademicYearID having R.AbsentReasonCode=1)R1
on AT.StudentID=R1.StudentID
left Join
(select sum(DATEDIFF(dd,(StartDate+1),EndDate))as Reason2 ,A.StudentID from Attendance A inner join AbsentReason R
on A.AbsentReasonCode=R.AbsentReasonID
group by R.AbsentReasonCode,A.StudentID,A.AcademicYearID having R.AbsentReasonCode=2)R2
on AT.StudentID=R2.StudentID
left join
(select sum(DATEDIFF(dd,(StartDate+1),EndDate))as Reason3 ,A.StudentID from Attendance A inner join AbsentReason R
on A.AbsentReasonCode=R.AbsentReasonID
group by R.AbsentReasonCode,A.StudentID,A.AcademicYearID having R.AbsentReasonCode=9)R3
on AT.StudentID=R3.StudentID
i Solve this like this and getcorrect answer so far i want to know is there any other way to solve more efficintly than my sol

rajin kp 11-Jan-13 0:36am

thanks for quick response

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2013-01-10T18:37:00

To get you started, to calculate the difference between two dates, have a look at DATEDIFF[^]. This helps you to calculate the difference between the start and the end date.

Now when that is done, you can use PIVOT to modify the result to desired format. For information about pivot, see Using PIVOT and UNPIVOT[^]

Aarti Meswania · Accepted Answer · 2013-01-10T18:45:00

Solution 3

Pivot Query
use below query to get result

SQL

select sid, isnull(sum([1]),0) as Reason1,isnull(sum([2]),0) as Reason2,isnull(sum([3]),0) as Reason3 from
(
    SELECT sid, [1], [2] , [3]
    FROM
    (
        select id,sid,datediff(d,Startdate,enddate) as diff,Reason from TBLNM
    ) up
    PIVOT (SUM(diff) FOR Reason IN ([1], [2],[3])) AS pvt
)
as t group by sid

Happy Coding!
:)

Posted 10-Jan-13 18:45pm

Aarti Meswania

Comments

rajin kp 11-Jan-13 5:39am

consider that my table contain other field such as SchoolID,ClassID,Acadamicyear..how can i add these values ,such that i want to find datediff() based on these values.

Aarti Meswania 11-Jan-13 5:43am

this way
for example

select classid,Classnm,sid,Studentname,Reason1,Reason2,Reason3
From
(
select sid, isnull(sum([1]),0) as Reason1,isnull(sum([2]),0) as Reason2,isnull(sum([3]),0) as Reason3 from
(
SELECT sid, [1], [2] , [3]
FROM
(
select id,sid,datediff(d,Startdate,enddate) as diff,Reason from TBLNM
) up
PIVOT (SUM(diff) FOR Reason IN ([1], [2],[3])) AS pvt
)
as t group by sid
)
as a
Left join TblStudent on TblStudent.Sid=a.Sid
Left join TblClass on TblStudent.ClsId=TblClass.ClsId