Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

dateAdd inside where clause Expand / Collapse
Author
Message
Posted Monday, November 24, 2008 9:10 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 2, 2012 4:26 PM
Points: 14, 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
Post #608017
Posted Monday, November 24, 2008 9:32 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
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."
Post #608023
Posted Monday, November 24, 2008 11:29 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 2, 2012 4:26 PM
Points: 14, 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



Post #608089
Posted Tuesday, November 25, 2008 1:08 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, May 5, 2011 1:38 AM
Points: 1,636, Visits: 604
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.



Post #608135
Posted Tuesday, November 25, 2008 2:24 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 6:36 AM
Points: 2,397, Visits: 3,405
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"
Post #608160
Posted Tuesday, November 25, 2008 8:51 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
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."
Post #608424
Posted Tuesday, November 25, 2008 12:37 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 2, 2012 4:26 PM
Points: 14, 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
Post #608628
Posted Thursday, November 27, 2008 5:39 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, May 5, 2011 1:38 AM
Points: 1,636, Visits: 604
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.



Post #609724
Posted Thursday, November 27, 2008 10:03 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
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."
Post #609900
Posted Friday, November 28, 2008 12:52 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, May 5, 2011 1:38 AM
Points: 1,636, Visits: 604
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.



Post #610130
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse