Lampoa Lampoa - 1 year ago 58
SQL Question

Filtering Data based on calculation in Excel

I have data coming in from a query. I originally filtered the data with SQL, but I'm not sure if what my boss would like me to do is possible in SQL.

I would like to tally the value in column C if "LOAD_BEER" is in Column D.

Group data by Person (Column F) and by Day (Column E).

Then, take each person's tally, and divide it by the sum of all of the values in Column C for that person for that day.

If that value is greater than 0.8, take all of that person's data for that day, and move it to a new sheet.

Below is a sample of the data I'm using:

enter image description here

Here is the 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
FROM WBR_RW.TALLY_TRAN_MSTR
WHERE (TALLY_TRAN_MSTR.PRI_GRP_CD='LOAD_BEER')
GROUP BY TALLY_TRAN_MSTR.LOGON_ID, TALLY_TRAN_MSTR.SHIFT_DT, TALLY_TRAN_MSTR.SHIFT_NBR


The SQL shows any data that has "LOAD_BEER", but it does not tally everything, divide it, and see if its greater than 0.8.

An example of what I'm trying to do is (Calculated manually)

Stevens has 7 items associated with his name on 6/15/2017. 3 of those are "LOAD_BEER". The LOAD_BEER summed are 165. The sum of all 7 items is 181. 165/181=0.91. 0.91 > 0.80, so I want to move all 7 data points to a new place, either on a new tab or elsewhere on the tab I'm currently on.

My questions are:


  1. Is what I'm trying to do possible in SQL?

    1.5. If it is, how can I make that a formula?

  2. Would this be easier to do in VBA?

    2.5. If so, what would be a good place to start?


Answer Source

There will be more subqueries to achieve all your requirements. The following query will pull all records from your table (ex. tab_beer - change it to your table name) and with the resulting data, you can now transfer it to a tab or anywhere within your VBA or whichever tool you use

WITH cte AS 
(
    SELECT 
        a.logon_id,  
        a.shift_dt, 
        a.shift_nbr,      
        CASE 
           WHEN CAST(a.tally_qty AS DECIMAL(12,2))/CAST(b.total_qty AS DECIMAL(12,2)) > 0.8
              THEN 'Y'
              ELSE 'N'
        END inc_flg
    FROM 
        (SELECT 
             logon_id, 
             SUM(full_pllt_qty) tally_qty,
             shift_dt,
             shift_nbr
         FROM 
             tab_beer
         WHERE 
             pri_grp_cd = 'LOAD_BEER'
         GROUP BY 
            logon_id, shift_dt, shift_nbr) a
    INNER JOIN 
        (SELECT 
             logon_id, 
             SUM(full_pllt_qty) total_qty,
             shift_dt,
             shift_nbr
         FROM 
             tab_beer
         GROUP BY 
             logon_id, shift_dt, shift_nbr) b ON a.logon_id = b.logon_id
)
SELECT t.*
FROM tab_beer t
INNER JOIN cte c ON t.logon_id = c.logon_id
WHERE t.shift_dt = c.shift_dt
  AND t.shift_nbr = c.shift_nbr
  AND 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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download