July 6, 2015 at 1:42 am
Hi All,
So I have the following query which pulls totals per hourly interval during the day:
select DATENAME (hh, date_time) as 'Hour', count (*)as 'Total'
from opencall
where costcenter like 'help@'
and day (date_time) = '03'
and month(date_time) = '08'
and year (date_time) = '2011'
group by datename (hh, date_time)
order by ABS (datename (hh, date_time)) ASC
Now they want to pull the data per 30 minutes. I have tried some things with dateadd and datediff but I get the error Implicit conversion from data type datetime to float is not allowed. Use the CONVERT function to run this query.
This is a datetime field, it contains seconds in it (which in reality we don't need for this query).
Can anyone offer any assistance on this one? The time fields are all dd/mm/yyyy hh:mm:ss.
Thanks in advance!
July 6, 2015 at 2:25 am
It would help a lot if you could provide the following:
1) Table definition
2) Sample Data
3) Expected output
4) What you have tried so far
See here for help and tools to post your question effectively: http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
-- Gianluca Sartori
July 6, 2015 at 3:01 am
No worries, my apologies here is more data (hopefully).
So the table is like this with the fields I will need to pull this data for the query:
callrefdate_timeevb_recipient_email
49898711/06/2015 08:22:04NULL
49974812/06/2015 13:24:33NULL
50471619/06/2015 15:21:11NULL
50730325/06/2015 10:29:47NULL
49371001/06/2015 11:40:46candidate_support@email.co.uk
50165316/06/2015 19:18:07candidate_support@email.co.uk
50189317/06/2015 10:08:52candidate_support@email.co.uk
49547704/06/2015 08:26:10help@email.co.uk
50201617/06/2015 11:52:50help@email.co.uk
49717108/06/2015 09:23:34help@email.net
In the last instance of asking, they wanted a total count of all callrefs per hour that were closed. I did this with the query that I included below.
I have tried the following query to get the data for the half hourly intervals but received the error Implicit conversion from data type datetime to float is not allowed. Use the CONVERT function to run this query.
select Count(*) as 'Total', DateAdd(Minute, 30 * (DateDiff(Minute, 0, closedate) / 30), 0) as 'Time Interval'
from vw_opencall
where evb_recipient_email like'help@email%'
and day (close_date) = '03'
and month(close_date) = '08'
and year (close_date) = '2014'
group by DateAdd(Minute, 30 * (DateDiff(Minute, 0, closedate) / 30), 0)
order by ABS (DateAdd(Minute, 30 * (DateDiff(Minute, 0, closedate) / 30), 0)) ASC
The above was a test for just grabbing out the time for a particular day on the basis they were running one day at a time.
Looking at how they would like to run the query themselves and that this now will be on multiple days I would expect the output data to look like this:
Date Time Total
06/07/201509:305
06/07/201510:008
06/07/201510:303
06/07/201511:002
Any help is appreciated, I admit I haven't tried to do the multiple dates but I have assumed this would be a convert statement to get out just this part of the datetime value. It's the 30 minute interval piece that I am stuck on.
Thank you again!
July 6, 2015 at 3:32 am
This should do:
SET DATEFORMAT DMY
CREATE TABLE #sampleData(
callref int NOT NULL PRIMARY KEY
, date_time datetime NOT NULL
, evb_recipient_email VARCHAR(29)
);
INSERT INTO #sampleData(callref,date_time,evb_recipient_email) VALUES (498987,'11/06/2015 08:22:04.000',NULL);
INSERT INTO #sampleData(callref,date_time,evb_recipient_email) VALUES (499748,'12/06/2015 13:24:33.000',NULL);
INSERT INTO #sampleData(callref,date_time,evb_recipient_email) VALUES (504716,'19/06/2015 15:21:11.000',NULL);
INSERT INTO #sampleData(callref,date_time,evb_recipient_email) VALUES (507303,'25/06/2015 10:29:47.000',NULL);
INSERT INTO #sampleData(callref,date_time,evb_recipient_email) VALUES (493710,'01/06/2015 11:40:46.000','candidate_support@email.co.uk');
INSERT INTO #sampleData(callref,date_time,evb_recipient_email) VALUES (501653,'16/06/2015 19:18:07.000','candidate_support@email.co.uk');
INSERT INTO #sampleData(callref,date_time,evb_recipient_email) VALUES (501893,'17/06/2015 10:08:52.000','candidate_support@email.co.uk');
INSERT INTO #sampleData(callref,date_time,evb_recipient_email) VALUES (495477,'04/06/2015 08:26:10.000','help@email.co.uk');
INSERT INTO #sampleData(callref,date_time,evb_recipient_email) VALUES (502016,'17/06/2015 11:52:50.000','help@email.co.uk');
INSERT INTO #sampleData(callref,date_time,evb_recipient_email) VALUES (497171,'08/06/2015 09:23:34.000','help@email.net');
select CAST(date_time AS date) AS [date],
CAST(DATEADD(minute, DATEPART(minute, date_time) / 30 * 30 , DATEADD(hour, DATEPART(hour, date_time), 0)) AS time) as [Time],
COUNT(*) AS Total
from #sampleData
where 1 = 1
-- your sample data does not contain values for these filters...
-- costcenter like 'help@'
--and day (date_time) = '03'
--and month(date_time) = '08'
--and year (date_time) = '2011'
group by CAST(date_time AS date),
CAST(DATEADD(minute, DATEPART(minute, date_time) / 30 * 30 , DATEADD(hour, DATEPART(hour, date_time), 0)) AS time)
order by [date], [time] ASC
-- Gianluca Sartori
July 6, 2015 at 3:49 am
Thank you so much Gianluca it is appreciated. I was getting mind boggled by the 30 minute thing. I did change the query slightly to this:
select convert(char(11),close_date, 103) as [Date],
CAST(DATEADD(minute, DATEPART(minute, close_date) / 30 * 30 , DATEADD(hour, DATEPART(hour, close_date), 0)) AS time) as [Time],count (*)as [Total]
from vw_opencall
where evb_recipient_email like 'help@email%'
--I will be adding dates between so edited this part, just used dates for testing.
--and day (date_time) = '03'
--and month(close_date) = '06'
--and year (close_date) = '2015'
group by convert(char(11),close_date, 103),
CAST(DATEADD(minute, DATEPART(minute, close_date) / 30 * 30 , DATEADD(hour, DATEPART(hour, close_date), 0)) AS time)
order by [date], [time] ASC
Just changed the first date format as they like it to be this way.
July 6, 2015 at 8:06 am
You were actually very close. The problem is here.
order by ABS (DateAdd(Minute, 30 * (DateDiff(Minute, 0, closedate) / 30), 0)) ASC
DATEADD() returns a date/time and ABS() takes a float, and it can't implicitly convert a datetime to a float.
The other thing I would suggest is that instead of this:
and day (close_date) = '03'
and month(close_date) = '08'
and year (close_date) = '2014'
you try this:
and CAST(close_date AS DATE) = '2014-08-03'
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 7, 2015 at 9:53 am
drew.allen (7/6/2015)
You were actually very close. The problem is here.
order by ABS (DateAdd(Minute, 30 * (DateDiff(Minute, 0, closedate) / 30), 0)) ASCDATEADD() returns a date/time and ABS() takes a float, and it can't implicitly convert a datetime to a float.
The other thing I would suggest is that instead of this:
and day (close_date) = '03'
and month(close_date) = '08'
and year (close_date) = '2014'
you try this:
and CAST(close_date AS DATE) = '2014-08-03'
Drew
You really should avoid all functions on columns whenever possible. Thus, here's the preferred method:
(close_date >= '20140803' and close_date < '20140804')
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply