Lampoa -3 years ago 154
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:

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
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?

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
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