February 22, 2012 at 3:42 pm
I had some familiarty with Macros before when I used some SAS, but now that I do SQL exclusively I struggle with performing functions that a macro would be ideal for.
Can anyone show me a good example of how to simplify this code to create a table? I took a good bit out of it since my [Days to Contract] field goes all the way up to 30 and I have 10+ LOBs.
Just want something that maybe can do maybe a do while loop that can count [Days to Contract] all the way up to 30 and give me a simplified way to add additional LOBs.
Thanks in advance for any help.
IF OBJECT_ID('TempDB..#TEMP','U') IS NOT NULL
DROP TABLE #TEMP
CREATE TABLE #TEMP
(
LOB VARCHAR(25),
[Days Since App] INT,
[Days to Contract] VARCHAR(6)
)
INSERT INTO #TEMP
(LOB, [Days Since App], [Days to Contract])
SELECT 'CHR','0','00' UNION ALL
SELECT 'CHR','1','00' UNION ALL
SELECT 'CHR','2','00' UNION ALL
SELECT 'CHR','3','00' UNION ALL
SELECT 'CHR','4','00' UNION ALL
SELECT 'CHR','5','00' UNION ALL
SELECT 'CHR','6','00' UNION ALL
SELECT 'CHR','7','00' UNION ALL
SELECT 'CHR','8','00' UNION ALL
SELECT 'CHR','9','00' UNION ALL
SELECT 'CHR','10','00' UNION ALL
SELECT 'CHR','11','00' UNION ALL
SELECT 'CHR','12','00' UNION ALL
SELECT 'CHR','13','00' UNION ALL
SELECT 'CHR','14','00' UNION ALL
SELECT 'CHR','15','00' UNION ALL
SELECT 'CHR','16','00' UNION ALL
SELECT 'CHR','17','00' UNION ALL
SELECT 'CHR','18','00' UNION ALL
SELECT 'CHR','19','00' UNION ALL
SELECT 'CHR','20','00' UNION ALL
SELECT 'CHR','21','00' UNION ALL
SELECT 'CHR','22','00' UNION ALL
SELECT 'CHR','23','00' UNION ALL
SELECT 'CHR','24','00' UNION ALL
SELECT 'CHR','25','00' UNION ALL
SELECT 'CHR','26','00' UNION ALL
SELECT 'CHR','27','00' UNION ALL
SELECT 'CHR','28','00' UNION ALL
SELECT 'CHR','29','00' UNION ALL
SELECT 'CHR','30','00' UNION ALL
SELECT 'CHR','31','00' UNION ALL
SELECT 'CHR','32','00' UNION ALL
SELECT 'CHR','33','00' UNION ALL
SELECT 'CHR','34','00' UNION ALL
SELECT 'CHR','35','00' UNION ALL
SELECT 'CHR','36','00' UNION ALL
SELECT 'CHR','37','00' UNION ALL
SELECT 'CHR','38','00' UNION ALL
SELECT 'CHR','39','00' UNION ALL
SELECT 'CHR','40','00' UNION ALL
SELECT 'CHR','41','00' UNION ALL
SELECT 'CHR','42','00' UNION ALL
SELECT 'CHR','43','00' UNION ALL
SELECT 'CHR','44','00' UNION ALL
SELECT 'CHR','45','00' UNION ALL
SELECT 'RL Retail','0','00' UNION ALL
SELECT 'RL Retail','1','00' UNION ALL
SELECT 'RL Retail','2','00' UNION ALL
SELECT 'RL Retail','3','00' UNION ALL
SELECT 'RL Retail','4','00' UNION ALL
SELECT 'RL Retail','5','00' UNION ALL
SELECT 'RL Retail','6','00' UNION ALL
SELECT 'RL Retail','7','00' UNION ALL
SELECT 'RL Retail','8','00' UNION ALL
SELECT 'RL Retail','9','00' UNION ALL
SELECT 'RL Retail','10','00' UNION ALL
SELECT 'RL Retail','11','00' UNION ALL
SELECT 'RL Retail','12','00' UNION ALL
SELECT 'RL Retail','13','00' UNION ALL
SELECT 'RL Retail','14','00' UNION ALL
SELECT 'RL Retail','15','00' UNION ALL
SELECT 'RL Retail','16','00' UNION ALL
SELECT 'RL Retail','17','00' UNION ALL
SELECT 'RL Retail','18','00' UNION ALL
SELECT 'RL Retail','19','00' UNION ALL
SELECT 'RL Retail','20','00' UNION ALL
SELECT 'RL Retail','21','00' UNION ALL
SELECT 'RL Retail','22','00' UNION ALL
SELECT 'RL Retail','23','00' UNION ALL
SELECT 'RL Retail','24','00' UNION ALL
SELECT 'RL Retail','25','00' UNION ALL
SELECT 'RL Retail','26','00' UNION ALL
SELECT 'RL Retail','27','00' UNION ALL
SELECT 'RL Retail','28','00' UNION ALL
SELECT 'RL Retail','29','00' UNION ALL
SELECT 'RL Retail','30','00' UNION ALL
SELECT 'RL Retail','31','00' UNION ALL
SELECT 'RL Retail','32','00' UNION ALL
SELECT 'RL Retail','33','00' UNION ALL
SELECT 'RL Retail','34','00' UNION ALL
SELECT 'RL Retail','35','00' UNION ALL
SELECT 'RL Retail','36','00' UNION ALL
SELECT 'RL Retail','37','00' UNION ALL
SELECT 'RL Retail','38','00' UNION ALL
SELECT 'RL Retail','39','00' UNION ALL
SELECT 'RL Retail','40','00' UNION ALL
SELECT 'RL Retail','41','00' UNION ALL
SELECT 'RL Retail','42','00' UNION ALL
SELECT 'RL Retail','43','00' UNION ALL
SELECT 'RL Retail','44','00' UNION ALL
SELECT 'RL Retail','45','00' UNION ALL
SELECT 'SAF','0','00' UNION ALL
SELECT 'SAF','1','00' UNION ALL
SELECT 'SAF','2','00' UNION ALL
SELECT 'SAF','3','00' UNION ALL
SELECT 'SAF','4','00' UNION ALL
SELECT 'SAF','5','00' UNION ALL
SELECT 'SAF','6','00' UNION ALL
SELECT 'SAF','7','00' UNION ALL
SELECT 'SAF','8','00' UNION ALL
SELECT 'SAF','9','00' UNION ALL
SELECT 'SAF','10','00' UNION ALL
SELECT 'SAF','11','00' UNION ALL
SELECT 'SAF','12','00' UNION ALL
SELECT 'SAF','13','00' UNION ALL
SELECT 'SAF','14','00' UNION ALL
SELECT 'SAF','15','00' UNION ALL
SELECT 'SAF','16','00' UNION ALL
SELECT 'SAF','17','00' UNION ALL
SELECT 'SAF','18','00' UNION ALL
SELECT 'SAF','19','00' UNION ALL
SELECT 'SAF','20','00' UNION ALL
SELECT 'SAF','21','00' UNION ALL
SELECT 'SAF','22','00' UNION ALL
SELECT 'SAF','23','00' UNION ALL
SELECT 'SAF','24','00' UNION ALL
SELECT 'SAF','25','00' UNION ALL
SELECT 'SAF','26','00' UNION ALL
SELECT 'SAF','27','00' UNION ALL
SELECT 'SAF','28','00' UNION ALL
SELECT 'SAF','29','00' UNION ALL
SELECT 'SAF','30','00' UNION ALL
SELECT 'SAF','31','00' UNION ALL
SELECT 'SAF','32','00' UNION ALL
SELECT 'SAF','33','00' UNION ALL
SELECT 'SAF','34','00' UNION ALL
SELECT 'SAF','35','00' UNION ALL
SELECT 'SAF','36','00' UNION ALL
SELECT 'SAF','37','00' UNION ALL
SELECT 'SAF','38','00' UNION ALL
SELECT 'SAF','39','00' UNION ALL
SELECT 'SAF','40','00' UNION ALL
SELECT 'SAF','41','00' UNION ALL
SELECT 'SAF','42','00' UNION ALL
SELECT 'SAF','43','00' UNION ALL
SELECT 'SAF','44','00' UNION ALL
SELECT 'SAF','45','00' UNION ALL
SELECT 'CHR','0','01' UNION ALL
SELECT 'CHR','1','01' UNION ALL
SELECT 'CHR','2','01' UNION ALL
SELECT 'CHR','3','01' UNION ALL
SELECT 'CHR','4','01' UNION ALL
SELECT 'CHR','5','01' UNION ALL
SELECT 'CHR','6','01' UNION ALL
SELECT 'CHR','7','01' UNION ALL
SELECT 'CHR','8','01' UNION ALL
SELECT 'CHR','9','01' UNION ALL
SELECT 'CHR','10','01' UNION ALL
SELECT 'CHR','11','01' UNION ALL
SELECT 'CHR','12','01' UNION ALL
SELECT 'CHR','13','01' UNION ALL
SELECT 'CHR','14','01' UNION ALL
SELECT 'CHR','15','01' UNION ALL
SELECT 'CHR','16','01' UNION ALL
SELECT 'CHR','17','01' UNION ALL
SELECT 'CHR','18','01' UNION ALL
SELECT 'CHR','19','01' UNION ALL
SELECT 'CHR','20','01' UNION ALL
SELECT 'CHR','21','01' UNION ALL
SELECT 'CHR','22','01' UNION ALL
SELECT 'CHR','23','01' UNION ALL
SELECT 'CHR','24','01' UNION ALL
SELECT 'CHR','25','01' UNION ALL
SELECT 'CHR','26','01' UNION ALL
SELECT 'CHR','27','01' UNION ALL
SELECT 'CHR','28','01' UNION ALL
SELECT 'CHR','29','01' UNION ALL
SELECT 'CHR','30','01' UNION ALL
SELECT 'CHR','31','01' UNION ALL
SELECT 'CHR','32','01' UNION ALL
SELECT 'CHR','33','01' UNION ALL
SELECT 'CHR','34','01' UNION ALL
SELECT 'CHR','35','01' UNION ALL
SELECT 'CHR','36','01' UNION ALL
SELECT 'CHR','37','01' UNION ALL
SELECT 'CHR','38','01' UNION ALL
SELECT 'CHR','39','01' UNION ALL
SELECT 'CHR','40','01' UNION ALL
SELECT 'CHR','41','01' UNION ALL
SELECT 'CHR','42','01' UNION ALL
SELECT 'CHR','43','01' UNION ALL
SELECT 'CHR','44','01' UNION ALL
SELECT 'CHR','45','01' UNION ALL
SELECT 'RL Retail','0','01' UNION ALL
SELECT 'RL Retail','1','01' UNION ALL
SELECT 'RL Retail','2','01' UNION ALL
SELECT 'RL Retail','3','01' UNION ALL
SELECT 'RL Retail','4','01' UNION ALL
SELECT 'RL Retail','5','01' UNION ALL
SELECT 'RL Retail','6','01' UNION ALL
SELECT 'RL Retail','7','01' UNION ALL
SELECT 'RL Retail','8','01' UNION ALL
SELECT 'RL Retail','9','01' UNION ALL
SELECT 'RL Retail','10','01' UNION ALL
SELECT 'RL Retail','11','01' UNION ALL
SELECT 'RL Retail','12','01' UNION ALL
SELECT 'RL Retail','13','01' UNION ALL
SELECT 'RL Retail','14','01' UNION ALL
SELECT 'RL Retail','15','01' UNION ALL
SELECT 'RL Retail','16','01' UNION ALL
SELECT 'RL Retail','17','01' UNION ALL
SELECT 'RL Retail','18','01' UNION ALL
SELECT 'RL Retail','19','01' UNION ALL
SELECT 'RL Retail','20','01' UNION ALL
SELECT 'RL Retail','21','01' UNION ALL
SELECT 'RL Retail','22','01' UNION ALL
SELECT 'RL Retail','23','01' UNION ALL
SELECT 'RL Retail','24','01' UNION ALL
SELECT 'RL Retail','25','01' UNION ALL
SELECT 'RL Retail','26','01' UNION ALL
SELECT 'RL Retail','27','01' UNION ALL
SELECT 'RL Retail','28','01' UNION ALL
SELECT 'RL Retail','29','01' UNION ALL
SELECT 'RL Retail','30','01' UNION ALL
SELECT 'RL Retail','31','01' UNION ALL
SELECT 'RL Retail','32','01' UNION ALL
SELECT 'RL Retail','33','01' UNION ALL
SELECT 'RL Retail','34','01' UNION ALL
SELECT 'RL Retail','35','01' UNION ALL
SELECT 'RL Retail','36','01' UNION ALL
SELECT 'RL Retail','37','01' UNION ALL
SELECT 'RL Retail','38','01' UNION ALL
SELECT 'RL Retail','39','01' UNION ALL
SELECT 'RL Retail','40','01' UNION ALL
SELECT 'RL Retail','41','01' UNION ALL
SELECT 'RL Retail','42','01' UNION ALL
SELECT 'RL Retail','43','01' UNION ALL
SELECT 'RL Retail','44','01' UNION ALL
SELECT 'RL Retail','45','01' UNION ALL
SELECT 'SAF','0','01' UNION ALL
SELECT 'SAF','1','01' UNION ALL
SELECT 'SAF','2','01' UNION ALL
SELECT 'SAF','3','01' UNION ALL
SELECT 'SAF','4','01' UNION ALL
SELECT 'SAF','5','01' UNION ALL
SELECT 'SAF','6','01' UNION ALL
SELECT 'SAF','7','01' UNION ALL
SELECT 'SAF','8','01' UNION ALL
SELECT 'SAF','9','01' UNION ALL
SELECT 'SAF','10','01' UNION ALL
SELECT 'SAF','11','01' UNION ALL
SELECT 'SAF','12','01' UNION ALL
SELECT 'SAF','13','01' UNION ALL
SELECT 'SAF','14','01' UNION ALL
SELECT 'SAF','15','01' UNION ALL
SELECT 'SAF','16','01' UNION ALL
SELECT 'SAF','17','01' UNION ALL
SELECT 'SAF','18','01' UNION ALL
SELECT 'SAF','19','01' UNION ALL
SELECT 'SAF','20','01' UNION ALL
SELECT 'SAF','21','01' UNION ALL
SELECT 'SAF','22','01' UNION ALL
SELECT 'SAF','23','01' UNION ALL
SELECT 'SAF','24','01' UNION ALL
SELECT 'SAF','25','01' UNION ALL
SELECT 'SAF','26','01' UNION ALL
SELECT 'SAF','27','01' UNION ALL
SELECT 'SAF','28','01' UNION ALL
SELECT 'SAF','29','01' UNION ALL
SELECT 'SAF','30','01' UNION ALL
SELECT 'SAF','31','01' UNION ALL
SELECT 'SAF','32','01' UNION ALL
SELECT 'SAF','33','01' UNION ALL
SELECT 'SAF','34','01' UNION ALL
SELECT 'SAF','35','01' UNION ALL
SELECT 'SAF','36','01' UNION ALL
SELECT 'SAF','37','01' UNION ALL
SELECT 'SAF','38','01' UNION ALL
SELECT 'SAF','39','01' UNION ALL
SELECT 'SAF','40','01' UNION ALL
SELECT 'SAF','41','01' UNION ALL
SELECT 'SAF','42','01' UNION ALL
SELECT 'SAF','43','01' UNION ALL
SELECT 'SAF','44','01' UNION ALL
SELECT 'SAF','45','01' UNION ALL
SELECT 'CHR','0','02' UNION ALL
SELECT 'CHR','1','02' UNION ALL
SELECT 'CHR','2','02' UNION ALL
SELECT 'CHR','3','02' UNION ALL
SELECT 'CHR','4','02' UNION ALL
SELECT 'CHR','5','02' UNION ALL
SELECT 'CHR','6','02' UNION ALL
SELECT 'CHR','7','02' UNION ALL
SELECT 'CHR','8','02' UNION ALL
SELECT 'CHR','9','02' UNION ALL
SELECT 'CHR','10','02' UNION ALL
SELECT 'CHR','11','02' UNION ALL
SELECT 'CHR','12','02' UNION ALL
SELECT 'CHR','13','02' UNION ALL
SELECT 'CHR','14','02' UNION ALL
SELECT 'CHR','15','02' UNION ALL
SELECT 'CHR','16','02' UNION ALL
SELECT 'CHR','17','02' UNION ALL
SELECT 'CHR','18','02' UNION ALL
SELECT 'CHR','19','02' UNION ALL
SELECT 'CHR','20','02' UNION ALL
SELECT 'CHR','21','02' UNION ALL
SELECT 'CHR','22','02' UNION ALL
SELECT 'CHR','23','02' UNION ALL
SELECT 'CHR','24','02' UNION ALL
SELECT 'CHR','25','02' UNION ALL
SELECT 'CHR','26','02' UNION ALL
SELECT 'CHR','27','02' UNION ALL
SELECT 'CHR','28','02' UNION ALL
SELECT 'CHR','29','02' UNION ALL
SELECT 'CHR','30','02' UNION ALL
SELECT 'CHR','31','02' UNION ALL
SELECT 'CHR','32','02' UNION ALL
SELECT 'CHR','33','02' UNION ALL
SELECT 'CHR','34','02' UNION ALL
SELECT 'CHR','35','02' UNION ALL
SELECT 'CHR','36','02' UNION ALL
SELECT 'CHR','37','02' UNION ALL
SELECT 'CHR','38','02' UNION ALL
SELECT 'CHR','39','02' UNION ALL
SELECT 'CHR','40','02' UNION ALL
SELECT 'CHR','41','02' UNION ALL
SELECT 'CHR','42','02' UNION ALL
SELECT 'CHR','43','02' UNION ALL
SELECT 'CHR','44','02' UNION ALL
SELECT 'CHR','45','02' UNION ALL
SELECT 'RL Retail','0','02' UNION ALL
SELECT 'RL Retail','1','02' UNION ALL
SELECT 'RL Retail','2','02' UNION ALL
SELECT 'RL Retail','3','02' UNION ALL
SELECT 'RL Retail','4','02' UNION ALL
SELECT 'RL Retail','5','02' UNION ALL
SELECT 'RL Retail','6','02' UNION ALL
SELECT 'RL Retail','7','02' UNION ALL
SELECT 'RL Retail','8','02' UNION ALL
SELECT 'RL Retail','9','02' UNION ALL
SELECT 'RL Retail','10','02' UNION ALL
SELECT 'RL Retail','11','02' UNION ALL
SELECT 'RL Retail','12','02' UNION ALL
SELECT 'RL Retail','13','02' UNION ALL
SELECT 'RL Retail','14','02' UNION ALL
SELECT 'RL Retail','15','02' UNION ALL
SELECT 'RL Retail','16','02' UNION ALL
SELECT 'RL Retail','17','02' UNION ALL
SELECT 'RL Retail','18','02' UNION ALL
SELECT 'RL Retail','19','02' UNION ALL
SELECT 'RL Retail','20','02' UNION ALL
SELECT 'RL Retail','21','02' UNION ALL
SELECT 'RL Retail','22','02' UNION ALL
SELECT 'RL Retail','23','02' UNION ALL
SELECT 'RL Retail','24','02' UNION ALL
SELECT 'RL Retail','25','02' UNION ALL
SELECT 'RL Retail','26','02' UNION ALL
SELECT 'RL Retail','27','02' UNION ALL
SELECT 'RL Retail','28','02' UNION ALL
SELECT 'RL Retail','29','02' UNION ALL
SELECT 'RL Retail','30','02' UNION ALL
SELECT 'RL Retail','31','02' UNION ALL
SELECT 'RL Retail','32','02' UNION ALL
SELECT 'RL Retail','33','02' UNION ALL
SELECT 'RL Retail','34','02' UNION ALL
SELECT 'RL Retail','35','02' UNION ALL
SELECT 'RL Retail','36','02' UNION ALL
SELECT 'RL Retail','37','02' UNION ALL
SELECT 'RL Retail','38','02' UNION ALL
SELECT 'RL Retail','39','02' UNION ALL
SELECT 'RL Retail','40','02' UNION ALL
SELECT 'RL Retail','41','02' UNION ALL
SELECT 'RL Retail','42','02' UNION ALL
SELECT 'RL Retail','43','02' UNION ALL
SELECT 'RL Retail','44','02' UNION ALL
SELECT 'RL Retail','45','02' UNION ALL
SELECT 'SAF','0','02' UNION ALL
SELECT 'SAF','1','02' UNION ALL
SELECT 'SAF','2','02' UNION ALL
SELECT 'SAF','3','02' UNION ALL
SELECT 'SAF','4','02' UNION ALL
SELECT 'SAF','5','02' UNION ALL
SELECT 'SAF','6','02' UNION ALL
SELECT 'SAF','7','02' UNION ALL
SELECT 'SAF','8','02' UNION ALL
SELECT 'SAF','9','02' UNION ALL
SELECT 'SAF','10','02' UNION ALL
SELECT 'SAF','11','02' UNION ALL
SELECT 'SAF','12','02' UNION ALL
SELECT 'SAF','13','02' UNION ALL
SELECT 'SAF','14','02' UNION ALL
SELECT 'SAF','15','02' UNION ALL
SELECT 'SAF','16','02' UNION ALL
SELECT 'SAF','17','02' UNION ALL
SELECT 'SAF','18','02' UNION ALL
SELECT 'SAF','19','02' UNION ALL
SELECT 'SAF','20','02' UNION ALL
SELECT 'SAF','21','02' UNION ALL
SELECT 'SAF','22','02' UNION ALL
SELECT 'SAF','23','02' UNION ALL
SELECT 'SAF','24','02' UNION ALL
SELECT 'SAF','25','02' UNION ALL
SELECT 'SAF','26','02' UNION ALL
SELECT 'SAF','27','02' UNION ALL
SELECT 'SAF','28','02' UNION ALL
SELECT 'SAF','29','02' UNION ALL
SELECT 'SAF','30','02' UNION ALL
SELECT 'SAF','31','02' UNION ALL
SELECT 'SAF','32','02' UNION ALL
SELECT 'SAF','33','02' UNION ALL
SELECT 'SAF','34','02' UNION ALL
SELECT 'SAF','35','02' UNION ALL
SELECT 'SAF','36','02' UNION ALL
SELECT 'SAF','37','02' UNION ALL
SELECT 'SAF','38','02' UNION ALL
SELECT 'SAF','39','02' UNION ALL
SELECT 'SAF','40','02' UNION ALL
SELECT 'SAF','41','02' UNION ALL
SELECT 'SAF','42','02' UNION ALL
SELECT 'SAF','43','02' UNION ALL
SELECT 'SAF','44','02' UNION ALL
SELECT 'SAF','45','02' UNION ALL
SELECT 'CHR','0','03' UNION ALL
SELECT 'CHR','1','03' UNION ALL
SELECT 'CHR','2','03' UNION ALL
SELECT 'CHR','3','03' UNION ALL
SELECT 'CHR','4','03' UNION ALL
SELECT 'CHR','5','03' UNION ALL
SELECT 'CHR','6','03' UNION ALL
SELECT 'CHR','7','03' UNION ALL
SELECT 'CHR','8','03' UNION ALL
SELECT 'CHR','9','03' UNION ALL
SELECT 'CHR','10','03' UNION ALL
SELECT 'CHR','11','03' UNION ALL
SELECT 'CHR','12','03' UNION ALL
SELECT 'CHR','13','03' UNION ALL
SELECT 'CHR','14','03' UNION ALL
SELECT 'CHR','15','03' UNION ALL
SELECT 'CHR','16','03' UNION ALL
SELECT 'CHR','17','03' UNION ALL
SELECT 'CHR','18','03' UNION ALL
SELECT 'CHR','19','03' UNION ALL
SELECT 'CHR','20','03' UNION ALL
SELECT 'CHR','21','03' UNION ALL
SELECT 'CHR','22','03' UNION ALL
SELECT 'CHR','23','03' UNION ALL
SELECT 'CHR','24','03' UNION ALL
SELECT 'CHR','25','03' UNION ALL
SELECT 'CHR','26','03' UNION ALL
SELECT 'CHR','27','03' UNION ALL
SELECT 'CHR','28','03' UNION ALL
SELECT 'CHR','29','03' UNION ALL
SELECT 'CHR','30','03' UNION ALL
SELECT 'CHR','31','03' UNION ALL
SELECT 'CHR','32','03' UNION ALL
SELECT 'CHR','33','03' UNION ALL
SELECT 'CHR','34','03' UNION ALL
SELECT 'CHR','35','03' UNION ALL
SELECT 'CHR','36','03' UNION ALL
SELECT 'CHR','37','03' UNION ALL
SELECT 'CHR','38','03' UNION ALL
SELECT 'CHR','39','03' UNION ALL
SELECT 'CHR','40','03' UNION ALL
SELECT 'CHR','41','03' UNION ALL
SELECT 'CHR','42','03' UNION ALL
SELECT 'CHR','43','03' UNION ALL
SELECT 'CHR','44','03' UNION ALL
SELECT 'CHR','45','03' UNION ALL
SELECT 'RL Retail','0','03' UNION ALL
SELECT 'RL Retail','1','03' UNION ALL
SELECT 'RL Retail','2','03' UNION ALL
SELECT 'RL Retail','3','03' UNION ALL
SELECT 'RL Retail','4','03' UNION ALL
SELECT 'RL Retail','5','03' UNION ALL
SELECT 'RL Retail','6','03' UNION ALL
SELECT 'RL Retail','7','03' UNION ALL
SELECT 'RL Retail','8','03' UNION ALL
SELECT 'RL Retail','9','03' UNION ALL
SELECT 'RL Retail','10','03' UNION ALL
SELECT 'RL Retail','11','03' UNION ALL
SELECT 'RL Retail','12','03' UNION ALL
SELECT 'RL Retail','13','03' UNION ALL
SELECT 'RL Retail','14','03' UNION ALL
SELECT 'RL Retail','15','03' UNION ALL
SELECT 'RL Retail','16','03' UNION ALL
SELECT 'RL Retail','17','03' UNION ALL
SELECT 'RL Retail','18','03' UNION ALL
SELECT 'RL Retail','19','03' UNION ALL
SELECT 'RL Retail','20','03' UNION ALL
SELECT 'RL Retail','21','03' UNION ALL
SELECT 'RL Retail','22','03' UNION ALL
SELECT 'RL Retail','23','03' UNION ALL
SELECT 'RL Retail','24','03' UNION ALL
SELECT 'RL Retail','25','03' UNION ALL
SELECT 'RL Retail','26','03' UNION ALL
SELECT 'RL Retail','27','03' UNION ALL
SELECT 'RL Retail','28','03' UNION ALL
SELECT 'RL Retail','29','03' UNION ALL
SELECT 'RL Retail','30','03' UNION ALL
SELECT 'RL Retail','31','03' UNION ALL
SELECT 'RL Retail','32','03' UNION ALL
SELECT 'RL Retail','33','03' UNION ALL
SELECT 'RL Retail','34','03' UNION ALL
SELECT 'RL Retail','35','03' UNION ALL
SELECT 'RL Retail','36','03' UNION ALL
SELECT 'RL Retail','37','03' UNION ALL
SELECT 'RL Retail','38','03' UNION ALL
SELECT 'RL Retail','39','03' UNION ALL
SELECT 'RL Retail','40','03' UNION ALL
SELECT 'RL Retail','41','03' UNION ALL
SELECT 'RL Retail','42','03' UNION ALL
SELECT 'RL Retail','43','03' UNION ALL
SELECT 'RL Retail','44','03' UNION ALL
SELECT 'RL Retail','45','03' UNION ALL
SELECT 'SAF','0','03' UNION ALL
SELECT 'SAF','1','03' UNION ALL
SELECT 'SAF','2','03' UNION ALL
SELECT 'SAF','3','03' UNION ALL
SELECT 'SAF','4','03' UNION ALL
SELECT 'SAF','5','03' UNION ALL
SELECT 'SAF','6','03' UNION ALL
SELECT 'SAF','7','03' UNION ALL
SELECT 'SAF','8','03' UNION ALL
SELECT 'SAF','9','03' UNION ALL
SELECT 'SAF','10','03' UNION ALL
SELECT 'SAF','11','03' UNION ALL
SELECT 'SAF','12','03' UNION ALL
SELECT 'SAF','13','03' UNION ALL
SELECT 'SAF','14','03' UNION ALL
SELECT 'SAF','15','03' UNION ALL
SELECT 'SAF','16','03' UNION ALL
SELECT 'SAF','17','03' UNION ALL
SELECT 'SAF','18','03' UNION ALL
SELECT 'SAF','19','03' UNION ALL
SELECT 'SAF','20','03' UNION ALL
SELECT 'SAF','21','03' UNION ALL
SELECT 'SAF','22','03' UNION ALL
SELECT 'SAF','23','03' UNION ALL
SELECT 'SAF','24','03' UNION ALL
SELECT 'SAF','25','03' UNION ALL
SELECT 'SAF','26','03' UNION ALL
SELECT 'SAF','27','03' UNION ALL
SELECT 'SAF','28','03' UNION ALL
SELECT 'SAF','29','03' UNION ALL
SELECT 'SAF','30','03' UNION ALL
SELECT 'SAF','31','03' UNION ALL
SELECT 'SAF','32','03' UNION ALL
SELECT 'SAF','33','03' UNION ALL
SELECT 'SAF','34','03' UNION ALL
SELECT 'SAF','35','03' UNION ALL
SELECT 'SAF','36','03' UNION ALL
SELECT 'SAF','37','03' UNION ALL
SELECT 'SAF','38','03' UNION ALL
SELECT 'SAF','39','03' UNION ALL
SELECT 'SAF','40','03' UNION ALL
SELECT 'SAF','41','03' UNION ALL
SELECT 'SAF','42','03' UNION ALL
SELECT 'SAF','43','03' UNION ALL
SELECT 'SAF','44','03' UNION ALL
SELECT 'SAF','45','03' UNION ALL
SELECT 'CHR','0','04' UNION ALL
SELECT 'CHR','1','04' UNION ALL
SELECT 'CHR','2','04' UNION ALL
SELECT 'CHR','3','04' UNION ALL
SELECT 'CHR','4','04' UNION ALL
SELECT 'CHR','5','04' UNION ALL
SELECT 'CHR','6','04' UNION ALL
SELECT 'CHR','7','04' UNION ALL
SELECT 'CHR','8','04' UNION ALL
SELECT 'CHR','9','04' UNION ALL
SELECT 'CHR','10','04' UNION ALL
SELECT 'CHR','11','04' UNION ALL
SELECT 'CHR','12','04' UNION ALL
SELECT 'CHR','13','04' UNION ALL
SELECT 'CHR','14','04' UNION ALL
SELECT 'CHR','15','04' UNION ALL
SELECT 'CHR','16','04' UNION ALL
SELECT 'CHR','17','04' UNION ALL
SELECT 'CHR','18','04' UNION ALL
SELECT 'CHR','19','04' UNION ALL
SELECT 'CHR','20','04' UNION ALL
SELECT 'CHR','21','04' UNION ALL
SELECT 'CHR','22','04' UNION ALL
SELECT 'CHR','23','04' UNION ALL
SELECT 'CHR','24','04' UNION ALL
SELECT 'CHR','25','04' UNION ALL
SELECT 'CHR','26','04' UNION ALL
SELECT 'CHR','27','04' UNION ALL
SELECT 'CHR','28','04' UNION ALL
SELECT 'CHR','29','04' UNION ALL
SELECT 'CHR','30','04' UNION ALL
SELECT 'CHR','31','04' UNION ALL
SELECT 'CHR','32','04' UNION ALL
SELECT 'CHR','33','04' UNION ALL
SELECT 'CHR','34','04' UNION ALL
SELECT 'CHR','35','04' UNION ALL
SELECT 'CHR','36','04' UNION ALL
SELECT 'CHR','37','04' UNION ALL
SELECT 'CHR','38','04' UNION ALL
SELECT 'CHR','39','04' UNION ALL
SELECT 'CHR','40','04' UNION ALL
SELECT 'CHR','41','04' UNION ALL
SELECT 'CHR','42','04' UNION ALL
SELECT 'CHR','43','04' UNION ALL
SELECT 'CHR','44','04' UNION ALL
SELECT 'CHR','45','04' UNION ALL
SELECT 'RL Retail','0','04' UNION ALL
SELECT 'RL Retail','1','04' UNION ALL
SELECT 'RL Retail','2','04' UNION ALL
SELECT 'RL Retail','3','04' UNION ALL
SELECT 'RL Retail','4','04' UNION ALL
SELECT 'RL Retail','5','04' UNION ALL
SELECT 'RL Retail','6','04' UNION ALL
SELECT 'RL Retail','7','04' UNION ALL
SELECT 'RL Retail','8','04' UNION ALL
SELECT 'RL Retail','9','04' UNION ALL
SELECT 'RL Retail','10','04' UNION ALL
SELECT 'RL Retail','11','04' UNION ALL
SELECT 'RL Retail','12','04' UNION ALL
SELECT 'RL Retail','13','04' UNION ALL
SELECT 'RL Retail','14','04' UNION ALL
SELECT 'RL Retail','15','04' UNION ALL
SELECT 'RL Retail','16','04' UNION ALL
SELECT 'RL Retail','17','04' UNION ALL
SELECT 'RL Retail','18','04' UNION ALL
SELECT 'RL Retail','19','04' UNION ALL
SELECT 'RL Retail','20','04' UNION ALL
SELECT 'RL Retail','21','04' UNION ALL
SELECT 'RL Retail','22','04' UNION ALL
SELECT 'RL Retail','23','04' UNION ALL
SELECT 'RL Retail','24','04' UNION ALL
SELECT 'RL Retail','25','04' UNION ALL
SELECT 'RL Retail','26','04' UNION ALL
SELECT 'RL Retail','27','04' UNION ALL
SELECT 'RL Retail','28','04' UNION ALL
SELECT 'RL Retail','29','04' UNION ALL
SELECT 'RL Retail','30','04' UNION ALL
SELECT 'RL Retail','31','04' UNION ALL
SELECT 'RL Retail','32','04' UNION ALL
SELECT 'RL Retail','33','04' UNION ALL
SELECT 'RL Retail','34','04' UNION ALL
SELECT 'RL Retail','35','04' UNION ALL
SELECT 'RL Retail','36','04' UNION ALL
SELECT 'RL Retail','37','04' UNION ALL
SELECT 'RL Retail','38','04' UNION ALL
SELECT 'RL Retail','39','04' UNION ALL
SELECT 'RL Retail','40','04' UNION ALL
SELECT 'RL Retail','41','04' UNION ALL
SELECT 'RL Retail','42','04' UNION ALL
SELECT 'RL Retail','43','04' UNION ALL
SELECT 'RL Retail','44','04' UNION ALL
SELECT 'RL Retail','45','04' UNION ALL
SELECT 'SAF','0','04' UNION ALL
SELECT 'SAF','1','04' UNION ALL
SELECT 'SAF','2','04' UNION ALL
SELECT 'SAF','3','04' UNION ALL
SELECT 'SAF','4','04' UNION ALL
SELECT 'SAF','5','04' UNION ALL
SELECT 'SAF','6','04' UNION ALL
SELECT 'SAF','7','04' UNION ALL
SELECT 'SAF','8','04' UNION ALL
SELECT 'SAF','9','04' UNION ALL
SELECT 'SAF','10','04' UNION ALL
SELECT 'SAF','11','04' UNION ALL
SELECT 'SAF','12','04' UNION ALL
SELECT 'SAF','13','04' UNION ALL
SELECT 'SAF','14','04' UNION ALL
SELECT 'SAF','15','04' UNION ALL
SELECT 'SAF','16','04' UNION ALL
SELECT 'SAF','17','04' UNION ALL
SELECT 'SAF','18','04' UNION ALL
SELECT 'SAF','19','04' UNION ALL
SELECT 'SAF','20','04' UNION ALL
SELECT 'SAF','21','04' UNION ALL
SELECT 'SAF','22','04' UNION ALL
SELECT 'SAF','23','04' UNION ALL
SELECT 'SAF','24','04' UNION ALL
SELECT 'SAF','25','04' UNION ALL
SELECT 'SAF','26','04' UNION ALL
SELECT 'SAF','27','04' UNION ALL
SELECT 'SAF','28','04' UNION ALL
SELECT 'SAF','29','04' UNION ALL
SELECT 'SAF','30','04' UNION ALL
SELECT 'SAF','31','04' UNION ALL
SELECT 'SAF','32','04' UNION ALL
SELECT 'SAF','33','04' UNION ALL
SELECT 'SAF','34','04' UNION ALL
SELECT 'SAF','35','04' UNION ALL
SELECT 'SAF','36','04' UNION ALL
SELECT 'SAF','37','04' UNION ALL
SELECT 'SAF','38','04' UNION ALL
SELECT 'SAF','39','04' UNION ALL
SELECT 'SAF','40','04' UNION ALL
SELECT 'SAF','41','04' UNION ALL
SELECT 'SAF','42','04' UNION ALL
SELECT 'SAF','43','04' UNION ALL
SELECT 'SAF','44','04' UNION ALL
SELECT 'SAF','45','04' UNION ALL
SELECT 'CHR','0','05' UNION ALL
SELECT 'CHR','1','05' UNION ALL
SELECT 'CHR','2','05' UNION ALL
SELECT 'CHR','3','05' UNION ALL
SELECT 'CHR','4','05' UNION ALL
SELECT 'CHR','5','05' UNION ALL
SELECT 'CHR','6','05' UNION ALL
SELECT 'CHR','7','05' UNION ALL
SELECT 'CHR','8','05' UNION ALL
SELECT 'CHR','9','05' UNION ALL
SELECT 'CHR','10','05' UNION ALL
SELECT 'CHR','11','05' UNION ALL
SELECT 'CHR','12','05' UNION ALL
SELECT 'CHR','13','05' UNION ALL
SELECT 'CHR','14','05' UNION ALL
SELECT 'CHR','15','05' UNION ALL
SELECT 'CHR','16','05' UNION ALL
SELECT 'CHR','17','05' UNION ALL
SELECT 'CHR','18','05' UNION ALL
SELECT 'CHR','19','05' UNION ALL
SELECT 'CHR','20','05' UNION ALL
SELECT 'CHR','21','05' UNION ALL
SELECT 'CHR','22','05' UNION ALL
SELECT 'CHR','23','05' UNION ALL
SELECT 'CHR','24','05' UNION ALL
SELECT 'CHR','25','05' UNION ALL
SELECT 'CHR','26','05' UNION ALL
SELECT 'CHR','27','05' UNION ALL
SELECT 'CHR','28','05' UNION ALL
SELECT 'CHR','29','05' UNION ALL
SELECT 'CHR','30','05' UNION ALL
SELECT 'CHR','31','05' UNION ALL
SELECT 'CHR','32','05' UNION ALL
SELECT 'CHR','33','05' UNION ALL
SELECT 'CHR','34','05' UNION ALL
SELECT 'CHR','35','05' UNION ALL
SELECT 'CHR','36','05' UNION ALL
SELECT 'CHR','37','05' UNION ALL
SELECT 'CHR','38','05' UNION ALL
SELECT 'CHR','39','05' UNION ALL
SELECT 'CHR','40','05' UNION ALL
SELECT 'CHR','41','05' UNION ALL
SELECT 'CHR','42','05' UNION ALL
SELECT 'CHR','43','05' UNION ALL
SELECT 'CHR','44','05' UNION ALL
SELECT 'CHR','45','05' UNION ALL
SELECT 'RL Retail','0','05' UNION ALL
SELECT 'RL Retail','1','05' UNION ALL
SELECT 'RL Retail','2','05' UNION ALL
SELECT 'RL Retail','3','05' UNION ALL
SELECT 'RL Retail','4','05' UNION ALL
SELECT 'RL Retail','5','05' UNION ALL
SELECT 'RL Retail','6','05' UNION ALL
SELECT 'RL Retail','7','05' UNION ALL
SELECT 'RL Retail','8','05' UNION ALL
SELECT 'RL Retail','9','05' UNION ALL
SELECT 'RL Retail','10','05' UNION ALL
SELECT 'RL Retail','11','05' UNION ALL
SELECT 'RL Retail','12','05' UNION ALL
SELECT 'RL Retail','13','05' UNION ALL
SELECT 'RL Retail','14','05' UNION ALL
SELECT 'RL Retail','15','05' UNION ALL
SELECT 'RL Retail','16','05' UNION ALL
SELECT 'RL Retail','17','05' UNION ALL
SELECT 'RL Retail','18','05' UNION ALL
SELECT 'RL Retail','19','05' UNION ALL
SELECT 'RL Retail','20','05' UNION ALL
SELECT 'RL Retail','21','05' UNION ALL
SELECT 'RL Retail','22','05' UNION ALL
SELECT 'RL Retail','23','05' UNION ALL
SELECT 'RL Retail','24','05' UNION ALL
SELECT 'RL Retail','25','05' UNION ALL
SELECT 'RL Retail','26','05' UNION ALL
SELECT 'RL Retail','27','05' UNION ALL
SELECT 'RL Retail','28','05' UNION ALL
SELECT 'RL Retail','29','05' UNION ALL
SELECT 'RL Retail','30','05' UNION ALL
SELECT 'RL Retail','31','05' UNION ALL
SELECT 'RL Retail','32','05' UNION ALL
SELECT 'RL Retail','33','05' UNION ALL
SELECT 'RL Retail','34','05' UNION ALL
SELECT 'RL Retail','35','05' UNION ALL
SELECT 'RL Retail','36','05' UNION ALL
SELECT 'RL Retail','37','05' UNION ALL
SELECT 'RL Retail','38','05' UNION ALL
SELECT 'RL Retail','39','05' UNION ALL
SELECT 'RL Retail','40','05' UNION ALL
SELECT 'RL Retail','41','05' UNION ALL
SELECT 'RL Retail','42','05' UNION ALL
SELECT 'RL Retail','43','05' UNION ALL
SELECT 'RL Retail','44','05' UNION ALL
SELECT 'RL Retail','45','05' UNION ALL
SELECT 'SAF','0','05' UNION ALL
SELECT 'SAF','1','05' UNION ALL
SELECT 'SAF','2','05' UNION ALL
SELECT 'SAF','3','05' UNION ALL
SELECT 'SAF','4','05' UNION ALL
SELECT 'SAF','5','05' UNION ALL
SELECT 'SAF','6','05' UNION ALL
SELECT 'SAF','7','05' UNION ALL
SELECT 'SAF','8','05' UNION ALL
SELECT 'SAF','9','05' UNION ALL
SELECT 'SAF','10','05' UNION ALL
SELECT 'SAF','11','05' UNION ALL
SELECT 'SAF','12','05' UNION ALL
SELECT 'SAF','13','05' UNION ALL
SELECT 'SAF','14','05' UNION ALL
SELECT 'SAF','15','05' UNION ALL
SELECT 'SAF','16','05' UNION ALL
SELECT 'SAF','17','05' UNION ALL
SELECT 'SAF','18','05' UNION ALL
SELECT 'SAF','19','05' UNION ALL
SELECT 'SAF','20','05' UNION ALL
SELECT 'SAF','21','05' UNION ALL
SELECT 'SAF','22','05' UNION ALL
SELECT 'SAF','23','05' UNION ALL
SELECT 'SAF','24','05' UNION ALL
SELECT 'SAF','25','05' UNION ALL
SELECT 'SAF','26','05' UNION ALL
SELECT 'SAF','27','05' UNION ALL
SELECT 'SAF','28','05' UNION ALL
SELECT 'SAF','29','05' UNION ALL
SELECT 'SAF','30','05' UNION ALL
SELECT 'SAF','31','05' UNION ALL
SELECT 'SAF','32','05' UNION ALL
SELECT 'SAF','33','05' UNION ALL
SELECT 'SAF','34','05' UNION ALL
SELECT 'SAF','35','05' UNION ALL
SELECT 'SAF','36','05' UNION ALL
SELECT 'SAF','37','05' UNION ALL
SELECT 'SAF','38','05' UNION ALL
SELECT 'SAF','39','05' UNION ALL
SELECT 'SAF','40','05' UNION ALL
SELECT 'SAF','41','05' UNION ALL
SELECT 'SAF','42','05' UNION ALL
SELECT 'SAF','43','05' UNION ALL
SELECT 'SAF','44','05' UNION ALL
SELECT 'SAF','45','05' UNION ALL
SELECT 'CHR','0','06' UNION ALL
SELECT 'CHR','1','06' UNION ALL
SELECT 'CHR','2','06' UNION ALL
SELECT 'CHR','3','06' UNION ALL
SELECT 'CHR','4','06' UNION ALL
SELECT 'CHR','5','06' UNION ALL
SELECT 'CHR','6','06' UNION ALL
SELECT 'CHR','7','06' UNION ALL
SELECT 'CHR','8','06' UNION ALL
SELECT 'CHR','9','06' UNION ALL
SELECT 'CHR','10','06' UNION ALL
SELECT 'CHR','11','06' UNION ALL
SELECT 'CHR','12','06' UNION ALL
SELECT 'CHR','13','06' UNION ALL
SELECT 'CHR','14','06' UNION ALL
SELECT 'CHR','15','06' UNION ALL
SELECT 'CHR','16','06' UNION ALL
SELECT 'CHR','17','06' UNION ALL
SELECT 'CHR','18','06' UNION ALL
SELECT 'CHR','19','06' UNION ALL
SELECT 'CHR','20','06' UNION ALL
SELECT 'CHR','21','06' UNION ALL
SELECT 'CHR','22','06' UNION ALL
SELECT 'CHR','23','06' UNION ALL
SELECT 'CHR','24','06' UNION ALL
SELECT 'CHR','25','06' UNION ALL
SELECT 'CHR','26','06' UNION ALL
SELECT 'CHR','27','06' UNION ALL
SELECT 'CHR','28','06' UNION ALL
SELECT 'CHR','29','06' UNION ALL
SELECT 'CHR','30','06' UNION ALL
SELECT 'CHR','31','06' UNION ALL
SELECT 'CHR','32','06' UNION ALL
SELECT 'CHR','33','06' UNION ALL
SELECT 'CHR','34','06' UNION ALL
SELECT 'CHR','35','06' UNION ALL
SELECT 'CHR','36','06' UNION ALL
SELECT 'CHR','37','06' UNION ALL
SELECT 'CHR','38','06' UNION ALL
SELECT 'CHR','39','06' UNION ALL
SELECT 'CHR','40','06' UNION ALL
SELECT 'CHR','41','06' UNION ALL
SELECT 'CHR','42','06' UNION ALL
SELECT 'CHR','43','06' UNION ALL
SELECT 'CHR','44','06' UNION ALL
SELECT 'CHR','45','06' UNION ALL
SELECT 'RL Retail','0','06' UNION ALL
SELECT 'RL Retail','1','06' UNION ALL
SELECT 'RL Retail','2','06' UNION ALL
SELECT 'RL Retail','3','06' UNION ALL
SELECT 'RL Retail','4','06' UNION ALL
SELECT 'RL Retail','5','06' UNION ALL
SELECT 'RL Retail','6','06' UNION ALL
SELECT 'RL Retail','7','06' UNION ALL
SELECT 'RL Retail','8','06' UNION ALL
SELECT 'RL Retail','9','06' UNION ALL
SELECT 'RL Retail','10','06' UNION ALL
SELECT 'RL Retail','11','06' UNION ALL
SELECT 'RL Retail','12','06' UNION ALL
SELECT 'RL Retail','13','06' UNION ALL
SELECT 'RL Retail','14','06' UNION ALL
SELECT 'RL Retail','15','06' UNION ALL
SELECT 'RL Retail','16','06' UNION ALL
SELECT 'RL Retail','17','06' UNION ALL
SELECT 'RL Retail','18','06' UNION ALL
SELECT 'RL Retail','19','06' UNION ALL
SELECT 'RL Retail','20','06' UNION ALL
SELECT 'RL Retail','21','06' UNION ALL
SELECT 'RL Retail','22','06' UNION ALL
SELECT 'RL Retail','23','06' UNION ALL
SELECT 'RL Retail','24','06' UNION ALL
SELECT 'RL Retail','25','06' UNION ALL
SELECT 'RL Retail','26','06' UNION ALL
SELECT 'RL Retail','27','06' UNION ALL
SELECT 'RL Retail','28','06' UNION ALL
SELECT 'RL Retail','29','06' UNION ALL
SELECT 'RL Retail','30','06' UNION ALL
SELECT 'RL Retail','31','06' UNION ALL
SELECT 'RL Retail','32','06' UNION ALL
SELECT 'RL Retail','33','06' UNION ALL
SELECT 'RL Retail','34','06' UNION ALL
SELECT 'RL Retail','35','06' UNION ALL
SELECT 'RL Retail','36','06' UNION ALL
SELECT 'RL Retail','37','06' UNION ALL
SELECT 'RL Retail','38','06' UNION ALL
SELECT 'RL Retail','39','06' UNION ALL
SELECT 'RL Retail','40','06' UNION ALL
SELECT 'RL Retail','41','06' UNION ALL
SELECT 'RL Retail','42','06' UNION ALL
SELECT 'RL Retail','43','06' UNION ALL
SELECT 'RL Retail','44','06' UNION ALL
SELECT 'RL Retail','45','06' UNION ALL
SELECT 'SAF','0','06' UNION ALL
SELECT 'SAF','1','06' UNION ALL
SELECT 'SAF','2','06' UNION ALL
SELECT 'SAF','3','06' UNION ALL
SELECT 'SAF','4','06' UNION ALL
SELECT 'SAF','5','06' UNION ALL
SELECT 'SAF','6','06' UNION ALL
SELECT 'SAF','7','06' UNION ALL
SELECT 'SAF','8','06' UNION ALL
SELECT 'SAF','9','06' UNION ALL
SELECT 'SAF','10','06' UNION ALL
SELECT 'SAF','11','06' UNION ALL
SELECT 'SAF','12','06' UNION ALL
SELECT 'SAF','13','06' UNION ALL
SELECT 'SAF','14','06' UNION ALL
SELECT 'SAF','15','06' UNION ALL
SELECT 'SAF','16','06' UNION ALL
SELECT 'SAF','17','06' UNION ALL
SELECT 'SAF','18','06' UNION ALL
SELECT 'SAF','19','06' UNION ALL
SELECT 'SAF','20','06' UNION ALL
SELECT 'SAF','21','06' UNION ALL
SELECT 'SAF','22','06' UNION ALL
SELECT 'SAF','23','06' UNION ALL
SELECT 'SAF','24','06' UNION ALL
SELECT 'SAF','25','06' UNION ALL
SELECT 'SAF','26','06' UNION ALL
SELECT 'SAF','27','06' UNION ALL
SELECT 'SAF','28','06' UNION ALL
SELECT 'SAF','29','06' UNION ALL
SELECT 'SAF','30','06' UNION ALL
SELECT 'SAF','31','06' UNION ALL
SELECT 'SAF','32','06' UNION ALL
SELECT 'SAF','33','06' UNION ALL
SELECT 'SAF','34','06' UNION ALL
SELECT 'SAF','35','06' UNION ALL
SELECT 'SAF','36','06' UNION ALL
SELECT 'SAF','37','06' UNION ALL
SELECT 'SAF','38','06' UNION ALL
SELECT 'SAF','39','06' UNION ALL
SELECT 'SAF','40','06' UNION ALL
SELECT 'SAF','41','06' UNION ALL
SELECT 'SAF','42','06' UNION ALL
SELECT 'SAF','43','06' UNION ALL
SELECT 'SAF','44','06' UNION ALL
SELECT 'SAF','45','06' UNION ALL
SELECT 'CHR','0','07' UNION ALL
SELECT 'CHR','1','07' UNION ALL
SELECT 'CHR','2','07' UNION ALL
SELECT 'CHR','3','07' UNION ALL
SELECT 'CHR','4','07' UNION ALL
SELECT 'CHR','5','07' UNION ALL
SELECT 'CHR','6','07' UNION ALL
SELECT 'CHR','7','07' UNION ALL
SELECT 'CHR','8','07' UNION ALL
SELECT 'CHR','9','07' UNION ALL
SELECT 'CHR','10','07' UNION ALL
SELECT 'CHR','11','07' UNION ALL
SELECT 'CHR','12','07' UNION ALL
SELECT 'CHR','13','07' UNION ALL
SELECT 'CHR','14','07' UNION ALL
SELECT 'CHR','15','07' UNION ALL
SELECT 'CHR','16','07' UNION ALL
SELECT 'CHR','17','07' UNION ALL
SELECT 'CHR','18','07' UNION ALL
SELECT 'CHR','19','07' UNION ALL
SELECT 'CHR','20','07' UNION ALL
SELECT 'CHR','21','07' UNION ALL
SELECT 'CHR','22','07' UNION ALL
SELECT 'CHR','23','07' UNION ALL
SELECT 'CHR','24','07' UNION ALL
SELECT 'CHR','25','07' UNION ALL
SELECT 'CHR','26','07' UNION ALL
SELECT 'CHR','27','07' UNION ALL
SELECT 'CHR','28','07' UNION ALL
SELECT 'CHR','29','07' UNION ALL
SELECT 'CHR','30','07' UNION ALL
SELECT 'CHR','31','07' UNION ALL
SELECT 'CHR','32','07' UNION ALL
SELECT 'CHR','33','07' UNION ALL
SELECT 'CHR','34','07' UNION ALL
SELECT 'CHR','35','07' UNION ALL
SELECT 'CHR','36','07' UNION ALL
SELECT 'CHR','37','07' UNION ALL
SELECT 'CHR','38','07' UNION ALL
SELECT 'CHR','39','07' UNION ALL
SELECT 'CHR','40','07' UNION ALL
SELECT 'CHR','41','07' UNION ALL
SELECT 'CHR','42','07' UNION ALL
SELECT 'CHR','43','07' UNION ALL
SELECT 'CHR','44','07' UNION ALL
SELECT 'CHR','45','07' UNION ALL
SELECT 'RL Retail','0','07' UNION ALL
SELECT 'RL Retail','1','07' UNION ALL
SELECT 'RL Retail','2','07' UNION ALL
SELECT 'RL Retail','3','07' UNION ALL
SELECT 'RL Retail','4','07' UNION ALL
SELECT 'RL Retail','5','07' UNION ALL
SELECT 'RL Retail','6','07' UNION ALL
SELECT 'RL Retail','7','07' UNION ALL
SELECT 'RL Retail','8','07' UNION ALL
SELECT 'RL Retail','9','07' UNION ALL
SELECT 'RL Retail','10','07' UNION ALL
SELECT 'RL Retail','11','07' UNION ALL
SELECT 'RL Retail','12','07' UNION ALL
SELECT 'RL Retail','13','07' UNION ALL
SELECT 'RL Retail','14','07' UNION ALL
SELECT 'RL Retail','15','07' UNION ALL
SELECT 'RL Retail','16','07' UNION ALL
SELECT 'RL Retail','17','07' UNION ALL
SELECT 'RL Retail','18','07' UNION ALL
SELECT 'RL Retail','19','07' UNION ALL
SELECT 'RL Retail','20','07' UNION ALL
SELECT 'RL Retail','21','07' UNION ALL
SELECT 'RL Retail','22','07' UNION ALL
SELECT 'RL Retail','23','07' UNION ALL
SELECT 'RL Retail','24','07' UNION ALL
SELECT 'RL Retail','25','07' UNION ALL
SELECT 'RL Retail','26','07' UNION ALL
SELECT 'RL Retail','27','07' UNION ALL
SELECT 'RL Retail','28','07' UNION ALL
SELECT 'RL Retail','29','07' UNION ALL
SELECT 'RL Retail','30','07' UNION ALL
SELECT 'RL Retail','31','07' UNION ALL
SELECT 'RL Retail','32','07' UNION ALL
SELECT 'RL Retail','33','07' UNION ALL
SELECT 'RL Retail','34','07' UNION ALL
SELECT 'RL Retail','35','07' UNION ALL
SELECT 'RL Retail','36','07' UNION ALL
SELECT 'RL Retail','37','07' UNION ALL
SELECT 'RL Retail','38','07' UNION ALL
SELECT 'RL Retail','39','07' UNION ALL
SELECT 'RL Retail','40','07' UNION ALL
SELECT 'RL Retail','41','07' UNION ALL
SELECT 'RL Retail','42','07' UNION ALL
SELECT 'RL Retail','43','07' UNION ALL
SELECT 'RL Retail','44','07' UNION ALL
SELECT 'RL Retail','45','07' UNION ALL
SELECT 'SAF','0','07' UNION ALL
SELECT 'SAF','1','07' UNION ALL
SELECT 'SAF','2','07' UNION ALL
SELECT 'SAF','3','07' UNION ALL
SELECT 'SAF','4','07' UNION ALL
SELECT 'SAF','5','07' UNION ALL
SELECT 'SAF','6','07' UNION ALL
SELECT 'SAF','7','07' UNION ALL
SELECT 'SAF','8','07' UNION ALL
SELECT 'SAF','9','07' UNION ALL
SELECT 'SAF','10','07' UNION ALL
SELECT 'SAF','11','07' UNION ALL
SELECT 'SAF','12','07' UNION ALL
SELECT 'SAF','13','07' UNION ALL
SELECT 'SAF','14','07' UNION ALL
SELECT 'SAF','15','07' UNION ALL
SELECT 'SAF','16','07' UNION ALL
SELECT 'SAF','17','07' UNION ALL
SELECT 'SAF','18','07' UNION ALL
SELECT 'SAF','19','07' UNION ALL
SELECT 'SAF','20','07' UNION ALL
SELECT 'SAF','21','07' UNION ALL
SELECT 'SAF','22','07' UNION ALL
SELECT 'SAF','23','07' UNION ALL
SELECT 'SAF','24','07' UNION ALL
SELECT 'SAF','25','07' UNION ALL
SELECT 'SAF','26','07' UNION ALL
SELECT 'SAF','27','07' UNION ALL
SELECT 'SAF','28','07' UNION ALL
SELECT 'SAF','29','07' UNION ALL
SELECT 'SAF','30','07' UNION ALL
SELECT 'SAF','31','07' UNION ALL
SELECT 'SAF','32','07' UNION ALL
SELECT 'SAF','33','07' UNION ALL
SELECT 'SAF','34','07' UNION ALL
SELECT 'SAF','35','07' UNION ALL
SELECT 'SAF','36','07' UNION ALL
SELECT 'SAF','37','07' UNION ALL
SELECT 'SAF','38','07' UNION ALL
SELECT 'SAF','39','07' UNION ALL
SELECT 'SAF','40','07' UNION ALL
SELECT 'SAF','41','07' UNION ALL
SELECT 'SAF','42','07' UNION ALL
SELECT 'SAF','43','07' UNION ALL
SELECT 'SAF','44','07' UNION ALL
SELECT 'SAF','45','07' UNION ALL
SELECT 'CHR','0','08' UNION ALL
SELECT 'CHR','1','08' UNION ALL
SELECT 'CHR','2','08' UNION ALL
SELECT 'CHR','3','08' UNION ALL
SELECT 'CHR','4','08' UNION ALL
SELECT 'CHR','5','08' UNION ALL
SELECT 'CHR','6','08' UNION ALL
SELECT 'CHR','7','08' UNION ALL
SELECT 'CHR','8','08' UNION ALL
SELECT 'CHR','9','08' UNION ALL
SELECT 'CHR','10','08' UNION ALL
SELECT 'CHR','11','08' UNION ALL
SELECT 'CHR','12','08' UNION ALL
SELECT 'CHR','13','08' UNION ALL
SELECT 'CHR','14','08' UNION ALL
SELECT 'CHR','15','08' UNION ALL
SELECT 'CHR','16','08' UNION ALL
SELECT 'CHR','17','08' UNION ALL
SELECT 'CHR','18','08' UNION ALL
SELECT 'CHR','19','08' UNION ALL
SELECT 'CHR','20','08' UNION ALL
SELECT 'CHR','21','08' UNION ALL
SELECT 'CHR','22','08' UNION ALL
SELECT 'CHR','23','08' UNION ALL
SELECT 'CHR','24','08' UNION ALL
SELECT 'CHR','25','08' UNION ALL
SELECT 'CHR','26','08' UNION ALL
SELECT 'CHR','27','08' UNION ALL
SELECT 'CHR','28','08' UNION ALL
SELECT 'CHR','29','08' UNION ALL
SELECT 'CHR','30','08' UNION ALL
SELECT 'CHR','31','08' UNION ALL
SELECT 'CHR','32','08' UNION ALL
SELECT 'CHR','33','08' UNION ALL
SELECT 'CHR','34','08' UNION ALL
SELECT 'CHR','35','08' UNION ALL
SELECT 'CHR','36','08' UNION ALL
SELECT 'CHR','37','08' UNION ALL
SELECT 'CHR','38','08' UNION ALL
SELECT 'CHR','39','08' UNION ALL
SELECT 'CHR','40','08' UNION ALL
SELECT 'CHR','41','08' UNION ALL
SELECT 'CHR','42','08' UNION ALL
SELECT 'CHR','43','08' UNION ALL
SELECT 'CHR','44','08' UNION ALL
SELECT 'CHR','45','08' UNION ALL
SELECT 'RL Retail','0','08' UNION ALL
SELECT 'RL Retail','1','08' UNION ALL
SELECT 'RL Retail','2','08' UNION ALL
SELECT 'RL Retail','3','08' UNION ALL
SELECT 'RL Retail','4','08' UNION ALL
SELECT 'RL Retail','5','08' UNION ALL
SELECT 'RL Retail','6','08' UNION ALL
SELECT 'RL Retail','7','08' UNION ALL
SELECT 'RL Retail','8','08' UNION ALL
SELECT 'RL Retail','9','08' UNION ALL
SELECT 'RL Retail','10','08' UNION ALL
SELECT 'RL Retail','11','08' UNION ALL
SELECT 'RL Retail','12','08' UNION ALL
SELECT 'RL Retail','13','08' UNION ALL
SELECT 'RL Retail','14','08' UNION ALL
SELECT 'RL Retail','15','08' UNION ALL
SELECT 'RL Retail','16','08' UNION ALL
SELECT 'RL Retail','17','08' UNION ALL
SELECT 'RL Retail','18','08' UNION ALL
SELECT 'RL Retail','19','08' UNION ALL
SELECT 'RL Retail','20','08' UNION ALL
SELECT 'RL Retail','21','08' UNION ALL
SELECT 'RL Retail','22','08' UNION ALL
SELECT 'RL Retail','23','08' UNION ALL
SELECT 'RL Retail','24','08' UNION ALL
SELECT 'RL Retail','25','08' UNION ALL
SELECT 'RL Retail','26','08' UNION ALL
SELECT 'RL Retail','27','08' UNION ALL
SELECT 'RL Retail','28','08' UNION ALL
SELECT 'RL Retail','29','08' UNION ALL
SELECT 'RL Retail','30','08' UNION ALL
SELECT 'RL Retail','31','08' UNION ALL
SELECT 'RL Retail','32','08' UNION ALL
SELECT 'RL Retail','33','08' UNION ALL
SELECT 'RL Retail','34','08' UNION ALL
SELECT 'RL Retail','35','08' UNION ALL
SELECT 'RL Retail','36','08' UNION ALL
SELECT 'RL Retail','37','08' UNION ALL
SELECT 'RL Retail','38','08' UNION ALL
SELECT 'RL Retail','39','08' UNION ALL
SELECT 'RL Retail','40','08' UNION ALL
SELECT 'RL Retail','41','08' UNION ALL
SELECT 'RL Retail','42','08' UNION ALL
SELECT 'RL Retail','43','08' UNION ALL
SELECT 'RL Retail','44','08' UNION ALL
SELECT 'RL Retail','45','08' UNION ALL
SELECT 'SAF','0','08' UNION ALL
SELECT 'SAF','1','08' UNION ALL
SELECT 'SAF','2','08' UNION ALL
SELECT 'SAF','3','08' UNION ALL
SELECT 'SAF','4','08' UNION ALL
SELECT 'SAF','5','08' UNION ALL
SELECT 'SAF','6','08' UNION ALL
SELECT 'SAF','7','08' UNION ALL
SELECT 'SAF','8','08' UNION ALL
SELECT 'SAF','9','08' UNION ALL
SELECT 'SAF','10','08' UNION ALL
SELECT 'SAF','11','08' UNION ALL
SELECT 'SAF','12','08' UNION ALL
SELECT 'SAF','13','08' UNION ALL
SELECT 'SAF','14','08' UNION ALL
SELECT 'SAF','15','08' UNION ALL
SELECT 'SAF','16','08' UNION ALL
SELECT 'SAF','17','08' UNION ALL
SELECT 'SAF','18','08' UNION ALL
SELECT 'SAF','19','08' UNION ALL
SELECT 'SAF','20','08' UNION ALL
SELECT 'SAF','21','08' UNION ALL
SELECT 'SAF','22','08' UNION ALL
SELECT 'SAF','23','08' UNION ALL
SELECT 'SAF','24','08' UNION ALL
SELECT 'SAF','25','08' UNION ALL
SELECT 'SAF','26','08' UNION ALL
SELECT 'SAF','27','08' UNION ALL
SELECT 'SAF','28','08' UNION ALL
SELECT 'SAF','29','08' UNION ALL
SELECT 'SAF','30','08' UNION ALL
SELECT 'SAF','31','08' UNION ALL
SELECT 'SAF','32','08' UNION ALL
SELECT 'SAF','33','08' UNION ALL
SELECT 'SAF','34','08' UNION ALL
SELECT 'SAF','35','08' UNION ALL
SELECT 'SAF','36','08' UNION ALL
SELECT 'SAF','37','08' UNION ALL
SELECT 'SAF','38','08' UNION ALL
SELECT 'SAF','39','08' UNION ALL
SELECT 'SAF','40','08' UNION ALL
SELECT 'SAF','41','08' UNION ALL
SELECT 'SAF','42','08' UNION ALL
SELECT 'SAF','43','08' UNION ALL
SELECT 'SAF','44','08' UNION ALL
SELECT 'SAF','45','08' UNION ALL
SELECT 'CHR','0','09' UNION ALL
SELECT 'CHR','1','09' UNION ALL
SELECT 'CHR','2','09' UNION ALL
SELECT 'CHR','3','09' UNION ALL
SELECT 'CHR','4','09' UNION ALL
SELECT 'CHR','5','09' UNION ALL
SELECT 'CHR','6','09' UNION ALL
SELECT 'CHR','7','09' UNION ALL
SELECT 'CHR','8','09' UNION ALL
SELECT 'CHR','9','09' UNION ALL
SELECT 'CHR','10','09' UNION ALL
SELECT 'CHR','11','09' UNION ALL
SELECT 'CHR','12','09' UNION ALL
SELECT 'CHR','13','09' UNION ALL
SELECT 'CHR','14','09' UNION ALL
SELECT 'CHR','15','09' UNION ALL
SELECT 'CHR','16','09' UNION ALL
SELECT 'CHR','17','09' UNION ALL
SELECT 'CHR','18','09' UNION ALL
SELECT 'CHR','19','09' UNION ALL
SELECT 'CHR','20','09' UNION ALL
SELECT 'CHR','21','09' UNION ALL
SELECT 'CHR','22','09' UNION ALL
SELECT 'CHR','23','09' UNION ALL
SELECT 'CHR','24','09' UNION ALL
SELECT 'CHR','25','09' UNION ALL
SELECT 'CHR','26','09' UNION ALL
SELECT 'CHR','27','09' UNION ALL
SELECT 'CHR','28','09' UNION ALL
SELECT 'CHR','29','09' UNION ALL
SELECT 'CHR','30','09' UNION ALL
SELECT 'CHR','31','09' UNION ALL
SELECT 'CHR','32','09' UNION ALL
SELECT 'CHR','33','09' UNION ALL
SELECT 'CHR','34','09' UNION ALL
SELECT 'CHR','35','09' UNION ALL
SELECT 'CHR','36','09' UNION ALL
SELECT 'CHR','37','09' UNION ALL
SELECT 'CHR','38','09' UNION ALL
SELECT 'CHR','39','09' UNION ALL
SELECT 'CHR','40','09' UNION ALL
SELECT 'CHR','41','09' UNION ALL
SELECT 'CHR','42','09' UNION ALL
SELECT 'CHR','43','09' UNION ALL
SELECT 'CHR','44','09' UNION ALL
SELECT 'CHR','45','09' UNION ALL
SELECT 'RL Retail','0','09' UNION ALL
SELECT 'RL Retail','1','09' UNION ALL
SELECT 'RL Retail','2','09' UNION ALL
SELECT 'RL Retail','3','09' UNION ALL
SELECT 'RL Retail','4','09' UNION ALL
SELECT 'RL Retail','5','09' UNION ALL
SELECT 'RL Retail','6','09' UNION ALL
SELECT 'RL Retail','7','09' UNION ALL
SELECT 'RL Retail','8','09' UNION ALL
SELECT 'RL Retail','9','09' UNION ALL
SELECT 'RL Retail','09','09' UNION ALL
SELECT 'RL Retail','11','09' UNION ALL
SELECT 'RL Retail','12','09' UNION ALL
SELECT 'RL Retail','13','09' UNION ALL
SELECT 'RL Retail','14','09' UNION ALL
SELECT 'RL Retail','15','09' UNION ALL
SELECT 'RL Retail','16','09' UNION ALL
SELECT 'RL Retail','17','09' UNION ALL
SELECT 'RL Retail','18','09' UNION ALL
SELECT 'RL Retail','19','09' UNION ALL
SELECT 'RL Retail','20','09' UNION ALL
SELECT 'RL Retail','21','09' UNION ALL
SELECT 'RL Retail','22','09' UNION ALL
SELECT 'RL Retail','23','09' UNION ALL
SELECT 'RL Retail','24','09' UNION ALL
SELECT 'RL Retail','25','09' UNION ALL
SELECT 'RL Retail','26','09' UNION ALL
SELECT 'RL Retail','27','09' UNION ALL
SELECT 'RL Retail','28','09' UNION ALL
SELECT 'RL Retail','29','09' UNION ALL
SELECT 'RL Retail','30','09' UNION ALL
SELECT 'RL Retail','31','09' UNION ALL
SELECT 'RL Retail','32','09' UNION ALL
SELECT 'RL Retail','33','09' UNION ALL
SELECT 'RL Retail','34','09' UNION ALL
SELECT 'RL Retail','35','09' UNION ALL
SELECT 'RL Retail','36','09' UNION ALL
SELECT 'RL Retail','37','09' UNION ALL
SELECT 'RL Retail','38','09' UNION ALL
SELECT 'RL Retail','39','09' UNION ALL
SELECT 'RL Retail','40','09' UNION ALL
SELECT 'RL Retail','41','09' UNION ALL
SELECT 'RL Retail','42','09' UNION ALL
SELECT 'RL Retail','43','09' UNION ALL
SELECT 'RL Retail','44','09' UNION ALL
SELECT 'RL Retail','45','09' UNION ALL
SELECT 'SAF','0','09' UNION ALL
SELECT 'SAF','1','09' UNION ALL
SELECT 'SAF','2','09' UNION ALL
SELECT 'SAF','3','09' UNION ALL
SELECT 'SAF','4','09' UNION ALL
SELECT 'SAF','5','09' UNION ALL
SELECT 'SAF','6','09' UNION ALL
SELECT 'SAF','7','09' UNION ALL
SELECT 'SAF','8','09' UNION ALL
SELECT 'SAF','9','09' UNION ALL
SELECT 'SAF','10','09' UNION ALL
SELECT 'SAF','11','09' UNION ALL
SELECT 'SAF','12','09' UNION ALL
SELECT 'SAF','13','09' UNION ALL
SELECT 'SAF','14','09' UNION ALL
SELECT 'SAF','15','09' UNION ALL
SELECT 'SAF','16','09' UNION ALL
SELECT 'SAF','17','09' UNION ALL
SELECT 'SAF','18','09' UNION ALL
SELECT 'SAF','19','09' UNION ALL
SELECT 'SAF','20','09' UNION ALL
SELECT 'SAF','21','09' UNION ALL
SELECT 'SAF','22','09' UNION ALL
SELECT 'SAF','23','09' UNION ALL
SELECT 'SAF','24','09' UNION ALL
SELECT 'SAF','25','09' UNION ALL
SELECT 'SAF','26','09' UNION ALL
SELECT 'SAF','27','09' UNION ALL
SELECT 'SAF','28','09' UNION ALL
SELECT 'SAF','29','09' UNION ALL
SELECT 'SAF','30','09' UNION ALL
SELECT 'SAF','31','09' UNION ALL
SELECT 'SAF','32','09' UNION ALL
SELECT 'SAF','33','09' UNION ALL
SELECT 'SAF','34','09' UNION ALL
SELECT 'SAF','35','09' UNION ALL
SELECT 'SAF','36','09' UNION ALL
SELECT 'SAF','37','09' UNION ALL
SELECT 'SAF','38','09' UNION ALL
SELECT 'SAF','39','09' UNION ALL
SELECT 'SAF','40','09' UNION ALL
SELECT 'SAF','41','09' UNION ALL
SELECT 'SAF','42','09' UNION ALL
SELECT 'SAF','43','09' UNION ALL
SELECT 'SAF','44','09' UNION ALL
SELECT 'SAF','45','09' UNION ALL
SELECT 'CHR','0','10' UNION ALL
SELECT 'CHR','1','10' UNION ALL
SELECT 'CHR','2','10' UNION ALL
SELECT 'CHR','3','10' UNION ALL
SELECT 'CHR','4','10' UNION ALL
SELECT 'CHR','5','10' UNION ALL
SELECT 'CHR','6','10' UNION ALL
SELECT 'CHR','7','10' UNION ALL
SELECT 'CHR','8','10' UNION ALL
SELECT 'CHR','9','10' UNION ALL
SELECT 'CHR','10','10' UNION ALL
SELECT 'CHR','11','10' UNION ALL
SELECT 'CHR','12','10' UNION ALL
SELECT 'CHR','13','10' UNION ALL
SELECT 'CHR','14','10' UNION ALL
SELECT 'CHR','15','10' UNION ALL
SELECT 'CHR','16','10' UNION ALL
SELECT 'CHR','17','10' UNION ALL
SELECT 'CHR','18','10' UNION ALL
SELECT 'CHR','19','10' UNION ALL
SELECT 'CHR','20','10' UNION ALL
SELECT 'CHR','21','10' UNION ALL
SELECT 'CHR','22','10' UNION ALL
SELECT 'CHR','23','10' UNION ALL
SELECT 'CHR','24','10' UNION ALL
SELECT 'CHR','25','10' UNION ALL
SELECT 'CHR','26','10' UNION ALL
SELECT 'CHR','27','10' UNION ALL
SELECT 'CHR','28','10' UNION ALL
SELECT 'CHR','29','10' UNION ALL
SELECT 'CHR','30','10' UNION ALL
SELECT 'CHR','31','10' UNION ALL
SELECT 'CHR','32','10' UNION ALL
SELECT 'CHR','33','10' UNION ALL
SELECT 'CHR','34','10' UNION ALL
SELECT 'CHR','35','10' UNION ALL
SELECT 'CHR','36','10' UNION ALL
SELECT 'CHR','37','10' UNION ALL
SELECT 'CHR','38','10' UNION ALL
SELECT 'CHR','39','10' UNION ALL
SELECT 'CHR','40','10' UNION ALL
SELECT 'CHR','41','10' UNION ALL
SELECT 'CHR','42','10' UNION ALL
SELECT 'CHR','43','10' UNION ALL
SELECT 'CHR','44','10' UNION ALL
SELECT 'CHR','45','10' UNION ALL
SELECT 'RL Retail','0','10' UNION ALL
SELECT 'RL Retail','1','10' UNION ALL
SELECT 'RL Retail','2','10' UNION ALL
SELECT 'RL Retail','3','10' UNION ALL
SELECT 'RL Retail','4','10' UNION ALL
SELECT 'RL Retail','5','10' UNION ALL
SELECT 'RL Retail','6','10' UNION ALL
SELECT 'RL Retail','7','10' UNION ALL
SELECT 'RL Retail','8','10' UNION ALL
SELECT 'RL Retail','9','10' UNION ALL
SELECT 'RL Retail','10','10' UNION ALL
SELECT 'RL Retail','11','10' UNION ALL
SELECT 'RL Retail','12','10' UNION ALL
SELECT 'RL Retail','13','10' UNION ALL
SELECT 'RL Retail','14','10' UNION ALL
SELECT 'RL Retail','15','10' UNION ALL
SELECT 'RL Retail','16','10' UNION ALL
SELECT 'RL Retail','17','10' UNION ALL
SELECT 'RL Retail','18','10' UNION ALL
SELECT 'RL Retail','19','10' UNION ALL
SELECT 'RL Retail','20','10' UNION ALL
SELECT 'RL Retail','21','10' UNION ALL
SELECT 'RL Retail','22','10' UNION ALL
SELECT 'RL Retail','23','10' UNION ALL
SELECT 'RL Retail','24','10' UNION ALL
SELECT 'RL Retail','25','10' UNION ALL
SELECT 'RL Retail','26','10' UNION ALL
SELECT 'RL Retail','27','10' UNION ALL
SELECT 'RL Retail','28','10' UNION ALL
SELECT 'RL Retail','29','10' UNION ALL
SELECT 'RL Retail','30','10' UNION ALL
SELECT 'RL Retail','31','10' UNION ALL
SELECT 'RL Retail','32','10' UNION ALL
SELECT 'RL Retail','33','10' UNION ALL
SELECT 'RL Retail','34','10' UNION ALL
SELECT 'RL Retail','35','10' UNION ALL
SELECT 'RL Retail','36','10' UNION ALL
SELECT 'RL Retail','37','10' UNION ALL
SELECT 'RL Retail','38','10' UNION ALL
SELECT 'RL Retail','39','10' UNION ALL
SELECT 'RL Retail','40','10' UNION ALL
SELECT 'RL Retail','41','10' UNION ALL
SELECT 'RL Retail','42','10' UNION ALL
SELECT 'RL Retail','43','10' UNION ALL
SELECT 'RL Retail','44','10' UNION ALL
SELECT 'RL Retail','45','10' UNION ALL
SELECT 'SAF','0','10' UNION ALL
SELECT 'SAF','1','10' UNION ALL
SELECT 'SAF','2','10' UNION ALL
SELECT 'SAF','3','10' UNION ALL
SELECT 'SAF','4','10' UNION ALL
SELECT 'SAF','5','10' UNION ALL
SELECT 'SAF','6','10' UNION ALL
SELECT 'SAF','7','10' UNION ALL
SELECT 'SAF','8','10' UNION ALL
SELECT 'SAF','9','10' UNION ALL
SELECT 'SAF','10','10' UNION ALL
SELECT 'SAF','11','10' UNION ALL
SELECT 'SAF','12','10' UNION ALL
SELECT 'SAF','13','10' UNION ALL
SELECT 'SAF','14','10' UNION ALL
SELECT 'SAF','15','10' UNION ALL
SELECT 'SAF','16','10' UNION ALL
SELECT 'SAF','17','10' UNION ALL
SELECT 'SAF','18','10' UNION ALL
SELECT 'SAF','19','10' UNION ALL
SELECT 'SAF','20','10' UNION ALL
SELECT 'SAF','21','10' UNION ALL
SELECT 'SAF','22','10' UNION ALL
SELECT 'SAF','23','10' UNION ALL
SELECT 'SAF','24','10' UNION ALL
SELECT 'SAF','25','10' UNION ALL
SELECT 'SAF','26','10' UNION ALL
SELECT 'SAF','27','10' UNION ALL
SELECT 'SAF','28','10' UNION ALL
SELECT 'SAF','29','10' UNION ALL
SELECT 'SAF','30','10' UNION ALL
SELECT 'SAF','31','10' UNION ALL
SELECT 'SAF','32','10' UNION ALL
SELECT 'SAF','33','10' UNION ALL
SELECT 'SAF','34','10' UNION ALL
SELECT 'SAF','35','10' UNION ALL
SELECT 'SAF','36','10' UNION ALL
SELECT 'SAF','37','10' UNION ALL
SELECT 'SAF','38','10' UNION ALL
SELECT 'SAF','39','10' UNION ALL
SELECT 'SAF','40','10' UNION ALL
SELECT 'SAF','41','10' UNION ALL
SELECT 'SAF','42','10' UNION ALL
SELECT 'SAF','43','10' UNION ALL
SELECT 'SAF','44','10' UNION ALL
SELECT 'SAF','45','10' UNION ALL
SELECT 'CHR','0','Total' UNION ALL
SELECT 'CHR','1','Total' UNION ALL
SELECT 'CHR','2','Total' UNION ALL
SELECT 'CHR','3','Total' UNION ALL
SELECT 'CHR','4','Total' UNION ALL
SELECT 'CHR','5','Total' UNION ALL
SELECT 'CHR','6','Total' UNION ALL
SELECT 'CHR','7','Total' UNION ALL
SELECT 'CHR','8','Total' UNION ALL
SELECT 'CHR','9','Total' UNION ALL
SELECT 'CHR','10','Total' UNION ALL
SELECT 'CHR','11','Total' UNION ALL
SELECT 'CHR','12','Total' UNION ALL
SELECT 'CHR','13','Total' UNION ALL
SELECT 'CHR','14','Total' UNION ALL
SELECT 'CHR','15','Total' UNION ALL
SELECT 'CHR','16','Total' UNION ALL
SELECT 'CHR','17','Total' UNION ALL
SELECT 'CHR','18','Total' UNION ALL
SELECT 'CHR','19','Total' UNION ALL
SELECT 'CHR','20','Total' UNION ALL
SELECT 'CHR','21','Total' UNION ALL
SELECT 'CHR','22','Total' UNION ALL
SELECT 'CHR','23','Total' UNION ALL
SELECT 'CHR','24','Total' UNION ALL
SELECT 'CHR','25','Total' UNION ALL
SELECT 'CHR','26','Total' UNION ALL
SELECT 'CHR','27','Total' UNION ALL
SELECT 'CHR','28','Total' UNION ALL
SELECT 'CHR','29','Total' UNION ALL
SELECT 'CHR','30','Total' UNION ALL
SELECT 'CHR','31','Total' UNION ALL
SELECT 'CHR','32','Total' UNION ALL
SELECT 'CHR','33','Total' UNION ALL
SELECT 'CHR','34','Total' UNION ALL
SELECT 'CHR','35','Total' UNION ALL
SELECT 'CHR','36','Total' UNION ALL
SELECT 'CHR','37','Total' UNION ALL
SELECT 'CHR','38','Total' UNION ALL
SELECT 'CHR','39','Total' UNION ALL
SELECT 'CHR','40','Total' UNION ALL
SELECT 'CHR','41','Total' UNION ALL
SELECT 'CHR','42','Total' UNION ALL
SELECT 'CHR','43','Total' UNION ALL
SELECT 'CHR','44','Total' UNION ALL
SELECT 'CHR','45','Total' UNION ALL
SELECT 'RL Retail','0','Total' UNION ALL
SELECT 'RL Retail','1','Total' UNION ALL
SELECT 'RL Retail','2','Total' UNION ALL
SELECT 'RL Retail','3','Total' UNION ALL
SELECT 'RL Retail','4','Total' UNION ALL
SELECT 'RL Retail','5','Total' UNION ALL
SELECT 'RL Retail','6','Total' UNION ALL
SELECT 'RL Retail','7','Total' UNION ALL
SELECT 'RL Retail','8','Total' UNION ALL
SELECT 'RL Retail','9','Total' UNION ALL
SELECT 'RL Retail','10','Total' UNION ALL
SELECT 'RL Retail','11','Total' UNION ALL
SELECT 'RL Retail','12','Total' UNION ALL
SELECT 'RL Retail','13','Total' UNION ALL
SELECT 'RL Retail','14','Total' UNION ALL
SELECT 'RL Retail','15','Total' UNION ALL
SELECT 'RL Retail','16','Total' UNION ALL
SELECT 'RL Retail','17','Total' UNION ALL
SELECT 'RL Retail','18','Total' UNION ALL
SELECT 'RL Retail','19','Total' UNION ALL
SELECT 'RL Retail','20','Total' UNION ALL
SELECT 'RL Retail','21','Total' UNION ALL
SELECT 'RL Retail','22','Total' UNION ALL
SELECT 'RL Retail','23','Total' UNION ALL
SELECT 'RL Retail','24','Total' UNION ALL
SELECT 'RL Retail','25','Total' UNION ALL
SELECT 'RL Retail','26','Total' UNION ALL
SELECT 'RL Retail','27','Total' UNION ALL
SELECT 'RL Retail','28','Total' UNION ALL
SELECT 'RL Retail','29','Total' UNION ALL
SELECT 'RL Retail','30','Total' UNION ALL
SELECT 'RL Retail','31','Total' UNION ALL
SELECT 'RL Retail','32','Total' UNION ALL
SELECT 'RL Retail','33','Total' UNION ALL
SELECT 'RL Retail','34','Total' UNION ALL
SELECT 'RL Retail','35','Total' UNION ALL
SELECT 'RL Retail','36','Total' UNION ALL
SELECT 'RL Retail','37','Total' UNION ALL
SELECT 'RL Retail','38','Total' UNION ALL
SELECT 'RL Retail','39','Total' UNION ALL
SELECT 'RL Retail','40','Total' UNION ALL
SELECT 'RL Retail','41','Total' UNION ALL
SELECT 'RL Retail','42','Total' UNION ALL
SELECT 'RL Retail','43','Total' UNION ALL
SELECT 'RL Retail','44','Total' UNION ALL
SELECT 'RL Retail','45','Total' UNION ALL
SELECT 'SAF','0','Total' UNION ALL
SELECT 'SAF','1','Total' UNION ALL
SELECT 'SAF','2','Total' UNION ALL
SELECT 'SAF','3','Total' UNION ALL
SELECT 'SAF','4','Total' UNION ALL
SELECT 'SAF','5','Total' UNION ALL
SELECT 'SAF','6','Total' UNION ALL
SELECT 'SAF','7','Total' UNION ALL
SELECT 'SAF','8','Total' UNION ALL
SELECT 'SAF','9','Total' UNION ALL
SELECT 'SAF','10','Total' UNION ALL
SELECT 'SAF','11','Total' UNION ALL
SELECT 'SAF','12','Total' UNION ALL
SELECT 'SAF','13','Total' UNION ALL
SELECT 'SAF','14','Total' UNION ALL
SELECT 'SAF','15','Total' UNION ALL
SELECT 'SAF','16','Total' UNION ALL
SELECT 'SAF','17','Total' UNION ALL
SELECT 'SAF','18','Total' UNION ALL
SELECT 'SAF','19','Total' UNION ALL
SELECT 'SAF','20','Total' UNION ALL
SELECT 'SAF','21','Total' UNION ALL
SELECT 'SAF','22','Total' UNION ALL
SELECT 'SAF','23','Total' UNION ALL
SELECT 'SAF','24','Total' UNION ALL
SELECT 'SAF','25','Total' UNION ALL
SELECT 'SAF','26','Total' UNION ALL
SELECT 'SAF','27','Total' UNION ALL
SELECT 'SAF','28','Total' UNION ALL
SELECT 'SAF','29','Total' UNION ALL
SELECT 'SAF','30','Total' UNION ALL
SELECT 'SAF','31','Total' UNION ALL
SELECT 'SAF','32','Total' UNION ALL
SELECT 'SAF','33','Total' UNION ALL
SELECT 'SAF','34','Total' UNION ALL
SELECT 'SAF','35','Total' UNION ALL
SELECT 'SAF','36','Total' UNION ALL
SELECT 'SAF','37','Total' UNION ALL
SELECT 'SAF','38','Total' UNION ALL
SELECT 'SAF','39','Total' UNION ALL
SELECT 'SAF','40','Total' UNION ALL
SELECT 'SAF','41','Total' UNION ALL
SELECT 'SAF','42','Total' UNION ALL
SELECT 'SAF','43','Total' UNION ALL
SELECT 'SAF','44','Total' UNION ALL
SELECT 'SAF','45','Total'
SELECT * FROM #TEMP
February 23, 2012 at 1:52 am
You could use a CTE to achieve something similar to what you have posted. Here is something I created a few moments ago which uses two CTE's to generate the data. Just populate the @LOB table with your values, set the @DaysContract to the upper limit and run the script. I've used INTs for two of the fields where you have used VARCHAR though but you can cast them during the final select if you really need them as VARCHAR.
Hope it helps in some way.
DECLARE @LOBs TABLE (LOB varchar(25))
INSERT INTO @LOBs VALUES ('CHR')
INSERT INTO @LOBs VALUES ('RL Retail')
INSERT INTO @LOBs VALUES ('SAF')
DECLARE @DaysContract INT
SET @DaysContract = 10
;
WITH xCTE (LOB, DaysSince) AS
(
SELECT LOB, 0 AS 'DaysSince' FROM @LOBs
UNION ALL
SELECT LOB, DaysSince + 1 FROM xCTE
WHERE DaysSince < 45
),
xCTE2 (LOB, DaysSince, DaysContract)
AS
(
SELECT LOB, DaysSince, 0 AS DaysContract FROM xCTE
UNION ALL
SELECT LOB, DaysSince, DaysContract + 1 FROM xCTE2
WHERE DaysContract < @DaysContract
)
SELECT * FROM xCTE2
ORDER BY LOB, DaysSince
February 23, 2012 at 9:45 am
Thanks this is much better. And yeah in the end I need those 2 fields to be VARCHAR since I want to add one more value called 'Total'. I understand they have to be INT to do the + 1 though. I'll see if I can figure out a way to add a 'Total' line in there using another CTE. But so far this has cut 16,000 lines of code down to 40 for me so a big improvement. At least now I know what a cte is so I can probably figure out how to do these things on my own going forward.
February 23, 2012 at 9:54 am
I'm glad it helps and if you get stuck again then please post back on this thread and i'll be happy (or indeed others i'm sure) to try and help further.
February 28, 2012 at 5:21 pm
I have another question now. I was trying to use a CTE to come up with a way to create a field. Basically I want to create the [Event] field in #TEMP2 Below. I want to Group by the [Rep], [Vendor ID], and [Event Time] . "Entry" is WHen [Event Type] = 10 and "Exit" is when [Event Type] = 9.
So basically when a rep triggers an "Entry" Event (Event Type 10), at a specific [Vendor ID] on a specific Day, I want to flag the first [Event ID] as "Entry 1" and then find the next highest [Event ID] with [Event Type] = 9 ("Exit" event) and flag it as "Exit 1". Then if they "Enter" again to the same vendor later that day, I want to flag records as "Enter 2" / "Exit 2", etc.
Hope this was a clear explanation and thanks in advance for any help.
IF OBJECT_ID('TempDB..#TEMP','U') IS NOT NULL
DROP TABLE #TEMP
CREATE TABLE #TEMP
(
[Event ID] INT,
[Rep] VARCHAR(25),
[Event Type] VARCHAR(25),
[Vendor ID] INT,
[Event Time] VARCHAR(25)
)
INSERT INTO #TEMP
([Event ID], [Rep], [Event Type], [Vendor ID], [Event Time])
SELECT '1','Smith','10','1','Day 1' UNION ALL
SELECT '2','Smith','9','1','Day 1' UNION ALL
SELECT '3','Smith','10','1','Day 1' UNION ALL
SELECT '4','Smith','9','1','Day 1' UNION ALL
SELECT '5','Smith','10','1','Day 1' UNION ALL
SELECT '6','Smith','9','1','Day 1' UNION ALL
SELECT '7','Smith','10','2','Day 1' UNION ALL
SELECT '8','Smith','9','2','Day 1' UNION ALL
SELECT '9','Smith','10','2','Day 1' UNION ALL
SELECT '10','Smith','9','2','Day 1' UNION ALL
SELECT '11','Smith','10','2','Day 1' UNION ALL
SELECT '12','Smith','9','2','Day 1' UNION ALL
SELECT '13','Smith','10','1','Day 2' UNION ALL
SELECT '14','Smith','9','1','Day 2' UNION ALL
SELECT '15','Smith','10','1','Day 2' UNION ALL
SELECT '16','Smith','9','1','Day 2' UNION ALL
SELECT '17','Smith','10','1','Day 2' UNION ALL
SELECT '18','Smith','9','1','Day 2' UNION ALL
SELECT '19','Smith','10','2','Day 2' UNION ALL
SELECT '20','Smith','9','2','Day 2' UNION ALL
SELECT '21','Smith','10','2','Day 2' UNION ALL
SELECT '22','Smith','9','2','Day 2' UNION ALL
SELECT '23','Smith','10','2','Day 2' UNION ALL
SELECT '24','Smith','9','2','Day 2'
--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
IF OBJECT_ID('TempDB..#TEMP2','U') IS NOT NULL
DROP TABLE #TEMP2
CREATE TABLE #TEMP2
(
[Event ID] INT,
[Rep] VARCHAR(25),
[Event Type] VARCHAR(25),
[Vendor ID] INT,
[Event Time] VARCHAR(25),
[Event] VARCHAR(25)
)
INSERT INTO #TEMP2
([Event ID], [Rep], [Event Type], [Vendor ID], [Event Time], [Event])
SELECT '1','Smith','10','1','Day 1','Entry 1' UNION ALL
SELECT '2','Smith','9','1','Day 1','Exit 1' UNION ALL
SELECT '3','Smith','10','1','Day 1','Entry 2' UNION ALL
SELECT '4','Smith','9','1','Day 1','Exit 2' UNION ALL
SELECT '5','Smith','10','1','Day 1','Entry 3' UNION ALL
SELECT '6','Smith','9','1','Day 1','Exit 3' UNION ALL
SELECT '7','Smith','10','2','Day 1','Entry 1' UNION ALL
SELECT '8','Smith','9','2','Day 1','Exit 1' UNION ALL
SELECT '9','Smith','10','2','Day 1','Entry 2' UNION ALL
SELECT '10','Smith','9','2','Day 1','Exit 2' UNION ALL
SELECT '11','Smith','10','2','Day 1','Entry 3' UNION ALL
SELECT '12','Smith','9','2','Day 1','Exit 3' UNION ALL
SELECT '13','Smith','10','1','Day 2','Entry 1' UNION ALL
SELECT '14','Smith','9','1','Day 2','Exit 1' UNION ALL
SELECT '15','Smith','10','1','Day 2','Entry 2' UNION ALL
SELECT '16','Smith','9','1','Day 2','Exit 2' UNION ALL
SELECT '17','Smith','10','1','Day 2','Entry 3' UNION ALL
SELECT '18','Smith','9','1','Day 2','Exit 3' UNION ALL
SELECT '19','Smith','10','2','Day 2','Entry 1' UNION ALL
SELECT '20','Smith','9','2','Day 2','Exit 1' UNION ALL
SELECT '21','Smith','10','2','Day 2','Entry 2' UNION ALL
SELECT '22','Smith','9','2','Day 2','Exit 2' UNION ALL
SELECT '23','Smith','10','2','Day 2','Entry 3' UNION ALL
SELECT '24','Smith','9','2','Day 2','Exit 3'
SELECT * FROM #TEMP
SELECT * FROM #TEMP2
February 28, 2012 at 8:55 pm
arthurolcot (2/23/2012)
I'm glad it helps and if you get stuck again then please post back on this thread and i'll be happy (or indeed others i'm sure) to try and help further.
Arthur,
Please see the following article for why you should usually avoid recursive CTE's that count.
http://www.sqlservercentral.com/articles/T-SQL/74118/
--Jeff Moden
Change is inevitable... Change for the better is not.
February 28, 2012 at 9:16 pm
arpost (2/23/2012)
Thanks this is much better. And yeah in the end I need those 2 fields to be VARCHAR since I want to add one more value called 'Total'. I understand they have to be INT to do the + 1 though. I'll see if I can figure out a way to add a 'Total' line in there using another CTE. But so far this has cut 16,000 lines of code down to 40 for me so a big improvement. At least now I know what a cte is so I can probably figure out how to do these things on my own going forward.
Let's cut it down a little bit more (a lot more, actually) and introduce you to what my friend, Gianluca Sartori, calls "The Swiss Army Knife of T-SQL". It's called a Tally Table and it can make for some very fast and very simple code. Your request is no exception.
First, let's make a permanent Tally Table for you. Here's how.
--===================================================================
-- Create a Tally table from 0 to 11000
--===================================================================
--===== Create and populate the Tally table on the fly.
SELECT TOP 11001
IDENTITY(INT,0,1) AS N
INTO dbo.Tally
FROM Master.sys.ALL_Columns ac1
CROSS JOIN Master.sys.ALL_Columns ac2
;
--===== Add a CLUSTERED Primary Key to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
;
--===== Allow the general public to use it
GRANT SELECT ON dbo.Tally TO PUBLIC
;
GO
Now that you have that, let's see how simple it can be to write code that will generate the data that you requested.
SELECT lob.LOB,
[Days Since App] = CAST(dsa.N AS VARCHAR(2)),
[Days to Contract] = CASE dtc.N WHEN 11 THEN 'Total' ELSE RIGHT('0'+CAST(dtc.N AS VARCHAR(2)),2) END
FROM (SELECT 'CHR' UNION ALL SELECT 'RL Retail' UNION ALL SELECT 'SAF') lob (LOB)
CROSS JOIN dbo.Tally dsa
CROSS JOIN dbo.Tally dtc
WHERE dsa.N BETWEEN 0 AND 45
AND dtc.N BETWEEN 0 AND 11
;
For more information on what a Tally Table is and how it can be used to replace certain loops and other forms of RBAR, such as recursive CTEs that count, please see the following article.
http://www.sqlservercentral.com/articles/T-SQL/62867/
--Jeff Moden
Change is inevitable... Change for the better is not.
February 28, 2012 at 9:54 pm
arpost (2/28/2012)
I have another question now. I was trying to use a CTE to come up with a way to create a field. Basically I want to create the [Event] field in #TEMP2 Below. I want to Group by the [Rep], [Vendor ID], and [Event Time] . "Entry" is WHen [Event Type] = 10 and "Exit" is when [Event Type] = 9.So basically when a rep triggers an "Entry" Event (Event Type 10), at a specific [Vendor ID] on a specific Day, I want to flag the first [Event ID] as "Entry 1" and then find the next highest [Event ID] with [Event Type] = 9 ("Exit" event) and flag it as "Exit 1". Then if they "Enter" again to the same vendor later that day, I want to flag records as "Enter 2" / "Exit 2", etc.
Hope this was a clear explanation and thanks in advance for any help.
IF OBJECT_ID('TempDB..#TEMP','U') IS NOT NULL
DROP TABLE #TEMP
CREATE TABLE #TEMP
(
[Event ID] INT,
[Rep] VARCHAR(25),
[Event Type] VARCHAR(25),
[Vendor ID] INT,
[Event Time] VARCHAR(25)
)
INSERT INTO #TEMP
([Event ID], [Rep], [Event Type], [Vendor ID], [Event Time])
SELECT '1','Smith','10','1','Day 1' UNION ALL
SELECT '2','Smith','9','1','Day 1' UNION ALL
SELECT '3','Smith','10','1','Day 1' UNION ALL
SELECT '4','Smith','9','1','Day 1' UNION ALL
SELECT '5','Smith','10','1','Day 1' UNION ALL
SELECT '6','Smith','9','1','Day 1' UNION ALL
SELECT '7','Smith','10','2','Day 1' UNION ALL
SELECT '8','Smith','9','2','Day 1' UNION ALL
SELECT '9','Smith','10','2','Day 1' UNION ALL
SELECT '10','Smith','9','2','Day 1' UNION ALL
SELECT '11','Smith','10','2','Day 1' UNION ALL
SELECT '12','Smith','9','2','Day 1' UNION ALL
SELECT '13','Smith','10','1','Day 2' UNION ALL
SELECT '14','Smith','9','1','Day 2' UNION ALL
SELECT '15','Smith','10','1','Day 2' UNION ALL
SELECT '16','Smith','9','1','Day 2' UNION ALL
SELECT '17','Smith','10','1','Day 2' UNION ALL
SELECT '18','Smith','9','1','Day 2' UNION ALL
SELECT '19','Smith','10','2','Day 2' UNION ALL
SELECT '20','Smith','9','2','Day 2' UNION ALL
SELECT '21','Smith','10','2','Day 2' UNION ALL
SELECT '22','Smith','9','2','Day 2' UNION ALL
SELECT '23','Smith','10','2','Day 2' UNION ALL
SELECT '24','Smith','9','2','Day 2'
--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
IF OBJECT_ID('TempDB..#TEMP2','U') IS NOT NULL
DROP TABLE #TEMP2
CREATE TABLE #TEMP2
(
[Event ID] INT,
[Rep] VARCHAR(25),
[Event Type] VARCHAR(25),
[Vendor ID] INT,
[Event Time] VARCHAR(25),
[Event] VARCHAR(25)
)
INSERT INTO #TEMP2
([Event ID], [Rep], [Event Type], [Vendor ID], [Event Time], [Event])
SELECT '1','Smith','10','1','Day 1','Entry 1' UNION ALL
SELECT '2','Smith','9','1','Day 1','Exit 1' UNION ALL
SELECT '3','Smith','10','1','Day 1','Entry 2' UNION ALL
SELECT '4','Smith','9','1','Day 1','Exit 2' UNION ALL
SELECT '5','Smith','10','1','Day 1','Entry 3' UNION ALL
SELECT '6','Smith','9','1','Day 1','Exit 3' UNION ALL
SELECT '7','Smith','10','2','Day 1','Entry 1' UNION ALL
SELECT '8','Smith','9','2','Day 1','Exit 1' UNION ALL
SELECT '9','Smith','10','2','Day 1','Entry 2' UNION ALL
SELECT '10','Smith','9','2','Day 1','Exit 2' UNION ALL
SELECT '11','Smith','10','2','Day 1','Entry 3' UNION ALL
SELECT '12','Smith','9','2','Day 1','Exit 3' UNION ALL
SELECT '13','Smith','10','1','Day 2','Entry 1' UNION ALL
SELECT '14','Smith','9','1','Day 2','Exit 1' UNION ALL
SELECT '15','Smith','10','1','Day 2','Entry 2' UNION ALL
SELECT '16','Smith','9','1','Day 2','Exit 2' UNION ALL
SELECT '17','Smith','10','1','Day 2','Entry 3' UNION ALL
SELECT '18','Smith','9','1','Day 2','Exit 3' UNION ALL
SELECT '19','Smith','10','2','Day 2','Entry 1' UNION ALL
SELECT '20','Smith','9','2','Day 2','Exit 1' UNION ALL
SELECT '21','Smith','10','2','Day 2','Entry 2' UNION ALL
SELECT '22','Smith','9','2','Day 2','Exit 2' UNION ALL
SELECT '23','Smith','10','2','Day 2','Entry 3' UNION ALL
SELECT '24','Smith','9','2','Day 2','Exit 3'
SELECT * FROM #TEMP
SELECT * FROM #TEMP2
No need for a Tally Table and certainly no need for a COUNTING Recursive CTE here. We'll just use the natural power of the "Pseudo Cursor" formed by the SELECT in the CTE.
WITH
cteEnumerateEvents AS
(
SELECT [Event ID], [Rep], [Event Type], [Vendor ID], [Event Time],
[EventNum] = CAST((ROW_NUMBER() OVER (PARTITION BY [Event Time],[Vendor ID], [Rep]
ORDER BY [Event ID])-1) /2 +1 AS VARCHAR(10))
FROM #TEMP
)
SELECT [Event ID], [Rep], [Event Type], [Vendor ID], [Event Time],
[Event] = CASE [Event Type]
WHEN '10' THEN 'Entry ' + [EventNum]
WHEN '9' THEN 'Exit ' + [EventNum]
ELSE 'Other'
END
FROM cteEnumerateEvents
ORDER BY [Event ID]
;
Results from run above...
Event ID Rep Event Type Vendor ID Event Time Event
-------- ------ ---------- ----------- ---------- ----------------
1 Smith 10 1 Day 1 Entry 1
2 Smith 9 1 Day 1 Exit 1
3 Smith 10 1 Day 1 Entry 2
4 Smith 9 1 Day 1 Exit 2
5 Smith 10 1 Day 1 Entry 3
6 Smith 9 1 Day 1 Exit 3
7 Smith 10 2 Day 1 Entry 1
8 Smith 9 2 Day 1 Exit 1
9 Smith 10 2 Day 1 Entry 2
10 Smith 9 2 Day 1 Exit 2
11 Smith 10 2 Day 1 Entry 3
12 Smith 9 2 Day 1 Exit 3
13 Smith 10 1 Day 2 Entry 1
14 Smith 9 1 Day 2 Exit 1
15 Smith 10 1 Day 2 Entry 2
16 Smith 9 1 Day 2 Exit 2
17 Smith 10 1 Day 2 Entry 3
18 Smith 9 1 Day 2 Exit 3
19 Smith 10 2 Day 2 Entry 1
20 Smith 9 2 Day 2 Exit 1
21 Smith 10 2 Day 2 Entry 2
22 Smith 9 2 Day 2 Exit 2
23 Smith 10 2 Day 2 Entry 3
24 Smith 9 2 Day 2 Exit 3
--Jeff Moden
Change is inevitable... Change for the better is not.
February 28, 2012 at 9:56 pm
As a bit of a sidebar, thank you very much for the way you posted "readily consumable" data and expected results. It made it a whole lot easier for me to both understand the problems you posted and to quickly solve them for you. Well done.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 29, 2012 at 8:10 am
Thanks you very much for the hasty response, and I'm happy to provide sample data sets. This community is proving most helpful to me.
Just one more question, while posting here, how do I get my code to appear in a box? I tried to figure it out yesterday, but didn't have luck. I assume it's using this IFCode Shortcusts, . I tried putting the SQL where the sql is in , but it didn't work. Maybe if I replace the ""s too??
February 29, 2012 at 3:56 pm
OK, sorry for the continuous questions, but I have another. The data I'm working with is a bit imperfect. Let's just say a certain cellphone manufacturer who is named after a fruit who happens to be losing marketshare to another cellphone manufacturer named after a fruit, has some issues with their GPS hardware.
I'm keeping this example almost the same as above, but throwing in a couple of wrinkles.
[Event Type] = 10 is still an "entry" event while [Event Type] = 9 is an "exit" event.
I'm still counting the number of "entry" / "Exit" events in a day, but want to label each pair of "entry" / "exits" as "Visits" and count them.
Now I want to assess the exact time of the events. Basically I want to not count any visits which do not occur over a specified time limits, which will be 5 minutes for now, but subject to change.
So in the First and Second datasets I provide below, you can see [Rep] Smith initiated an "enter" event at 10:40, then simultaneously had an "exit", "Enter", and "Exit" event at 12:41. This was followed by a 3rd Visit that day from 14:40 until 16:20.
I'd prefer this read as 2 visits instead of 3. The 3rd dataset is what I'd like to arrive at, where there are only 2 visits. In the first visit, my exit time would be the exit time of the 2nd visit, since there was not 5 minutes between the 1st exit event and 2nd enter event. I included a few other examples as well.
Thanks in advance for any help. SOrry if it's against board ettiquette to post so many questions, but I feel I could bumble around with this one for an extended period of time and probably couldn't come up with a good way to parse out the data that's flawed.
---------------------------------------------------------------------------------------
---------------------------- Create Initial Dataset -----------------------------------
IF OBJECT_ID('TempDB..#TEMP','U') IS NOT NULL
DROP TABLE #TEMP
CREATE TABLE #TEMP
(
[Event ID] INT,
[Rep] VARCHAR(25),
[Event Type] INT,
[Vendor ID] INT,
[Event Time] DATETIME
)
INSERT INTO #TEMP
([Event ID], [Rep], [Event Type], [Vendor ID], [Event Time])
SELECT '1','Smith','10','24916','2011-12-12 10:40:50.000' UNION ALL
SELECT '2','Smith','9','24916','2011-12-12 12:41:00.000' UNION ALL
SELECT '3','Smith','10','24916','2011-12-12 12:41:01.000' UNION ALL
SELECT '4','Smith','9','24916','2011-12-12 12:41:02.000' UNION ALL
SELECT '5','Smith','10','24916','2011-12-12 14:40:24.000' UNION ALL
SELECT '6','Smith','9','24916','2011-12-12 16:20:31.000' UNION ALL
SELECT '7','Jones','10','36527','2012-01-12 14:02:01.000' UNION ALL
SELECT '8','Jones','9','36527','2012-01-12 14:22:13.000' UNION ALL
SELECT '9','Jones','10','36527','2012-01-12 14:22:14.000' UNION ALL
SELECT '10','Jones','9','36527','2012-01-12 15:42:10.000' UNION ALL
SELECT '11','Jones','10','36527','2012-01-12 16:42:03.000' UNION ALL
SELECT '12','Jones','9','36527','2012-01-12 16:42:04.000' UNION ALL
SELECT '13','Rezek','10','105303','2012-02-07 15:41:29.000' UNION ALL
SELECT '14','Rezek','9','105303','2012-02-07 16:21:31.000' UNION ALL
SELECT '15','Rezek','10','105303','2012-02-07 16:21:34.000' UNION ALL
SELECT '16','Rezek','9','105303','2012-02-07 16:21:37.000' UNION ALL
SELECT '17','Rezek','10','105303','2012-02-07 16:21:40.000' UNION ALL
SELECT '18','Rezek','9','105303','2012-02-07 16:42:13.000' UNION ALL
SELECT '19','Deckard','10','105341','2011-11-25 09:32:38.000' UNION ALL
SELECT '20','Deckard','9','105341','2011-11-25 10:32:37.000' UNION ALL
SELECT '21','Deckard','10','105341','2011-11-25 10:32:38.000' UNION ALL
SELECT '22','Deckard','9','105341','2011-11-25 12:12:41.000' UNION ALL
SELECT '23','Deckard','10','105341','2011-11-25 12:52:38.000' UNION ALL
SELECT '24','Deckard','9','105341','2011-11-25 12:52:39.000' UNION ALL
SELECT '25','Deckard','10','105341','2011-11-25 12:52:40.000' UNION ALL
SELECT '26','Deckard','9','105341','2011-11-25 13:32:43.000'
---------------------------------------------------------------------------------------
------------------------- Use CTE to label Visit Numbers ------------------------------
DROP TABLE #TEMP2;
WITH cteEnumerateEvents1 AS
(SELECT
[Event ID],
[Rep],
[Event Type],
[Vendor ID],
[Event Date] = RIGHT(CONVERT(VARCHAR(10),[Event Time],101),4) + '/' + LEFT(CONVERT(VARCHAR(10),[Event Time],101),5),
[Event Time] = [Event Time],
[EventNum] = CAST((ROW_NUMBER() OVER (PARTITION BY RIGHT(CONVERT(VARCHAR(10),[Event Time],101),4) + '/' + LEFT(CONVERT(VARCHAR(10),[Event Time],101),5), [Vendor ID], [Rep]
ORDER BY [Event ID])-1) /2 +1 AS VARCHAR(10))
FROM #TEMP)
SELECT
[Event ID],
[Rep],
[Event Type],
[Vendor ID],
[Event Date],
[Event Time],
[Event] = 'Visit ' + [EventNum]
INTO #TEMP2
FROM cteEnumerateEvents1
ORDER BY 4,1
;
---------------------------------------------------------------------------------------
--------------------------- Create Desired Final Data ---------------------------------
IF OBJECT_ID('TempDB..#TEMP3','U') IS NOT NULL
DROP TABLE #TEMP3
CREATE TABLE #TEMP3
(
[Event ID] INT,
[Rep] VARCHAR(25),
[Event Type] INT,
[Vendor ID] INT,
[Event Date] VARCHAR(10),
[Event Time] DATETIME,
[Event] VARCHAR(10)
)
INSERT INTO #TEMP3
([Event ID], [Rep], [Event Type], [Vendor ID], [Event Date], [Event Time], [Event])
SELECT '1','Smith','10','24916','2011/12/12','2011-12-12 10:40:50.000','Visit 1' UNION ALL
SELECT '4','Smith','9','24916','2011/12/12','2011-12-12 12:41:02.000','Visit 1' UNION ALL
SELECT '5','Smith','10','24916','2011/12/12','2011-12-12 14:40:24.000','Visit 2' UNION ALL
SELECT '6','Smith','9','24916','2011/12/12','2011-12-12 16:20:31.000','Visit 2' UNION ALL
SELECT '7','Jones','10','36527','2012/01/12','2012-01-12 14:02:01.000','Visit 1' UNION ALL
SELECT '10','Jones','9','36527','2012/01/12','2012-01-12 15:42:10.000','Visit 1' UNION ALL
SELECT '13','Rezek','10','105303','2012/02/07','2012-02-07 15:41:29.000','Visit 1' UNION ALL
SELECT '18','Rezek','9','105303','2012/02/07','2012-02-07 16:42:13.000','Visit 1' UNION ALL
SELECT '19','Deckard','10','105341','2011/11/25','2011-11-25 09:32:38.000','Visit 1' UNION ALL
SELECT '22','Deckard','9','105341','2011/11/25','2011-11-25 12:12:41.000','Visit 1' UNION ALL
SELECT '23','Deckard','10','105341','2011/11/25','2011-11-25 12:52:38.000','Visit 2' UNION ALL
SELECT '26','Deckard','9','105341','2011/11/25','2011-11-25 13:32:43.000','Visit 2'
---------------------------------------------------------------------------------------
----------------------------------- Display Data --------------------------------------
SELECT * FROM #TEMP
SELECT * FROM #TEMP2
SELECT * FROM #TEMP3
March 1, 2012 at 3:06 pm
Need a bit more detail on your last request. Is it possible to have an "exit" that doesn't have a matching "entry" or an "entry" that has no matching "exit" like what may happen if someone holds the door for someone else?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 1, 2012 at 3:32 pm
That's not possible. Every Entry will have an exit. Basically an entry event logs when a 1/3rd of a mile radius of a location is entered and the exit event logs when they leave the 1/3rd of a mile radius.
One problem I have is that when the entry events occurs on one day (say around 23:00 p.m.) and the exit event occurs the next day (say around 01:00 a.m.). However I'm going to throw those out since visits shouldn't be taking place in the middle of the night.
Hope I've explained ok, basically the problem I'm having is that exit / enter events are triggering within milliseconds of eachother so shouldn't count. I want to put a constraint so I don't trigger an event when, for instance, a rep just drives by a tracked location.
Sometimes also, which I think my example demonstrated, The exit of visit 1 might happen simulataneously with the enter of visit 2. In that case, I want to take the exit of visit 2 and record that as the exit of visit 1. Assuming it is not another case of a false positive, where the enter 2 / exit 2 events fire simultaneously.
I'd like it to be a bit flexible so I can just specify the amount of minutes needed b/t enter and exit (or exit 1 then enter 2) for it to be considered a visist and not a false positive.
March 1, 2012 at 3:36 pm
but to more specifically answer ur last post, no this is not data from a card scanner from a door. So someone holding a door open is not possible. These are records using GPS within cellphones to monitor activity. So like i said above, these things might trigger when just driving by a place, or visiting a nearby place. Also GPS can lose signal when going indoors or entering certain areas where signal is disrupted, like an urban area with tall buildings.
Just trying to parse out the data to get it more accurate, although it will always be a bit imperfect.
March 1, 2012 at 6:25 pm
arpost (3/1/2012)
That's not possible. Every Entry will have an exit.
Heh... so it's not possible that someone could enter an area and the battery on their cell phone gives out? It's also not possible that someone driving along enters an are and discovers they forgot to turn their cell phone and and promptly does so? 😉
One problem I have is that when the entry events occurs on one day (say around 23:00 p.m.) and the exit event occurs the next day (say around 01:00 a.m.). However I'm going to throw those out since visits shouldn't be taking place in the middle of the night.
Nah... no need to throw those out. Traversing days shouldn't be a problem. No sense in throwing out data.
Sometimes also, which I think my example demonstrated, The exit of visit 1 might happen simulataneously with the enter of visit 2. In that case, I want to take the exit of visit 2 and record that as the exit of visit 1. Assuming it is not another case of a false positive, where the enter 2 / exit 2 events fire simultaneously.
Do you mean you want the following???
Enter 1, record as Enter 1
Exit 1 and immediately Enter 2... no record
Exit 2, record as Exit 1.
I'd like it to be a bit flexible so I can just specify the amount of minutes needed b/t enter and exit (or exit 1 then enter 2) for it to be considered a visist and not a false positive.
Heh... so long as that flexibility doesn't require full blown AI, probably not a problem but let's hammer out the basic requirements first. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply