Sunday, 8 September 2013

Identify the period start date for each day in a range of dates

Identify the period start date for each day in a range of dates

When selecting a range of dates, I need to identify the specific period
start date for each sales date. The periods are every two weeks and all I
know is the start date for the most recent "previous period". I also know
that the two week periods are calculated every two weeks from 10th
September 2012.
For example, give the date range between '2013-07-03' and '2013-09-08':
emp|salesdate |sales |prevperiod |item
123|2013-07-27 |12000 |2013-08-12 |A12
123|2013-07-27 |1800 |2013-08-12 |A17
123|2013-07-28 |400 |2013-08-12 |B03
227|2013-07-27 |1500 |2013-08-12 |A17
How would my SQL work to include the sales date's period start date:
emp|salesdate |sales |prevperiod |item |salesdateperiodstart
123|2013-07-27 |12000 |2013-08-12 |A12 |2013-07-15
123|2013-07-27 |1800 |2013-08-12 |A17 |2013-07-15
123|2013-07-28 |400 |2013-08-12 |B03 |2013-07-15
123|2013-07-29 |400 |2013-08-12 |B03 |2013-07-29
227|2013-07-27 |1500 |2013-08-12 |A17 |2013-07-15
123|2013-07-28 |400 |2013-08-12 |B03 |2013-07-15

No comments:

Post a Comment