Catyclaire85
New Member
- Joined
- Nov 23, 2021
- Messages
- 20
- Office Version
- 2016
- Platform
- Windows
HI,
I am stumped with how to approach this and don't know if it is possible in VBA. I have attached a dummy document, with correct headings, as I need to show in the summary the number of appointments by type and appointment status at Branch Level and on a monthly basis. In the next columns section I need to have the same for solutions and contributions by type.
I then need to calculate the FTE in the following columns per branch per month, multiplying the volume by the AHT, dividing by 60 then 21 then 7.
Can I do this using VBA as doing it with formulas in the cells keeps causing the whole thing to crash.
I have SQL code for the outcome I desire but not the facility to be able to use it.
I am stumped with how to approach this and don't know if it is possible in VBA. I have attached a dummy document, with correct headings, as I need to show in the summary the number of appointments by type and appointment status at Branch Level and on a monthly basis. In the next columns section I need to have the same for solutions and contributions by type.
I then need to calculate the FTE in the following columns per branch per month, multiplying the volume by the AHT, dividing by 60 then 21 then 7.
Can I do this using VBA as doing it with formulas in the cells keeps causing the whole thing to crash.
I have SQL code for the outcome I desire but not the facility to be able to use it.
SQL:
CREATE TABLE #calcs (
appt_date DATETIME,
branch VARCHAR(200),
appt_type VARCHAR(200),
appt_status VARCHAR(200),
appt_length INT,
volume INT
)
INSERT INTO #calcs VALUES
('2022/02/01', 'LLS', 'CA', 'Fulfilled', 60, 20),
('2022/03/01', 'LLS', 'Loan', 'Fulfilled', 60, 80),
('2023/02/01', 'LLS', 'DA', 'Fulfilled', 60, 20),
('2022/02/01', 'LLS', 'Loan', 'No Show', 60, 30),
('2022/02/01', 'LLS', 'CA', 'Not Updated', 60, 20),
('2022/01/01', 'LLS', 'CA', 'Fulfilled', 60, 20),
('2022/01/01', 'LLS', 'CA', 'Fulfilled', 45, 50),
('2022/02/01', 'LLS', 'CA', 'Fulfilled', 30, 50),
('2022/02/01', 'LLS', 'Loan', 'Fulfiled', 45, 70)
CREATE TABLE #vars(
year INT,
month INT,
working_days INT,
shrinkage DECIMAL(20,3)
)
INSERT INTO #vars VALUES
(2022, 2, 20, 0.253),
(2022, 3, 22, 0.351),
(2023, 2, 18, 0.253),
(2022, 1, 21, 0.253)
CREATE TABLE #results(
year INT,
month INT,
branch VARCHAR(200),
appt_type VARCHAR(200),
appt_status VARCHAR(200),
calc DECIMAL(32,2)
)
SELECT * FROM #calcs
SELECT * FROM #vars
INSERT INTO #results
SELECT year, month, branch, appt_type, appt_status,
ROUND( ROUND( ROUND( hours / working_days, 2) / 7, 2) * (1 + shrinkage), 2) calc
FROM ( SELECT YEAR(appt_date) year, MONTH(appt_date) month, branch, appt_type, appt_status,
ROUND( CAST( SUM( appt_length * volume ) AS DECIMAL(32,6) ) / 60, 2 ) hours
FROM #calcs
GROUP BY YEAR(appt_date), MONTH(appt_date), branch, appt_type, appt_status
) calcs
CROSS APPLY(
SELECT working_days, shrinkage
FROM #vars vars
WHERE calcs.year = vars.year
AND calcs.month = vars.month
) vars
SELECT year, month,
CASE month WHEN 1 THEN year - 1 ELSE year END prev_months_year,
CASE month WHEN 1 THEN 12 ELSE month - 1 END prev_month,
branch, appt_type, appt_status
FROM #results
WHERE appt_status IN('Fulfilled', 'Not Updated')
SELECT branch, appt_type, appt_status, year, month, prev_year, prev_month, calc, prev_calc, calc - prev_calc difference
FROM(
SELECT year, month, branch, appt_type, appt_status, calc
FROM #results
WHERE appt_status IN('Fulfilled', 'Not Updated')
) curr
OUTER APPLY(
SELECT year prev_year, month prev_month, calc prev_calc
FROM #results prev
WHERE CASE curr.month WHEN 1 THEN curr.year - 1 ELSE curr.year END = prev.year
AND CASE curr.month WHEN 1 THEN 12 ELSE curr.month - 1 END = prev.month
AND curr.branch = prev.branch
AND curr.appt_type = prev.appt_type
AND curr.appt_status = prev.appt_status
) previous
DROP TABLE #calcs
DROP TABLE #vars
DROP TABLE #results
