InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 09:01:45 GMT20http://www.sqlservercentral.com/Forums/Topic608017-338-1.aspxYes, the latter is correct.Fri, 28 Nov 2008 01:20:22 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic608017-338-1.aspxOK, 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.Fri, 28 Nov 2008 00:52:25 GMTVladanhttp://www.sqlservercentral.com/Forums/Topic608017-338-1.aspx[quote][b]Vladan (11/27/2008)[/b][hr][quote][b]rbarryyoung (11/25/2008)[/b][hr]I think that Vladan's "-7" should probably be "+7", but I am not sure[/quote]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. [/quote]Right, that is what I assumed that you intended, and if you look at the OP's last follow-up post, they do say "[i]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]" So I would think that you really wanted +7 instead.Thu, 27 Nov 2008 10:03:09 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic608017-338-1.aspx[quote][b]rbarryyoung (11/25/2008)[/b][hr]I think that Vladan's "-7" should probably be "+7", but I am not sure[/quote]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. [code]SELECT count(*)FROM table TWHERE T.date > dateadd(dd,-7,GETDATE())[/code]uses index seek and is immediate, while[code]SELECT count(*)FROM table TWHERE GETDATE() > dateadd(dd,7, T.date)[/code]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.Thu, 27 Nov 2008 05:39:41 GMTVladanhttp://www.sqlservercentral.com/Forums/Topic608017-338-1.aspxHi 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.SeanTue, 25 Nov 2008 12:37:16 GMTLowFatSpreadhttp://www.sqlservercentral.com/Forums/Topic608017-338-1.aspxEither 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.Tue, 25 Nov 2008 08:51:26 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic608017-338-1.aspxSELECT s.StoreName, s.StoreID, h.StoreDate, h.OpenTime, h.ClosingTime, h.Commentsfrom schema.mytable2 as sleft 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'Tue, 25 Nov 2008 02:24:48 GMTSwePesohttp://www.sqlservercentral.com/Forums/Topic608017-338-1.aspxHi,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:[code]SELECT s.StoreName, s.StoreID, h.StoreDate, h.OpenTime, h.ClosingTime, h.Comments FROM stores sJOIN tblhours h ON s.[StoreID] = h.[StoreID]WHERE h.[StoreDate] >= DATEADD(d,-7,GETDATE()) AND h.[StoreDate] <= GETDATE() AND s.StoreName = 'store'[/code]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.Tue, 25 Nov 2008 01:08:09 GMTVladanhttp://www.sqlservercentral.com/Forums/Topic608017-338-1.aspxHI 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!--- tableSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE 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]GOSET ANSI_PADDING OFF!---- sample data 4902 2008-11-25 00:00:00.000 10:00a 5:30p comments4902 2008-11-26 00:00:00.000 10:00a 5:30p comments4902 2008-11-27 00:00:00.000 10:00a 5:30p comments4902 2008-11-28 00:00:00.000 10:00a 5:30p comments4903 2008-11-25 00:00:00.000 10:00a 5:30p comments4903 2008-11-26 00:00:00.000 10:00a 5:30p comments4903 2008-11-27 00:00:00.000 10:00a 5:30p comments4903 2008-11-28 00:00:00.000 10:00a 5:30p comments!------------------------- tableSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE 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]GOSET ANSI_PADDING OFF!--------------------------------- sample data4903 storeone4903 storetwoMon, 24 Nov 2008 23:29:32 GMTLowFatSpreadhttp://www.sqlservercentral.com/Forums/Topic608017-338-1.aspxThis statement:[quote]I want to only the records from the join + 7 days in the result.[/quote] is not intelligible.Please provide sample data and examples of the result set that you want.Mon, 24 Nov 2008 21:32:02 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic608017-338-1.aspxHi 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 sleft 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 Eventsstore 0001 2008-11-27 00:00:00.000 9:00a 9:00p No Special Eventsstore 0001 2008-11-28 00:00:00.000 9:00a 5:30p No Special Eventsstore 0001 2008-11-29 00:00:00.000 9:00a 5:30p No Special EventsMon, 24 Nov 2008 21:10:02 GMTLowFatSpread