

Valued Member
Group: General Forum Members
Last Login: Thursday, November 20, 2014 10:51 PM
Points: 71,
Visits: 423


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




SSCertifiable
Group: General Forum Members
Last Login: Today @ 9:43 AM
Points: 6,869,
Visits: 14,178


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




Valued Member
Group: General Forum Members
Last Login: Thursday, November 20, 2014 10:51 PM
Points: 71,
Visits: 423


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



