sql - Aggregative sum of objects belonging to objects residing inside hierarchy structure -


my problem similar in way this one, yet different enough in understanding.

i have 3 tables:

  1. units ([unitid] int, [unitparentid] int)
  2. students ([studentid] int, [unitid] int)
  3. events ([eventid] int, [eventtypeid] int, [studentid] int)

students belong units, units stacked in hierarchy (tree form - 1 parent per child), , each student can have events of different types.

i need sum number of events of each type per user, aggregate users in unit, aggregate through hierarchy until reach mother of units.

the result should this: result example

my tools sql server 2008 , report builder 3. put sql fiddle sample data fun.

use query:

;with cte(id, parentid, clevel, title, ord) (     select          u.unitid, u.unitparentid, 1,          cast('unit ' + cast(row_number() on (order u.unitid) varchar(3)) varchar(max)),         cast(right('000' + cast(row_number() on (order u.unitid) varchar(3)), 3) varchar(max))              dbo.units u              u.unitparentid null     union     select         u.unitid, u.unitparentid, c.clevel + 1,          c.title + '.' + cast(row_number() on (partition c.clevel order c.id) varchar(3)),         c.ord + right('000' + cast(row_number() on (order u.unitid) varchar(3)), 3)             dbo.units u         join         cte c on c.id = u.unitparentid              u.unitparentid not null ), units ( select      u.id, u.parentid, u.clevel, u.title, u.ord,     sum(case when e.eventtypeid = 1 1 else 0 end) eventa,     sum(case when e.eventtypeid = 2 1 else 0 end) eventb,     sum(case when e.eventtypeid = 3 1 else 0 end) eventc,     sum(case when e.eventtypeid = 4 1 else 0 end) eventd      cte u     left join     dbo.students s on u.id = s.unitid     left join     dbo.[events] e on s.studentid = e.studentid group     u.id, u.parentid, u.clevel, u.title, u.ord ), addstudents ( select * units union select      s.studentid, u.id, u.clevel + 1,     'student ' + cast(s.studentid varchar(3)),     u.ord + right('000' + cast(s.studentid varchar(3)), 0),     sum(case when e.eventtypeid = 1 1 else 0 end),     sum(case when e.eventtypeid = 2 1 else 0 end),     sum(case when e.eventtypeid = 3 1 else 0 end),     sum(case when e.eventtypeid = 4 1 else 0 end) units u     join     dbo.students s on u.id = s.unitid     left join     dbo.[events] e on s.studentid = e.studentid group     s.studentid, u.id, u.clevel, u.ord ) select --top(10)     replicate(' ', clevel) + title title,      eventa, eventb, eventc, eventd     addstudents  order     ord

for this:

title            | eventa | eventb  | eventc | eventd -----------------+--------+---------+--------+--------  unit 1          | 0      | 1       | 0      | 0   student 6      | 0      | 1       | 0      | 0   unit 1.1       | 0      | 0       | 0      | 1    student 21    | 0      | 0       | 0      | 1    student 33    | 0      | 0       | 0      | 0    unit 1.1.1    | 0      | 0       | 0      | 0     student 23   | 0      | 0       | 0      | 0     unit 1.1.1.1 | 3      | 2       | 3      | 0      student 10  | 0      | 0       | 0      | 0      student 17  | 1      | 0       | 0      | 0 ...

sql fiddle demo


Comments

Post a Comment

Popular posts from this blog

c# - Validate object ID from GET to POST -

i have method allows user access comment have made on site. have post method lets them update comment. my normal solution send entire comment model through, let them update it, update on database when post back. involve sending commentid through in hiddenfor , can manipulated. how can verify commentid sent in get method same i'm getting in post , not able alter comment wish? how can verify commentid sent in method same i'm getting in post? basically have validate following things in post - user logged-in user. authenticated users post comments. commentid in post , should valid commentid , should present in database. userid associated logged-in user should same userid associated comment . comment should contain userid column, can check @ time of update. to make sure update happens comment has been sent in get - hold commentid in session , in post action compare commentid value in session .
Read more

node.js - Custom Model Validator SailsJS -

when using custom model validator checks record within model. return within spread doesn't seem end control flow. //post.js model /** * custom validator **/ types: { isuservalid: function(user_id) { var promise = require('bluebird'); promise.all([ user.findone({id: user_id}) ]) .spread(function(user) { console.log(user); if (user === null || user === undefined) { console.log('failed'); return false; }else{ console.log('passed'); return true; } }); } }, my response standard validation failed response. { "error": "e_validation", "status": 400, "summary": "1 attribute invalid", "model": "post", "invalidattributes": { "owner": [ { "rule": "isuservalid", ...
Read more

php - Find a regex to take part of Email -

i have following emails need extract part from: bestellnummer xxx: 1 von xxx, 1er pack ------------- anfang der nachricht ------------- foo bar baz foo bar baz // <<<<< need text here ------------- ende der nachricht ------------- ------------- anfang der nachricht ------------- foo bar baz foo bar baz ------------- ende der nachricht ------------- there 0 unlimited occurences of ------------- anfang der nachricht ------------- ------------- ende der nachricht ------------- and i'm able extract first part regex: $re = "/------------- .*? -------------.?(.*?).?------------- .*? -------------/s"; but, i'm quite new on learning regex, i'm pretty sure there must better regex extract part (foo bar baz foo bar baz) of text between ------------- anfang der nachricht ------------- ------------- ende der nachricht ------------- as can in different languages, i'm using .? to match between hyphens. i...
Read more