Reference no: EM13843689

 

1)

A. How do you access the VBA window?

B. Write code in VBA to determine the row number of the last row with data. Are there any precautions/edge cases to be aware of for that code?

C. Please see EXCEL Attached file with two sheets:

1. "Base", which has data from 7/13-7/26

2. "Addition", which has data from 7/20-8/02.

Write a function in VBA to refresh the data in "Base" with that in "Addition". In other words:

1. Remove records in "Base" if they fall within the date range of records in "Addition"

2. Append the records from "Addition" to "Base". Do not include the Grand Total row

when appending

2)

SQL Case Study:

This is one of the worst query written according to a production support personnel. Why do you think that is the case? And how can you improve? Give 2-3 areas that can be improved.

SELECT
t.*
FROM
(
SELECT
sub.*
FROM
(
SELECT
log_inner.*
FROM
(
SELECT
-- log_visit.*, log_action.name
-- log_visit.*, log_link_visit_action.server_time, log_action.name
log_visit.idvisit,
log_visit.idsite,
CONV(HEX(log_visit.idvisitor), 16, 10) AS idvisitor,
visitor_localtime,
visitor_returning,
visitor_count_visits,
visitor_days_since_last,
visitor_days_since_order,
visitor_days_since_first,
visit_first_action_time,
visit_last_action_time,
visit_exit_idaction_url,
visit_exit_idaction_name,
visit_entry_idaction_url,
visit_entry_idaction_name,
visit_total_actions,
visit_total_searches,
visit_total_events,
visit_total_time,
visit_goal_converted,
visit_goal_buyer,
referer_type,
referer_name,
referer_url,
referer_keyword,
config_id,
config_os,
config_os_version,
config_browser_name,
config_browser_version,
config_device_type,
config_device_brand,
config_device_model,
config_resolution,
config_pdf,
config_flash,
config_java,
config_director,
config_quicktime,
config_realplayer,
config_windowsmedia,
config_gears,
config_silverlight,
config_cookie,
inet_ntoa(CONV(HEX(location_ip), 16, 10)) AS location_ip,
location_browser_lang,
location_country,
location_region,
location_city,
location_latitude,
location_longitude,
log_visit.custom_var_k1,
log_visit.custom_var_v1,
log_visit.custom_var_k2,
log_visit.custom_var_v2,
log_visit.custom_var_k3,
log_visit.custom_var_v3,
log_visit.custom_var_k4,
log_visit.custom_var_v4,
log_visit.custom_var_k5,
log_visit.custom_var_v5,
location_provider,
log_link_visit_action.server_time,
log_action. NAME
FROM
piwik_log_visit AS log_visit
LEFT JOIN piwik_log_link_visit_action AS log_link_visit_action ON log_link_visit_action.idvisit = log_visit.idvisit
LEFT JOIN piwik_log_action AS log_action ON log_action.idaction = log_link_visit_action.idaction_url
WHERE
(
log_visit.idsite IN ('8')
AND log_visit.visit_last_action_time>= '2014-10-31 00:00:00'
AND log_visit.visit_last_action_time<= '2014-10-31 23:59:59'
)
AND (
(
log_link_visit_action.idaction_url IN (
SELECT
idaction
FROM
piwik_log_action
WHERE
(
NAME LIKE CONCAT('%', 'success', '%') -- non indexed string comparison against the entire 91million row log_action table
AND type = 1
)
)
)
)
GROUP BY
log_visit.idvisit
) AS log_inner
ORDER BY
idsite,
visit_last_action_time DESC
) AS sub
GROUP BY
sub.idvisit
ORDER BY
sub.visit_last_action_time DESC
) AS t
WHERE
t. NAME = 'burialplanning.com/free-planning-kit-success/'
OR t. NAME = 'burialplanning.com/free-planning-kit-success-no-match/'
OR t. NAME = 'burialplanning.com/free-planning-kit-home-success/'
OR t. NAME = 'burialplanning.com/request-consultation-success/'
OR t. NAME REGEXP '^burialplanning.com/cemeteries/.+/successind/

OR t. NAME REGEXP '^burialplanning.com/cemeteries/.+/cemetery-post-planning-success/

OR t. NAME REGEXP '^burialplanning.com/cemeteries/.+/success-region/