-3

Trying to create a column that will tell me the weekofyear() as well as the dates:

Week  Date                       org   cost
--------------------------------------------
50    "2016/12/12-2016/12/18"    1     400
51    "2016/12/19-2016/12/25"    1     400
52    "2016/12/26"               1     400

So this tells me that Week 52 only includes data from the December 26th and not for the whole week.

here is what my query looks like:

SELECT 
    weekofyear(date) as week_number,
    exchange,
    country,
    channel,
    sum (media_cost) as mc 
FROM 
    imps
WHERE 
    date BETWEEN '2016-12-26' AND '2017-01-08'
GROUP BY
    weekofyear(date) as week_number,
    exchange,
    country,
    channel;

I have looked at How do you get the “week start date” and “week end date” from week number in SQL Server? but this won't solve the issue of seeing what days it does include. Would using bins work for this maybe? What are your suggestions.

Community
  • 1
  • 1
LMP
  • 79
  • 1
  • 1
  • 9
  • 3
    It's very hard to understand a question with so little information. Please provide some sample data and the result you'd like to get for that sample. – Mureinik Jan 05 '17 at 21:47
  • 1
    See http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query – Strawberry Jan 05 '17 at 21:49
  • Really don't know what you are asking for, but the strtotime function in PHP can convert a week number into a date like this: `date("Y-m-d", strtotime("2017W01"));`. That will give you the first date in the first week of 2017. – CptMisery Jan 05 '17 at 21:54
  • @CptMisery tried to make the question more clear – LMP Jan 09 '17 at 18:07
  • @Mureinik have added more data and sample result – LMP Jan 09 '17 at 18:14

1 Answers1

0

In your query, that WHERE will not work because the string "2016/12/12-2016/12/18" will never be between two dates. You would need to make two date columns such as week_begin and week_end. Then your WHERE could be:

WHERE week_begin BETWEEN '2016-12-26' AND '2017-01-08'
   OR week_end BETWEEN '2016-12-26' AND '2017-01-08'
CptMisery
  • 612
  • 4
  • 15