Formatting Store Hours - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Formatting Store Hours

Author

Topic

livezone
Starting Member

10 Posts

Posted - 2011-07-18 : 11:09:41

I have a table which contains opening & closing hours for all 7 days by store (one row). Hours from Monday to Friday, I have to format them as one line as shown below:

Mon-Fri:10:00am-9:00pm

But there could be multiple possiblites and even a store is also closed in the middle of the week.

1. Mon-Fri: Same hours
2. Mon-Tue: same hours and Wed-Thu: same hours
3. Mon-Thu: same hours and Fri : same hours
4. Mon-Wed: same hours and Thu-Fri: same hours
5. Mon: Hours Tue: Hours, Wed: Hours, Thu: Hours
6. ....

Is there a way to format the output using T-SQL considering all different possibilites?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-18 : 18:01:22

From your description, it seems like the requirement is not well-defined. For example, if a store decided to:

1. open at the start of every hour and close 15 minutes later on Mondays,
2. open at 10 minutes past the hour and close 15 minutes later every Tuesday

and so on for the rest of the days in the week, what should your output look like?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-18 : 20:29:16

quote:
I have a table which contains opening & closing hours for all 7 days by store (one row).

please show us how does it looks like. Or we will not be able to help you with formatting them as one line.

quote:
I have to format them as one line as shown below:
Mon-Fri:10:00am-9:00pm

quote:
But there could be multiple possiblites and even a store is also closed in the middle of the week.

1. Mon-Fri: Same hours
2. Mon-Tue: same hours and Wed-Thu: same hours
3. Mon-Thu: same hours and Fri : same hours
4. Mon-Wed: same hours and Thu-Fri: same hours
5. Mon: Hours Tue: Hours, Wed: Hours, Thu: Hours
6. ....

So where is these information stored ? How do you want to format the result ?

KH
[spoiler]Time is always against us[/spoiler]

livezone
Starting Member

10 Posts

Posted - 2011-07-19 : 08:45:43

This is a simple illustration of data, format required is shown below

Location O_Mon O_Tue O_Wed O_Thu O_Fri C_Mon C_Tue C_Wed C_Thu C_Fri
2222 9:30 9:30 9:30 9:30 9:30 21:00 21:00 21:00 21:00 21:00
3333 9:30 9:30 9:30 9:30 9:30 21:00 21:00 21:00 21:00 21:00
4444 9:30 9:30 9:30 9:30 9:30 18:00 18:00 21:00 21:00 21:00
5555 9:30 9:30 9:30 9:30 9:30 18:00 18:00 21:00 21:00 21:00

Above is the sample data, expected output should be as shown below
2222 Mon-Fri:09:30-21:00
3333 Mon-Fri:09:30-21:00
4444 Mon-Tue:09:30-18:00,Wed-Fri:09:30-2100

Thanks
Shafiq

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-19 : 11:24:39

[code]
;with
cte as
(
select Location, WDay = '1Mon', WD = 1, Op = O_Mon, Cl = C_Mon
from tbl
union all
select Location, WDay = '2Tue', WD = 2, Op = O_Tue, Cl = C_Tue
from tbl
union all
select Location, WDay = '3Wed', WD = 3, Op = O_Wed, Cl = C_Wed
from tbl
union all
select Location, WDay = '4Thu', WD = 4, Op = O_Thu, Cl = C_Thu
from tbl
union all
select Location, WDay = '5Fri', WD = 5, Op = O_Fri, Cl = C_Fri
from tbl
),
cte2 as
(
select *,
r1 = dense_rank() over (partition by Location, Op, Cl order by WD)
from cte
),
cte3 as
(
select Location,
WD = min(WD),
BizHour = right(min(WDay), 3) + '-' + right(max(WDay), 3)
+ ':'
+ min(Op) + '-' + max(Cl)
from cte2
group by Location, r1 - WD
)
select Location,
BizHour = stuff(
(
select ',' + BizHour
from cte3 x
where x.Location = c.Location
order by WD
for xml path('')
), 1, 1, '')
from cte3 c
group by Location
[/code]
[code]
/* RESULT :
Location BizHour
-------- ---------------------------------------
2222 Mon-Fri:09:30-21:00
3333 Mon-Fri:09:30-21:00
4444 Mon-Tue:09:30-18:00,Wed-Fri:09:30-21:00
5555 Mon-Tue:09:30-18:00,Wed-Fri:09:30-21:00
*/
[/code]

KH
[spoiler]Time is always against us[/spoiler]

livezone
Starting Member

10 Posts

Posted - 2011-07-20 : 15:24:11

Thanks for your response.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources