Hi,
I need a hourly basis report for month from database
Database Table Structute:
| Process |
Date Time |
| Sample |
4/2/2013 15:19 |
| Sample |
4/2/2013 18:08 |
| Sample |
4/3/2013 21:08 |
| Sample |
4/4/2013 23:08 |
| Sample |
4/5/2013 22:08 |
| Sample |
4/6/2013 19:08 |
| Sample |
4/7/2013 15:08 |
Sample Report Structure:
| Hrly Report |
1st April 2013 |
2nd April 2013 |
3rd April 2013 |
4th April 2013 |
5th April 2013 |
6th April 2013 |
7th April 2013 |
8th April 2013 |
9th April 2013 |
10th April 2013 |
| 0:00 - 1:00 |
0 |
4 |
1 |
7 |
6 |
4 |
6 |
5 |
6 |
6 |
| 1:00 - 2:00 |
0 |
0 |
2 |
4 |
1 |
2 |
5 |
1 |
3 |
9 |
| 2:00 - 3:00 |
0 |
2 |
1 |
6 |
2 |
3 |
1 |
1 |
3 |
5 |
| 3:00 - 4:00 |
0 |
1 |
2 |
0 |
1 |
1 |
3 |
2 |
3 |
1 |
| 4:00 - 5:00 |
0 |
1 |
2 |
1 |
2 |
3 |
2 |
0 |
3 |
2 |
| 5:00 - 6:00 |
0 |
1 |
2 |
3 |
0 |
2 |
4 |
2 |
3 |
3 |
| 6:00 - 7:00 |
0 |
4 |
4 |
1 |
1 |
2 |
2 |
1 |
2 |
6 |
| 7:00 - 8:00 |
0 |
0 |
5 |
1 |
1 |
2 |
3 |
4 |
2 |
10 |
| 8:00 - 9:00 |
0 |
5 |
8 |
0 |
2 |
7 |
7 |
5 |
5 |
9 |
| 9:00 -10:00 |
0 |
9 |
11 |
10 |
8 |
9 |
6 |
11 |
13 |
22
|
select datepart(hour,date_time) as interval,(convert(char(11), date_time, 113)) as mon,sum(case when process = 'Sample' then 1 else 0 end) as uk_lm_tot from tbl_sampel group by datepart(hour,date_time),(convert(char(11), date_time, 113)) order by interval asc
Advance Thanks for Your Support
