2 days 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 2005 Forums

Transact-SQL (2005)

2 days data

Author

Topic

smitha
Posting Yak Master

100 Posts

Posted - 2010-01-29 : 06:37:06

Hi,
I am using select statement as given below to take tag1 data from table1 for today's date from 11 to 12 pm. Now if I want to continue to take data from tommorrows 1 am to 2 am, how I should add the date.

select logdate, logtime, tag1 from table1
WHERE(DATEPART(HOUR, LOGTIME) IN (22, 23))
GROUP BY LOGDATE, DATEADD(HOUR, DATEDIFF(HOUR, 0, LOGTIME) + 1, 0), CONVERT(CHAR(10), LOGDATE, 103)

and I am Grouping because I am taking the average value of tag1 for that time intervals.

Smitha

smitha
Posting Yak Master

100 Posts

Posted - 2010-01-29 : 06:39:16

sorry I am missed this
select logdate, logtime, avg(tag1)as tag1_avg from table1
WHERE(DATEPART(HOUR, LOGTIME) IN (22, 23))
GROUP BY LOGDATE, DATEADD(HOUR, DATEDIFF(HOUR, 0, LOGTIME) + 1, 0), CONVERT(CHAR(10), LOGDATE, 103)

Smitha

mymatrix
Starting Member

24 Posts

Posted - 2010-01-29 : 07:13:08

Smitha why you need to look for 22 if you have to get data between (23:00 to 23:59)?

select logdate, logtime, avg(tag1)as tag1_avg from table1
WHERE(DATEPART(HOUR, LOGTIME) IN (1, 23))
GROUP BY LOGDATE, DATEADD(HOUR, DATEDIFF(HOUR, 0, LOGTIME) + 1, 0), CONVERT(CHAR(10), LOGDATE, 103)

I guess this should work for both (23:00-23:59 PM and 01:00 am to 01:59 PM)

thnks
Gaurav

Even my blood group says be -ve to all the negatives.

smitha
Posting Yak Master

100 Posts

Posted - 2010-01-29 : 07:18:50

I am taking the tag1 average values for all the time between
22 to 23,
23 to 24,
1 to 2,
2 to 3

Smitha

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-30 : 01:02:49

so are you trying to group on hours and get aggregated data for each group?

smitha
Posting Yak Master

100 Posts

Posted - 2010-01-30 : 01:49:48

your guess is correct

quote:
Originally posted by visakh16

so are you trying to group on hours and get aggregated data for each group?

Smitha

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-30 : 01:59:45

so for case like time part is 23:00 you include it in slot 22-23 or 23-24?

smitha
Posting Yak Master

100 Posts

Posted - 2010-01-30 : 02:35:49

OK, for today's data I will take upto midnight 24:00. Meanwhile I have to take the next day data i.e., from 00:00 to 1:00 and so on.
So, how do I club these data in a single select statement

Smitha

smitha
Posting Yak Master

100 Posts

Posted - 2010-02-02 : 00:05:46

Is there anyone who can solve this problem

Smitha

Sachin.Nand

2937 Posts

Posted - 2010-02-02 : 00:59:34

declare @tbl as table(typ int,enddt datetime,value int )

insert into @tbl

select 1,'2009-08-12 08:01:00.000',1 union all

select 2,'2009-08-12 09:02:00.000',2 union all

select 3,'2009-08-12 09:40:00.000',34 union all

select 4,'2009-08-12 10:14:00.000',5 union all

select 5,'2009-08-12 10:25:00.000',10 union all

select 6,'2009-08-13 08:12:00.000',12 union all

select 7,'2009-08-13 08:20:00.000',18

select

convert(datetime, convert(varchar(14), enddt, 120) +

replace(str((datepart(minute, enddt) / 60) * 60, 2), ' ', '0'), 120)as startslot,
DATEADD(hh,1, convert(datetime, convert(varchar(14), enddt, 120) +

replace(str((datepart(minute, enddt) / 60) * 60, 2), ' ', '0'), 120)) as endslot,

sum(value)as Sum

from @tbl

group by

convert(datetime, convert(varchar(14), enddt, 120) +

replace(str((datepart(minute, enddt) / 60) * 60, 2), ' ', '0'), 120)

PBUH

mymatrix
Starting Member

24 Posts

Posted - 2010-02-02 : 01:32:54

Can you please try this...

select convert(LOGDATE as varchar(10)) + convert(DATEPART(HOUR, LOGTIME) as varchar(2)), avg(tag1)as tag1_avg from table1
WHERE(DATEPART(HOUR, LOGTIME) IN (1,2,22,23))
GROUP BY convert(LOGDATE as varchar(10)) + convert(DATEPART(HOUR, LOGTIME) as varchar(2))

This can be improved further if it satisfies your requirement.

thnks
Gaurav

Even my blood group says be -ve to all the negatives.

smitha
Posting Yak Master

100 Posts

Posted - 2010-02-06 : 05:23:44

I tried both the methods. But I am not getting results.
My requirement is simple I want to club the data for time between 10pm to 11pm, 11pm to 12am, 12am to 1am, 1am to 2am, 2am to 3am.Thus 2 day data has to get combined.

Smitha

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-06 : 06:43:18

so how should be your output?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources