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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy