WHOLE DAY DATA - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

WHOLE DAY DATA

Author

Topic

smitha
Posting Yak Master

100 Posts

Posted - 2010-03-01 : 06:02:32

How I can take the average of 24 hour data (from morning 6 o clock to next day morning 6 0 clock)

Smitha

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-01 : 06:15:13

[code]
select avg ( somecol )
from sometable
where logtime >= dateadd(hour, 6, datediff(day, 0, getdate())),
and logtime < dateadd(hour, 6, datediff(day, 0, getdate() + 1))
[/code]

KH
[spoiler]Time is always against us[/spoiler]

smitha
Posting Yak Master

100 Posts

Posted - 2010-03-01 : 06:30:59

sorry I am not getting any data

quote:
Originally posted by khtan

select avg ( somecol )
from sometable
where logtime >= dateadd(hour, 6, datediff(day, 0, getdate())),
and logtime < dateadd(hour, 6, datediff(day, 0, getdate() + 1))

KH
[spoiler]Time is always against us[/spoiler]

Smitha

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-01 : 06:41:39

do you have any record within today 6 am and tomorrow 6 am ?

KH
[spoiler]Time is always against us[/spoiler]

smitha
Posting Yak Master

100 Posts

Posted - 2010-03-01 : 06:49:03

I am having data for last month and for that I am changing that getdata() to my col. name as shown below
WHERE (LOGTIME >= DATEADD(hour, 6, DATEDIFF(day, 0, LOGDATE))) AND (LOGTIME < DATEADD(hour, 6, DATEDIFF(day, 0, LOGDATE + 1)))

quote:
Originally posted by khtan

do you have any record within today 6 am and tomorrow 6 am ?

KH
[spoiler]Time is always against us[/spoiler]

Smitha

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-01 : 06:57:28

can you explain your requirement again ? with sample data and expected result ?

KH
[spoiler]Time is always against us[/spoiler]

smitha
Posting Yak Master

100 Posts

Posted - 2010-03-01 : 07:16:55

I am storing the data in a table every minute.
I am writing a query to take the average value of the data from yesterday morning 6 o clock to today's morning 6 0 clock.
I should get one value with yesterday's date.

Smitha

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-01 : 07:23:03

Try this

select avg ( somecol )
from sometable
where logtime >= dateadd(hour, 6, datediff(day, 0, getdate()-1)),
and logtime < dateadd(hour, 6, datediff(day, 0, getdate() ))

Madhivanan

Failing to plan is Planning to fail

smitha
Posting Yak Master

100 Posts

Posted - 2010-03-01 : 07:30:38

sorry I am not getting any result

quote:
Originally posted by madhivanan

Try this

select avg ( somecol )
from sometable
where logtime >= dateadd(hour, 6, datediff(day, 0, getdate()-1)),
and logtime < dateadd(hour, 6, datediff(day, 0, getdate() ))

Madhivanan

Failing to plan is Planning to fail

Smitha

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-01 : 07:32:19

quote:
Originally posted by smitha

sorry I am not getting any result
quote:
Originally posted by madhivanan

Try this

select avg ( somecol )
from sometable
where logtime >= dateadd(hour, 6, datediff(day, 0, getdate()-1)),
and logtime < dateadd(hour, 6, datediff(day, 0, getdate() ))

Madhivanan

Failing to plan is Planning to fail

Smitha

Post some actual data with expected result

Madhivanan

Failing to plan is Planning to fail

smitha
Posting Yak Master

100 Posts

Posted - 2010-03-01 : 08:37:38

LOGDATE LOGTIME TAG1

2/10/2010 12:00:00 AM 1/1/1900 11:00:02 PM 12.00
2/11/2010 12:00:00 AM 1/1/1900 12:00:01 AM 12.00
2/11/2010 12:00:00 AM 1/1/1900 1:06:37 AM 12.00
2/11/2010 12:00:00 AM 1/1/1900 2:06:48 AM 12.00
2/11/2010 12:00:00 AM 1/1/1900 3:06:54 AM 12.00
2/11/2010 12:00:00 AM 1/1/1900 5:07:09 AM 12.00
2/11/2010 12:00:00 AM 1/1/1900 6:07:16 AM 12.00

I want the average value of the tag1 with the date as 2/10/2010

Smitha

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-01 : 08:45:27

quote:
Originally posted by smitha

LOGDATE LOGTIME TAG1

2/10/2010 12:00:00 AM 1/1/1900 11:00:02 PM 12.00
2/11/2010 12:00:00 AM 1/1/1900 12:00:01 AM 12.00
2/11/2010 12:00:00 AM 1/1/1900 1:06:37 AM 12.00
2/11/2010 12:00:00 AM 1/1/1900 2:06:48 AM 12.00
2/11/2010 12:00:00 AM 1/1/1900 3:06:54 AM 12.00
2/11/2010 12:00:00 AM 1/1/1900 5:07:09 AM 12.00
2/11/2010 12:00:00 AM 1/1/1900 6:07:16 AM 12.00

I want the average value of the tag1 with the date as 2/10/2010

Smitha

You dont have data for yesterday's date. How will you get the result?

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-01 : 09:00:31

[code]
declare @sample table
(
LOGDATE datetime,
LOGTIME datetime,
TAG1 decimal(10,2)
)

insert into @sample
select '2/10/2010 12:00:00 AM', '1/1/1900 11:00:02 PM', 12.00 union all
select '2/11/2010 12:00:00 AM', '1/1/1900 12:00:01 AM', 12.00 union all
select '2/11/2010 12:00:00 AM', '1/1/1900 1:06:37 AM', 12.00 union all
select '2/11/2010 12:00:00 AM', '1/1/1900 2:06:48 AM', 12.00 union all
select '2/11/2010 12:00:00 AM', '1/1/1900 3:06:54 AM', 12.00 union all
select '2/11/2010 12:00:00 AM', '1/1/1900 5:07:09 AM', 12.00 union all
select '2/11/2010 12:00:00 AM', '1/1/1900 6:07:16 AM', 12.00

select LDATE, AVGTAG = avg(TAG1)
from
(
select *,
LDATE = case when LOGTIME < '06:00' then dateadd(day, -1, LOGDATE) else LOGDATE end
from @sample
) l
group by LDATE

/*
LDATE AVGTAG
----------- ---------
2010-02-10 12.000000
2010-02-11 12.000000

(2 row(s) affected)
*/
[/code]

KH
[spoiler]Time is always against us[/spoiler]

smitha
Posting Yak Master

100 Posts

Posted - 2010-03-01 : 10:31:29

in the query instead of Getdate() I changed to LOGDATE

quote:
Originally posted by madhivanan

quote:
Originally posted by smitha

LOGDATE LOGTIME TAG1

2/10/2010 12:00:00 AM 1/1/1900 11:00:02 PM 12.00
2/11/2010 12:00:00 AM 1/1/1900 12:00:01 AM 12.00
2/11/2010 12:00:00 AM 1/1/1900 1:06:37 AM 12.00
2/11/2010 12:00:00 AM 1/1/1900 2:06:48 AM 12.00
2/11/2010 12:00:00 AM 1/1/1900 3:06:54 AM 12.00
2/11/2010 12:00:00 AM 1/1/1900 5:07:09 AM 12.00
2/11/2010 12:00:00 AM 1/1/1900 6:07:16 AM 12.00

I want the average value of the tag1 with the date as 2/10/2010

Smitha

You dont have data for yesterday's date. How will you get the result?

Madhivanan

Failing to plan is Planning to fail

Smitha

smitha
Posting Yak Master

100 Posts

Posted - 2010-03-01 : 10:45:08

sorry,there will be lakhs of data. so, I cannot make union of all the data

quote:

Originally posted by khtan


declare @sample table
(
	LOGDATE 	datetime,
	LOGTIME 	datetime,
	TAG1		decimal(10,2)
)

insert into @sample
select	'2/10/2010 12:00:00 AM', '1/1/1900 11:00:02 PM', 12.00	union all
select	'2/11/2010 12:00:00 AM', '1/1/1900 12:00:01 AM', 12.00	union all
select	'2/11/2010 12:00:00 AM', '1/1/1900 1:06:37 AM',  12.00	union all
select	'2/11/2010 12:00:00 AM', '1/1/1900 2:06:48 AM',  12.00	union all
select	'2/11/2010 12:00:00 AM', '1/1/1900 3:06:54 AM',  12.00	union all
select	'2/11/2010 12:00:00 AM', '1/1/1900 5:07:09 AM',  12.00	union all
select	'2/11/2010 12:00:00 AM', '1/1/1900 6:07:16 AM',  12.00

select	LDATE, AVGTAG = avg(TAG1)
from
(
	select	*,
		LDATE	= case when LOGTIME < '06:00' then dateadd(day, -1, LOGDATE) else LOGDATE end
	from	@sample
) l
group by LDATE

/*
LDATE       AVGTAG                                   
----------- ---------
2010-02-10  12.000000
2010-02-11  12.000000

(2 row(s) affected)
*/

KH
[spoiler]Time is always against us[/spoiler]

Smitha

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-01 : 11:04:14

just use the QUERY part and replace with your actual table name. The @sample as for demonstration of the query


select	LDATE, AVGTAG = avg(TAG1)
from
(
	select	*,
		LDATE	= case when LOGTIME < '06:00' then dateadd(day, -1, LOGDATE) else LOGDATE end
	from	@sample
) l
group by LDATE

KH
[spoiler]Time is always against us[/spoiler]

smitha
Posting Yak Master

100 Posts

Posted - 2010-03-01 : 11:19:09

I used the code but it gave me the result as follows,

10/02/2010 23:00:00 12.000000
10/02/2010 00:00:00 12.000000
11/02/2010 01:00:00 12.000000
11/02/2010 02:00:00 12.000000
11/02/2010 03:00:00 12.000000
11/02/2010 04:00:00 12.000000
11/02/2010 05:00:00 12.000000
11/02/2010 06:00:00 12.000000
11/02/2010 07:00:00 12.000000
11/02/2010 08:00:00 12.000000
11/02/2010 23:00:00 12.000000
11/02/2010 00:00:00 12.000000
12/02/2010 01:00:00 12.000000
12/02/2010 02:00:00 11.000000
12/02/2010 03:00:00 11.000000
12/02/2010 04:00:00 7.000000
12/02/2010 05:00:00 7.000000
12/02/2010 06:00:00 12.000000
12/02/2010 07:00:00 12.000000
12/02/2010 08:00:00 12.000000
12/02/2010 10:00:00 12.000000
12/02/2010 11:00:00 12.000000
12/02/2010 12:00:00 12.000000
instead I want for each day only one value

quote:
Originally posted by khtan

just use the QUERY part and replace with your actual table name. The @sample as for demonstration of the query

select LDATE, AVGTAG = avg(TAG1)
from
(
select *,
LDATE = case when LOGTIME < '06:00' then dateadd(day, -1, LOGDATE) else LOGDATE end
from @sample
) l
group by LDATE

KH
[spoiler]Time is always against us[/spoiler]

Smitha

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-01 : 11:25:42

do you mean this?


declare @sample table
(
	LOGDATE 	datetime,
	LOGTIME 	datetime,
	TAG1		decimal(10,2)
)

insert into @sample
select	'2/10/2010 12:00:00 AM', '1/1/1900 11:00:02 PM', 12.00	union all
select	'2/11/2010 12:00:00 AM', '1/1/1900 12:00:01 AM', 12.00	union all
select	'2/11/2010 12:00:00 AM', '1/1/1900 1:06:37 AM',  12.00	union all
select	'2/11/2010 12:00:00 AM', '1/1/1900 2:06:48 AM',  12.00	union all
select	'2/11/2010 12:00:00 AM', '1/1/1900 3:06:54 AM',  12.00	union all
select	'2/11/2010 12:00:00 AM', '1/1/1900 5:07:09 AM',  12.00	union all
select	'2/11/2010 12:00:00 AM', '1/1/1900 6:07:16 AM',  12.00

Select DATEADD(dd,DATEDIFF(dd,0,DateAdj),1) AS Date, AVG(TAG1) AS Average
FROM
(
select DATEADD(hh,-18,DATEADD(SS,DATEDIFF(ss,0,LOGTIME),LOGDATE)) AS DateAdj,TAG1
from @sample
)t
GROUP BY DATEADD(dd,DATEDIFF(dd,0,DateAdj),1)

output
-----------------------------
Date	                Average
2010-02-11 00:00:00.000	12.000000

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

smitha
Posting Yak Master

100 Posts

Posted - 2010-03-01 : 11:47:54

For the following code
SELECT CONVERT(CHAR(10), DATEADD(dd, DATEDIFF(dd, 0, logdate), 0), 103) AS LOGDATE1, AVG(E1_ELE1) AS E1_ELE1_AVG
FROM (SELECT DATEADD(hh, - 24, DATEADD(SS, DATEDIFF(ss, 0, LOGTIME), LOGDATE)) AS logdate, E1_ELE1
FROM dbo.E1_SHIFTREPA) AS t
GROUP BY DATEADD(dd, DATEDIFF(dd, 0, logdate), 0)

I am getting the result as
09/02/2010 12.000000
10/02/2010 12.000000
11/02/2010 10.400000

whereas for the another code

SELECT CONVERT(CHAR(10), DATEADD(dd, DATEDIFF(dd, 0, logdate), 0), 103) AS LOGDATE1, AVG(E1_ELE1) AS E1_ELE1_AVG
FROM (SELECT DATEADD(hh, 24, DATEADD(SS, DATEDIFF(ss, 0, LOGTIME), LOGDATE)) AS logdate, E1_ELE1
FROM dbo.E1_SHIFTREPA) AS t
GROUP BY DATEADD(dd, DATEDIFF(dd, 0, logdate), 0)

I am getting the result as

11/02/2010 12.000000
12/02/2010 12.000000
13/02/2010 10.400000

but I want the dates 10/02/2010, 11/02/2010 and 12/02/2010

quote:

Originally posted by visakh16

do you mean this?


declare @sample table
(
	LOGDATE 	datetime,
	LOGTIME 	datetime,
	TAG1		decimal(10,2)
)

insert into @sample
select	'2/10/2010 12:00:00 AM', '1/1/1900 11:00:02 PM', 12.00	union all
select	'2/11/2010 12:00:00 AM', '1/1/1900 12:00:01 AM', 12.00	union all
select	'2/11/2010 12:00:00 AM', '1/1/1900 1:06:37 AM',  12.00	union all
select	'2/11/2010 12:00:00 AM', '1/1/1900 2:06:48 AM',  12.00	union all
select	'2/11/2010 12:00:00 AM', '1/1/1900 3:06:54 AM',  12.00	union all
select	'2/11/2010 12:00:00 AM', '1/1/1900 5:07:09 AM',  12.00	union all
select	'2/11/2010 12:00:00 AM', '1/1/1900 6:07:16 AM',  12.00

Select DATEADD(dd,DATEDIFF(dd,0,DateAdj),1) AS Date, AVG(TAG1) AS Average
FROM
(
select DATEADD(hh,-18,DATEADD(SS,DATEDIFF(ss,0,LOGTIME),LOGDATE)) AS DateAdj,TAG1
from @sample
)t
GROUP BY DATEADD(dd,DATEDIFF(dd,0,DateAdj),1)

output
-----------------------------
Date	                Average
2010-02-11 00:00:00.000	12.000000

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Smitha

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-01 : 11:50:11

can you post what should be your output out of data i posted?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

smitha
Posting Yak Master

100 Posts

Posted - 2010-03-01 : 11:56:30

sorry, I forgot to add 1 in this place

SELECT CONVERT(CHAR(10), DATEADD(dd, DATEDIFF(dd, 0, logdate), 0), 103)

I got the result,
Thanks

quote:
Originally posted by smitha

For the following code
SELECT CONVERT(CHAR(10), DATEADD(dd, DATEDIFF(dd, 0, logdate), 0), 103) AS LOGDATE1, AVG(E1_ELE1) AS E1_ELE1_AVG
FROM (SELECT DATEADD(hh, - 24, DATEADD(SS, DATEDIFF(ss, 0, LOGTIME), LOGDATE)) AS logdate, E1_ELE1
FROM dbo.E1_SHIFTREPA) AS t
GROUP BY DATEADD(dd, DATEDIFF(dd, 0, logdate), 0)

I am getting the result as
09/02/2010 12.000000
10/02/2010 12.000000
11/02/2010 10.400000

whereas for the another code

SELECT CONVERT(CHAR(10), DATEADD(dd, DATEDIFF(dd, 0, logdate), 0), 103) AS LOGDATE1, AVG(E1_ELE1) AS E1_ELE1_AVG
FROM (SELECT DATEADD(hh, 24, DATEADD(SS, DATEDIFF(ss, 0, LOGTIME), LOGDATE)) AS logdate, E1_ELE1
FROM dbo.E1_SHIFTREPA) AS t
GROUP BY DATEADD(dd, DATEDIFF(dd, 0, logdate), 0)

I am getting the result as

11/02/2010 12.000000
12/02/2010 12.000000
13/02/2010 10.400000

but I want the dates 10/02/2010, 11/02/2010 and 12/02/2010
quote:
Originally posted by visakh16

do you mean this?

declare @sample table
(
LOGDATE datetime,
LOGTIME datetime,
TAG1 decimal(10,2)
)

insert into @sample
select '2/10/2010 12:00:00 AM', '1/1/1900 11:00:02 PM', 12.00 union all
select '2/11/2010 12:00:00 AM', '1/1/1900 12:00:01 AM', 12.00 union all
select '2/11/2010 12:00:00 AM', '1/1/1900 1:06:37 AM', 12.00 union all
select '2/11/2010 12:00:00 AM', '1/1/1900 2:06:48 AM', 12.00 union all
select '2/11/2010 12:00:00 AM', '1/1/1900 3:06:54 AM', 12.00 union all
select '2/11/2010 12:00:00 AM', '1/1/1900 5:07:09 AM', 12.00 union all
select '2/11/2010 12:00:00 AM', '1/1/1900 6:07:16 AM', 12.00

Select DATEADD(dd,DATEDIFF(dd,0,DateAdj),1) AS Date, AVG(TAG1) AS Average
FROM
(
select DATEADD(hh,-18,DATEADD(SS,DATEDIFF(ss,0,LOGTIME),LOGDATE)) AS DateAdj,TAG1
from @sample
)t
GROUP BY DATEADD(dd,DATEDIFF(dd,0,DateAdj),1)

output
-----------------------------
Date Average
2010-02-11 00:00:00.000 12.000000

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Smitha

Smitha

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources