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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
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