SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


dateAdd inside where clause


dateAdd inside where clause

Author
Message
LowFatSpread
LowFatSpread
SSC-Enthusiastic
SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)

Group: General Forum Members
Points: 180 Visits: 39
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'




store 0001 2008-11-26 00:00:00.000 9:00a 5:30p No Special Events
store 0001 2008-11-27 00:00:00.000 9:00a 9:00p No Special Events
store 0001 2008-11-28 00:00:00.000 9:00a 5:30p No Special Events
store 0001 2008-11-29 00:00:00.000 9:00a 5:30p No Special Events
RBarryYoung
RBarryYoung
SSC Guru
SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)

Group: General Forum Members
Points: 55144 Visits: 9518
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.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
LowFatSpread
LowFatSpread
SSC-Enthusiastic
SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)

Group: General Forum Members
Points: 180 Visits: 39
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


4902 2008-11-25 00:00:00.000 10:00a 5:30p comments
4902 2008-11-26 00:00:00.000 10:00a 5:30p comments
4902 2008-11-27 00:00:00.000 10:00a 5:30p comments
4902 2008-11-28 00:00:00.000 10:00a 5:30p comments

4903 2008-11-25 00:00:00.000 10:00a 5:30p comments
4903 2008-11-26 00:00:00.000 10:00a 5:30p comments
4903 2008-11-27 00:00:00.000 10:00a 5:30p comments
4903 2008-11-28 00:00:00.000 10:00a 5:30p comments


!------------------------- 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


4903 storeone
4903 storetwo
Vladan
Vladan
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8226 Visits: 762
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.



SwePeso
SwePeso
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15167 Visits: 3433
SELECT s.StoreName,
s.StoreID,
h.StoreDate,
h.OpenTime,
h.ClosingTime,
h.Comments
from schema.mytable2 as s
left JOIN schema.mytable1 as h on h.[StoreID] = s.[StoreID]
AND h.[StoreDate] between GetDate() and DateAdd(dd, 7, h.StoreDate)
WHERE s.StoreName = 'store'


N 56°04'39.16"
E 12°55'05.25"
RBarryYoung
RBarryYoung
SSC Guru
SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)

Group: General Forum Members
Points: 55144 Visits: 9518
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.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
LowFatSpread
LowFatSpread
SSC-Enthusiastic
SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)

Group: General Forum Members
Points: 180 Visits: 39
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
Vladan
Vladan
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8226 Visits: 762
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.



RBarryYoung
RBarryYoung
SSC Guru
SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)

Group: General Forum Members
Points: 55144 Visits: 9518
Vladan (11/27/2008)
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.

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.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Vladan
Vladan
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8226 Visits: 762
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.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search