June 26, 2015 at 2:09 pm
You definitely don't need a cursor. Using the following setup:
DECLARE @StoreHours TABLE
(
store_id 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
I came up with the following query:
;
WITH store_hours_crosstab AS (
select store_id
,MAX(CASE WHEN day_name = 'Mon' THEN day_name END) AS mon_hours
,MAX(CASE WHEN day_name = 'Tue' THEN day_name END) AS tue_hours
,MAX(CASE WHEN day_name = 'Wed' THEN day_name END) AS wed_hours
,MAX(CASE WHEN day_name = 'Thu' THEN day_name END) AS thu_hours
,MAX(CASE WHEN day_name = 'Fri' THEN day_name END) AS fri_hours
,MAX(CASE WHEN day_name = 'Sat' THEN day_name END) AS sat_hours
,MAX(CASE WHEN day_name = 'Sun' THEN day_name END) AS sun_hours
,store_hours
,MIN(day_sort) AS sort_order
from @StoreHours
CROSS APPLY (
SELECT 'Mon', MonOpen + ISNULL('-' + MonClose, ''), 1
UNION
SELECT 'Tue', TueOpen + ISNULL('-' + TueClose, ''), 2
UNION
SELECT 'Wed', WedOpen + ISNULL('-' + WedClose, ''), 3
UNION
SELECT 'Thu', ThuOpen + ISNULL('-' + ThuClose, ''), 4
UNION
SELECT 'Fri', FriOpen + ISNULL('-' + FriClose, ''), 5
UNION
SELECT 'Sat', SatOpen + ISNULL('-' + SatClose, ''), 6
UNION
SELECT 'Sun', SunOpen + ISNULL('-' + SunClose, ''), 7
) AS store_hours_unpivoted(day_name, store_hours, day_sort) -- unpivot the table here
GROUP BY store_id, store_hours
)
SELECT store_id
,STUFF(ISNULL(', ' + mon_hours, '')
+ISNULL(CASE WHEN mon_hours IS NULL THEN ', ' WHEN wed_hours IS NULL THEN '-' ELSE NULL END + tue_hours, '')
+ISNULL(CASE WHEN tue_hours IS NULL THEN ', ' WHEN thu_hours IS NULL THEN '-' ELSE NULL END + wed_hours, '')
+ISNULL(CASE WHEN wed_hours IS NULL THEN ', ' WHEN fri_hours IS NULL THEN '-' ELSE NULL END + thu_hours, '')
+ISNULL(CASE WHEN thu_hours IS NULL THEN ', ' WHEN sat_hours IS NULL THEN '-' ELSE NULL END + fri_hours, '')
+ISNULL(CASE WHEN fri_hours IS NULL THEN ', ' WHEN sun_hours IS NULL THEN '-' ELSE NULL END + sat_hours, '')
+ISNULL(CASE WHEN sat_hours IS NULL THEN ', ' ELSE '-' END + sun_hours, '')
, 1, 2, '')
,store_hours
FROM store_hours_crosstab
ORDER BY store_id, sort_order
There are three parts to the query:
1) "unpivot" the data. I used a CROSS APPLY to do this, because I think it is more intuitive than UNPIVOT
2) "pivot" the data. I grouped on the store id and the hours to get this.
3) formatting calculations. I could have done this with the previous step, but the formulas would have been even more unwieldy than they currently are. Whether each day is displayed depends on whether the previous and following days have the same hours.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 26, 2015 at 2:29 pm
Words can't express how excited I am. I am testing the code and it the hours look good.
Here is a sample.
111322Mon-Fri7:30 AM-5:30 PM
111322Sat8:00 AM-3:00 PM
111322SunClosed
111323Mon-Fri7:30 AM-5:00 PM
111323Sat8:00 AM-12:00 PM
111323SunClosed
111336Mon-Fri7:30 AM-5:30 PM
111336Sat-SunClosed
111340Mon, Wed7:00 AM-8:00 PM
111340Tue, Thu7:00 AM-6:00 PM
111340Fri7:00 AM-5:00 PM
111340Sat8:00 AM-4:00 PM
111340SunClosed
111342Mon-Fri8:00 AM-6:00 PM
111342Sat8:00 AM-12:00 PM
111342SunClosed
Awesome!!
June 26, 2015 at 3:23 pm
Looks like there have been some new posts since I started in on my version of the solution... I haven't had a chance to test any of them against what I came up with.
For better or worse, I wrote it... It seems to meet the requirements... So I'm posting it. 😀
-- Create test data --
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
DROP TABLE #temp;
CREATE TABLE #temp (
StoreID INT NOT NULL PRIMARY KEY,
MonOpen VARCHAR(10),
MonClosed VARCHAR(10),
TueOpen VARCHAR(10),
TueClosed VARCHAR(10),
WedOpen VARCHAR(10),
WedClosed VARCHAR(10),
ThuOpen VARCHAR(10),
ThuClosed VARCHAR(10),
FriOpen VARCHAR(10),
FriClosed VARCHAR(10),
SatOpen VARCHAR(10),
SatClosed VARCHAR(10),
SunOpen VARCHAR(10),
SunClosed VARCHAR(10)
);
INSERT #temp (StoreID,MonOpen,MonClosed,TueOpen,TueClosed,WedOpen,WedClosed,ThuOpen,ThuClosed,FriOpen,FriClosed,SatOpen,SatClosed,SunOpen,SunClosed) VALUES
(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),
(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);
-- The solution --
WITH UnPiv AS (-- Start by unpivoting the data
SELECT
x.StoreID, x.DayOrder, x.DayAbbr, x.OpenTime, x.ClosedTime
FROM
#temp t
CROSS APPLY (VALUES
(t.StoreID, 1, 'Mon', t.MonOpen, t.MonClosed),
(t.StoreID, 2, 'Tue', t.TueOpen, t.TueClosed),
(t.StoreID, 3, 'Wed', t.WedOpen, t.WedClosed),
(t.StoreID, 4, 'Thu', t.ThuOpen, t.ThuClosed),
(t.StoreID, 5, 'Fri', t.FriOpen, t.FriClosed),
(t.StoreID, 6, 'Sat', t.SatOpen, t.SatClosed),
(t.StoreID, 7, 'Sun', t.SunOpen, t.SunClosed)
) x (StoreID, DayOrder, DayAbbr, OpenTime, ClosedTime)
), GetRanges AS (-- Then identify the changes in store hours between days. If a day is the same as the previous day, leave it null.
SELECT
up.StoreID,
up.DayOrder,
up.DayAbbr,
up.OpenTime,
up.ClosedTime,
CASE
WHEN CONCAT(up.OpenTime, up.ClosedTime) <> COALESCE(LAG(CONCAT(up.OpenTime, up.ClosedTime), 1) OVER (PARTITION BY up.StoreID ORDER BY up.DayOrder), '')
THEN ROW_NUMBER() OVER (PARTITION BY up.StoreID ORDER BY up.DayOrder)
END AS NewRangeID
FROM
UnPiv up
), RangeSmear AS (-- Smear the non-null values into the null values to create solid range blocks
SELECT
gr.StoreID,
gr.DayOrder,
gr.DayAbbr,
gr.OpenTime,
gr.ClosedTime,
MAX( gr.NewRangeID) OVER (PARTITION BY gr.StoreID ORDER BY gr.DayOrder ROWS UNBOUNDED PRECEDING) AS RangeSmear
FROM
GetRanges gr
)
-- The final select...
SELECT
rs.StoreID,
CASE
WHEN MIN(rs.DayOrder) = MAX(rs.DayOrder)
THEN CAST(SUBSTRING(MIN(CAST(rs.DayOrder AS BINARY(4)) + CAST(rs.DayAbbr AS BINARY(4))), 5, 4) AS CHAR(3))
ELSE CAST(SUBSTRING(MIN(CAST(rs.DayOrder AS BINARY(4)) + CAST(rs.DayAbbr AS BINARY(4))), 5, 4) AS CHAR(3)) + ' - ' +
CAST(SUBSTRING(MAX(CAST(rs.DayOrder AS BINARY(4)) + CAST(rs.DayAbbr AS BINARY(4))), 5, 4) AS CHAR(3))
END AS StoreDays,
CASE
WHEN rs.OpenTime IS NULL THEN rs.ClosedTime
WHEN rs.ClosedTime IS NULL THEN OpenTime
ELSE CONCAT(rs.OpenTime, ' - ', rs.ClosedTime)
END AS HoursOfOperation
FROM
RangeSmear rs
GROUP BY
rs.StoreID,
rs.OpenTime,
rs.ClosedTime,
rs.RangeSmear
ORDER BY
rs.StoreID,
MIN(rs.DayOrder)
Here are the results...
StoreIDStoreDaysHoursOfOperation
1Mon - Fri7:30 AM - 5:30 PM
1Sat - SunClosed
2Mon - Tue7:00 AM - 6:00 PM
2Wed7:00 AM - 7:00 PM
2Thu - Fri7:00 AM - 6:00 PM
2Sat8:00 AM - 5:00 PM
2SunClosed
HTH,
Jason
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply