InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 01:15:07 GMT20http://www.sqlservercentral.com/Forums/Topic1366210-392-1.aspx[quote] how about a query without setting up all that extra stuff... using only my original DDL and sample data.[/quote]Your weak DDL will lead to self-joins and complicated queries while destroying data integrity in the DML. Again, SQL is a [i]data language[/i] and not a procedural or computational one. The more you can put into the DDL, then you know that it is right, it is consistent and that [i]everyone[/i] who follows you will be protected. Lynn gave you an answer that you can use to avoid being a good SQL programmer, if that is what you really want to do. Start thinking in sets and logic, not records and procedural code.Sun, 30 Sep 2012 11:58:43 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1366210-392-1.aspxSomething like this perhaps?[code="sql"]--DDL and some data:create table Dates (UserID int not null,InDate date not null,Weeks int not null);insert into Dates (UserID,InDate, Weeks)select 1,'8/31/2012',5 unionselect 2,'9/14/2012',2;--Expected results:--UserID,OutDates--1,9/7/2012--1,9/14/2012--1,9/21/2012--1,9/28/2012--1,10/5/2012--2,9/21/2012--2,9/28/2012with cteTally(n) as (select top (select max(d1.Weeks) from Dates d1) row_number() over (order by (select null)) as nfrom sys.all_columns a cross join sys.all_columns b)select d.UserID, dateadd(wk, t.n, d.InDate) OutDatesfrom Dates d cross join cteTally twhere t.n <= d.Weeksorder by d.UserID, OutDates;godrop table Dates;go[/code]Sat, 29 Sep 2012 22:52:01 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1366210-392-1.aspxhow about a query without setting up all that extra stuff... using only my original ddl and sample data.Sat, 29 Sep 2012 18:38:00 GMTchrismathttp://www.sqlservercentral.com/Forums/Topic1366210-392-1.aspx[quote] I suppose at this point it's academic: what would a possible query look like?[/quote]SELECT cal_date FROM Calendar WHERE cal_date BETWEEN @in_start_date AND @in_end_dateSat, 29 Sep 2012 18:33:43 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1366210-392-1.aspxThat's a lot of great info Celko, but I feel it's way overthought for my situation. This isn't for a business nor do standards or performance matter. I suppose at this point it's academic: what would a possible query look like?Sat, 29 Sep 2012 16:54:54 GMTchrismathttp://www.sqlservercentral.com/Forums/Topic1366210-392-1.aspx>> I need a query that will produce rows of dates given a starting date and incrementing that date for a number of weeks <<You got the date display format wrong; ANSI/ISO Standards use the ISO-8601 format (yyyy-mm-dd). Build a calendar table with one column for the calendar date and other columns to show whatever your business needs in the way of temporal information. Do not try to calculate holidays in SQL -- Easter alone requires too much math. CREATE TABLE Calendar(cal_date DATE NOT NULL PRIMARY KEY, fiscal_year SMALLINT NOT NULL, fiscal_month SMALLINT NOT NULL, week_in_year SMALLINT NOT NULL, -- SQL Server is not ISO standard SMALLINT NOT NULL, -- day_in_year julian_business_day INTEGER NOT NULL, ...);Since SQL is a database language, we prefer to do look ups and not calculations. They can be optimized while temporal math messes up optimization. A useful idiom is a report period calendar that everyone uses so there is no way to get disagreements in the DML. The report period table gives a name to a range of dates that is common to the entire enterprise. CREATE TABLE Report_Periods(report_name VARCHAR(30) NOT NULL PRIMARY KEY, report_start_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL, report_end_date DATE NOT NULL,CONSTRAINT date_orderingCHECK (report_start_date <= report_end_date),etc);These report periods can overlap or have gaps. I like the MySQL convention of using double zeroes for months and years, That is 'yyyy-mm-00' for a month within a year and 'yyyy-00-00' for the whole year. The advantage is that it will sort with the ISO-8601 data format required by Standard SQL. Weeks within Year: I would build a calendar table with a ISO-8601 week-within-year format column in addition to the usual Common Era date. This format is 'yyyyWww-d' where yyyy is the year, W is as separator token, ww is (01-53) week number and d is (1-7) day of the week.You input any calendar date, find the week-within-year column and return the dates that match on a LIKE predicate.WHERE sale_day LIKE '2012W26-[67]'There are several websites with calendars you can cut & paste, but you can start your search with: http://www.calendar-365.com/week-number.htmlSat, 29 Sep 2012 16:21:58 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1366210-392-1.aspxI need a query that will produce rows of dates given a starting date and incrementing that date for a number of weeks (also given on the same source date row).DDL and some data:create table Dates (UserID int not null,InDate date not null,Weeks int not null);insert into Dates (UserID,InDate, Weeks)select 1,'8/31/2012',5 unionselect 2,'9/14/2012',2;Expected results:UserID,OutDates1,9/7/20121,9/14/20121,9/21/20121,9/28/20121,10/5/20122,9/21/20122,9/28/2012Sat, 29 Sep 2012 12:42:49 GMTchrismat