Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Sorting Data 2

Status
Not open for further replies.
NerdTop72

NerdTop72

Programmer
Mar 14, 2005
117
0
0
US
I am having an issue with displaying information properly for reporting.

This is the structure of the raw data in the Table FTB_MHF
Code: 
DateandTime		TagIndex	Val
9/1/2013 00:31:15 AM	129		6.2	 	 
9/1/2013 00:33:15 AM	130		0	 	 
9/1/2013 00:33:15 AM	131		1	 	 
9/1/2013 01:33:15 AM	132		0	 	 
9/2/2013 01:42:17 AM	129		6.1	 	 
9/2/2013 01:42:17 AM	130		0	 	 
9/2/2013 10:42:17 AM	131		0	 	 
9/2/2013 10:42:17 AM	132		1

SQL: 
SELECT DATEADD(hour, DATEDIFF(hour, 0, DateAndTime), 0) AS hour, avg(val) as DHourlyAverage
FROM FTBL_MHF 
WHERE  (TagIndex IN ('129')) AND (DateAndTime >= CONVERT(DATETIME, 
	'2013-9-1 00:00:00', 102)) 
		AND (DateAndTime < CONVERT(DATETIME, 
	'2013-10-1 00:00:00', 102) )
GROUP BY DATEADD(hour, DATEDIFF(hour, 0, DateAndTime), 0)
ORDER BY hour


Results:
Code: 
Hour	   		DPHourlyAverage		
2013-09-01 00:00:00.000	3.6159331838289899	
2013-09-01 01:00:00.000	3.6001444657643638	
2013-09-01 02:00:00.000	3.5930899846351751

How do you get the results to display this way:

Code: 
	  Hour of the day
	  0:00	  1:00	  2:00	etc...
Date
9/1/2013  3.61	  3.60	  3.59	
9/2/2013
9/3/2013
etc...
 
Sort by date Sort by votes
something like
SQL: 
declare @T as table
(
DateandTime datetime,
TagIndex int,
Val float
)

insert into @T
select 
'9/1/2013 00:31:15 AM',	129,		6.2	 	 
union
select
'9/1/2013 00:33:15 AM',	130,		0	 	 
union
select
'9/1/2013 00:33:15 AM',	131,		1	 	 
union
select
'9/1/2013 01:33:15 AM',	132,		0	 	 
union
select
'9/2/2013 01:42:17 AM',	129,		6.1	 	 
union
select
'9/2/2013 01:42:17 AM',	130,		0	 	 
union
select
'9/2/2013 10:42:17 AM',	131,		0	 	 
union
select
'9/2/2013 10:42:17 AM',	132,		1 


;with c as 
(
select convert(varchar(12), DateandTime, 101) as dt, cast(DATEPART(HOUR,DateandTime) as varchar) h,  val 
from @T
WHERE  (TagIndex IN (129,130,131)) AND (DateAndTime >= '9/1/2013')
		AND (DateAndTime < '10/1/2013') 
)
select * from c
pivot (avg (val) for h in ([0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) as DHourlyAverage



 
Upvote 0 Downvote
Thanks! I will look into PIVOT, that seems like the right direction.

I cannot get the code to run GK53

I am using SQL analyzer for SQL Server 2000

I am trying to fill up a months worth of data with the averages per hour from a tagindex that only = '129' and between 2 dates. really just an entire months worth of data but the averages per hour


Code: 
          00:00 01:00 02:00 03:00
9/1/2013  avg/hour(tag index ='129')
9/2/2013
9/3/2013
9/4/2013
9/5/2013

Never made a PIVOT query before. Ill let you know what I come up with.
Thanks for the direction!
-Nerd
 
Upvote 0 Downvote
Pivot was added in SQL2005.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
nerd - as you are still using an outdated version of SQL Server you will need to use the old ways - too many examples on the net for me to bother putting one here.

so google for "rows to columns sql server 2000" and you will find lots of examples - as it seems you have a limited number of columns most of them will be suitable to you

Regards

Frederico Fonseca
SysSoft Integrated Ltd

FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
Thanks for the info! I get stumped trying to be clear to google when I have questions like this! The solution to search for is so simple too.

I appreciate the help this forum provides me!
 
Upvote 0 Downvote
OK so I feel like I am heading in the right direction...

I am now stuck trying to separate my data into hours? GMMastros helped me a long time ago with grouping averages per hour. Since I want to use hours as columns. I am stuck on how to get an average of minute data for only 1 hour over the course of several days? I am confused on what to search for again in google... :-(

Here is my current sql statement
SQL: 
SELECT     DATEADD(hour, DATEDIFF(hour, 1, DateAndTime), 0) AS hour, avg(val) as DPDailyAverage, count(Val) as DPValidReadings
FROM         FTBL_MHF 
WHERE     (TagIndex IN ('129')) AND (DateAndTime >= CONVERT(DATETIME, 
'2013-9-1 00:00:00', 102)) 
AND (DateAndTime < CONVERT(DATETIME, 
'2013-9-3 00:00:00', 102) )
GROUP BY DATEADD(hour, DATEDIFF(hour, 1, DateAndTime), 0)
ORDER BY hour

Results from above query...
SQL: 
2013-09-01 00:00:00.000	3.6159331838289899	60
2013-09-01 01:00:00.000	3.6001444657643638	60
2013-09-01 02:00:00.000	3.5930899846351751	59
2013-09-01 03:00:00.000	3.5910700360933938	60
2013-09-01 04:00:00.000	3.5806832234064738	60
2013-09-01 05:00:00.000	3.576980209350586	60
2013-09-01 06:00:00.000	3.5951799750328064	60
2013-09-01 07:00:00.000	3.5862988074620565	60
2013-09-01 08:00:00.000	3.587712887922923	60
2013-09-01 09:00:00.000	3.5936336437861125	60
2013-09-01 10:00:00.000	3.5893507639567059	60
2013-09-01 11:00:00.000	3.5896457990010577	60
2013-09-01 12:00:00.000	3.5807442466417947	60
2013-09-01 13:00:00.000	3.5741472446312339	59
2013-09-01 14:00:00.000	3.5751897335052489	60
2013-09-01 15:00:00.000	3.5753626664479574	60
2013-09-01 16:00:00.000	3.7190990805625916	60
2013-09-01 17:00:00.000	4.3983600735664368	60
2013-09-01 18:00:00.000	5.3910150448481238	60
2013-09-01 19:00:00.000	5.3440456231435141	60
2013-09-01 20:00:00.000	5.3484440900511663	59
2013-09-01 21:00:00.000	5.3382164796193443	60
2013-09-01 22:00:00.000	5.337178802490234	60
2013-09-01 23:00:00.000	5.3991230726242065	60
2013-09-02 00:00:00.000	5.8315529982248941	60
2013-09-02 01:00:00.000	6.6779283285140991	60
2013-09-02 02:00:00.000	6.7543388684590662	60
2013-09-02 03:00:00.000	6.9886263688405359	60
2013-09-02 04:00:00.000	6.9632639076750156	59
2013-09-02 05:00:00.000	6.9755640824635821	60
2013-09-02 06:00:00.000	7.1875216484069826	60
2013-09-02 07:00:00.000	7.2009502092997231	60
2013-09-02 08:00:00.000	7.1950497309366863	60
2013-09-02 09:00:00.000	7.1524751106897986	60
2013-09-02 10:00:00.000	7.2959469954172773	60
2013-09-02 11:00:00.000	7.3933243751525879	60
2013-09-02 12:00:00.000	7.414698147773743	60
2013-09-02 13:00:00.000	7.309111110369364	60
2013-09-02 14:00:00.000	7.3804905293351517	59
2013-09-02 15:00:00.000	7.3598241647084555	60
2013-09-02 16:00:00.000	7.1811837434768675	60
2013-09-02 17:00:00.000	7.209983921051025	60
2013-09-02 18:00:00.000	7.2055890798568729	60
2013-09-02 19:00:00.000	7.1555881102879839	60
2013-09-02 20:00:00.000	7.0587397019068403	60
2013-09-02 21:00:00.000	7.1143766641616821	60
2013-09-02 22:00:00.000	7.1151701688766478	60
2013-09-02 23:00:00.000	7.1080191822375278	59

I guess now I want the results to be an average of only hour 1 between a dateandtime query

SQL: 
2013-09-01 00:00:00.000	3.6159331838289899	60
2013-09-02 00:00:00.000	5.8315529982248941	60
2013-09-03 00:00:00.000	5.8315529982248941	60

Then based on changing row titles into columns for reporting (My original post question), I will need to run 24 separate select statements to get hour 1 - 24 to get my columns aligned correctly. I am stuck on the datepart dateadd query now. Im not sure how to grab the average of only 1 hour 00:00 to 01:00 between dates?

Thanks
 
Upvote 0 Downvote
Why do you want to run 24 different queries? Can't you get the results you want with an ORDER BY clause in the original query?

Try:

Code: 
ORDER BY CAST(DATEADD(hour, DATEDIFF(hour, 1, DateAndTime), 0) AS TIME), hour

Tamar
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

coorsman
  • Locked
  • Question
Column Data Type. 2
Replies
9
Views
141
Chris Miller
Chris Miller
peac3
  • Locked
  • Question
fill the gap of hierarchy data
Replies
11
Views
170
Chris Miller
Chris Miller
Andrzejek
  • Locked
  • Question
Data types and sizes
Replies
18
Views
171
gmmastros
gmmastros
gussy81
  • Locked
  • Question
Decimal Datatype with negative value
Replies
5
Views
258
Chris Miller
Chris Miller
marn1997
  • Locked
  • Question
Large data base to back word engineer
Replies
5
Views
61
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top