May 5, 2010 at 12:59 pm
Looking for a little help on a query I am having trouble getting my mind around. No, this is not somebodys homework assignment. It just feels like it.
I have two tables. The first holds location specific info such as store hours and the second holds the data.
Location_Table
Location (varchar)
Sat_open(int)
Sat_close(int)
Sun_open(int)
Sun_close(int)
Mon_open(int)
etc...
Data_Table
Location(varchar)
Date(datetime)
Hour(int)
Data(float)
I need to select the data for a date range for all locations but only data where the location was open. Open and close hours change for each day of the week and are different for each location. How do I write the join for this? Can anybody help ?
May 5, 2010 at 1:06 pm
Help us help you. Please provide the DDL (CREATE TABLE statement(s)) for the table(s), sample data (series of INSERT INTO tablename statements) the represents your problem domain, expected results (in tabular format so we can compare our results) based on the sample data, and the code you have written sofar along with what problems your are encountering.
May 5, 2010 at 2:10 pm
Sorry about that. Here's the code block;
create table Location_Table(
Location varchar(20),
Sat_Open int,
Sat_Close int,
Sun_Open int,
Sun_Close int,
Mon_Open int,
Mon_Close int,
Tue_Open int,
Tue_Close int,
Wed_Open int,
Wed_Close int,
Thur_Open int,
Thur_Close int,
Fri_Open int,
Fri_Close int)
create table Data_table(
Location varchar(20),
Date datetime,
[Hour] int,
Data int)
insert into Location_Table values ('Dallas',10,14,12,16,9,15,9,15,9,15,9,15,9,15)
insert into Location_Table values ('Toronto',10,13,12,13,10,16,10,14,9,14,9,16,9,16)
insert into Location_Table values ('Paris',9,16,12,16,9,18,9,16,9,18,10,18,10,19)
Insert into Data_table values ('Dallas','4/2/2010',8,1)
Insert into Data_table values ('Dallas','4/2/2010',9,23)
Insert into Data_table values ('Dallas','4/2/2010',10,22)
Insert into Data_table values ('Dallas','4/2/2010',11,55)
Insert into Data_table values ('Dallas','4/2/2010',12,32)
Insert into Data_table values ('Dallas','4/2/2010',13,31)
Insert into Data_table values ('Dallas','4/2/2010',14,23)
Insert into Data_table values ('Dallas','4/2/2010',15,12)
Insert into Data_table values ('Dallas','4/2/2010',16,25)
Insert into Data_table values ('Dallas','4/2/2010',17,22)
Insert into Data_table values ('Dallas','4/2/2010',18,3)
Insert into Data_table values ('Toronto','4/2/2010',8,5)
Insert into Data_table values ('Toronto','4/2/2010',9,21)
Insert into Data_table values ('Toronto','4/2/2010',10,22)
Insert into Data_table values ('Toronto','4/2/2010',11,42)
Insert into Data_table values ('Toronto','4/2/2010',12,32)
Insert into Data_table values ('Toronto','4/2/2010',13,21)
Insert into Data_table values ('Toronto','4/2/2010',14,42)
Insert into Data_table values ('Toronto','4/2/2010',15,13)
Insert into Data_table values ('Toronto','4/2/2010',16,24)
Insert into Data_table values ('Toronto','4/2/2010',17,22)
Insert into Data_table values ('Toronto','4/2/2010',18,3)
Insert into Data_table values ('Paris','4/2/2010',8,3)
Insert into Data_table values ('Paris','4/2/2010',9,23)
Insert into Data_table values ('Paris','4/2/2010',10,43)
Insert into Data_table values ('Paris','4/2/2010',11,21)
Insert into Data_table values ('Paris','4/2/2010',12,45)
Insert into Data_table values ('Paris','4/2/2010',13,12)
Insert into Data_table values ('Paris','4/2/2010',14,33)
Insert into Data_table values ('Paris','4/2/2010',15,21)
Insert into Data_table values ('Paris','4/2/2010',16,33)
Insert into Data_table values ('Paris','4/2/2010',17,34)
Insert into Data_table values ('Paris','4/2/2010',18,6)
Insert into Data_table values ('Dallas','4/3/2010',8,1)
Insert into Data_table values ('Dallas','4/3/2010',9,23)
Insert into Data_table values ('Dallas','4/3/2010',10,34)
Insert into Data_table values ('Dallas','4/3/2010',11,21)
Insert into Data_table values ('Dallas','4/3/2010',12,33)
Insert into Data_table values ('Dallas','4/3/2010',13,24)
Insert into Data_table values ('Dallas','4/3/2010',14,22)
Insert into Data_table values ('Dallas','4/3/2010',15,33)
Insert into Data_table values ('Dallas','4/3/2010',16,52)
Insert into Data_table values ('Dallas','4/3/2010',17,12)
Insert into Data_table values ('Dallas','4/3/2010',18,6)
Insert into Data_table values ('Toronto','4/3/2010',8,3)
Insert into Data_table values ('Toronto','4/3/2010',9,23)
Insert into Data_table values ('Toronto','4/3/2010',10,34)
Insert into Data_table values ('Toronto','4/3/2010',11,42)
Insert into Data_table values ('Toronto','4/3/2010',12,12)
Insert into Data_table values ('Toronto','4/3/2010',13,12)
Insert into Data_table values ('Toronto','4/3/2010',14,32)
Insert into Data_table values ('Toronto','4/3/2010',15,23)
Insert into Data_table values ('Toronto','4/3/2010',16,44)
Insert into Data_table values ('Toronto','4/3/2010',17,11)
Insert into Data_table values ('Toronto','4/3/2010',18,13)
Insert into Data_table values ('Paris','4/3/2010',8,3)
Insert into Data_table values ('Paris','4/3/2010',9,12)
Insert into Data_table values ('Paris','4/3/2010',10,32)
Insert into Data_table values ('Paris','4/3/2010',11,22)
Insert into Data_table values ('Paris','4/3/2010',12,34)
Insert into Data_table values ('Paris','4/3/2010',13,23)
Insert into Data_table values ('Paris','4/3/2010',14,34)
Insert into Data_table values ('Paris','4/3/2010',15,31)
Insert into Data_table values ('Paris','4/3/2010',16,22)
Insert into Data_table values ('Paris','4/3/2010',17,22)
Insert into Data_table values ('Paris','4/3/2010',18,5)
Insert into Data_table values ('Dallas','4/4/2010',8,2)
Insert into Data_table values ('Dallas','4/4/2010',9,12)
Insert into Data_table values ('Dallas','4/4/2010',10,22)
Insert into Data_table values ('Dallas','4/4/2010',11,25)
Insert into Data_table values ('Dallas','4/4/2010',12,29)
Insert into Data_table values ('Dallas','4/4/2010',13,31)
Insert into Data_table values ('Dallas','4/4/2010',14,12)
Insert into Data_table values ('Dallas','4/4/2010',15,44)
Insert into Data_table values ('Dallas','4/4/2010',16,12)
Insert into Data_table values ('Dallas','4/4/2010',17,26)
Insert into Data_table values ('Dallas','4/4/2010',18,4)
Insert into Data_table values ('Toronto','4/4/2010',8,5)
Insert into Data_table values ('Toronto','4/4/2010',9,22)
Insert into Data_table values ('Toronto','4/4/2010',10,42)
Insert into Data_table values ('Toronto','4/4/2010',11,24)
Insert into Data_table values ('Toronto','4/4/2010',12,35)
Insert into Data_table values ('Toronto','4/4/2010',13,33)
Insert into Data_table values ('Toronto','4/4/2010',14,32)
Insert into Data_table values ('Toronto','4/4/2010',15,13)
Insert into Data_table values ('Toronto','4/4/2010',16,25)
Insert into Data_table values ('Toronto','4/4/2010',17,26)
Insert into Data_table values ('Toronto','4/4/2010',18,46)
Insert into Data_table values ('Paris','4/4/2010',8,1)
Insert into Data_table values ('Paris','4/4/2010',9,13)
Insert into Data_table values ('Paris','4/4/2010',10,22)
Insert into Data_table values ('Paris','4/4/2010',11,27)
Insert into Data_table values ('Paris','4/4/2010',12,72)
Insert into Data_table values ('Paris','4/4/2010',13,14)
Insert into Data_table values ('Paris','4/4/2010',14,36)
Insert into Data_table values ('Paris','4/4/2010',15,34)
Insert into Data_table values ('Paris','4/4/2010',16,25)
Insert into Data_table values ('Paris','4/4/2010',17,26)
Insert into Data_table values ('Paris','4/4/2010',18,3)
Insert into Data_table values ('Dallas','4/5/2010',8,1)
Insert into Data_table values ('Dallas','4/5/2010',9,14)
Insert into Data_table values ('Dallas','4/5/2010',10,26)
Insert into Data_table values ('Dallas','4/5/2010',11,66)
Insert into Data_table values ('Dallas','4/5/2010',12,44)
Insert into Data_table values ('Dallas','4/5/2010',13,32)
Insert into Data_table values ('Dallas','4/5/2010',14,52)
Insert into Data_table values ('Dallas','4/5/2010',15,18)
Insert into Data_table values ('Dallas','4/5/2010',16,6)
Insert into Data_table values ('Toronto','4/5/2010',8,22)
Insert into Data_table values ('Toronto','4/5/2010',9,32)
Insert into Data_table values ('Toronto','4/5/2010',10,14)
Insert into Data_table values ('Toronto','4/5/2010',11,32)
Insert into Data_table values ('Toronto','4/5/2010',12,77)
Insert into Data_table values ('Toronto','4/5/2010',13,31)
Insert into Data_table values ('Toronto','4/5/2010',14,42)
Insert into Data_table values ('Toronto','4/5/2010',15,30)
Insert into Data_table values ('Toronto','4/5/2010',16,9)
Insert into Data_table values ('Paris','4/5/2010',8,33)
Insert into Data_table values ('Paris','4/5/2010',9,16)
Insert into Data_table values ('Paris','4/5/2010',10,65)
Insert into Data_table values ('Paris','4/5/2010',11,23)
Insert into Data_table values ('Paris','4/5/2010',12,52)
Insert into Data_table values ('Paris','4/5/2010',13,14)
Insert into Data_table values ('Paris','4/5/2010',14,55)
Insert into Data_table values ('Paris','4/5/2010',15,8)
Insert into Data_table values ('Paris','4/5/2010',16,4)
-- select data for stores from 4/3/2010 to 4/4/2010
--expected results
Dallas4/3/20101034
Dallas4/3/20101121
Dallas4/3/20101233
Dallas4/3/20101324
Dallas4/3/20101422
Toronto4/3/20101034
Toronto4/3/20101142
Toronto4/3/20101212
Toronto4/3/20101312
Paris4/3/2010912
Paris4/3/20101032
Paris4/3/20101122
Paris4/3/20101234
Paris4/3/20101323
Paris4/3/20101434
Paris4/3/20101531
Paris4/3/20101622
Dallas4/4/20101229
Dallas4/4/20101331
Dallas4/4/20101412
Dallas4/4/20101544
Dallas4/4/20101612
Toronto4/4/20101235
Toronto4/4/20101333
Paris4/4/20101272
Paris4/4/20101314
Paris4/4/20101436
Paris4/4/20101534
Paris4/4/20101625
Not sure why I'm having so much trouble with this. Maybe just having a bad day.
May 5, 2010 at 2:25 pm
You should consider normalizing your table (e.g. DayOfWeek, OpenTime (DATETIME),CloseTime (DATETIME) )
What would you do with your design if one of the shops decides to open 8:30?
Regarding the DayOfWeek value you need to decide what day of a week would be day 1. (Since your sample data show Dallas, Toronto and Paris you might run into a DATEFIRST setting issue...).
May 5, 2010 at 2:40 pm
Thanks for the comments,
Normalizing the table or making changes to the structure isn't possible at this time as we would need to change 60+ stored procedures some of which are using dynamic sql (I know a bad practice but necessary in this case) and affect other systems.
The system that the source data is coming from only tracks at the hour granularity so the 9:30 opening is not an issue.
The DDL and data are only examples that show the problem. They are not the actual data and table DDL just what is needed for testing purposes.
May 5, 2010 at 3:13 pm
I'm a little confused regarding your expected result:
April 3rd 2010 was a Saturday. So the Dallas shop was open 10 till 14.
Why do you expect to see data for hours 9 and 15 for that date?
Side note: How would you handle a scenario where a shop decides to open from Sat 10 am until Sun 1am and Sun 10am until 3pm?
May 5, 2010 at 3:25 pm
Ok, here's how I would do it (assuming it was a typo in your expected result set...)
;WITH cte AS -- transfomr the table into a more normalized structure
(
-- use the CrossTab method to get a table in the format Location, Day_Number (to be used in a Modulo calculation later on), Open and Close
SELECT
Location,
CASE LEFT(day_status,3)
WHEN 'Mon' THEN 0
WHEN 'Tue' THEN 1
WHEN 'Wed' THEN 2
WHEN 'Thu' THEN 3
WHEN 'Fri' THEN 4
WHEN 'Sat' THEN 5
ELSE 6 END AS Day_Number,
MAX(CASE WHEN Day_Status LIKE '%Open' THEN hr ELSE NULL END) AS [OPEN],
MAX(CASE WHEN Day_Status LIKE '%Close' THEN hr ELSE NULL END) AS [CLOSE]
FROM
-- UNPIVOT to transform the columns into rows
(SELECT * FROM Location_Table) p
UNPIVOT
(Hr FOR Day_Status IN
(Sat_Open ,Sat_Close ,Sun_Open ,Sun_Close ,Mon_Open ,Mon_Close ,Tue_Open ,Tue_Close ,
Wed_Open ,Wed_Close ,Thur_Open ,Thur_Close ,Fri_Open ,Fri_Close)
)AS unpvt
GROUP BY Location,LEFT(day_status,3)
)
-- final join
SELECT d.*
FROM cte
INNER JOIN Data_table d ON cte.location =d.location
WHERE cte.day_number = DATEDIFF(dd,'19000101',d.DATE)%7
AND d.DATE >='20100403'
AND d.DATE <'20100405'
AND d.[HOUR]>=cte.[OPEN]
AND d.[HOUR]<=cte.[CLOSE]
May 5, 2010 at 3:26 pm
I'm very sorry about that. I was looking at the wrong month on the calendar and have since updated the expected results.
As for shops that cross over days we have a just a few of them and they are treated differently. I've written a view that I pull the data through which takes care of all of the necessary adjustments for our purposes.
May 5, 2010 at 3:37 pm
keent (5/5/2010)
I'm very sorry about that. I was looking at the wrong month on the calendar and have since updated the expected results.As for shops that cross over days we have a just a few of them and they are treated differently. I've written a view that I pull the data through which takes care of all of the necessary adjustments for our purposes.
No problem. Stuff like that happens once in a while when creating fake data... 🙂
Regarding your 2nd point:
As you figured, a "semi-optimal" table structure requires addtl. effort to still being able to use it... 😉 Therefore my advice in the first post: "Normalize your tables!"
But sometimes it's just a wishful thinking...
May 5, 2010 at 3:40 pm
Thanks soo much for your time and effort!
The solution you came up with is interesting and appears to be very fast. I've come up with a solution that appears to work as well although it's probably quite a bit slower than yours as it involves a couple of case statements.
select D.location, D.Date, D.Hour, D.Data
from
data_table D join Location_table L
on
L.Location = D.Location
and
D.Hour >= (select
case
--Saturday
when datepart(dw,D.Date) = 7 THEN
(Select L.Sat_Open where d.Location = L.location)
--Sunday
when datepart(dw,D.Date) = 1 THEN
(Select L.Sun_Open where d.Location = L.location)
--Monday
when datepart(dw,D.Date)= 2 THEN
(Select L.Mon_Open where d.Location = L.location)
--Tuesday
when datepart(dw,D.Date) = 3 THEN
(Select L.Tue_Open where d.Location = L.location)
--Wedsday
when datepart(dw,D.Date) = 4 THEN
(Select L.Wed_Open where d.Location = L.location)
--Thursday
when datepart(dw,D.Date) = 5 THEN
(Select L.Thur_Open where d.Location = L.location)
--Friday
when datepart(dw,D.Date) = 6 THEN
(Select L.Fri_Open where d.Location = L.location)
end)
and
D.Hour <=
(select
case
--Saturday
when datepart(dw,D.Date) = 7 THEN
(Select L.Sat_Close where d.Location = L.location)
--Sunday
when datepart(dw,D.Date) = 1 THEN
(Select L.Sun_Close where d.Location = L.location)
--Monday
when datepart(dw,D.Date) = 2 THEN
(Select L.Mon_Close where d.Location = L.location)
--Tuesday
when datepart(dw,D.Date) = 3 THEN
(Select L.Tue_Close where d.Location = L.location)
--Wedsday
when datepart(dw,D.Date) = 4 THEN
(Select L.Wed_Close where d.Location = L.location)
--Thursday
when datepart(dw,D.Date) = 5 THEN
(Select L.Thur_Close where d.Location = L.location)
--Friday
when datepart(dw,D.Date) = 6 THEN
(Select L.Fri_Close where d.Location = L.location)
end)
where D.Date >= '4/3/2010' and D.Date<= '4/4/2010'
order by location,date, hour
I'll do a little testing before I decide which to use.
Again, thank you.
May 5, 2010 at 3:52 pm
When you do your testing, try the following:
before running your query, add
SET DATEFIRST 1 -- set the first day of a week = Monday
The result of your query might be different...
The reason is that DATEPART depends on the setting of DATEFIRST during the runtime of that query and is influenced by the value of @@language as well.
Example: If you add a user with "French" as the standard language (since the shop is located in the French speaking area of Canada), this user will get different results running exactly the same query you posted than you would (assuming us_english setting).
To summarize it: you should avoid using DATEPART(dw,..) and DATEPART(wk,...) if possible.
May 5, 2010 at 4:06 pm
Excellent point. I may have to add a line to set that on all of my stored procedures just for safety. We are a small shop and I am the only one with the privileges to make those kinds of changes (as far as I know) so it's not a big concern but better to be safe than sorry.
Again, thanks!
May 5, 2010 at 4:30 pm
keent (5/5/2010)
Excellent point. I may have to add a line to set that on all of my stored procedures just for safety. We are a small shop and I am the only one with the privileges to make those kinds of changes (as far as I know) so it's not a big concern but better to be safe than sorry.Again, thanks!
Well, that would be a shot in the wrong direction...
I strongly vote against your approach to add a SET DATEFIRST on your sp's.
This setting will remain active during the rest of the session or until another SET DATEFIRST overrides. The scary part of it is: if you add a SET LANGUAGE statement within one of your sp's, it will change the value of @@datefirst as well UNLESS you have a SET DATEFIRST statement within that session prior to your SET LANGUAGE statement. In this case, the language setting will NOT change the value of @@datefirst. Confusing, heh?
I would rather recommend you to modify the related sp's not to use DATEPART() anymore. Instead of using DATEPART(dw,...) you could use DATEDIFF(dd,'19000101',YourDate)%7. This will ALWAYS return 0 for Monday rsp. 6 for Sunday, regardless of any @@language or @@datefirst setting.
May 5, 2010 at 5:10 pm
lmu92 (5/5/2010)
I would rather recommend you to modify the related sp's not to use DATEPART() anymore. Instead of using DATEPART(dw,...) you could use DATEDIFF(dd,'19000101',YourDate)%7. This will ALWAYS return 0 for Monday rsp. 6 for Sunday, regardless of any @@language or @@datefirst setting.
Lutz... good idea here.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 5, 2010 at 5:46 pm
WayneS (5/5/2010)
lmu92 (5/5/2010)
I would rather recommend you to modify the related sp's not to use DATEPART() anymore. Instead of using DATEPART(dw,...) you could use DATEDIFF(dd,'19000101',YourDate)%7. This will ALWAYS return 0 for Monday rsp. 6 for Sunday, regardless of any @@language or @@datefirst setting.Lutz... good idea here.
Thanx, Wayne. It's based on a discussion with Paul White a while ago (don't remember the thread though...) where we did some testing how DATEFIRST and LANGUAGE influence each other within a batch or even across batches. Might be agood idea to turn it into an article...
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply