August 13, 2013 at 12:44 am
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
August 13, 2013 at 2:11 am
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
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
August 14, 2013 at 3:48 am
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply