Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 to find avg between intervel Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, August 13, 2013 12:44 AM
 Valued Member Group: General Forum Members Last Login: Monday, December 09, 2013 11:00 PM Points: 52, Visits: 291
Post #1483584
 Posted Tuesday, August 13, 2013 2:11 AM
 SSCertifiable Group: General Forum Members Last Login: Today @ 10:10 AM Points: 6,286, Visits: 12,104
 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()) ) tCROSS 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 temp56CROSS APPLY ( SELECT DateGroup = CASE WHEN @typ IN (15,30,60) THEN DATEADD(MINUTE,DATEDIFF(MINUTE,0,DateAndTime)/@typ*@typ,0) ELSE DateAndTime END) xGROUP BY x.DateGroupORDER 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 ShawFor 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 ModenExploring Recursive CTEs by Example Dwain Camps
Post #1483601
 Posted Wednesday, August 14, 2013 3:48 AM
 Valued Member Group: General Forum Members Last Login: Monday, December 09, 2013 11:00 PM Points: 52, Visits: 291
 thank you very muchyes your query is shorter and faster than main?there is another problem? I want result to be like thistime 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 replythank you once again
Post #1484160

 Permissions