sql server - SQL JOIN doubling values -

-

i have 2 temp tables following data:

temp_aht

enter image description here

temp_1

enter image description here

i use query join 2 tables on column incident:

    select         count(distinct a.incident) nr_of_tickets        ,a.[service]       ,sum(case when a.status_switch  in ('assigned » in progress','assigned » pending','assigned » resolved','assigned » closed') a.totalseconds else null end) assignedtime       ,sum(case when a.status_switch  in ('in progress » assigned','in progress » assigned','in progress » pending','in progress » resolved') a.totalseconds else null end) progresstime       ,sum(case when a.status_switch  in ('pending » assigned','pending » in progress') a.totalseconds else null end) pendingtime       ,sum(b.creation) creation       ,sum(b.[call duration]) [call duration]   ##temp_aht inner join ##temp_1 b on a.incident = b.incident_id  group a.[service],b.creation,b.[call duration]

if join tables above query sums won't correct. think because in both tables have multiple rows same incident id.

but how can join table , take first row ##temp_1 if match found ?

the problem have multiple rows each incident in both tables. further complicated fact joining on 1 column (incident) , aggregating (service).

then, group by includes aggregated columns b table, doesn't make sense.

the solution aggregate both tables before doing join. think want:

select sum(nr_of_tickets), a.[service],        sum(assignedtime) assignedtime,        sum(progresstime) progresstime,        sum(pendingtime) pendingtime,        sum(b.creation) creation,        sum(b.[call duration]) [call duration]  (select a.service, count(distinct a.incident) nr_of_tickets,              sum(case when a.status_switch  in ('assigned » in progress','assigned » pending','assigned » resolved','assigned » closed') a.totalseconds end) assignedtime              sum(case when a.status_switch  in ('in progress » assigned','in progress » assigned','in progress » pending','in progress » resolved') a.totalseconds end) progresstime,              sum(case when a.status_switch  in ('pending » assigned','pending » in progress') a.totalseconds end) pendingtime       ##temp_aht       group a.incident, a.service      ) inner join      (select b.incident,               sum(b.creation) creation,              sum(b.[call duration]) [call duration]       ##temp_1 b       group b.incident      ) b      on a.incident = b.incident_id group a.[service]

Comments

Post a Comment

Popular posts from this blog

sequelize.js - Sequelize group by with association includes id -

-
so when requesting group sequelize follows: return models.workingcalendar .findall({ attributes: [ 'workingcalendar.periodid', 'workingcalendar.date', 'period.name' ], include: [ { model: models.period, attributes: [] } ], where: { getsudoid: currentgetsudo.id, unitplantid: unitplantid }, group: ['workingcalendar.periodid', 'workingcalendar.date', 'period.name'], }); sequelize run query: select [workingcalendar].[id], [workingcalendar].[periodid], [workingcalendar].[date], [period].[name] [workingcalendars] [workingcalendar] left outer join [periods] [period] on [wor...
Read more

java - Android raising EPERM (Operation not permitted) when attempting to send UDP packet after network connection -

-
when responding supplicant_state_changed_action broadcast , attempting send udp packet, exception: java.net.socketexception: sendto failed: eperm (operation not permitted) @ libcore.io.iobridge.maybethrowaftersendto(iobridge.java:542) @ libcore.io.iobridge.sendto(iobridge.java:511) @ java.net.plaindatagramsocketimpl.send(plaindatagramsocketimpl.java:184) @ java.net.datagramsocket.send(datagramsocket.java:305) @ com.mycompany.app.serviceclass.discover(serviceclass.java:355)at com.mycompany.app.serviceclass.access$600(serviceclass.java:39) @ com.mycompany.app.serviceclass$statemachine.run(serviceclass.java:242) @ java.lang.thread.run(thread.java:818) caused by: android.system.errnoexception: sendto failed: eperm (operation not permitted) @ libcore.io.posix.sendtobytes(native method) @ libcore.io.posix.sendto(posix.java:211) @ libcore.io.blockguardos.sendto(blockguardos.java:278) @ libcore.io.iobridge.sendto(iobridge.java:509) @ java.net.plaindat...
Read more

c++ - Migration from QScriptEngine to QJSEngine -

-
i'm migrating qscriptengine code on qjsengine. now, have: class pars { public: static qscriptvalue printmainlog(qscriptcontext* c, qscriptengine* e); }; qscriptvalue pars::printmainlog(qscriptcontext* c, qscriptengine* e) { //some actions return e->globalobject().property(""); } ... qscriptengine engine; ... engine.globalobject().setproperty("printlog",engine.newfunction(pars::printmainlog)); so, user can put printlog("what ever"); in application in , example, qlineedit , function pars::printmainlog evalute. is there way qjsengine? so, user put same printlog("what ever");? way find here , user should put logger.printlog("what ever"); logger class inherited qobject printlog slot.
Read more