sql - Filtering Data based on calculation in Excel -


i have data coming in query. filtered data sql, i'm not sure if boss me possible in sql.

i tally value in column c if "load_beer" in column d.

group data person (column f) , day (column e).

then, take each person's tally, , divide sum of of values in column c person day.

if value greater 0.8, take of person's data day, , move new sheet.

below sample of data i'm using:

enter image description here

here sql i'm using:

select tally_tran_mstr.logon_id, sum(tally_tran_mstr.full_pllt_qty), tally_tran_mstr.shift_dt,tally_tran_mstr.shift_nbr wbr_rw.tally_tran_mstr  (tally_tran_mstr.pri_grp_cd='load_beer') group tally_tran_mstr.logon_id, tally_tran_mstr.shift_dt, tally_tran_mstr.shift_nbr

the sql shows data has "load_beer", not tally everything, divide it, , see if greater 0.8.

an example of i'm trying (calculated manually)

stevens has 7 items associated name on 6/15/2017. 3 of "load_beer". load_beer summed 165. sum of 7 items 181. 165/181=0.91. 0.91 > 0.80, want move 7 data points new place, either on new tab or elsewhere on tab i'm on.

my questions are:

  1. is i'm trying possible in sql?

    1.5. if is, how can make formula?

  2. would easier in vba?

    2.5. if so, place start?

there more subqueries achieve requirements. following query pull records table (ex. tab_beer - change table name) , resulting data, can transfer tab or anywhere within vba or whichever tool use

with cte  (     select          a.logon_id,           a.shift_dt,          a.shift_nbr,               case             when cast(a.tally_qty decimal(12,2))/cast(b.total_qty decimal(12,2)) > 0.8               'y'               else 'n'         end inc_flg              (select               logon_id,               sum(full_pllt_qty) tally_qty,              shift_dt,              shift_nbr                        tab_beer                        pri_grp_cd = 'load_beer'          group              logon_id, shift_dt, shift_nbr)     inner join          (select               logon_id,               sum(full_pllt_qty) total_qty,              shift_dt,              shift_nbr                        tab_beer          group               logon_id, shift_dt, shift_nbr) b on a.logon_id = b.logon_id ) select t.* tab_beer t inner join cte c on t.logon_id = c.logon_id t.shift_dt = c.shift_dt   , t.shift_nbr = c.shift_nbr   , c.inc_flg = 'y'

result

logon_id    full_pllt_qty   pri_grp_cd  shift_dt                shift_nbr 3           1               move        2017-06-15 00:00:00.000 stevens, tj 3           2               unload      2017-06-15 00:00:00.000 stevens, tj 3           6               unload      2017-06-15 00:00:00.000 stevens, tj 3           7               unload      2017-06-15 00:00:00.000 stevens, tj 3           49              load_beer   2017-06-15 00:00:00.000 stevens, tj 3           114             load_beer   2017-06-15 00:00:00.000 stevens, tj 3           2               load_beer   2017-06-15 00:00:00.000 stevens, tj

Comments

Post a Comment

Popular posts from this blog

android - InAppBilling registering BroadcastReceiver in AndroidManifest -

i'm implementing inapp billing in android app. works well, however, i'm trying decouple broadcast receiver activity manifest. suggestion in android's trivialdrive sample: // important: dynamically register broadcast messages updated purchases. // register receiver here instead of <receiver> in manifest // because call getpurchases() @ startup, therefore can ignore // broadcasts sent while app isn't running. // note: registering listener in activity bad idea, done here // because sample. regardless, receiver must registered after // iabhelper setup, before first call getpurchases(). currently there's class extends broadcastreceiver : public class iabbroadcastreceiver extends broadcastreceiver { /** * intent action receiver should filter for. */ public static final string action = "com.android.vending.billing.purchases_updated"; private final iabbroadcastlistener mlistener; public iabbroadcastreceiver(iabbroadcastlistener listener) { m...
Read more

python Tkinter Capturing keyboard events save as one single string -

is possible store keyboard events 1 string? code below stores 1 char , prints it. card reader or bar code reader, contains collection of character/ string not 1 character @ time. goal save char pressed text variable. from tkinter import * root = tk() def key(event): text= event.char text+= event.char print ("pressed", text) def callback(event): frame.focus_set() print ("clicked at", event.x, event.y) frame = frame(root, width=100, height=100) frame.bind("<key>", key) frame.bind("<button-1>", callback) frame.pack() root.mainloop() currently, creating text variable , everytime key() function gets called, , text stores last character typed. you can define text module level variable , , use module level text inside key function - text = '' def key(event): global text text+= event.char print("pressed", text)
Read more

sql server - Why does Linq-to-SQL add unnecessary COUNT()? -

here tables: create table customer ( cid int primary key, name varchar(32) ) create table ord ( oid int primary key, cid int foreign key references customer, address varchar(20) ) here's linq-to-sql statement: var aaa = c in db.customer select new { c, o = c.ord.tolist() }; and here's inexplicable query generated linq-to-sql: select [t0].[cid] [cid], [t0].[name] [name], [t1].[oid] [oid], [t1].[cid] [cid2], [t1].[address] [address], (select count(*) [dbo].[ord] [t2] [t2].[cid] = [t0].[cid]) [value] [dbo].[customer] [t0] left outer join [dbo].[ord] [t1] on [t1].[cid] = [t0].[cid] order [t0].[cid], [t1].[oid] i want understand how rid of count(*) part. it's entirely uncalled for! linq needs count(*) in order determine number of entries returned o = c.ord.tolist() .
Read more