sql - Combine results to count totals for individuals -

-

i have table in database keeps track of pages sent individual users , groups. users part of groups. individual users can answer pages. here ddl table:

    --pagestatus 1 = expired     --pagestatus 2 = answered     --pagestatus 3 = canceled      create table [pagings] (     [id] int not null identity(1,1) ,     [userprofileid] int null ,     [groupid] int null ,     [message] nvarchar(max) collate sql_latin1_general_cp1_ci_as null ,     [pagestatus] int not null ,     [datecreated] datetime null ,     [datemodified] datetime null ,     [isrecurring] bit not null default ((0)) ,     [answeredbyid] int null ,     [answereddatetime] datetime null ,     constraint [pk_ft.pagings] primary key ([id])       )     on [primary]     textimage_on [primary]     go

anytime pagestatus expired (1) or canceled (3) not have data answeredbyid or answereddatetime columns. if answered set value of userprofileid coming application in answeredbyid column of person answered it. if group paged answered page assumed part of group , userprofileid set inside answeredbyid column.

here sample result , sqlfiddle accompany data. enter image description here

i need figure out how total count of pages user including group belong to, how many pages answered, , group totals. here example of expect result based on set above:

userid groupid  totalpagesforuser  totalansweredforuser totalpagesforgroup totalansweredforgroup ------ -------  -----------------  -------------------- ------------------ ---------------------- 1       2         3                1                     1                 1 3       1         3                1                     2                 2 4       1         2                1                     2                 2

i've tried joining table on userprofileid , answeredbyid , group table exists in database, results way off , end a lot of duplicated data.

i break 2 parts, first assemble aggregate numbers users, group numbers either in subquery part of main query or 2 separate queries results being assembled @ end. anyway, rough first attempt:

select u.id userid,   g.id groupid, g.name groupname, count(0) totalpagesforuser, sum(case when p.answeredbyid not null 1 else 0 end) totalansweredforuser, (select count(0) pagings groupid = g.id) totalpagesforgroup, (select count(0) pagings groupid = g.id , answeredbyid not null) totalansweredforgroup userprofiles u inner join groups g on g.id = u.groupid inner join pagings p on p.userprofileid = u.id or p.answeredbyid = u.id group u.id, g.id, g.name order u.id

although i'm getting different values projecting...but haven't had chance on source data in detail yet.


Comments

Post a Comment

Popular posts from this blog

yii2 - Yii 2 Running a Cron in the basic template -

-
i have seen instructions on running cron in advanced template, cant figure out how on basic template. have following controller in basic controllers folder. <?php namespace app\controllers; use yii\console\controller; /** * cron controller */ class croncontroller extends controller { public function actionindex() { echo "cron service runnning"; } public function actionmail($to) { echo "sending mail " . $to; } } i have navigated root of application , tried these comands yii cron php yii cron im getting unknown comand "cron" i know old question failed find actual answer this. if @ code yii exec file you'll notice requires /common/config/aliases.php file console/config/main.php. can naturally change these or copy on these advanced template. hope solves problem wanting same thing.
Read more

asp.net - 'System.Web.HttpContext' does not contain a definition for 'GetOwinContext' Mystery -

-
i realize question might seem trivial some, it's these types of things find myself fighting quite bit , want make sense of despite seeming losing battle in .net (for me anyway). so, if following: using system.web; ... applicationuser user = system.web.httpcontext.getowincontext().getusermanager<applicationusermanager>().findbyid(system.web.httpcontext.user.identity.getuserid()); that produces error in title , red getowincontext() , error cannot resolve symbol 'getowincontext()' however, if following (remove system.web in front of httpcontext ), works expected (or @ least no errors): using system.web; ... applicationuser user = httpcontext.getowincontext().getusermanager<applicationusermanager>().findbyid(system.web.httpcontext.user.identity.getuserid()); however, if (same line that's working using system.web commented out): //using system.web; ... applicationuser user = httpcontext.getowincontext().getusermana...
Read more

mercurial graft feature, can it copy? -

-
there new feature in mercurial 3, called 'graft' (graft local). job of moving change-set different branch. is there way "copy" change-set branch? i interested in in moving stuff qa branch production branch, still need code reside in qa. want copy. found , have tried few things, maybe it'll become clear more tinkering , reading... does have better way of 'cherry picking' change-sets move cross branch (or trunk)??? graft copies changesets, doesn't move them. hg graft (emphasis mine): this command uses mercurial's merge logic copy individual changes other branches without merging branches in history graph. known 'backporting' or 'cherry-picking' . default, graft copy user, date, , description source changesets.
Read more