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

query to get dates for a number of weeks Expand / Collapse
Author
Message
Posted Saturday, September 29, 2012 12:42 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 3:55 PM
Points: 22, Visits: 593
I 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 union
select 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/2012

Post #1366210
Posted Saturday, September 29, 2012 4:21 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:27 PM
Points: 1,945, Visits: 3,068
>> 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_ordering
CHECK (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.html


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1366218
Posted Saturday, September 29, 2012 4:54 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 3:55 PM
Points: 22, Visits: 593
That'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?
Post #1366219
Posted Saturday, September 29, 2012 6:33 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:27 PM
Points: 1,945, Visits: 3,068
I suppose at this point it's academic: what would a possible query look like?


SELECT cal_date
FROM Calendar
WHERE cal_date BETWEEN @in_start_date AND @in_end_date


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1366224
Posted Saturday, September 29, 2012 6:38 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 3:55 PM
Points: 22, Visits: 593
how about a query without setting up all that extra stuff... using only my original ddl and sample data.
Post #1366225
Posted Saturday, September 29, 2012 10:52 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:27 PM
Points: 20,734, Visits: 32,505
Something like this perhaps?


--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 union
select 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/2012

with cteTally(n) as (
select top (select max(d1.Weeks) from Dates d1)
row_number() over (order by (select null)) as n
from
sys.all_columns a
cross join sys.all_columns b
)
select
d.UserID,
dateadd(wk, t.n, d.InDate) OutDates
from
Dates d
cross join cteTally t
where
t.n <= d.Weeks
order by
d.UserID,
OutDates
;
go

drop table Dates;
go





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1366229
Posted Sunday, September 30, 2012 11:58 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:27 PM
Points: 1,945, Visits: 3,068
how about a query without setting up all that extra stuff... using only my original DDL and sample data.


Your weak DDL will lead to self-joins and complicated queries while destroying data integrity in the DML. Again, SQL is a data language 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 everyone 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.


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1366283
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse