HALF HOURLY BREAK DOWN QUERIE USING DISITNCT -

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

HALF HOURLY BREAK DOWN QUERIE USING DISITNCT -

Author

Topic

nicky
Starting Member

4 Posts

Posted - 2004-03-30 : 16:06:39

Hello and please help!

Table example is as follows:
Table name: activity
reason - Varchar
date - datetime

Results may be:

24/12/2003 12:10:00 - Help
26/12/2003 10:07:05 - Extreme
etc etc

I would like to have a break down of each half hour interval between 8am and 6pm in one simple querie that gives me the total count for each reason at each time.

I have tried the following and it is not working on MSSQL 2000, I would be so appreciative if some one would check this querie for me. I am a complete beginner!!

SELECT DISTINCT reason as reason1
(SELECT COUNT(*) FROM activity s1
WHERE s2.reason1 = s.1.reason1
AND CONVERT(VARCHAR(10), s2.date, 112) = CONVERT(VARCHAR(10), getDate(), 112) AND
CONVERT(VARCHAR(10), s2.date, 108)>='08:00' AND
CONVERT(VARCHAR(10), s2.date,108)< '08.30')
slot1,
(SELECT COUNT(*) FROM activity s1
WHERE s2.reason1 = s.1.reason1
AND CONVERT(VARCHAR(10), s2.date, 112) = CONVERT(VARCHAR(10), getDate(), 112) AND
CONVERT(VARCHAR(10), s2.date, 108)>='08:30' AND
CONVERT(VARCHAR(10), s2.date,108)< '09.00')
slot2
FROM activity
WHERE CONVERT(VARCHAR(10), s1.date, 112) = CONVERT(VARCHAR(10), getDate(), 112)

(If I could get this to work I would continue the query in order to get all the required times).

Nicky

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-30 : 16:07:46

Please provide the CREATE TABLE statement for your table, INSERT INTO statements for sample data. You have already provided the expected result set, so we're good on that part.

Tara

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-03-30 : 16:51:42

This could help get you started.

declare	@StartDate datetime

set @StartDate = '3/30/2004 08:00:00'

select	@StartDate
	, GETDATE()
	, DATEDIFF(mi, @startdate, GETDATE())/30 'PeriodNumber'
	, dateadd(mi, (30*(DATEDIFF(mi, @startdate, GETDATE())/30))
	  , @startdate) 'PeriodStart'
	, dateadd(mi, (30*((DATEDIFF(mi, @startdate, GETDATE())/30)+1))
	  , @startdate) 'PeriodEnd'

Without CREATE TABLE and INSERT statements it will be impossible for us to tailor this for your needs, but this could put you on the right direction. You will need to learn more date functions like DATEPART() to further develop along these lines.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-03-30 : 17:30:28

Hi Nicky,

Something like this might do the trick.

--************************************************************

use northwind

go

--*** Create and populate test table

create table Activity(Date datetime, Reason varchar(20));

insert into Activity
select '2003-12-24 12:10:00', 'Help' UNION ALL
select '2003-12-24 10:07:05', 'Extreme' UNION ALL
select '2003-12-24 10:07:05', 'Insane' UNION ALL
select '2003-12-24 12:57:00', 'Help' UNION ALL
select '2003-12-24 13:37:05', 'Extreme' UNION ALL
select '2003-12-24 16:07:05', 'Insane' UNION ALL
select '2003-12-24 18:23:00', 'Help' UNION ALL
select '2003-12-24 10:07:05', 'Extreme' UNION ALL
select '2003-12-24 19:07:05', 'Insane' UNION ALL
select '2003-12-24 22:10:00', 'Help' UNION ALL
select '2003-12-24 20:07:05', 'Extreme' UNION ALL
select '2003-12-24 11:37:05', 'Insane' UNION ALL
select '2003-12-24 12:59:00', 'Help' UNION ALL
select '2003-12-24 10:29:05', 'Extreme' UNION ALL
select '2003-12-24 10:49:05', 'Insane' UNION ALL
select '2003-12-24 23:23:00', 'Help' UNION ALL
select '2003-12-24 12:12:12', 'Extreme' UNION ALL
select '2003-12-24 12:12:22', 'Insane' UNION ALL
select '2003-12-24 22:12:00', 'Help' UNION ALL
select '2003-12-24 10:57:05', 'Extreme' UNION ALL
select '2003-12-24 10:47:05', 'Insane'

GO

--***** The query

SELECT DATEADD(mi, (DATEDIFF(mi, CAST(STR(YEAR(Date), 4) + '-' + RIGHT('00' + LTRIM(STR(MONTH(DATE),2)),2) + '-' + RIGHT('00' + LTRIM(STR(DAY(DATE),2)),2) as DATETIME), Date) / 30) * 30, CAST(STR(YEAR(Date), 4) + '-' + RIGHT('00' + LTRIM(STR(MONTH(DATE),2)),2) + '-' + RIGHT('00' + LTRIM(STR(DAY(DATE),2)),2) as DATETIME)) as StartTimeInterval, Reason, COUNT(*)
FROM activity
GROUP BY DATEADD(mi, (DATEDIFF(mi, CAST(STR(YEAR(Date), 4) + '-' + RIGHT('00' + LTRIM(STR(MONTH(DATE),2)),2) + '-' + RIGHT('00' + LTRIM(STR(DAY(DATE),2)),2) as DATETIME), Date) / 30) * 30, CAST(STR(YEAR(Date), 4) + '-' + RIGHT('00' + LTRIM(STR(MONTH(DATE),2)),2) + '-' + RIGHT('00' + LTRIM(STR(DAY(DATE),2)),2) as DATETIME)), Reason
ORDER BY 1

Duane.

nicky
Starting Member

4 Posts

Posted - 2004-03-31 : 02:31:25

Thank you to everyone who replied sp quickly. I will be busy today working on this!!!

Nicky

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources