April 2, 2012 at 12:59 pm
Hi,
I need some help with the below query.
select
convert(Varchar(10) ,a.Date,101) AS Date,
SUM(d.Ins) as SumofIns,
SUM(d.Outs) as SumofOuts,
d.Period as Hourly_Periods,
c.Name as Location_Name
from Publish a
Join Traffic d on d.PublishID = a.ID
Join Location c on c.ID = d.LocationID
where a.Date <= GETDATE()
and a.Date > GETDATE()-1
group by a.Date ,d.Period,d.Ins, c.Name
order by a.Date Desc
below is the result of the above query.
Date SumofIns SumofOuts Hourly_Periods Location_Name
4/2/2012 0 1 0 Location 5
4/2/2012 0 1 0 Location 8
4/2/2012 1 1 0 Location 1
4/2/2012 1 2 0 Location 6
4/2/2012 2 0 0 Location 4
4/2/2012 3 4 0 Location 9
4/2/2012 4 2 0 Location 7
4/2/2012 6 7 0 Location 2
4/2/2012 6 10 0 Location 3
4/2/2012 7 8 0 Location 11
4/2/2012 10 17 0 Location 13
4/2/2012 11 8 0 Location 10
4/2/2012 11 11 0 Location 12
4/2/2012 0 0 1 Location 4
4/2/2012 0 0 1 Location 5
4/2/2012 0 1 1 Location 7
4/2/2012 0 0 1 Location 8
4/2/2012 0 0 1 Location 9
4/2/2012 0 3 1 Location 12
4/2/2012 1 2 1 Location 2
4/2/2012 1 0 1 Location 6
4/2/2012 2 0 1 Location 1
4/2/2012 2 2 1 Location 10
4/2/2012 5 18 1 Location 13
4/2/2012 9 5 1 Location 11
4/2/2012 10 11 1 Location 3
4/2/2012 0 0 2 Location 4
4/2/2012 0 0 2 Location 5
4/2/2012 0 0 2 Location 7
4/2/2012 0 0 2 Location 8
4/2/2012 0 0 2 Location 9
4/2/2012 1 1 2 Location 6
4/2/2012 1 0 2 Location 12
4/2/2012 3 1 2 Location 1
4/2/2012 3 2 2 Location 10
4/2/2012 3 6 2 Location 11
4/2/2012 8 9 2 Location 13
4/2/2012 10 7 2 Location 2
4/2/2012 12 16 2 Location 3
4/2/2012 0 0 3 Location 4
4/2/2012 0 0 3 Location 5
4/2/2012 0 0 3 Location 6
4/2/2012 0 0 3 Location 7
4/2/2012 0 0 3 Location 8
4/2/2012 0 0 3 Location 9
4/2/2012 0 1 3 Location 10
4/2/2012 3 0 3 Location 12
4/2/2012 6 10 3 Location 2
4/2/2012 6 2 3 Location 13
4/2/2012 7 2 3 Location 1
4/2/2012 11 6 3 Location 11
4/2/2012 13 10 3 Location 3
4/2/2012 0 0 4 Location 1
4/2/2012 0 0 4 Location 5
4/2/2012 0 0 4 Location 6
4/2/2012 0 0 4 Location 7
4/2/2012 0 0 4 Location 8
4/2/2012 0 1 4 Location 9
4/2/2012 1 0 4 Location 10
4/2/2012 2 0 4 Location 4
4/2/2012 5 5 4 Location 2
4/2/2012 11 6 4 Location 11
4/2/2012 12 4 4 Location 13
4/2/2012 13 10 4 Location 3
4/2/2012 14 4 4 Location 12
4/2/2012 1 5 5 Location 4
4/2/2012 2 3 5 Location 6
4/2/2012 3 11 5 Location 7
4/2/2012 3 1 5 Location 8
4/2/2012 4 5 5 Location 5
4/2/2012 6 4 5 Location 1
4/2/2012 11 9 5 Location 2
4/2/2012 19 21 5 Location 9
4/2/2012 19 11 5 Location 10
4/2/2012 21 20 5 Location 3
4/2/2012 32 23 5 Location 12
4/2/2012 46 43 5 Location 11
4/2/2012 78 37 5 Location 13
4/2/2012 0 0 6 Location 8
4/2/2012 1 0 6 Location 6
4/2/2012 4 5 6 Location 1
4/2/2012 5 1 6 Location 7
4/2/2012 6 0 6 Location 5
4/2/2012 9 0 6 Location 4
4/2/2012 24 19 6 Location 2
4/2/2012 25 13 6 Location 10
4/2/2012 30 26 6 Location 9
4/2/2012 39 52 6 Location 3
4/2/2012 47 29 6 Location 11
4/2/2012 56 42 6 Location 12
4/2/2012 107 59 6 Location 13
4/2/2012 0 1 7 Location 8
4/2/2012 1 4 7 Location 4
4/2/2012 3 5 7 Location 5
4/2/2012 7 14 7 Location 9
4/2/2012 8 9 7 Location 6
4/2/2012 9 11 7 Location 7
4/2/2012 25 24 7 Location 10
4/2/2012 27 12 7 Location 12
4/2/2012 59 14 7 Location 1
4/2/2012 88 26 7 Location 11
4/2/2012 90 41 7 Location 2
4/2/2012 184 96 7 Location 3
4/2/2012 187 79 7 Location 13
4/2/2012 0 1 8 Location 8
4/2/2012 4 7 8 Location 5
4/2/2012 7 18 8 Location 7
4/2/2012 8 3 8 Location 4
4/2/2012 8 7 8 Location 6
4/2/2012 10 18 8 Location 9
4/2/2012 25 12 8 Location 12
4/2/2012 29 26 8 Location 10
4/2/2012 64 23 8 Location 1
4/2/2012 108 57 8 Location 11
4/2/2012 112 34 8 Location 2
4/2/2012 150 46 8 Location 13
4/2/2012 187 172 8 Location 3
4/2/2012 2 8 9 Location 6
4/2/2012 2 2 9 Location 8
4/2/2012 4 9 9 Location 7
4/2/2012 7 4 9 Location 4
4/2/2012 7 6 9 Location 5
4/2/2012 8 9 9 Location 9
4/2/2012 19 16 9 Location 10
4/2/2012 57 13 9 Location 12
4/2/2012 118 54 9 Location 1
4/2/2012 158 92 9 Location 13
4/2/2012 183 53 9 Location 11
4/2/2012 188 64 9 Location 2
4/2/2012 343 248 9 Location 3
4/2/2012 0 1 10 Location 8
4/2/2012 8 18 10 Location 7
4/2/2012 11 13 10 Location 6
4/2/2012 13 18 10 Location 9
4/2/2012 14 3 10 Location 4
4/2/2012 14 25 10 Location 5
4/2/2012 35 40 10 Location 10
4/2/2012 70 29 10 Location 12
4/2/2012 180 47 10 Location 1
4/2/2012 203 125 10 Location 2
4/2/2012 222 114 10 Location 13
4/2/2012 419 127 10 Location 11
4/2/2012 624 442 10 Location 3
4/2/2012 4 4 11 Location 8
4/2/2012 8 7 11 Location 4
4/2/2012 9 43 11 Location 6
4/2/2012 10 43 11 Location 7
4/2/2012 13 23 11 Location 9
4/2/2012 16 20 11 Location 5
4/2/2012 33 41 11 Location 10
4/2/2012 123 59 11 Location 12
4/2/2012 218 148 11 Location 2
4/2/2012 265 75 11 Location 1
4/2/2012 332 205 11 Location 13
4/2/2012 476 183 11 Location 11
4/2/2012 554 714 11 Location 3
4/2/2012 2 3 12 Location 8
4/2/2012 11 14 12 Location 4
4/2/2012 11 32 12 Location 6
4/2/2012 12 38 12 Location 5
4/2/2012 15 57 12 Location 7
4/2/2012 24 57 12 Location 10
4/2/2012 32 39 12 Location 9
4/2/2012 113 80 12 Location 12
4/2/2012 250 232 12 Location 2
4/2/2012 265 120 12 Location 1
4/2/2012 382 298 12 Location 13
4/2/2012 513 347 12 Location 11
4/2/2012 671 1074 12 Location 3
4/2/2012 2 6 13 Location 8
4/2/2012 12 24 13 Location 4
4/2/2012 17 56 13 Location 6
4/2/2012 20 78 13 Location 7
4/2/2012 25 37 13 Location 5
4/2/2012 35 62 13 Location 9
4/2/2012 45 65 13 Location 10
4/2/2012 132 113 13 Location 1
4/2/2012 282 84 13 Location 12
4/2/2012 340 241 13 Location 2
4/2/2012 385 363 13 Location 13
4/2/2012 456 461 13 Location 11
4/2/2012 753 1256 13 Location 3
4/2/2012 0 0 14 Location 1
4/2/2012 0 0 14 Location 2
4/2/2012 0 0 14 Location 3
4/2/2012 0 0 14 Location 4
4/2/2012 0 0 14 Location 5
4/2/2012 0 0 14 Location 6
4/2/2012 0 0 14 Location 7
4/2/2012 0 0 14 Location 8
4/2/2012 0 0 14 Location 9
4/2/2012 0 0 14 Location 10
4/2/2012 0 0 14 Location 11
4/2/2012 0 0 14 Location 12
4/2/2012 0 0 14 Location 13
4/2/2012 0 0 15 Location 1
4/2/2012 0 0 15 Location 2
4/2/2012 0 0 15 Location 3
4/2/2012 0 0 15 Location 4
4/2/2012 0 0 15 Location 5
4/2/2012 0 0 15 Location 6
4/2/2012 0 0 15 Location 7
4/2/2012 0 0 15 Location 8
4/2/2012 0 0 15 Location 9
4/2/2012 0 0 15 Location 10
4/2/2012 0 0 15 Location 11
4/2/2012 0 0 15 Location 12
4/2/2012 0 0 15 Location 13
4/2/2012 0 0 16 Location 1
4/2/2012 0 0 16 Location 2
4/2/2012 0 0 16 Location 3
4/2/2012 0 0 16 Location 4
4/2/2012 0 0 16 Location 5
4/2/2012 0 0 16 Location 6
4/2/2012 0 0 16 Location 7
4/2/2012 0 0 16 Location 8
4/2/2012 0 0 16 Location 9
4/2/2012 0 0 16 Location 10
4/2/2012 0 0 16 Location 11
4/2/2012 0 0 16 Location 12
4/2/2012 0 0 16 Location 13
4/2/2012 0 0 17 Location 1
4/2/2012 0 0 17 Location 2
4/2/2012 0 0 17 Location 3
4/2/2012 0 0 17 Location 4
4/2/2012 0 0 17 Location 5
4/2/2012 0 0 17 Location 6
4/2/2012 0 0 17 Location 7
4/2/2012 0 0 17 Location 8
4/2/2012 0 0 17 Location 9
4/2/2012 0 0 17 Location 10
4/2/2012 0 0 17 Location 11
4/2/2012 0 0 17 Location 12
4/2/2012 0 0 17 Location 13
4/2/2012 0 0 18 Location 1
4/2/2012 0 0 18 Location 2
4/2/2012 0 0 18 Location 3
4/2/2012 0 0 18 Location 4
4/2/2012 0 0 18 Location 5
4/2/2012 0 0 18 Location 6
4/2/2012 0 0 18 Location 7
4/2/2012 0 0 18 Location 8
4/2/2012 0 0 18 Location 9
4/2/2012 0 0 18 Location 10
4/2/2012 0 0 18 Location 11
4/2/2012 0 0 18 Location 12
4/2/2012 0 0 18 Location 13
4/2/2012 0 0 19 Location 1
4/2/2012 0 0 19 Location 2
4/2/2012 0 0 19 Location 3
4/2/2012 0 0 19 Location 4
4/2/2012 0 0 19 Location 5
4/2/2012 0 0 19 Location 6
4/2/2012 0 0 19 Location 7
4/2/2012 0 0 19 Location 8
4/2/2012 0 0 19 Location 9
4/2/2012 0 0 19 Location 10
4/2/2012 0 0 19 Location 11
4/2/2012 0 0 19 Location 12
4/2/2012 0 0 19 Location 13
4/2/2012 0 0 20 Location 1
4/2/2012 0 0 20 Location 2
4/2/2012 0 0 20 Location 3
4/2/2012 0 0 20 Location 4
4/2/2012 0 0 20 Location 5
4/2/2012 0 0 20 Location 6
4/2/2012 0 0 20 Location 7
4/2/2012 0 0 20 Location 8
4/2/2012 0 0 20 Location 9
4/2/2012 0 0 20 Location 10
4/2/2012 0 0 20 Location 11
4/2/2012 0 0 20 Location 12
4/2/2012 0 0 20 Location 13
4/2/2012 0 0 21 Location 1
4/2/2012 0 0 21 Location 2
4/2/2012 0 0 21 Location 3
4/2/2012 0 0 21 Location 4
4/2/2012 0 0 21 Location 5
4/2/2012 0 0 21 Location 6
4/2/2012 0 0 21 Location 7
4/2/2012 0 0 21 Location 8
4/2/2012 0 0 21 Location 9
4/2/2012 0 0 21 Location 10
4/2/2012 0 0 21 Location 11
4/2/2012 0 0 21 Location 12
4/2/2012 0 0 21 Location 13
4/2/2012 0 0 22 Location 1
4/2/2012 0 0 22 Location 2
4/2/2012 0 0 22 Location 3
4/2/2012 0 0 22 Location 4
4/2/2012 0 0 22 Location 5
4/2/2012 0 0 22 Location 6
4/2/2012 0 0 22 Location 7
4/2/2012 0 0 22 Location 8
4/2/2012 0 0 22 Location 9
4/2/2012 0 0 22 Location 10
4/2/2012 0 0 22 Location 11
4/2/2012 0 0 22 Location 12
4/2/2012 0 0 22 Location 13
4/2/2012 0 0 23 Location 1
4/2/2012 0 0 23 Location 2
4/2/2012 0 0 23 Location 3
4/2/2012 0 0 23 Location 4
4/2/2012 0 0 23 Location 5
4/2/2012 0 0 23 Location 6
4/2/2012 0 0 23 Location 7
4/2/2012 0 0 23 Location 8
4/2/2012 0 0 23 Location 9
4/2/2012 0 0 23 Location 10
4/2/2012 0 0 23 Location 11
4/2/2012 0 0 23 Location 12
4/2/2012 0 0 23 Location 13
Hourly periods are from 0 to 23.
sum of ins is the visitors visited for that particular hour from a particular location. sum of out is the visitors left for that particular hour from a particular location.
I need to calculate sum of visitors from all of the locations for a particular hour.
The result should look like Below.
Date Visitors Visited at 12 a.m Visitors Visited at 1 a.m Visitors Visited at 2 a.m Visitors Visited at 3 a.m Visitors Visited at 4 a.m Visitors Visited at 5 a.m Visitors Visited at 6 a.m Visitors Visited at 7 a.m Visitors Visited at 8 a.m Visitors Visited at 9 a.m Visitors Visited at 10 a.m Visitors Visited at 11 a.m Visitors Visited at 12 p.m Visitors Visited at 1 p.m Visitors Visited at 2 p.m Visitors Visited at 3 p.m Visitors Visited at 4 p.m Visitors Visited at 5 p.m Visitors Visited at 6 p.m Visitors Visited at 7 p.m Visitors Visited at 8 p.m Visitors Visited at 9 p.m Visitors Visited at 10 p.m Visitors Visited at 11 p.m Visitors Left at 12 a.m Visitors Left at 1 a.m Visitors Left at 2 a.m Visitors Left at 3 a.m Visitors Left at 4 a.m Visitors Left at 5 a.m Visitors Left at 6 a.m Visitors Left at 7 a.m Visitors Left at 8 a.m Visitors Left at 9 a.m Visitors Left at 10 a.m Visitors Left at 11 a.m Visitors Left at 12 p.m Visitors Left at 1 p.m Visitors Left at 2 p.m Visitors Left at 3 p.m Visitors Left at 4 p.m Visitors Left at 5 p.m Visitors Left at 6 p.m Visitors Left at 7 p.m Visitors Left at 8 p.m Visitors Left at 9 p.m Visitors Left at 10 p.m Visitors Left at 11 p.m
can you please help me with the below query.
Thank You,
April 2, 2012 at 1:02 pm
The first step to an answer can be found by reading the first link in my signature.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 2, 2012 at 1:04 pm
Hi sql2k8,
With 151 points and 699 visits, you sure know how to post the questions as per the guidelines.
Please have a look at the following article on how to post your sample data and how to format your questions : http://www.sqlservercentral.com/articles/Best+Practices/61537/
With the amount of sample data you provided, it is a laborious work to import it into SQL database and work on the request.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply