2

Working with the following sql tables:

table: fiscal 

DateID   | date                  | fiscal_year | fiscal_week 
20170101   2017-01-01 00:00:00.0   2017          2017 WK 01
20170102   2017-01-02 00:00:00.0   2017          2017 WK 01

table: email_info

email_id | email_name    | email_subjectline
123        New_Year_2017   Welcome the new year!
345        Reminder        Don't forget

table: sent_info

email_id | sent_date
123      | 1/1/2017 8:58:39 PM
345      | 1/2/2017 6:33:39 AM

table: click_info

recipient | email_id | click_date
XYZ         123         1/7/2017 4:25:27 PM
ABC         123         1/5/2017 3:13:56 AM
CDF         345         1/6/2017 2:20:16 AM
ABC         345         1/14/2017 3:33:25 AM

Obviously there are many rows in each table.

The joining between the email tables is straightforward. 

SELECT * 
FROM email_info
JOIN sent_info
ON sent_info.email_id = email_info.email_id
JOIN click_info
ON click_info.email_id = email_info.email_id

I am struggling with the following:

  • how to get all dates into the same format? ( I don't need the times, only the day)
  • how to join the fiscal table so I can filter by fiscal week for example
  • how to count all clicks for an email for 7 days after the sent date (this cannot be hard-coded by dates, but must be dynamic)

This is the output I am looking for (filtered by fiscal week = 2017 WK 01): 

email_id | email_name    | sent_date |  fiscal_week | Clicks
123        New_year_2017   1/1/2017     2017 WK 01     2
345        Reminder        1/2/2017     2017 WK 01     1

*Please note that the last click in the click_info table example was not counted, because it was beyond the 7 days after sent date.

** DateID is an integer and sent_date and click_date are strings/varchar

jeangelj
  • 4,338
  • 16
  • 54
  • 98

1 Answers1

2

assuming that dateId is varchar and the others are datetime should be 

select a.email_id, a.email_name, date(b.sent_date), c.fiscal_week, count(d.click_date)
from email_info a 
inner join fiscal c on  str_to_date(c.dateID, '%Y%m%d') = date(b.sent_date)
inner join  sent_info b on b.email_id = c.email_id
inner join click_info d on d.email_id = b.email_id 
    and date(d.click_date) between date(b.sent_date) and DATEADD(week,1,date(b.sent_date))
group by  a.email_id, a.email_name, date(b.sent_date), c.fiscal_week

PS do the fact the query struct is defined if you have other format you can convert properly and change the single piece

ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
  • unfortunately, the other dates are not datetime, they are strings/varchar – jeangelj Sep 13 '17 at 18:30
  • then you can simply convert the string into the datetime using str_to_date and for the integer you can simply cast as a string ... should not is difficult .. the structure of the query is already build so you can only the single piece .. – ScaisEdge Sep 13 '17 at 18:32
  • I am very new to cast, would you mind showing me, how you would do that? Thank you very much for your help – jeangelj Sep 13 '17 at 18:44
  • cast int to string https://stackoverflow.com/questions/15368753/cast-int-to-varchar and str_to_date https://stackoverflow.com/questions/15396058/how-to-convert-varchar-to-datetime-format-in-mysql – ScaisEdge Sep 13 '17 at 18:49
  • how do get all dates into the same format? ( I don't need the times, only the day) DATE({0}) – Andrey Belykh Sep 13 '17 at 18:50
  • @AndreyBelykh you are not the OP owner .. what's happen ? .. if you have a question post your question coorectly document as new post – ScaisEdge Sep 13 '17 at 18:52
  • Thank you very much @scaisEdge - testing it now – jeangelj Sep 13 '17 at 18:57