Convert 2 week period to weekly period

  • Thread starter Thread starter Lucky
  • Start date Start date
L

Lucky

I need to convert Period dates to Weekly dates. The
Period is a 2-week period like this:
Period 1 starts on 1/6/03 and ends 1/19/03,
Period 2 starts on 1/20/03 and ends 2/2/03, and so
on.

Now I need to convert this into weekly dates like this:
Week 1 starts on 1/6/03 and ends 1/12/03,
Week 2 starts on 1/13/03 and ends 1/19/03,
Week 3 starts on 1/20/03 and ends 1/26/03,
Week 4 starts on 1/27/03 and ends 2/2/03, and so (I need
to create a complete year, 52 weeks).

First I thought about creating two queries, filter the
Period by odd and even number, adjust the date with
DateAdd, and consolidate the two queries in a union
query. But I do not know how to create filter to get
only even or odd number or even if it will work.
And it seems like a too many hoops to jump through and
there must be more elegant, simpler way to do this.

Any suggestions are greatly appreciated.

Lucky
 
Is this in a table? If so, what is the structure? If it is something
like Desc, StartDate, EndDate, then you could do something like:

SELECT "Week " + (SELECT COUNT(*)
FROM yourTable
WHERE StartDate < T.StartDate) * 2 +
1 as Desc
, StartDate
, DateAdd("d", 6, StartDate) as EndDate
FROM YourTable T
UNION ALL
SELECT "Week " + (SELECT COUNT(*)
FROM yourTable
WHERE EndDate < T.EndDate) * 2 + 2 as
Desc
, DateAdd("d", -6, EndtDate) as StartDate
, EndDate
FROM YourTable T


--
HTH

Dale Fye


I need to convert Period dates to Weekly dates. The
Period is a 2-week period like this:
Period 1 starts on 1/6/03 and ends 1/19/03,
Period 2 starts on 1/20/03 and ends 2/2/03, and so
on.

Now I need to convert this into weekly dates like this:
Week 1 starts on 1/6/03 and ends 1/12/03,
Week 2 starts on 1/13/03 and ends 1/19/03,
Week 3 starts on 1/20/03 and ends 1/26/03,
Week 4 starts on 1/27/03 and ends 2/2/03, and so (I need
to create a complete year, 52 weeks).

First I thought about creating two queries, filter the
Period by odd and even number, adjust the date with
DateAdd, and consolidate the two queries in a union
query. But I do not know how to create filter to get
only even or odd number or even if it will work.
And it seems like a too many hoops to jump through and
there must be more elegant, simpler way to do this.

Any suggestions are greatly appreciated.

Lucky
 
Back
Top