Comments by Member 14156756 (Top 25 by date)

we have this three table. College, Faculty and Student.
We need to get this result from using three table.

Can you help me to get sum of parent and their child

IN this recursive query we can not find total of main parent.

Still we can not get Main Parent total from this result.

We get Out put:-
1 NULL Header 100 100
2 1 Header 110 590
3 2 Child 111 230
4 2 Child 112 130
5 2 Child 113 230
6 1 Child 120 100
7 1 Header 130 180
8 7 Child 131 160
9 7 Child 132 20
10 NULL Header 200 270
11 10 Header 210 0
12 10 Child 221 160
13 10 Child 220 110
14 NULL Header 300 160
15 14 Child 310 0


Where Main Parent 'Header 100' Total is 870. But we Got 100.

parent sum is not calculate in my result.

ROOT Node of Parent total value is null. that is the problem.

Yes right, it is just for example.

i don't how to get this result from this table. I thought, firstly i split the parent and child hashcode from emailthreadchild column and after that i will compare the hashcode with emailthreadparenthashcode and display there loaddocumentid. that's why i use split_string function. may be i m wrong. but i try to get this result so..

sample data are already declare in question and Example of Out put like :-

DocId ParentDocId TheradGroupId ThreadLevel
13376 0 1 0
13377 13348 1 1
13379 13378 1 2
13380 13348 1 2
13371 13456 1 3
13372 13457 2 1

ssms 2016

i got this result.
TaskId WorkerId TIMESLOT
------ -------------------- ----------- -----------
1 1 1 240
2 1 2 120
3 1 3 240



When we need this type of result.

TASK TIME_SPEND
----------- -----------
1 140

TASK TIME_SPEND
----------- -----------
2 50

TASK TIME_SPEND
----------- -----------
3 170

WE have another example.. which is different from example 1.

Example 2 - Two workers who work on the same task.
Worker A works on task 1 from 9AM to 11AM
Worker A works on task 2 from 9AM to 10AM
Worker B works on task 1 from 11AM to 1PM

The length of time spent on task 1:
From 9AM to 10AM worker A spent 30 minutes because he worked on task2
From 10AM to 11AM worker A spent 60 minutes on task 1
From 11AM to 1PM worker B spent 120 minutes on task 1

CAn we get this type of output.

TASK TIme_spend
1 140
2 50
3 170

You can use DATEADD() for converting into minute.

eg. DATEADD(MINUTE,60,@StartDate)

I Have this type of table.....
Can we get this type of output, using select query OR via CTE....
IS IT OKAY OriginalGriff????