Scenario: Creating an automatic report or collecting data about existing jobs. In trying to translate the field freq_interval for freq_type 8 (weekly) to determine what days a job runs. SQL uses a code of Sunday=1, Monday=2, Tuesday=4, Wednesday=8, Thursday=16, Friday=32, Saturday=64. So for those days alone it’s an easy translation but what about Monday/Wednesday/Friday well that’s 42. They get that by adding 2+8+32. So to translate you have to determine what fits into each number from 1-127.
Solution: Working in excel to come up with all the possibilities, I created the following snippet for use as a table variable, this could easily be put into a function and just pass in the freq interval but, for my purposes it was valuable in a join statement.
Snippet:
Declare @Interval Table (freq_Interval Int, Interval_Days Varchar( 70 )) Insert Into @Interval Select 1 , 'Sunday' Insert Into @Interval Select 2 , 'Monday' Insert Into @Interval Select 3 , 'Sunday, Monday' Insert Into @Interval Select 4 , 'Tuesday' Insert Into @Interval Select 5 , 'Sunday, Tuesday' Insert Into @Interval Select 6 , 'Monday, Tuesday' Insert Into @Interval Select 7 , 'Sunday, Monday, Tuesday' Insert Into @Interval Select 8 , 'Wednesday' Insert Into @Interval Select 9 , 'Sunday, Wednesday' Insert Into @Interval Select 10 , 'Monday, Wednesday' Insert Into @Interval Select 11 , 'Sunday, Monday, Wednesday' Insert Into @Interval Select 12 , 'Tuesday, Wednesday' Insert Into @Interval Select 13 , 'Sunday, Tuesday, Wednesday' Insert Into @Interval Select 14 , 'Monday, Tuesday, Wednesday' Insert Into @Interval Select 15 , 'Sunday, Monday, Tuesday, Wednesday' Insert Into @Interval Select 16 , 'Thursday' Insert Into @Interval Select 17 , 'Sunday, Thursday' Insert Into @Interval Select 18 , 'Monday, Thursday' Insert Into @Interval Select 19 , 'Sunday, Monday, Thursday' Insert Into @Interval Select 20 , 'Tuesday, Thursday' Insert Into @Interval Select 21 , 'Sunday, Tuesday, Thursday' Insert Into @Interval Select 22 , 'Monday, Tuesday, Thursday' Insert Into @Interval Select 23 , 'Sunday, Monday, Tuesday, Thursday' Insert Into @Interval Select 24 , 'Wednesday, Thursday' Insert Into @Interval Select 25 , 'Sunday, Wednesday, Thursday' Insert Into @Interval Select 26 , 'Monday, Wednesday, Thursday' Insert Into @Interval Select 27 , 'Sunday, Monday, Wednesday, Thursday' Insert Into @Interval Select 28 , 'Tuesday, Wednesday, Thursday' Insert Into @Interval Select 29 , 'Sunday, Tuesday, Wednesday, Thursday' Insert Into @Interval Select 30 , 'Monday, Tuesday, Wednesday, Thursday' Insert Into @Interval Select 31 , 'Sunday, Monday, Tuesday, Wednesday, Thursday' Insert Into @Interval Select 32 , 'Friday' Insert Into @Interval Select 33 , 'Sunday, Friday' Insert Into @Interval Select 34 , 'Monday, Friday' Insert Into @Interval Select 35 , 'Sunday, Monday, Friday' Insert Into @Interval Select 36 , 'Tuesday, Friday' Insert Into @Interval Select 37 , 'Sunday, Tuesday, Friday' Insert Into @Interval Select 38 , 'Monday, Tuesday, Friday' Insert Into @Interval Select 39 , 'Sunday, Monday, Tuesday, Friday' Insert Into @Interval Select 40 , 'Wednesday, Friday' Insert Into @Interval Select 41 , 'Sunday, Wednesday, Friday' Insert Into @Interval Select 42 , 'Monday, Wednesday, Friday' Insert Into @Interval Select 43 , 'Sunday, Monday, Wednesday, Friday' Insert Into @Interval Select 44 , 'Tuesday, Wednesday, Friday' Insert Into @Interval Select 45 , 'Sunday, Tuesday, Wednesday, Friday' Insert Into @Interval Select 46 , 'Monday, Tuesday, Wednesday, Friday' Insert Into @Interval Select 47 , 'Sunday, Monday, Tuesday, Wednesday, Friday' Insert Into @Interval Select 48 , 'Thursday, Friday' Insert Into @Interval Select 49 , 'Sunday, Thursday, Friday' Insert Into @Interval Select 50 , 'Monday, Thursday, Friday' Insert Into @Interval Select 51 , 'Sunday, Monday, Thursday, Friday' Insert Into @Interval Select 52 , 'Tuesday, Thursday, Friday' Insert Into @Interval Select 53 , 'Sunday, Tuesday, Thursday, Friday' Insert Into @Interval Select 54 , 'Monday, Tuesday, Thursday, Friday' Insert Into @Interval Select 55 , 'Sunday, Monday, Tuesday, Thursday, Friday' Insert Into @Interval Select 56 , 'Wednesday, Thursday, Friday' Insert Into @Interval Select 57 , 'Sunday, Wednesday, Thursday, Friday' Insert Into @Interval Select 58 , 'Monday, Wednesday, Thursday, Friday' Insert Into @Interval Select 59 , 'Sunday, Monday, Wednesday, Thursday, Friday' Insert Into @Interval Select 60 , 'Tuesday, Wednesday, Thursday, Friday' Insert Into @Interval Select 61 , 'Sunday, Tuesday, Wednesday, Thursday, Friday' Insert Into @Interval Select 62 , 'Monday, Tuesday, Wednesday, Thursday, Friday' Insert Into @Interval Select 63 , 'Sunday, Monday, Tuesday, Wednesday, Thursday, Friday' Insert Into @Interval Select 64 , 'Saturday' Insert Into @Interval Select 65 , 'Sunday, Saturday' Insert Into @Interval Select 66 , 'Monday, Saturday' Insert Into @Interval Select 67 , 'Sunday, Monday, Saturday' Insert Into @Interval Select 68 , 'Tuesday, Saturday' Insert Into @Interval Select 69 , 'Sunday, Tuesday, Saturday' Insert Into @Interval Select 70 , 'Monday, Tuesday, Saturday' Insert Into @Interval Select 71 , 'Sunday, Monday, Tuesday, Saturday' Insert Into @Interval Select 72 , 'Wednesday, Saturday' Insert Into @Interval Select 73 , 'Sunday, Wednesday, Saturday' Insert Into @Interval Select 74 , 'Monday, Wednesday, Saturday' Insert Into @Interval Select 75 , 'Sunday, Monday, Wednesday, Saturday' Insert Into @Interval Select 76 , 'Tuesday, Wednesday, Saturday' Insert Into @Interval Select 77 , 'Sunday, Tuesday, Wednesday, Saturday' Insert Into @Interval Select 78 , 'Monday, Tuesday, Wednesday, Saturday' Insert Into @Interval Select 79 , 'Sunday, Monday, Tuesday, Wednesday, Saturday' Insert Into @Interval Select 80 , 'Thursday, Saturday' Insert Into @Interval Select 81 , 'Sunday, Thursday, Saturday' Insert Into @Interval Select 82 , 'Monday, Thursday, Saturday' Insert Into @Interval Select 83 , 'Sunday, Monday, Thursday, Saturday' Insert Into @Interval Select 84 , 'Tuesday, Thursday, Saturday' Insert Into @Interval Select 85 , 'Sunday, Tuesday, Thursday, Saturday' Insert Into @Interval Select 86 , 'Monday, Tuesday, Thursday, Saturday' Insert Into @Interval Select 87 , 'Sunday, Monday, Tuesday, Thursday, Saturday' Insert Into @Interval Select 88 , 'Wednesday, Thursday, Saturday' Insert Into @Interval Select 89 , 'Sunday, Wednesday, Thursday, Saturday' Insert Into @Interval Select 90 , 'Monday, Wednesday, Thursday, Saturday' Insert Into @Interval Select 91 , 'Sunday, Monday, Wednesday, Thursday, Saturday' Insert Into @Interval Select 92 , 'Tuesday, Wednesday, Thursday, Saturday' Insert Into @Interval Select 93 , 'Sunday, Tuesday, Wednesday, Thursday, Saturday' Insert Into @Interval Select 94 , 'Monday, Tuesday, Wednesday, Thursday, Saturday' Insert Into @Interval Select 95 , 'Sunday, Monday, Tuesday, Wednesday, Thursday, Saturday' Insert Into @Interval Select 96 , 'Friday, Saturday' Insert Into @Interval Select 97 , 'Sunday, Friday, Saturday' Insert Into @Interval Select 98 , 'Monday, Friday, Saturday' Insert Into @Interval Select 99 , 'Sunday, Monday, Friday, Saturday' Insert Into @Interval Select 100 , 'Tuesday, Friday, Saturday' Insert Into @Interval Select 101 , 'Sunday, Tuesday, Friday, Saturday' Insert Into @Interval Select 102 , 'Monday, Tuesday, Friday, Saturday' Insert Into @Interval Select 103 , 'Sunday, Monday, Tuesday, Friday, Saturday' Insert Into @Interval Select 104 , 'Wednesday, Friday, Saturday' Insert Into @Interval Select 105 , 'Sunday, Wednesday, Friday, Saturday' Insert Into @Interval Select 106 , 'Monday, Wednesday, Friday, Saturday' Insert Into @Interval Select 107 , 'Sunday, Monday, Wednesday, Friday, Saturday' Insert Into @Interval Select 108 , 'Tuesday, Wednesday, Friday, Saturday' Insert Into @Interval Select 109 , 'Sunday, Tuesday, Wednesday, Friday, Saturday' Insert Into @Interval Select 110 , 'Monday, Tuesday, Wednesday, Friday, Saturday' Insert Into @Interval Select 111 , 'Sunday, Monday, Tuesday, Wednesday, Friday, Saturday' Insert Into @Interval Select 112 , 'Thursday, Friday, Saturday' Insert Into @Interval Select 113 , 'Sunday, Thursday, Friday, Saturday' Insert Into @Interval Select 114 , 'Monday, Thursday, Friday, Saturday' Insert Into @Interval Select 115 , 'Sunday, Monday, Thursday, Friday, Saturday' Insert Into @Interval Select 116 , 'Tuesday, Thursday, Friday, Saturday' Insert Into @Interval Select 117 , 'Sunday, Tuesday, Thursday, Friday, Saturday' Insert Into @Interval Select 118 , 'Monday, Tuesday, Thursday, Friday, Saturday' Insert Into @Interval Select 119 , 'Sunday, Monday, Tuesday, Thursday, Friday, Saturday' Insert Into @Interval Select 120 , 'Wednesday, Thursday, Friday, Saturday' Insert Into @Interval Select 121 , 'Sunday, Wednesday, Thursday, Friday, Saturday' Insert Into @Interval Select 122 , 'Monday, Wednesday, Thursday, Friday, Saturday' Insert Into @Interval Select 123 , 'Sunday, Monday, Wednesday, Thursday, Friday, Saturday' Insert Into @Interval Select 124 , 'Tuesday, Wednesday, Thursday, Friday, Saturday' Insert Into @Interval Select 125 , 'Sunday, Tuesday, Wednesday, Thursday, Friday, Saturday' Insert Into @Interval Select 126 , 'Monday, Tuesday, Wednesday, Thursday, Friday, Saturday' Insert Into @Interval Select 127 , 'Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday' Select * from @Interval
Results:
freq_Interval Interval_Days ------------- ---------------------------------------------------------------------- 1 Sunday 2 Monday 3 Sunday, Monday 4 Tuesday 5 Sunday, Tuesday 6 Monday, Tuesday 7 Sunday, Monday, Tuesday 8 Wednesday 9 Sunday, Wednesday 10 Monday, Wednesday 11 Sunday, Monday, Wednesday 12 Tuesday, Wednesday 13 Sunday, Tuesday, Wednesday 14 Monday, Tuesday, Wednesday 15 Sunday, Monday, Tuesday, Wednesday 16 Thursday 17 Sunday, Thursday 18 Monday, Thursday 19 Sunday, Monday, Thursday 20 Tuesday, Thursday 21 Sunday, Tuesday, Thursday 22 Monday, Tuesday, Thursday 23 Sunday, Monday, Tuesday, Thursday 24 Wednesday, Thursday 25 Sunday, Wednesday, Thursday 26 Monday, Wednesday, Thursday 27 Sunday, Monday, Wednesday, Thursday 28 Tuesday, Wednesday, Thursday 29 Sunday, Tuesday, Wednesday, Thursday 30 Monday, Tuesday, Wednesday, Thursday 31 Sunday, Monday, Tuesday, Wednesday, Thursday 32 Friday 33 Sunday, Friday 34 Monday, Friday 35 Sunday, Monday, Friday 36 Tuesday, Friday 37 Sunday, Tuesday, Friday 38 Monday, Tuesday, Friday 39 Sunday, Monday, Tuesday, Friday 40 Wednesday, Friday 41 Sunday, Wednesday, Friday 42 Monday, Wednesday, Friday 43 Sunday, Monday, Wednesday, Friday 44 Tuesday, Wednesday, Friday 45 Sunday, Tuesday, Wednesday, Friday 46 Monday, Tuesday, Wednesday, Friday 47 Sunday, Monday, Tuesday, Wednesday, Friday 48 Thursday, Friday 49 Sunday, Thursday, Friday 50 Monday, Thursday, Friday 51 Sunday, Monday, Thursday, Friday 52 Tuesday, Thursday, Friday 53 Sunday, Tuesday, Thursday, Friday 54 Monday, Tuesday, Thursday, Friday 55 Sunday, Monday, Tuesday, Thursday, Friday 56 Wednesday, Thursday, Friday 57 Sunday, Wednesday, Thursday, Friday 58 Monday, Wednesday, Thursday, Friday 59 Sunday, Monday, Wednesday, Thursday, Friday 60 Tuesday, Wednesday, Thursday, Friday 61 Sunday, Tuesday, Wednesday, Thursday, Friday 62 Monday, Tuesday, Wednesday, Thursday, Friday 63 Sunday, Monday, Tuesday, Wednesday, Thursday, Friday 64 Saturday 65 Sunday, Saturday 66 Monday, Saturday 67 Sunday, Monday, Saturday 68 Tuesday, Saturday 69 Sunday, Tuesday, Saturday 70 Monday, Tuesday, Saturday 71 Sunday, Monday, Tuesday, Saturday 72 Wednesday, Saturday 73 Sunday, Wednesday, Saturday 74 Monday, Wednesday, Saturday 75 Sunday, Monday, Wednesday, Saturday 76 Tuesday, Wednesday, Saturday 77 Sunday, Tuesday, Wednesday, Saturday 78 Monday, Tuesday, Wednesday, Saturday 79 Sunday, Monday, Tuesday, Wednesday, Saturday 80 Thursday, Saturday 81 Sunday, Thursday, Saturday 82 Monday, Thursday, Saturday 83 Sunday, Monday, Thursday, Saturday 84 Tuesday, Thursday, Saturday 85 Sunday, Tuesday, Thursday, Saturday 86 Monday, Tuesday, Thursday, Saturday 87 Sunday, Monday, Tuesday, Thursday, Saturday 88 Wednesday, Thursday, Saturday 89 Sunday, Wednesday, Thursday, Saturday 90 Monday, Wednesday, Thursday, Saturday 91 Sunday, Monday, Wednesday, Thursday, Saturday 92 Tuesday, Wednesday, Thursday, Saturday 93 Sunday, Tuesday, Wednesday, Thursday, Saturday 94 Monday, Tuesday, Wednesday, Thursday, Saturday 95 Sunday, Monday, Tuesday, Wednesday, Thursday, Saturday 96 Friday, Saturday 97 Sunday, Friday, Saturday 98 Monday, Friday, Saturday 99 Sunday, Monday, Friday, Saturday 100 Tuesday, Friday, Saturday 101 Sunday, Tuesday, Friday, Saturday 102 Monday, Tuesday, Friday, Saturday 103 Sunday, Monday, Tuesday, Friday, Saturday 104 Wednesday, Friday, Saturday 105 Sunday, Wednesday, Friday, Saturday 106 Monday, Wednesday, Friday, Saturday 107 Sunday, Monday, Wednesday, Friday, Saturday 108 Tuesday, Wednesday, Friday, Saturday 109 Sunday, Tuesday, Wednesday, Friday, Saturday 110 Monday, Tuesday, Wednesday, Friday, Saturday 111 Sunday, Monday, Tuesday, Wednesday, Friday, Saturday 112 Thursday, Friday, Saturday 113 Sunday, Thursday, Friday, Saturday 114 Monday, Thursday, Friday, Saturday 115 Sunday, Monday, Thursday, Friday, Saturday 116 Tuesday, Thursday, Friday, Saturday 117 Sunday, Tuesday, Thursday, Friday, Saturday 118 Monday, Tuesday, Thursday, Friday, Saturday 119 Sunday, Monday, Tuesday, Thursday, Friday, Saturday 120 Wednesday, Thursday, Friday, Saturday 121 Sunday, Wednesday, Thursday, Friday, Saturday 122 Monday, Wednesday, Thursday, Friday, Saturday 123 Sunday, Monday, Wednesday, Thursday, Friday, Saturday 124 Tuesday, Wednesday, Thursday, Friday, Saturday 125 Sunday, Tuesday, Wednesday, Thursday, Friday, Saturday 126 Monday, Tuesday, Wednesday, Thursday, Friday, Saturday 127 Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday (127 row(s) affected)
SQL Code Snippets 
August 18th, 2014 at 11:51 am
Thank you for the table. Awesome!
May 31st, 2016 at 10:51 pm
This powershell code, but could be translated to any other code.
this uses a recursive day, the rest is simple.
$i=$args[0]
#find lower or equal power
$j=1
while($true)
{
$d=$j*2
if ($d -gt $i) { $day_in=$j ; break}
$j=$d
}
“Day included=$day_in”
if($day_in -eq $i){exit}
$rest=$i-$day_in
./find_days “$rest”
March 31st, 2021 at 12:28 pm
I know I am late to the party but just wanted to share a smaller bit of code to achieve the same result.
DECLARE @Days TABLE (DayVal tinyint, NameOfDay varchar(10), DayOfWeekVal tinyint)
INSERT INTO @Days
VALUES (0, NULL, 0)
,(1, ‘Sunday’, 1)
,(2, ‘Monday’, 2)
,(4, ‘Tuesday’, 3)
,(8, ‘Wednesday’, 4)
,(16, ‘Thursday’, 5)
,(32, ‘Friday’, 6)
,(64, ‘Saturday’, 7)
SELECT (Sun.DayVal + Mon.DayVal + Tue.DayVal + Wed.DayVal + Thu.DayVal + Fri.DayVal + Sat.DayVal) AS Id
, SUBSTRING(CONCAT(Case WHEN Sun.DayVal = 0 THEN NULL ELSE CONCAT(‘,’, Sun.NameOfDay) END
, Case WHEN Mon.DayVal = 0 THEN NULL ELSE CONCAT(‘,’, Mon.NameOfDay) END
, Case WHEN Tue.DayVal = 0 THEN NULL ELSE CONCAT(‘,’, Tue.NameOfDay) END
, Case WHEN Wed.DayVal = 0 THEN NULL ELSE CONCAT(‘,’, Wed.NameOfDay) END
, Case WHEN Thu.DayVal = 0 THEN NULL ELSE CONCAT(‘,’, Thu.NameOfDay) END
, Case WHEN Fri.DayVal = 0 THEN NULL ELSE CONCAT(‘,’, Fri.NameOfDay) END
, Case WHEN Sat.DayVal = 0 THEN NULL ELSE CONCAT(‘,’, Sat.NameOfDay) END), 2, 100)
FROM @Days Sun
INNER JOIN @Days Mon ON Mon.DayOfWeekVal IN (0,2)
INNER JOIN @Days Tue ON Tue.DayOfWeekVal IN (0,3)
INNER JOIN @Days Wed ON Wed.DayOfWeekVal IN (0,4)
INNER JOIN @Days Thu ON Thu.DayOfWeekVal IN (0,5)
INNER JOIN @Days Fri ON Fri.DayOfWeekVal IN (0,6)
INNER JOIN @Days Sat ON Sat.DayOfWeekVal IN (0,7)
WHERE Sun.DayOfWeekVal IN (0,1)
AND (Sun.DayVal + Mon.DayVal + Tue.DayVal + Wed.DayVal + Thu.DayVal + Fri.DayVal + Sat.DayVal) 0
ORDER BY 1