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