sql - Percentage of group total -

-

i'm trying add new column calculate percentage total of each pack type week.

the code below results in following.

image

so new column display percentage of total number of hectoliters specific week.

code:

set datefirst 1  select   datepart (wk, t0.u_orc_be_proddate) [week produced], --display week number of date produced ( case      when t5.u_orc_be_name '%cans%'          'cans'     when t5.u_orc_be_name '%bottles%'         'bottles'     when t5.u_orc_be_name '%key keg%'         'key keg'     when t5.u_orc_be_name '%ss keg%'         'ss keg'     when t5.u_orc_be_name '%e-keg%'         'ss keg' end )as [pack type], --collate item types pack types sum(t5.u_orc_be_hectoliter * t0.cmpltqty) [total hectoliters] --calculate total hl in order   owor t0   inner join oitt t1 on t0.itemcode = t1.code  inner join oitm t2 on t1.code = t2.itemcode left join [@orc_be_pack_type] t5 on t5.code = t2.u_orc_be_pack_type left join [@orc_be_style_h] t6 on t6.code= t2.u_orc_be_shortcode        (t5.u_orc_be_hectoliter * t0.cmpltqty) != 0  , u_orc_be_processtype = 'packaging' ,      (      t0.[u_orc_be_proddate] >=  dateadd(wk, datediff(wk, 0, getdate()) - 13, 0)      , t0.[u_orc_be_proddate] < dateadd(wk, datediff(wk, 0, getdate()), 0)     )  --for previous 12 weeks  group datepart(wk, t0.u_orc_be_proddate),        t0.u_operator,     (     case          when t5.u_orc_be_name '%cans%'              'cans'         when t5.u_orc_be_name '%bottles%'             'bottles'         when t5.u_orc_be_name '%key keg%'             'key keg'         when t5.u_orc_be_name '%ss keg%'             'ss keg'         when t5.u_orc_be_name '%e-keg%'             'ss keg'     end     )  order datepart (wk, t0.u_orc_be_proddate) asc

use sum() over() aggregate full amount each calendar unit , percentage calculation that.

 select       d.*     , ([total hectoliters] * 100.0) / sum([total hectoliters]) on (partition [week produced]) [weekly pct] (       select             datepart(wk, t0.u_orc_be_proddate)        [week produced]             --display week number of date produced           , (             case                   when t5.u_orc_be_name '%cans%' 'cans'                   when t5.u_orc_be_name '%bottles%' 'bottles'                   when t5.u_orc_be_name '%key keg%' 'key keg'                   when t5.u_orc_be_name '%ss keg%' 'ss keg'                   when t5.u_orc_be_name '%e-keg%' 'ss keg'             end             )                                         [pack type]             --collate item types pack types           , sum(t5.u_orc_be_hectoliter * t0.cmpltqty) [total hectoliters]       --calculate total hl in order       owor t0             inner join oitt t1 on t0.itemcode = t1.code             inner join oitm t2 on t1.code = t2.itemcode             inner join [@orc_be_pack_type] t5 on t5.code = t2.u_orc_be_pack_type             left join [@orc_be_style_h] t6 on t6.code = t2.u_orc_be_shortcode       (t5.u_orc_be_hectoliter * t0.cmpltqty) <> 0             , u_orc_be_processtype = 'packaging'             --for previous 12 weeks             , (t0.[u_orc_be_proddate] >= dateadd(wk, datediff(wk, 0, getdate()) - 13, 0)             , t0.[u_orc_be_proddate] < dateadd(wk, datediff(wk, 0, getdate()), 0)             )       group             datepart(wk, t0.u_orc_be_proddate)           , t0.u_operator           , (             case                   when t5.u_orc_be_name '%cans%' 'cans'                   when t5.u_orc_be_name '%bottles%' 'bottles'                   when t5.u_orc_be_name '%key keg%' 'key keg'                   when t5.u_orc_be_name '%ss keg%' 'ss keg'                   when t5.u_orc_be_name '%e-keg%' 'ss keg'             end             ) ) d order       [week produced] asc

by way because clause insists alias t5 meets conditions there no point in using left join on t5. is, because every row has meet conditions null result t5 ignored, therefore outer join irrelevant table.


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

android - Robolectric "INTERNET permission is required" -

-
i'm getting error when create activity robolectric: java.lang.illegalargumentexception: internet permission required. this i'm doing: @config(constants = buildconfig.class, sdk = testconfig.min_sdk_unit_tests, manifest = "src/main/androidmanifest.xml") @runwith(robolectricgradletestrunner.class) public class atest { @test public void testtrackdonotaskmeagainselected() throws exception { final anactivity activity = robolectric.buildactivity(anactivity.class).create().get(); } update 1: our current setup, have manifests: app/src/androidtest/androidmanifest.xml app/src/debug/androidmanifest.xml app/src/main/androidmanifest.xml app/src/test/androidmanifest.xml i found debug executed instead of test , that's why permission in app/src/test/androidmanifest.xml ignored. add manifest: <uses-permission android:name="android.permission.internet" />
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