How to create Chart Data using SQL query

Question

I'm trying to create a visualized chart using Jquery Highcharts plug-in.

But I'm unable to write a Query to create data which is needed to insert the JS.

What I need to do is to show the customer, how many unique visitors clicked his Job Ad. You can see the final output what I'm trying to do;

enter image description here

And this is a part of 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 the 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 multiple Count where job_statUnique=1 AND jobstat_Date is between now AND 7 days earlier from now in order to insert the data to 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 help.

Can you give us the sample data you would like returned by your query? — Abe Miessler
– Abe Miessler, Commented May 8, 2011 at 15:07
@Abe Miessler :I have added the expected output to the question. — Burak F. Kilicaslan
– Burak F. Kilicaslan, Commented May 8, 2011 at 15:32

Burak F. Kilicaslan · Accepted Answer · 2011-05-09 20:11:25Z

4

In case someone needs an answer, here is how I solved it;

  ;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

Note: job_stat_Date must be Date not DateTime

edited May 9, 2011 at 20:11

answered May 9, 2011 at 13:50

Burak F. Kilicaslan

5352 gold badges8 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dsw Wds Over a year ago

can u help on link

Abe Miessler · Accepted Answer · 2011-05-08 15:16:50Z

0

I am a little unclear on what you want the data to look like, but try something like this:

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

if you just want the counts you can drop the JobID from the select:

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

edited May 8, 2011 at 15:16

answered May 8, 2011 at 15:10

Abe Miessler

85.6k104 gold badges323 silver badges496 bronze badges

2 Comments

Burak F. Kilicaslan Over a year ago

Your query doesn't return the expected output I mentioned later on my question. Instead, it returns nothing actually. I changed where statement to this WHERE job_stat_Date >= DATEADD(d, -7, GETDATE()) AND job_stat_Date < GETDATE() but still no luck.

Abe Miessler Over a year ago

Is jobstat_Date a datetime column? If not, you will need to convert it for the comparison.

Collectives™ on Stack Overflow

How to create Chart Data using SQL query

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related