TSQL Help

Sonu619 · July 28, 2015, 5:35pm

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.

gbritton · July 28, 2015, 6:46pm

Not clear what you want. What's wrong with the query you posted?

djj55 · July 28, 2015, 6:50pm

I believe they what the hourly totals per the date requested.

Sonu619 · July 28, 2015, 6:53pm

Nothing wrong in the query, that is my first filter to get only today's data.
Now my second requirement are count the data Hourly base. e.g 12:00 am to 12:59 AM how many calls we receive, if no call then total count should be 0 and next 1:00 AM to 1:59 AM we received 2 calls, so total calls should be 2 and so on until 24 hrs.

This would be my sample data after execute the query...

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

Please let me know, If my questions is till not clear.

Thank You.

JamesK · July 28, 2015, 7:58pm

Your DataTable storing the date and time portions in two separate columns makes it a more complex to do the logic, so if at all possible, I would just have a single column of DATETIME data type to store both date and time.

Assuming you are able to do that, to get the results you want you will need a numbers table. This is because you want rows for time-slots in which you may not have any calls at all. If you don't have a numbers table, create one, for example like this:

CREATE TABLE #Tally(N INT NOT NULL PRIMARY KEY);

INSERT INTO #Tally 
        ( N )
SELECT TOP 10000
        ROW_NUMBER() OVER ( ORDER BY t1.number )-1 AS N
FROM    master..spt_values t1
        CROSS JOIN master..spt_values t2

Then your query would be like shown below - where I am assuming that the SDate column is of datetime type and it holds the date and time

DECLARE 
	@startDate DATETIME = '20150728',
	@endDate DATETIME = '20150731';
	

SELECT
	DATEADD(hh,N,@startDate) [StartHour],
	SUM(TotalCalls) AS TotalCalls
FROM
	#Tally t
	LEFT JOIN DataTable d ON 
		d.SDate >= DATEADD(hh,N,@startDate)
		AND d.SDate < DATEADD(hh,N+1,@startDate)
GROUP BY
	DATEADD(hh,N,@startDate);

Sonu619 · July 29, 2015, 2:59pm

James, Thank You for your reply. However, I am getting the result that I want... After I combine Date & Time Column and insert into Temp table and run above SQL to my Temp Table it is not Group By One-hour time frame. I successfully get the result that I want in a different way. But the Problem is if source data doesn't have that hour of data I am not getting any record. For e.g IF 9:00 AM To 9:59 AM Slot has no records it should show. 9:00 AM To 9:59 AM = 0. Any help or guide would be great appreciated.

Select
--LocalAirDateTime,
Case
When Datepart(HOUR,SDate) = 0 THEN Convert(varchar(20),'12:00 AM To 12:59 AM')
WHEN Datepart(HOUR,SDate) = 1 THEN CONVERT(VARCHAR(20),'1:00 AM To 1:59 AM')
WHEN Datepart(HOUR,SDate) = 2 THEN CONVERT(VARCHAR(20),'2:00 AM To 2:59 AM')
WHEN Datepart(HOUR,SDate) = 3 THEN CONVERT(VARCHAR(20),'3:00 AM To 3:59 AM')
WHEN Datepart(HOUR,SDate) = 4 THEN CONVERT(VARCHAR(20),'4:00 AM To 4:59 AM')
WHEN Datepart(HOUR,SDate) = 5 THEN CONVERT(VARCHAR(20),'5:00 AM To 5:59 AM')
WHEN Datepart(HOUR,SDate) = 6 THEN CONVERT(VARCHAR(20),'6:00 AM To 6:59 AM')
WHEN Datepart(HOUR,SDate) = 7 THEN CONVERT(VARCHAR(20),'7:00 AM To 7:59 AM')
WHEN Datepart(HOUR,SDate) = 8 THEN CONVERT(VARCHAR(20),'8:00 AM To 8:59 AM')
WHEN Datepart(HOUR,SDate) = 9 THEN CONVERT(VARCHAR(20),'9:00 AM To 9:59 AM')
WHEN Datepart(HOUR,SDate) = 10 THEN CONVERT(VARCHAR(20),'10:00 AM To 10:59 AM')
WHEN Datepart(HOUR,SDate) = 11 THEN CONVERT(VARCHAR(20),'11:00 AM To 11:59 AM')
WHEN Datepart(HOUR,SDate) = 12 THEN CONVERT(VARCHAR(20),'12:00 PM To 12:59 PM')
WHEN Datepart(HOUR,SDate) = 13 THEN CONVERT(VARCHAR(20),'1:00 PM To 1:59 PM')
WHEN Datepart(HOUR,SDate) = 14 THEN CONVERT(VARCHAR(20),'2:00 PM To 2:59 PM')
WHEN Datepart(HOUR,SDate) = 15 THEN CONVERT(VARCHAR(20),'3:00 PM To 3:59 PM')
WHEN Datepart(HOUR,SDate) = 16 THEN CONVERT(VARCHAR(20),'4:00 PM To 4:59 PM')
WHEN Datepart(HOUR,SDate) = 17 THEN CONVERT(VARCHAR(20),'5:00 PM To 5:59 PM')
WHEN Datepart(HOUR,SDate) = 18 THEN CONVERT(VARCHAR(20),'6:00 PM To 6:59 PM')
WHEN Datepart(HOUR,SDate) = 19 THEN CONVERT(VARCHAR(20),'7:00 PM To 7:59 PM')
WHEN Datepart(HOUR,SDate) = 20 THEN CONVERT(VARCHAR(20),'8:00 PM To 8:59 PM')
WHEN Datepart(HOUR,SDate) = 21 THEN CONVERT(VARCHAR(20),'9:00 PM To 9:59 PM')
WHEN Datepart(HOUR,SDate) = 22 THEN CONVERT(VARCHAR(20),'10:00 PM To 10:59 PM')
WHEN Datepart(HOUR,SDate) = 23 THEN CONVERT(VARCHAR(20),'11:00 PM To 11:59 PM')
ELSE CONVERT(VARCHAR(20),Datepart(HOUR,SDate))
END
,
ROUND(Sum(TotalCalls),0) as TotalCall

	from #temp1
	group by Datepart(HOUR,SDate)

JamesK · July 29, 2015, 3:21pm

This is why you need a Tally table. The query I posted seem to be giving the results that you are asking for, at least as I understood it. I have modified the code below to replace NULL with zeros.

-- First, create a sample table. 
-- When you post a question, it would be very helpful if you post DDL such as this 
-- that someone can copy and paste to run and write queries against.
-- Otherwise, the code posted would most likely be untested code.

CREATE TABLE #tmp( SDate DATETIME , TotalCalls INT)

INSERT INTO #tmp VALUES
('2015-07-28 11:11:10.420',11),
('2015-07-28 11:53:10.000',3),
('2015-07-29 12:10:10.420',9),
('2015-07-29 12:23:17.000',1),
('2015-07-29 11:23:17.000',6);

-- Create Tally table 
CREATE TABLE #Tally(N INT NOT NULL PRIMARY KEY);

INSERT INTO #Tally 
        ( N )
SELECT TOP 10000
        ROW_NUMBER() OVER ( ORDER BY t1.number )-1 AS N
FROM    master..spt_values t1
        CROSS JOIN master..spt_values t2

-- Query for 28th and 29th

DECLARE 
	@startDate DATETIME = '20150728',
	@endDate DATETIME = '20150729';
	

SELECT
	DATEADD(hh,N,@startDate) [StartHour],
	COALESCE(SUM(TotalCalls),0) AS TotalCalls
FROM
	#Tally t
	LEFT JOIN #tmp d ON 
		d.SDate >= DATEADD(hh,N,@startDate)
		AND d.SDate < DATEADD(hh,N+1,@startDate)
WHERE
	DATEADD(hh,N-24,@startDate) <= @endDate
GROUP BY
	DATEADD(hh,N,@startDate)
ORDER BY 
	[StartHour] ;'

When I run this, I get 49 rows of output, most of which have TotalCalls = 0. The three rows with non-zeros is as follows

StartHour				TotalCalls
2015-07-28 11:00:00.000		14
2015-07-29 11:00:00.000		6
2015-07-29 12:00:00.000		10

Isn't that what you were asking for, or is it something else?