How to create chart data using SQL query

Question

I am trying to create a visualized chart using the Jquery Highcharts plugin .

But I can not write a query to create the data needed to insert JS.

What I need to do is show the client how many unique visitors clicked on his job ad. You can see the end result, what I'm trying to do,

And this is part of the required JS;

xAxis: {
   categories: ['01.05', '02.05', '03.05', '04.05', '05.05',
                '06.05', '07.05','08.05']
   },
series: [{
            name: 'Unique',
            data: [12,8,9,10,5,4,11,30]
        }]

Table with statistics;

Job_Ad_Statistics
-----------------
jobID,  jobstat_Date,     job_statUnique
1       07.05.2011        0
1       07.05.2011        1
1       07.05.2011        1
2       06.05.2011        1
3       06.05.2011        1
1       05.05.2011        1
1       04.05.2011        1
*Currently, table has 20k rows.

I need to get a few Count, where job_statUnique = 1 And jobstat_Date is between now And 7 days earlier to insert data into the Series field.

Expected Result:
----------------
Day              Unique_ViewCount_of_jobID (Assuming ID 1)
.... start from 7 days earlier     
02.05            0
03.05            0    
04.05            1
05.05            1
06.05            0
07.05            2 
08.05            0 - today

Thanks for the help.

Answer 1

- , :

  ;WITH Date_Range_T(d_range) AS 
     (
       SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 6, 0) 
       UNION ALL SELECT DATEADD(DAY, 1, d_range) 
       FROM Date_Range_T 
       WHERE DATEADD(DAY, 1, d_range) < GETDATE()
     )

  SELECT d_range, COUNT(job_statID) as total 
  FROM Date_Range_T 
       LEFT JOIN Jobs_Stats on (job_stat_Date=d_range) 
  GROUP BY d_range ORDER BY d_range ASC

: job_stat_Date Date not DateTime

Answer 2

, , , - :

SELECT jobID, COUNT(jobID) 
FROM Job_Ad_Statistics
WHERE jobstat_Date BETWEEN GETDATE() AND DATEADD(day, -7, GETDATE())
GROUP BY JobID

, JobID :

SELECT COUNT(jobID) 
FROM Job_Ad_Statistics
WHERE jobstat_Date BETWEEN GETDATE() AND DATEADD(day, -7, GETDATE())
GROUP BY JobID