November 24, 2008 at 9:10 pm
Hi There,
I was wondering if someone could help me with a query that i'm having issues with. I want to only the records from the join + 7 days in the result.
Can someone take a look at my query and help with the syntax please as i'm not sure if I have used the correct join either.
SELECT s.StoreName, s.StoreID,h.StoreDate,h.OpenTime,h.ClosingTime,h.Comments from schema.mytable2 s
left JOIN schema.mytable1 h on
s.[StoreID] = h.[StoreID]
where
h.[StoreDate] between GetDate() and DateAdd(dd, 1, h.StoreDate)
and s.StoreName = 'store'
store00012008-11-26 00:00:00.0009:00a5:30pNo Special Events
store00012008-11-27 00:00:00.0009:00a9:00pNo Special Events
store00012008-11-28 00:00:00.0009:00a5:30pNo Special Events
store00012008-11-29 00:00:00.0009:00a5:30pNo Special Events
November 24, 2008 at 9:32 pm
This statement:
I want to only the records from the join + 7 days in the result.
is not intelligible.
Please provide sample data and examples of the result set that you want.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 24, 2008 at 11:29 pm
HI There,
Sorry about the low brow post it's been a long week. I have attached some sample data and table structures. Basically what i'm trying to do is return a list of store hours that exist in the database that are no more than 7 days from today inclusive.I need to pass the storename in a parameter.
Sean - thanks in advance
!--- table
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [tblhours](
[StoreID] [int] NULL,
[StoreDate] [datetime] NULL,
[OpenTime] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ClosingTime] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Comments] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
!---- sample data
49022008-11-25 00:00:00.00010:00a5:30pcomments
49022008-11-26 00:00:00.00010:00a5:30pcomments
49022008-11-27 00:00:00.00010:00a5:30pcomments
49022008-11-28 00:00:00.00010:00a5:30pcomments
49032008-11-25 00:00:00.00010:00a5:30pcomments
49032008-11-26 00:00:00.00010:00a5:30pcomments
49032008-11-27 00:00:00.00010:00a5:30pcomments
49032008-11-28 00:00:00.00010:00a5:30pcomments
!------------------------- table
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [stores](
[StoreID] [int] NOT NULL,
[StoreName] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_stores] PRIMARY KEY CLUSTERED
(
[StoreID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
!--------------------------------- sample data
4903storeone
4903storetwo
November 25, 2008 at 1:08 am
Hi,
as far as I can tell, LEFT is unnecessary here - in fact, the query performs as with INNER JOIN, because there is condition in the WHERE clause that works with the joined table. Therefore, I would suggest using inner join to make it visible on first glance.
Something like this should work:
SELECT s.StoreName, s.StoreID, h.StoreDate, h.OpenTime, h.ClosingTime, h.Comments
FROM stores s
JOIN tblhours h ON s.[StoreID] = h.[StoreID]
WHERE h.[StoreDate] >= DATEADD(d,-7,GETDATE())
AND h.[StoreDate] <= GETDATE()
AND s.StoreName = 'store'
The conditions in WHERE clause depend on what precisely is meant by maximum 7 days (i.e. included today-7 or not?), whether StoreDate has always time-part 00:00:00 or not etc. Since you posted data with no time, I didn't bother with it - it may require some changes before the query works as you expect.
I mostly avoid BETWEEN when working with time data, because often the right solution includes just one of the limits - not both. For example, if your dates include time, and you want to show data from one month, it is best to do it as >= (first day of month) AND < (first day of next month).
If you have any problems reaching the result you need, post back with more info about it.
November 25, 2008 at 2:24 am
SELECTs.StoreName,
s.StoreID,
h.StoreDate,
h.OpenTime,
h.ClosingTime,
h.Comments
fromschema.mytable2 as s
left JOINschema.mytable1 as h on h.[StoreID] = s.[StoreID]
AND h.[StoreDate] between GetDate() and DateAdd(dd, 7, h.StoreDate)
WHEREs.StoreName = 'store'
N 56°04'39.16"
E 12°55'05.25"
November 25, 2008 at 8:51 am
Either Vladan's or Peso's queries should do it for you (I think that Vladan's "-7" should probably be "+7", but I am not sure). If they are not sufficient, please let us know.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 25, 2008 at 12:37 pm
Hi Guys,
Thanks for the responses.I think the Peso's answer is a little more what I need. As there is only one month of data at a time I'm only interested in showing the dates available from today.
I appreciate the help from all of you.
Sean
November 27, 2008 at 5:39 am
rbarryyoung (11/25/2008)
I think that Vladan's "-7" should probably be "+7", but I am not sure
That depends whether you need data from the last 7 days (like if you need to find what projects were finished last week), or from the next 7 days (beginning today and going into future... like if you make a list of goods that should be delivered during next week).
My query is for the last 7 days. It is different from the one posted by Peso, because I subtract 7 days from today and Peso adds 7 days to the date stored in a row. Otherwise they are almost identical.
In my opinion, it is better for performance to subtract 7 days from a parameter (here: GETDATE()) once for the whole query, than to add 7 days to every row in the table.
I tested this on a large table in my DB.
SELECT count(*)
FROM table T
WHERE T.date > dateadd(dd,-7,GETDATE())
uses index seek and is immediate, while
SELECT count(*)
FROM table T
WHERE GETDATE() > dateadd(dd,7, T.date)
uses index scan and takes some 3 seconds.
The difference is negligible on small tables, but if you want to write well-performing SQL, this is one of the things that can be helpful.
November 27, 2008 at 10:03 am
Vladan (11/27/2008)
rbarryyoung (11/25/2008)
I think that Vladan's "-7" should probably be "+7", but I am not sureThat depends whether you need data from the last 7 days (like if you need to find what projects were finished last week), or from the next 7 days (beginning today and going into future... like if you make a list of goods that should be delivered during next week).
My query is for the last 7 days.
Right, that is what I assumed that you intended, and if you look at the OP's last follow-up post, they do say "what i'm trying to do is return a list of store hours that exist in the database that are no more than 7 days from today inclusive." So I would think that you really wanted +7 instead.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 28, 2008 at 12:52 am
OK, thanks :-). I'm not a native speaker, so I didn't catch that... for some reason I thought that previous 7 days are required. BTW, what does "store hours" mean? Originally I supposed it has to do with storage (how long something is stored in a warehouse), now it seems more like it is the time when a store (e.g. supermarket) will be open.
November 28, 2008 at 1:20 am
Yes, the latter is correct.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy