Bible Youversion links sql

youversionlinks.sql

DECLARE @refs as table
(rdate date, bibleref varchar(50), book varchar(5), chapters varchar(10), startChapter int)

INSERT INTO @refs
(rdate, bibleref, book, chapters)
SELECT rdate, bibleref, case when BibleRef like '%pas%' or bibleref like '%paa%' then UPPER(SUBSTRING(REPLACE(bibleref, 'pas', 'psa'), 1, 3))
when BibleRef like '1%' OR BibleRef like '2%' then UPPER(REPLACE(SUBSTRING(bibleref, 1, 5), ' ', ''))
else UPPER(SUBSTRING(bibleref, 1, 3)) end,
reverse(SUBSTRING(reverse(bibleref), 1, CHARINDEX(' ', reverse(bibleref))))
FROM dbo.Reading 
WHERE YEAR(rdate) = 2019

UPDATE @refs
SET startChapter = case when CHARINDEX('-', chapters) = 0
then chapters
else SUBSTRING(chapters, 1, CHARINDEX('-', chapters)- 1)
end

select 
DATE
rdate, bibleref, 'youversion://bible?reference=' + book + '.'+cast(startChapter as varchar) as link
from @refs