Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


to find avg between intervel


to find avg between intervel

Author
Message
super48
super48
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 500
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
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 9017 Visits: 19028
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
super48
super48
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 500
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search