June 29, 2018 at 12:21 am
Hello,
I have a query with the following fields. The fields are:
Date of visit Team Hour Slot Time of visit count of visits
2018-01-02 South 2 09:02 3
2018-01-04 North 5 14:22 5
I am trying to display the number of visits per hour on any given day in a month.
I would like to create a chart in report builder. On my y axis i would like the hour slots going from 0 to 23 and then on the x axis i would like the days going from day 1 to day 30. How can i create those numbers regardless of the query on both axises.
Thanks
June 29, 2018 at 3:45 am
I'm a little confused by your data, as it stands. For example, for South you have the hour slot as 2, the count of visits as 3, but the time as 09:02. Is this saying there were 3 visits at 09:02? What about between 09:03 and 10:00?
Date of visit Team Hour Slot Time of visit count of visits
2018-01-02 South 2 09:02 3
2018-01-04 North 5 14:22 5
Can you provide some move sample data here, and show what you're expecting to get? Please format your sample data, like I have done above, as well. Simply pasting your data without putting it in IF Markup makes it very difficult/impossible to read.
Thanks.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 29, 2018 at 5:32 am
Hello sorry for being vague. I have a better snapshot of the data.
I am trying to create a chart to show where the number of visits by the hour of the day.
My hourslot field is the actual hour
I would like to have the y axis of the chart to show the hour and then on the x axis I would like date of the visit in a month.
On any given date the hour slot should be summed.
DateOfvisit Team HourSlot TimeOfVisit Visits
2018-05-11 South 0 00:00 1
2018-05-15 South 0 00:00 1
2018-05-18 South 0 00:00 1
2018-05-27 South 0 00:00 1
2018-05-28 South 0 00:00 1
2018-05-19 South 0 00:00 1
2018-05-18 South 0 00:10 1
2018-05-28 South 1 01:10 1
2018-05-15 South 1 01:15 1
2018-05-07 South 1 01:20 1
2018-05-10 South 1 01:20 1
2018-05-28 South 1 01:21 1
2018-05-23 South 3 03:10 2
2018-05-24 South 3 03:10 1
2018-05-14 South 3 03:20 1
2018-05-29 South 3 03:20 1
2018-05-10 South 3 03:26 1
June 29, 2018 at 5:38 am
DateOfvisit Team HourSlot TimeOfVisit Visits
2018-05-11 South 0 00:00 1
2018-05-15 South 0 00:00 1
2018-05-18 South 0 00:00 1
2018-05-27 South 0 00:00 1
2018-05-28 South 0 00:00 1
2018-05-19 South 0 00:00 1
2018-05-18 South 0 00:10 1
2018-05-28 South 1 01:10 1
2018-05-15 South 1 01:15 1
2018-05-07 South 1 01:20 1
2018-05-10 South 1 01:20 1
2018-05-28 South 1 01:21 1
2018-05-23 South 3 03:10 2
2018-05-24 South 3 03:10 1
2018-05-14 South 3 03:20 1
2018-05-29 South 3 03:20 1
2018-05-10 South 3 03:26 1
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 29, 2018 at 5:48 am
Personally, I would aggregate your data using T-SQL. Then, you can build a chart of that. I've created an example for you below on how to get the aggregation for your data. You should be able to create a chart from this, as that data is already aggregated. If you're struggling, show you you've tried. A chart for data like this is one of the basics of SSRS, so you'll do better by doing and researching yourself, rather than having someone do the work for you:
/* White space formatting removal, as always, courtesy of SSC's text editor... */
CREATE TABLE #sample (DateOfVisit date,
Team varchar(5),
HourSlot tinyint,
TimeOfVisit time(0),
Visits tinyint);
INSERT INTO #sample (DateOfVisit,
Team,
HourSlot,
TimeOfVisit,
Visits)
VALUES ('20180511','South',0,'00:00',1),
('20180515','South',0,'00:00',1),
('20180518','South',0,'00:00',1),
('20180527','South',0,'00:00',1),
('20180528','South',0,'00:00',1),
('20180519','South',0,'00:00',1),
('20180518','South',0,'00:10',1),
('20180528','South',1,'01:10',1),
('20180515','South',1,'01:15',1),
('20180507','South',1,'01:20',1),
('20180510','South',1,'01:20',1),
('20180528','South',1,'01:21',1),
('20180523','South',3,'03:10',2),
('20180524','South',3,'03:10',1),
('20180514','South',3,'03:20',1),
('20180529','South',3,'03:20',1),
('20180510','South',3,'03:26',1);
GO
SELECT *
FROM #sample;
SELECT --Team, --not sure this is needed, so commented out
DateOfVisit,
CONVERT(time(0),DATEADD(HOUR, DATEDIFF(HOUR,'00:00',TimeOfVisit),'00:00')) AS HourOfVisit,
SUM(Visits) AS TotalVisits
FROM #sample
GROUP BY --Team, --not sure this is needed, so commented out,
DateOfVisit,
DATEADD(HOUR, DATEDIFF(HOUR,'00:00',TimeOfVisit),'00:00')
ORDER BY DateOfVisit,
HourOfVisit;
GO
DROP TABLE #sample;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply