/****************************************** Alteryx Weekly Challenge 417 ******************************************
NOTE - below script works with pre-condition that Alteryx input was loaded to database and table
under schema_name.table_name was created: [training].[awc417]
*/
WITH clean_input AS
(SELECT
LEFT([Employee_ID,Shift Dates,Shift Duration],CHARINDEX(',',[Employee_ID,Shift Dates,Shift Duration])-1) AS employee_id
,CAST(SUBSTRING([Employee_ID,Shift Dates,Shift Duration],CHARINDEX(',',[Employee_ID,Shift Dates,Shift Duration])+1,10) AS date) shift_dates
,CAST(RIGHT([Employee_ID,Shift Dates,Shift Duration],1) AS decimal(19,2)) AS duration
  FROM [training].[awc417])
,
above_four AS
(SELECT *
,CASE 
	WHEN [duration] > 4 THEN 'Above_4' ELSE 'Below_4' 
 END AS shift_above_4
FROM clean_input)
,
shift_counts AS
(SELECT employee_id, CAST(COUNT(shift_dates) AS decimal(19,2)) AS shift_count
FROM clean_input
GROUP BY employee_id)
,
above_four_shift_counts AS
(SELECT employee_id, CAST(COUNT(shift_dates) AS decimal(19,2)) AS above_four_shift_count
FROM above_four
WHERE shift_above_4 = 'Above_4'
GROUP BY employee_id)
,
below_four_shift_counts AS
(SELECT employee_id, CAST(COUNT(shift_dates) AS decimal(19,2)) AS below_four_shift_count
FROM above_four
WHERE shift_above_4 = 'Below_4'
GROUP BY employee_id)

-- Final result
SELECT sc.*
,ISNULL(af.above_four_shift_count,0) AS above_four_shift_count
,ISNULL(bf.below_four_shift_count,0) AS below_four_shift_count
,90 - sc.shift_count AS non_working_days
,CAST(ISNULL(bf.below_four_shift_count,0)/sc.shift_count*100 AS decimal(19,2)) AS pct_below_four_from_total
FROM shift_counts AS sc
LEFT OUTER JOIN above_four_shift_counts AS af ON af.employee_id = sc.employee_id
LEFT OUTER JOIN below_four_shift_counts AS bf ON bf.employee_id = sc.employee_id