Cacher is the code snippet organizer for pro developers

SELECT
s.JobID,
j.EmailName,
CASE WHEN 	j.FromName LIKE '%Audi%' THEN 'Audi'
WHEN 		j.FromName LIKE '%Volkswagen%' AND j.FromName NOT LIKE '%Bedrijfswagens' AND
			j.FromName NOT LIKE '%calamiteiten%' THEN 'Volkswagen'
WHEN 		j.FromName LIKE '%ŠKODA%' THEN 'Skoda'
WHEN 		j.FromName LIKE '%SEAT%' THEN 'SEAT' 
WHEN 		j.FromName LIKE '%Porsche%' THEN 'Porsche' 
WHEN 		j.FromName LIKE '%Bedrijfswagens%' THEN 'Volkswagen Bedrijfswagens' 
ELSE 'Not_specified'
END AS Brand,
FORMAT(CAST(s.Eventdate AS DATE),'yyyyMMdd') AS SendDate,
CASE WHEN (j.EmailName LIKE '%APK_Reminder%' OR (j.EmailName LIKE '%APK%' AND j.EmailName LIKE '%-remind%'))
THEN 'APK_Reminder'
	WHEN (j.EmailName LIKE '%APK_Merkonderhoud%'OR j.EmailName LIKE '%APK_MERKENONDERHOUD%'OR (j.EmailName LIKE '%-merkonderhoud%' AND j.EmailName LIKE '%APK%')
		OR j.EmailName LIKE '%APK_merkonderhoud%')
THEN 'APK_Onderhoud'
    WHEN j.EmailName LIKE '%Werkplaats%'
THEN 'Werkplaatsplanner'
	WHEN j.EmailName LIKE '%APK%'
THEN 'APK'
	WHEN (j.EmailName LIKE '%_NB_%' OR j.EmailName LIKE '%nieuwsbrief%' OR j.EmailName LIKE '%newsletter%')
THEN 'Nieuwsbrief'
	WHEN (j.EmailName LIKE '%VOI-VERVOLG%'OR j.EmailName LIKE '%VOI-herhaald%' OR (j.EmailName LIKE '%VOI%' AND j.EmailName LIKE '%remind%')) 
THEN 'VOI_Reminder'
	WHEN (j.EmailName LIKE '%onderhoud%'OR j.EmailName LIKE '%VOI-1%'OR j.EmailName LIKE '[%VOI na 2014%]'OR j.EmailName LIKE '%VOI%'OR j.EmailName LIKE '%Economy%'OR j.EmailName LIKE '%VAST%'OR j.EmailName LIKE '%VOI-BUITEN-GARANTIE%'OR j.EmailName LIKE '[%VOI electrisch%]') 
THEN 'VOI'
	WHEN j.EmailName LIKE '%brochure%'
THEN 'Brochure'
	WHEN j.EmailName LIKE '%proefrit%'
THEN 'Proefrit'
	WHEN (j.EmailName LIKE '%BDC%' AND j.EmailName LIKE '%EDM2%') 
THEN 'BDC_eDM2'
	WHEN (j.EmailName LIKE '%BDC%') 
THEN 'BDC_eDM1'
	WHEN j.EmailName LIKE '%100_DGN_01%'
THEN '100_DGN_01'
	WHEN j.EmailName LIKE '%100_DGN_02%'
THEN '100_DGN_02'
	WHEN j.EmailName LIKE '%100_DGN_03%'
THEN '100_DGN_03'
	WHEN j.EmailName LIKE '%100_DGN_04%'
THEN '100_DGN_04'
	WHEN j.EmailName LIKE '%100_DGN_05%'
THEN '100_DGN_05'
	WHEN j.EmailName LIKE '%occasion%'
THEN 'Occasion_Check'
	WHEN (j.EmailName LIKE '%KMI%' OR j.EmailName LIKE '%T-Cross%' OR j.EmailName LIKE '%etron%') 
THEN 'KMI'
ELSE 'Ad_Hoc'
END AS Type,

CASE WHEN (j.EmailName LIKE '%werkplaats%' OR j.EmailName LIKE '%brochure%'OR j.EmailName LIKE '%proefrit%'OR j.EmailName LIKE '%transactional_Nieuwsbrief%'
	OR j.EmailName LIKE '%transactional-newsletter%'OR j.EmailName LIKE '%Bevestiging%' OR j.EmailName LIKE '%Transac%' OR j.EmailName LIKE '%onboard%')
    THEN 'Transactional'
WHEN (j.EmailName LIKE '%_NB_'OR j.EmailName LIKE '%occasion%') 
THEN 'Ad_Hoc'
	WHEN (j.EmailName LIKE '%100_DGN%'OR j.EmailName LIKE '%APK%'OR j.EmailName LIKE '%BDC%'OR j.EmailName LIKE '%VOI-1%'
		OR j.EmailName LIKE '%VOI-VERVOLG%' OR j.EmailName LIKE '[%VOI na 2014%]'OR j.EmailName LIKE '%VOI%'
		OR j.EmailName LIKE '%Economy%'OR j.EmailName LIKE '%VAST%'OR j.EmailName LIKE '%VOI-BUITEN-GARANTIE%'OR j.EmailName LIKE '%VOI-VERVOLG%' OR j.EmailName LIKE '%BDC') 
THEN 'CLC'
	WHEN (j.EmailName LIKE '%KMI%'OR j.EmailName LIKE '%T-Cross%' OR j.EmailName LIKE '%etron%') 
THEN 'Ad_Hoc'

ELSE 'Ad_Hoc'
END AS Category,
COUNT(FORMAT(CAST(s.Eventdate AS DATE),'yyyyMMdd')) AS Send
FROM _Sent s
INNER JOIN _Job j ON s.JobID = j.JobID
WHERE CAST(s.EventDate AS DATE) >= DATEADD(ww, -1, CAST(GETDATE() AS Date)) AND
(j.FromName LIKE '%Audi%' OR
(j.FromName LIKE '%Volkswagen%' AND j.FromName NOT LIKE '%calamiteiten%') OR
j.FromName LIKE '%Bedrijfswagens' OR
j.FromName LIKE '%ŠKODA%' OR
j.FromName LIKE '%SEAT%' OR
j.FromName LIKE '%Porsche%') AND
(j.EmailSubject NOT LIKE '%Test%' AND j.EmailName NOT LIKE '%Test%') AND
j.Category NOT LIKE '%test%'
GROUP BY FORMAT(CAST(s.Eventdate AS DATE),'yyyyMMdd'), s.JobID, j.EmailName, j.FromName