2

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

Basically a period runs for two weeks and has been running since 10th September 2012. Given a date range for sales, I need to identify the closes period start date, for each sales day.

Withdalot
  • 109
  • 2
  • 3
  • 10
  • please tag what is your DBMS? – Luis LL Sep 08 '13 at 11:01
  • Thanks, what I asked for, was to add the tag MyMSQL just accept my edditing. – Luis LL Sep 08 '13 at 11:04
  • I was looking at a date diff calculation but I just can see how build the whole query. Am I best to count the days between the salesdate and the previous period start date and divide by 14? Which would possibly give me a date I can round back to the previous Monday... – Withdalot Sep 08 '13 at 11:08
  • Its a little confusing, would it be possible for you to indicate expected values – skv Sep 08 '13 at 11:13
  • So effectively that prevperiod is containing values from the next period? – skv Sep 08 '13 at 11:18
  • the previous period is only a reference date. I thought I could use this to somehow identify the 14 day blocks backwards to find the staring date for the period the sales fall in. Sorry this is very confusing... – Withdalot Sep 08 '13 at 11:26
  • Basically a period runs for two weeks and has been running since 10th September 2012. Given a date range for sales, I need to identify the closes period start date, for each sales day. – Withdalot Sep 08 '13 at 12:14
  • Can you clarify your DBMS please. Either "its a MySQL DB" (your comment) or it is "sql-server-2008" (your tag). It can't be both. – Anthony Faull Sep 08 '13 at 12:48

2 Answers2

1

Basically what you have to do is to

  • Solution 1: Calculate whole weeks from '2012-09-10' and then add it to '2012-09-10'
  • Solution 2: Calculate remainder when calculating whole weeks from '2012-09-10' and then substract it from salesdate:

-

select
    emp, salesdate, sales, prevperiod, item,
    dateadd(day, -datediff(day, '20120910', salesdate) % 14, salesdate) as salesdateperiodstart1,
    dateadd(day, (datediff(day, '20120910', salesdate) / 14) * 14, '20120910') as salesdateperiodstart2
from Table1

=> sql fiddle demo

Roman Pekar
  • 107,110
  • 28
  • 195
  • 197
  • Thanks Roman for your suggestion but the period is every two weeks and dates getting returned don't match the requirement. No sure if a MOD may help with this. A salesdate of 2013-07-28 should return a period date of 2013-07-15; and a salesdate of 2013-07-29 should return a period date of 2013-07-29... – Withdalot Sep 08 '13 at 12:59
  • This seems to be populating the period date of '2013-08-05' for salesdates of the 2013-08-05. This should still be in the 2013-07-29 period. – Withdalot Sep 08 '13 at 13:13
  • Thank you so much Roman. This is working correctly when the values are changed to 14. Much appreciated. – Withdalot Sep 08 '13 at 13:19
0

Your start date is Monday, September 10, 2012, and your periods run for fourteen days. So, the next period starts Monday, September 24, 2012, and so forth.

The following little formula will take any date (or datetime) and convert it -- we could say truncate it -- to the first date of the reporting period in which it lies.

 SELECT FROM_DAYS(TO_DAYS(thedate) -MOD(TO_DAYS(thedate) -9, 14))

This is a little obscure and nasty, but it is based on How to group by week in MySQL? , and is extensively tested for such things as ends-of-year rollovers.

Here's a little query in a sql-fiddle that will summarize sales by reporting period start date. http://sqlfiddle.com/#!2/5193c/1/0

I'm not sure what you need prevperiod for. But you don't need it for this computation.

Community
  • 1
  • 1
O. Jones
  • 103,626
  • 17
  • 118
  • 172