Here what I want, My first filter is If I running this report today first filter would be GETDATE()
and here the data that I want...basically I want to track the hourly call. Please see below...
CREATE table DateTable (
ID INT,
SDate DATE,
STime VARCHAR(20),
TotalCalls Decimal,
State varchar(10)
)
INSERT into DateTable
SELECT 1,'7/28/2015','10:00 AM','0.556','CA'
Union All
SELECT 2,'7/27/2015','11:00 AM','2','GA'
Union ALL
SELECT 3,'7/28/2015','02:00 AM','0.556','NY'
Union ALL
SELECT 4,'7/26/2015','1:00 AM','0.556','KT'
Union ALL
SELECT 5,'7/25/2015','10:00 AM','0.556','CA'
Union ALL
SELECT 6,'7/23/2015','10:00 AM','0.556','CA'
Union ALL
SELECT 7,'7/28/2015','05:00 AM','6','NY'
Union ALL
SELECT 8,'7/28/2015','04:00 AM','6','WA'
Union ALL
SELECT 8,'7/28/2015','03:00 PM','6','NY'
Union ALL
SELECT 8,'7/28/2015','11:00 AM','6','NY'
Union ALL
SELECT 8,'7/28/2015','09:00 PM','6','NY'
Union ALL
SELECT 8,'7/28/2015','08:00 PM','6','NY'
Union ALL
SELECT 8,'7/28/2015','11:00 PM','6','NY'
Union ALL
SELECT 8,'7/28/2015','3:00 AM','6','NY'
SELECT * from DateTable
WHERE CONVERT(dATE,DateTable.SDate) = CONVERT(DATE,GETDATE())
--drop table DateTable
SDate,TimeFrame,TotalCalls
2015-07-28,12:00 AM To 12:59 AM, 0
2015-07-28,1:00 AM To 1:59 AM, 0
2015-07-28,2:00 AM To 2:59 AM, 1
2015-07-28,3:00 AM To 3:59 AM, 12
2015-07-28,4:00 AM To 4:59 AM, 6
2015-07-28,5:00 AM To 5:59 AM, 6
2015-07-28,6:00 AM To 6:59 AM, 0
2015-07-28,7:00 AM To 7:59 AM, 0
2015-07-28,8:00 AM To 8:59 AM, 0
2015-07-28,9:00 AM To 9:59 AM, 0
2015-07-28,10:00 AM To 10:59 AM,0
2015-07-28,11:00 PM To 11:59 AM, 6
2015-07-28,12:00 PM To 12:59 PM, 0
2015-07-28,1:00 PM To 1:59 PM, 0
2015-07-28,2:00 PM To 2:59 PM, 0
2015-07-28,3:00 AM To 3:59 PM, 6
2015-07-28,4:00 AM To 4:59 PM, 0
2015-07-28,5:00 PM To 5:59 PM, 0
2015-07-28,6:00 PM To 6:59 PM, 0
2015-07-28,7:00 PM To 7:59 PM, 0
2015-07-28,8:00 PM To 8:59 PM, 6
2015-07-28,9:00 PM To 9:59 PM, 6
2015-07-28,10:00 PM To 10:59 PM,0
2015-07-28,11:00 AM To 11:59 PM,6
Thanks in Advance.