| Author |
 Topic  |
|
|
anaji
Starting Member
Kuwait
15 Posts |
 Posted - 06/07/2012 : 03:50:19
|
I need to modify this query below so that I can get the average injected water. The column ,[INJ_WATER] is stored hourly and I want the query to return just a single value which represent the average for the day.
SELECT CONVERT(varchar(20),[Date_Time],101) AS DATE_TIME
,[INJ_WATER] As INJ_WATER
,cast([INJ_WATER]*[EWDB].[dbo].[FO_GET_LAST_LAYER_PERCENT]([Date_Time],[CID],'W1')/100 as numeric(20,2)) As W1
,cast([INJ_WATER]*[EWDB].[dbo].[FO_GET_LAST_LAYER_PERCENT]([Date_Time],[CID],'W2')/100 as numeric(20,2)) As W2
,cast([INJ_WATER]*[EWDB].[dbo].[FO_GET_LAST_LAYER_PERCENT]([Date_Time],[CID],'W3')/100 as numeric(20,2)) As W3
,cast([INJ_WATER]*[EWDB].[dbo].[FO_GET_LAST_LAYER_PERCENT]([Date_Time],[CID],'W4')/100 as numeric(20,2)) As W4
,cast([INJ_WATER]*[EWDB].[dbo].[FO_GET_LAST_LAYER_PERCENT]([Date_Time],[CID],'W5')/100 as numeric(20,2)) As W5
,cast([INJ_WATER]*[EWDB].[dbo].[FO_GET_LAST_LAYER_PERCENT]([Date_Time],[CID],'W6')/100 as numeric(20,2)) As W6
,cast([INJ_WATER]*[EWDB].[dbo].[FO_GET_LAST_LAYER_PERCENT]([Date_Time],[CID],'W7')/100 as numeric(20,2)) As W7
FROM [EWDB].[dbo].[VO_Daily_Injection]
WHERE [CID] = 'EP_PROPERTY(EP_TEMPLATE(AVOCETVM COMPLETION TEMPLATE)$COMPLETION - NAME)'
AND CONVERT(varchar(20),[Date_Time],101) = CONVERT(varchar(20),EP_TIME-1,101)
the current output i'm getting is like this
06/7/2012 322413
06/7/2012 332453
06/7/2012 234535
06/7/2012 345355
06/7/2012 345355
The calculation is made on the hourly basis what i need is to get the average of the enitre day as 1 value only to display
thanks, |
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 06/07/2012 : 04:05:59
|
It looks like yoou shoould get the date followed by 8 more columns - not the 1 that yoou say you are getting.
What do yoou want to average?
Maybe put group by CONVERT(varchar(20),[Date_Time],101) at the end then avg around all other columns.
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
 |
|
|
anaji
Starting Member
Kuwait
15 Posts |
Posted - 06/07/2012 : 04:18:28
|
nigel,
yes you are right i do get 8 columes from this query, so the first colum is date, the second is the INJ_Water which i want to average, so in other words i want it to show me 1 date and the average of all the values that are in the INJ_Water, to try to explain more, i get a valuse calculated hourly so at the end of the day instead of having multiple values hourly i want to get just 1 average for the whole day |
|
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 06/07/2012 : 05:12:39
|
still not sure
maybe as a start
SELECT CONVERT(varchar(20),[Date_Time],101) AS DATE_TIME
,avg([INJ_WATER]) As INJ_WATER
WHERE [CID] = 'EP_PROPERTY(EP_TEMPLATE(AVOCETVM COMPLETION TEMPLATE)$COMPLETION - NAME)'
AND CONVERT(varchar(20),[Date_Time],101) = CONVERT(varchar(20),EP_TIME-1,101)
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
|
|
|
anaji
Starting Member
Kuwait
15 Posts |
Posted - 06/07/2012 : 06:08:07
|
Nigel,
thanks the avg and group by actually worked it was very straight forward, i guess after writing alot of complex quiries you forget that simple straigh ones work as well, appreciate your help |
|
|
| |
Topic |
|
|
|
calculating average
