Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

to find avg between intervel Expand / Collapse
Author
Message
Posted Tuesday, August 13, 2013 12:44 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 11:53 PM
Points: 68, Visits: 393
I had requirement to find avg of values between intervals like 15,30,60
I had written query browsing some internet fortunately I got results right bu I want know how datedif(min,0,datetime)/60*60 works please reply

ALTER procedure [dbo].[sale]
@st datetime,
@typ int
as
begin
create table temp56 (DateAndTime datetime, kwh float, PF float,LLAVG float,LNAVG float,[avg CURRENT] float,frequency float )
create table tempFIN (DateAndTime datetime, KWH float, PF float,LLAVG float,LNAVG float,AVGCURRENT float,FREQ float )
begin
insert into temp56
select a.DateAndTime as dateandtime,a.Val as kwh,b.val as PF,c.val as LLAVG,d.val as LNAVG ,e.val AS [avg CURRENT],f.Val AS frequency from
( select dateandtime ,val from dbo.FloatTable where tagindex=0 and dateandtime>=@st and dateandtime<=dateadd(dd,1,@st))as a join
(select dateandtime ,val from dbo.FloatTable where tagindex=1 and dateandtime>=@st and dateandtime<=dateadd(dd,1,@st))as b on(a.DateAndTime=b.DateAndTime) join
(select dateandtime ,val from dbo.FloatTable where tagindex=2 and dateandtime>=@st and dateandtime<=dateadd(dd,1,@st))as c on(b.DateAndTime=c.DateAndTime) join
(select dateandtime ,val from dbo.FloatTable where tagindex=3 and dateandtime>=@st and dateandtime<=dateadd(dd,1,@st))as d on(c.DateAndTime=d.DateAndTime) join
(select dateandtime ,val from dbo.FloatTable where tagindex=4 and dateandtime>=@st and dateandtime<=dateadd(dd,1,@st))as e on(d.DateAndTime=e.DateAndTime) join
(select dateandtime ,val from dbo.FloatTable where tagindex=5 and dateandtime>=@st and dateandtime<=dateadd(dd,1,@st))as f on(e.DateAndTime=f.DateAndTime)
end
if(@typ=15)
begin
INSERT INTO tempFIN
select dateadd(minute,datediff(minute,0,dateandtime)/15*15,0) as t,AVG(kwh) as KWH,AVG(PF)AS PF,AVG(LLAVG)AS LLAVG,AVG(LNAVG) AS LNAVG,AVG([AVG CURRENT])AS AVGCURRENT,AVG(frequency)AS FREQ from temp56
GROUP BY dateadd(minute,datediff(minute,0,dateandtime)/15*15,0)
order by t
end
else if(@typ=30)
begin
INSERT INTO tempFIN
select dateadd(minute,datediff(minute,0,dateandtime)/30*30,0) as t,AVG(kwh) as KWH,AVG(PF)AS PF,AVG(LLAVG)AS LLAVG,AVG(LNAVG) AS LNAVG,AVG([AVG CURRENT])AS AVGCURRENT,AVG(frequency)AS FREQ from temp56
GROUP BY dateadd(minute,datediff(minute,0,dateandtime)/30*30,0)
order by t
end
else if(@typ=60)
begin
INSERT INTO tempFIN
select dateadd(minute,datediff(minute,0,dateandtime)/60*60,0) as t,AVG(kwh) as KWH,AVG(PF)AS PF,AVG(LLAVG)AS LLAVG,AVG(LNAVG) AS LNAVG,AVG([AVG CURRENT])AS AVGCURRENT,AVG(frequency)AS FREQ from temp56
GROUP BY dateadd(minute,datediff(minute,0,dateandtime)/60*60,0)
end
else
begiN
INSERT INTO tempFIN
select * from temp56
end
select * from tempFIN
order by DateAndTime
drop table tempFIN
drop table temp56
end
Post #1483584
Posted Tuesday, August 13, 2013 2:11 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:12 AM
Points: 6,783, Visits: 12,893
Run this query and study the results. If you are still unsure then ask:
SELECT 
DateAndTime,
AbsoluteMinutes, -- minutes since 19000101.
AbsoluteMinutes/60.00, -- INT divided by DECIMAL: decimal fraction retained.
AbsoluteMinutes/60, -- INT divided by INT: decimal fraction truncated.
(AbsoluteMinutes/60)*60, -- round down to nearest hour boundary. Brackets for clarity.
RoundedDateAndTime = dateadd(minute,AbsoluteMinutes/60*60,0)
FROM ( -- some sample data
SELECT DateAndTime = GETDATE() UNION ALL
SELECT DateAndTime = DATEADD(minute,10,GETDATE()) UNION ALL
SELECT DateAndTime = DATEADD(minute,20,GETDATE()) UNION ALL
SELECT DateAndTime = DATEADD(minute,30,GETDATE()) UNION ALL
SELECT DateAndTime = DATEADD(minute,40,GETDATE()) UNION ALL
SELECT DateAndTime = DATEADD(minute,50,GETDATE()) UNION ALL
SELECT DateAndTime = DATEADD(minute,60,GETDATE()) UNION ALL
SELECT DateAndTime = DATEADD(minute,70,GETDATE()) UNION ALL
SELECT DateAndTime = DATEADD(minute,80,GETDATE()) UNION ALL
SELECT DateAndTime = DATEADD(minute,90,GETDATE()) UNION ALL
SELECT DateAndTime = DATEADD(minute,100,GETDATE()) UNION ALL
SELECT DateAndTime = DATEADD(minute,110,GETDATE()) UNION ALL
SELECT DateAndTime = DATEADD(minute,120,GETDATE())
) t
CROSS APPLY (SELECT AbsoluteMinutes = datediff(minute,0,DateAndTime)) x

Your query has much scope for improvement. Try this - if the figures are out, it shouldn't take much effort to tweak:

;WITH temp56 AS (
SELECT
DateAndTime,
kwh = SUM(CASE WHEN tagindex = 0 THEN Val ELSE 0 END),
PF = SUM(CASE WHEN tagindex = 1 THEN Val ELSE 0 END),
LLAVG = SUM(CASE WHEN tagindex = 2 THEN Val ELSE 0 END),
LNAVG = SUM(CASE WHEN tagindex = 3 THEN Val ELSE 0 END),
[avg CURRENT] = SUM(CASE WHEN tagindex = 4 THEN Val ELSE 0 END),
frequency = SUM(CASE WHEN tagindex = 5 THEN Val ELSE 0 END)
FROM dbo.FloatTable
WHERE tagindex BETWEEN 0 AND 5
AND dateandtime >= @st
AND dateandtime <= DATEADD(dd,1,@st)
GROUP BY DateAndTime
)

SELECT
DateAndTime = x.DateGroup,
KWH = AVG(kwh),
PF = AVG(PF),
LLAVG = AVG(LLAVG),
LNAVG = AVG(LNAVG),
AVGCURRENT = AVG([AVG CURRENT]),
FREQ = AVG(frequency)
FROM temp56
CROSS APPLY (
SELECT DateGroup = CASE
WHEN @typ IN (15,30,60) THEN DATEADD(MINUTE,DATEDIFF(MINUTE,0,DateAndTime)/@typ*@typ,0)
ELSE DateAndTime END
) x
GROUP BY x.DateGroup
ORDER BY x.DateGroup



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1483601
Posted Wednesday, August 14, 2013 3:48 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 11:53 PM
Points: 68, Visits: 393
thank you very much
yes your query is shorter and faster than main?
there is another problem?
I want result to be like this
time avg of(val)
11:15:00 34.5(it should include avg between 11 to 11:15)
11:30:00 43.6(it should include avg between 11:15 to 11:30)

that means time should start from 11.15 in first column it should contain avg of val between 11 to 11:15?......
1.how do preferred CTE in your query ? how to know which one works better than which ?is there any link that you can share?
I ALWAYS get confused while starting a new reporting project about using the CTE or temp table using case or if statement ? please reply
thank you once again

Post #1484160
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse