June 24, 2015 at 1:59 pm
I have a data file listing open and close hours for the day of the week. I need to format it like
Mon-Thurs 7:00 AM - 5:00 PM
Fri 8:00 AM - 5:00 PM
Sat 8:00 AM - 1:00 PM
Sun Closed
Here is what the data looks like now.
Mon OpenMon ClosedTue OpenTue ClosedWed OpenWed ClosedThu OpenThu ClosedFri OpenFri ClosedSat OpenSat ClosedSun OpenSun Closed
7:30 AM5:30 PM7:30 AM5:30 PM7:30 AM5:30 PM7:30 AM5:30 PM7:30 AM5:30 PMClosed Closed
7:00 AM6:00 PM7:00 AM6:00 PM7:00 AM7:00 PM7:00 AM6:00 PM7:00 AM6:00 PM8:00 AM5:00 PMClosed
There seems like I would end up 5 lines max?
Any idea how to even start?
Thanks
Bill
June 24, 2015 at 2:13 pm
blarson 11072 (6/24/2015)
I have a data file listing open and close hours for the day of the week. I need to format it likeMon-Thurs 7:00 AM - 5:00 PM
Fri 8:00 AM - 5:00 PM
Sat 8:00 AM - 1:00 PM
Sun Closed
Here is what the data looks like now.
Mon OpenMon ClosedTue OpenTue ClosedWed OpenWed ClosedThu OpenThu ClosedFri OpenFri ClosedSat OpenSat ClosedSun OpenSun Closed
7:30 AM5:30 PM7:30 AM5:30 PM7:30 AM5:30 PM7:30 AM5:30 PM7:30 AM5:30 PMClosed Closed
7:00 AM6:00 PM7:00 AM6:00 PM7:00 AM7:00 PM7:00 AM6:00 PM7:00 AM6:00 PM8:00 AM5:00 PMClosed
There seems like I would end up 5 lines max?
Any idea how to even start?
Thanks
Bill
I would start by normalizing that table into rows instead of columns. It becomes quite a bit simpler when the data is normalized.
Something like this.
if OBJECT_ID('StoreHours') is not null
drop table StoreHours
Create table StoreHours
(
StoreID int
, OpenTime time
, CloseTime time
, OperatingDay Char(3)
)
insert StoreHours
select 1, '7:30 AM', '5:30 PM', 'Mon' union all
select 1, '7:30 AM', '5:30 PM', 'Tue' union all
select 1, '7:30 AM', '5:30 PM', 'Wed' union all
select 1, '7:30 AM', '5:30 PM', 'Thu' union all
select 1, '8:00 AM', '5:00 PM', 'Fri'
select *
from StoreHours
If you are unable to normalize your table structure you will have to get a bit more creative. A cte could unpivot the original into rows. Once you get it in this format it a bit easier because it then just becomes an islands and gaps problem.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 24, 2015 at 2:14 pm
blarson 11072 (6/24/2015)
I have a data file listing open and close hours for the day of the week. I need to format it likeMon-Thurs 7:00 AM - 5:00 PM
Fri 8:00 AM - 5:00 PM
Sat 8:00 AM - 1:00 PM
Sun Closed
Here is what the data looks like now.
Mon OpenMon ClosedTue OpenTue ClosedWed OpenWed ClosedThu OpenThu ClosedFri OpenFri ClosedSat OpenSat ClosedSun OpenSun Closed
7:30 AM5:30 PM7:30 AM5:30 PM7:30 AM5:30 PM7:30 AM5:30 PM7:30 AM5:30 PMClosed Closed
7:00 AM6:00 PM7:00 AM6:00 PM7:00 AM7:00 PM7:00 AM6:00 PM7:00 AM6:00 PM8:00 AM5:00 PMClosed
There seems like I would end up 5 lines max?
Any idea how to even start?
Thanks
Bill
Looks like the 2nd record is missing a value for "Sun Closed", but more importantly, what is the significance of the 2nd record? Is there perhaps another field that identifies which business the hours belong to?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 24, 2015 at 2:20 pm
Yes these are two separate businesses. I just gave two sample records. In the data if Sunday Open has a value of "Closed" then Sunday Closed is NULL.
June 24, 2015 at 2:30 pm
Here is some sample data based on the example posted. Please notice how I put this together so it is readily consumable for others. In the future you should try to post like this so it is easy for us to work on the problem.
if OBJECT_ID('StoreHours') is not null
drop table StoreHours
create table StoreHours
(
StoredID int,
MonOpen varchar(10),
MonClose varchar(10),
TueOpen varchar(10),
TueClose varchar(10),
WedOpen varchar(10),
WedClose varchar(10),
ThuOpen varchar(10),
ThuClose varchar(10),
FriOpen varchar(10),
FriClose varchar(10),
SatOpen varchar(10),
SatClose varchar(10),
SunOpen varchar(10),
SunClose varchar(10)
)
insert StoreHours
select 1, '7:30 AM', '5:30 PM', '7:30 AM', '5:30 PM', '7:30 AM', '5:30 PM', '7:30 AM', '5:30 PM', '7:30 AM', '5:30 PM', 'Closed', NULL, 'Closed', NULL union all
select 2, '7:00 AM', '6:00 PM', '7:00 AM', '6:00 PM', '7:00 AM', '7:00 PM', '7:00 AM', '6:00 PM', '7:00 AM', '6:00 PM', '8:00 AM', '5:00 PM', 'Closed', NULL
select *
from StoreHours
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 24, 2015 at 2:31 pm
Okay. Post the data in consumable INSERT statements and provide the table create statements and include the business identifier column, and I'll take a shot at it. Using some kind of PIVOT may be possible if we join the two time values into a single field for each day first.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 24, 2015 at 2:38 pm
Thanks so much for your help.
June 24, 2015 at 3:14 pm
Please test the following, as it at least appears to work on the sample data from a previous post:
DECLARE @StoreHours AS TABLE (
StoreID int,
MonOpen varchar(10),
MonClose varchar(10),
TueOpen varchar(10),
TueClose varchar(10),
WedOpen varchar(10),
WedClose varchar(10),
ThuOpen varchar(10),
ThuClose varchar(10),
FriOpen varchar(10),
FriClose varchar(10),
SatOpen varchar(10),
SatClose varchar(10),
SunOpen varchar(10),
SunClose varchar(10)
);
INSERT @StoreHours
SELECT 1, '7:30 AM', '5:30 PM', '7:30 AM', '5:30 PM', '7:30 AM', '5:30 PM', '7:30 AM', '5:30 PM', '7:30 AM', '5:30 PM', 'Closed', NULL, 'Closed', NULL union all
SELECT 2, '7:00 AM', '6:00 PM', '7:00 AM', '6:00 PM', '7:00 AM', '7:00 PM', '7:00 AM', '6:00 PM', '7:00 AM', '6:00 PM', '8:00 AM', '5:00 PM', 'Closed', NULL;
WITH PIVOTABLE AS (
SELECT SH.StoreID, MonOpen + ' - ' + ISNULL(MonClose,'') AS Mon,
TueOpen + ' - ' + ISNULL(TueClose,'') AS Tue,
WedOpen + ' - ' + ISNULL(WedClose,'') AS Wed,
ThuOpen + ' - ' + ISNULL(ThuClose,'') AS Thu,
FriOpen + ' - ' + ISNULL(FriClose,'') AS Fri,
SatOpen + ' - ' + ISNULL(SatClose,'') AS Sat,
SunOpen + ' - ' + ISNULL(SunClose,'') AS Sun
FROM @StoreHours AS SH
),
PIVOTED AS (
SELECT StoreID, DAY_NAME, REPLACE(DayTime, 'Closed - ', 'Closed') AS DayTime
FROM PIVOTABLE
UNPIVOT (DayTime FOR DAY_NAME IN ([Mon], [Tue], [Wed], [Thu], [Fri], [Sat], [Sun])) AS UPVT
),
DATE_MAGIC AS (
SELECT CAST('1900-01-01' AS date) AS THE_DATE, 1 AS RN
UNION ALL
SELECT DATEADD(dd, 1, THE_DATE) AS THE_DATE, RN + 1 AS RN
FROM DATE_MAGIC AS DM
WHERE RN + 1 < 8
),
FINAL_SOURCE AS (
SELECT P.*, DM.RN
FROM PIVOTED AS P
INNER JOIN DATE_MAGIC AS DM
ON P.DAY_NAME = LEFT(DATENAME(dw, DM.THE_DATE),3)
),
UNIQUE_VALUES AS (
SELECT FS.StoreID, MIN(FS.RN) AS MIN_DAY_NUMBER, MAX(FS.RN) AS MAX_DAY_NUMBER, FS.DayTime
FROM FINAL_SOURCE AS FS
GROUP BY FS.StoreID, FS.DayTime
)
SELECT V.StoreID, F1.DAY_NAME + ISNULL(' - ' + NULLIF(F2.DAY_NAME, F1.DAY_NAME), '') AS DAY_RANGE, V.DayTime
FROM UNIQUE_VALUES AS V
INNER JOIN FINAL_SOURCE AS F1
ON V.StoreID = F1.StoreID
AND V.DayTime = F1.DayTime
AND V.MIN_DAY_NUMBER = F1.RN
INNER JOIN FINAL_SOURCE AS F2
ON V.StoreID = F2.StoreID
AND V.DayTime = F2.DayTime
AND V.MAX_DAY_NUMBER = F2.RN
ORDER BY V.StoreID, V.DayTime;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 25, 2015 at 8:44 am
Thanks again for the help. This is close but I don't think it will work. Here is some output from my data.
111340Fri7:00 AM - 5:00 PM
111340Tue - Thu7:00 AM - 6:00 PM
111340Mon - Wed7:00 AM - 8:00 PM
111340Sat8:00 AM - 4:00 PM
111340SunClosed
If you see the sort order is not correct and the "Tue - Thu" and "Mon - Wed" should be "Tue, Thu" and "Mon, Wed". I think I will need to put the data in the format below and cursor through it to build the strings.
ID OpenTimeCloseTimeOperatingDaySortOrder
1111157:30 AM5:30 PMMon 1
1111157:30 AM5:30 PMTue 2
1111157:30 AM5:30 PMWed 3
1111157:30 AM5:30 PMThu 4
1111157:30 AM5:30 PMFri 5
111115ClosedNULLSat 6
111115ClosedNULLSun 7
1111177:00 AM6:00 PMMon 1
1111177:00 AM6:00 PMTue 2
1111177:00 AM7:00 PMWed 3
1111177:00 AM6:00 PMThu 4
1111177:00 AM6:00 PMFri 5
1111178:00 AM5:00 PMSat 6
111117ClosedNULLSun 7
June 25, 2015 at 10:16 am
blarson 11072 (6/25/2015)
Thanks again for the help. This is close but I don't think it will work. Here is some output from my data.111340Fri7:00 AM - 5:00 PM
111340Tue - Thu7:00 AM - 6:00 PM
111340Mon - Wed7:00 AM - 8:00 PM
111340Sat8:00 AM - 4:00 PM
111340SunClosed
If you see the sort order is not correct and the "Tue - Thu" and "Mon - Wed" should be "Tue, Thu" and "Mon, Wed". I think I will need to put the data in the format below and cursor through it to build the strings.
ID OpenTimeCloseTimeOperatingDaySortOrder
1111157:30 AM5:30 PMMon 1
1111157:30 AM5:30 PMTue 2
1111157:30 AM5:30 PMWed 3
1111157:30 AM5:30 PMThu 4
1111157:30 AM5:30 PMFri 5
111115ClosedNULLSat 6
111115ClosedNULLSun 7
1111177:00 AM6:00 PMMon 1
1111177:00 AM6:00 PMTue 2
1111177:00 AM7:00 PMWed 3
1111177:00 AM6:00 PMThu 4
1111177:00 AM6:00 PMFri 5
1111178:00 AM5:00 PMSat 6
111117ClosedNULLSun 7
Yep, I see the problem. I'm going to need my SQL 2012 instance to rejigger the code to get a group that represents each continuous set of DayTime values. LEAD and LAG may be helpful here, and hopfully, someone else can get to this before I do, as my 2012 instance is at home and I won't be able to get to that until the weekend due to existing commitments tonight and tomorrow.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 26, 2015 at 11:36 am
I'm not getting very far with trying to loop through a cursor. I'll keep working on it.
June 26, 2015 at 12:25 pm
I'm not having great results trying to loop thru a cursor and track all the changes to up to 5 lines on hours. :crazy:
June 26, 2015 at 12:52 pm
blarson 11072 (6/26/2015)
I'm not having great results trying to loop thru a cursor and track all the changes to up to 5 lines on hours. :crazy:
I'm quite sure a cursor isn't necessary. I just won't have access to a SQL 2012 instance until at least later this evening in the GMT +5 time zone.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 26, 2015 at 1:02 pm
I understand. I just am trying to figure it out another way. Honestly, I just don't understand your code enough to run with it.
June 26, 2015 at 1:17 pm
blarson 11072 (6/26/2015)
I understand. I just am trying to figure it out another way. Honestly, I just don't understand your code enough to run with it.
Right now, my code groups on the DayTime value, but that ignores that it has to keep the groups in order by RN, and not just by the DayTime value. There's bound to be a way to use LEAD and/or LAG functions to generate a value that can be used to do the GROUP BY, and all it has to do is be a number that starts at 1 and only increments when the value of DayTime changes. If you can find a spot in my code early enough and generate such a number, then I can do the group by at the proper point in time and we're golden.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply