my sql query for last 7 days data?if data not there then even fetch date and shows with 0 or null

Question

i have made one demo Enity in sql Fidldle. my table have following collumn

displayId | displaytime | campaignId

now what i want is to select the last 7 days entry for the particular campaignId (i mean where campaignId="") there should be multiple entry for the same date with same campaign id..so i want to show sum of total campaignId with their date

so if there is 7 records for date 2013-3-8 and other date then it shows me record like

2013-03-02 | 1
2013-03-03 | 1
2013-03-04 | 0
2013-03-05 | 0
2013-03-06 | 0
2013-03-07 | 2
2013-03-08 | 7

in following query its just shows the date with their count which have more then 0 count... e.g. what i have tried is as follows..but it just give me one record which have entry in database...

 2013-03-08 | 7


SELECT date(a.displayTime) AS `DisplayTime`,
       ifnull(l.TCount,0) AS TCount
FROM a_ad_display AS a
INNER JOIN
  (SELECT count(campaignId) AS TCount,
          displayTime
   FROM a_ad_display
   WHERE CONVERT_TZ(displaytime,'+00:00','-11:00') >= DATE_SUB(CONVERT_TZ(CURDATE(),'+00:00','-11:00') ,INTERVAL 1 DAY)
     AND CONVERT_TZ(displaytime,'+00:00','-11:00') <= CONVERT_TZ(CURDATE(),'+00:00','-11:00')
     AND campaignId = 20747
   GROUP BY DATE(displayTime)) AS l ON date(a.displayTime) = date(l.displayTime)
GROUP BY DATE(a.displayTime)
ORDER BY a.displaytime DESC LIMIT 7

i have implemented time zone in my query so if u can help me with the simple query then its ok..dont include Convert_Tz line.

this is http://sqlfiddle.com/#!2/96600c/1 link of my dummy entitiy

lc. · Accepted Answer · 2013-03-08T11:28:06.500

4

If you use a dates table, your query can be as simple as:

select dates.fulldate, count(a_ad_display.id)
from dates
left outer join a_ad_display ON dates.fulldate = a_ad_display.displaytime
    [AND <other conditions here>]
where dates.fulldate BETWEEN date_add(curdate(), interval -6 day) AND curdate()
group by dates.fulldate

http://sqlfiddle.com/#!2/51e17/3

Without using a dates table, here's one way. It's ugly and probably terrible for performance:

select displaytime, sum(c)
from
(
  select displaytime, count(a_ad_display.id) AS c
  from a_ad_display
  where displaytime BETWEEN date_add(curdate(), interval -6 day) AND curdate()
  group by displaytime
  union all
  select curdate(), 0
  union all
  select date_add(curdate(), interval -1 day), 0
  union all
  select date_add(curdate(), interval -2 day), 0
  union all
  select date_add(curdate(), interval -3 day), 0
  union all
  select date_add(curdate(), interval -4 day), 0
  union all
  select date_add(curdate(), interval -5 day), 0
  union all
  select date_add(curdate(), interval -6 day), 0
) x
group by displaytime
order by displaytime

http://sqlfiddle.com/#!2/96600c/16

edited Mar 08 '13 at 11:28

answered Mar 08 '13 at 08:16

lc.

113,939
20
158
187

yes your query does work.but when i put second condition like where campaignId= then its not showing date with 0 count – BhavikKama Mar 08 '13 at 08:52
@BhavikKama You have to put the condition in the join condition, not in the outer query. – lc. Mar 08 '13 at 08:59
@BhavikKama I guest that you have tried "and campaignId = 10707". Because it is left join you should and add null checking to where cause "( campaignId = 10707 or campaignId is null)". GL! – www Mar 08 '13 at 09:00
can u please tell me where i have to put condition in join..? – BhavikKama Mar 08 '13 at 09:18
@BhavikKama Check the fiddle, you'll see how I added a dates table. – lc. Mar 08 '13 at 09:30
yes i did tried that but its not working in mysql..and m quite new in this – BhavikKama Mar 08 '13 at 09:32
@BhavikKama Edited again. It's not pretty though. You might honestly just want to do the whole darn thing in the front end instead. – lc. Mar 08 '13 at 11:29

score 0 · Answer 2 · answered Mar 03 '20 at 12:18

Simple!You can done by PHP. First create date array like

date = date('Y-m-d'); $date1 = date('Y-m-d', strtotime('-1 days')); $date2 = date('Y-m-d', strtotime('-2 days'));$date3 = date('Y-m-d', strtotime('-3 days'));$date4 = date('Y-m-d', strtotime('-4 days'));$date5 = date('Y-m-d', strtotime('-5 days'));$date6 = date('Y-m-d', strtotime('-6 days')); 
                $dt_arr = array($date6,$date5,$date4,$date3,$date2,$date1,$date);

for last 7 days from today then use foreach and run query with where date field inside foreach like

 foreach ($dt_arr as $value) 
    {
     $qry = mysqli_fetch_array(mysqli_query($link,"select * from table where dt_field = $value"));
       if($qry['value'] == '')
         {
           $valfield = 0;
          $dtfiled = $value;

     }

my sql query for last 7 days data?if data not there then even fetch date and shows with 0 or null

2 Answers2