January 12, 2016 at 12:06 pm
Hi all,
I have hours in table like Mon-Sat 9- 5,Sun 10 - 6
I want the output as
Mon Tue Wed Thu Fri Sat Sun
9 to 5 9 to 59 to 5 9 to 5 9 to 5 9 to 5 10 to 6
How do I perform this..?
Thanks for your help..!!
January 12, 2016 at 12:28 pm
Please provide some sample data in easily consumable format, as described in the link in my signature.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 12, 2016 at 12:41 pm
This is how the data looks like.
Select 1 ,'Mon-Sat 10am-9pm, Sun 11am-6pm'
UNION ALL
Select 2,'Mon-Sat 10am-8pm, Sun 10am-7pm'
UNION ALL
Select 3 'Mon-Thu 10am-9pm, Fri 10am-10pm, Sat 10am-9pm, Sun 10am-6pm'
January 12, 2016 at 1:49 pm
;WITH cte_days AS (
Select 1 AS day#, 'Mon' AS day UNION ALL
Select 2, 'Tue' UNION ALL
Select 3, 'Wed' UNION ALL
Select 4, 'Thu' UNION ALL
Select 5, 'Fri' UNION ALL
Select 6, 'Sat' UNION ALL
Select 7, 'Sun'
)
SELECT
id,
MAX(CASE WHEN cd.day# = 1 THEN REPLACE(REPLACE(REPLACE(SUBSTRING(ItemTrimmed, CHARINDEX(' ', ItemTrimmed) + 1, 100)
, '-', ' to '), 'am', ''), 'pm', '') END) AS Mon,
MAX(CASE WHEN cd.day# = 2 THEN REPLACE(REPLACE(REPLACE(SUBSTRING(ItemTrimmed, CHARINDEX(' ', ItemTrimmed) + 1, 100)
, 'am', ''), 'pm', ''), '-', ' to ') END) AS Tue,
MAX(CASE WHEN cd.day# = 3 THEN REPLACE(REPLACE(REPLACE(SUBSTRING(ItemTrimmed, CHARINDEX(' ', ItemTrimmed) + 1, 100)
, 'am', ''), 'pm', ''), '-', ' to ') END) AS Wed,
MAX(CASE WHEN cd.day# = 4 THEN REPLACE(REPLACE(REPLACE(SUBSTRING(ItemTrimmed, CHARINDEX(' ', ItemTrimmed) + 1, 100)
, 'am', ''), 'pm', ''), '-', ' to ') END) AS Thu,
MAX(CASE WHEN cd.day# = 5 THEN REPLACE(REPLACE(REPLACE(SUBSTRING(ItemTrimmed, CHARINDEX(' ', ItemTrimmed) + 1, 100)
, 'am', ''), 'pm', ''), '-', ' to ') END) AS Fri,
MAX(CASE WHEN cd.day# = 6 THEN REPLACE(REPLACE(REPLACE(SUBSTRING(ItemTrimmed, CHARINDEX(' ', ItemTrimmed) + 1, 100)
, 'am', ''), 'pm', ''), '-', ' to ') END) AS Sat,
MAX(CASE WHEN cd.day# = 7 THEN REPLACE(REPLACE(REPLACE(SUBSTRING(ItemTrimmed, CHARINDEX(' ', ItemTrimmed) + 1, 100)
, 'am', ''), 'pm', ''), '-', ' to ') END) AS Sun
FROM (
Select 1 AS id,'Mon-Sat 10am-9pm, Sun 11am-6pm' AS daily_hours
UNION ALL
Select 2,'Mon-Sat 10am-8pm, Sun 10am-7pm'
UNION ALL
Select 3, 'Mon-Thu 10am-9pm, Fri 10am-10pm, Sat 10am-9pm, Sun 10am-6pm'
) AS test_data
CROSS APPLY DBA.dbo.delimitedSplit8K ( daily_hours, ',' ) ds
CROSS APPLY (
SELECT LTRIM(ds.Item) AS ItemTrimmed
) AS assign_alias_names1
CROSS APPLY (
Select LEFT(ItemTrimmed, 3) AS day1,
Case when SUBSTRING(ItemTrimmed, 4, 1) = '-' then SUBSTRING(ItemTrimmed, 5, 3) else LEFT(ItemTrimmed, 3) end as day2
) AS assign_alias_names2
INNER JOIN cte_days cd ON
cd.day# BETWEEN CHARINDEX(assign_alias_names2.day1, '__MonTueWedThuFriSatSun') / 3 AND
CHARINDEX(assign_alias_names2.day2, '__MonTueWedThuFriSatSun') / 3
GROUP BY id
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
January 12, 2016 at 3:34 pm
Thanks Scott...that is exactly what I was looking for.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply