PLEASE HELP NEEDED? - with a select avg
-
hi all...
i'm working with sql server 2000, and this is my problem.
i have one table that consists of two columns. the first one is "state" (of a bank account on a day where there was a transaction) (type - numeric)" and the second one is "date" (days when there were transactions on that bank account) (type - datetime). it has just this values:
date time: dd-mm-yyyy
state__________ date
110__________2.2.2005
150__________3.2.2005
120__________4.2.2005
160__________17.2.2005
140__________24.2.2005
130__________3.3.2005
140__________4.3.2005
150__________8.3.2005
130__________12.3.2005
160__________18.3.2005
170__________27.3.2005
now i need to calculate an AVG of state of an account between lets say 3.2.2005 and 15.3.2005.......
but my problem lies here that i have to calculate all days in this date range....and the values of the days that r not here r for example:
4.2.2005 value for state is 120
for all days from 5.2.2005 till 16.2.2005 the value of state should be the one from 4.2.2005 (as it was the last day where there was a change)........then the same story for other days which r not dislayed in the table.......
i cant create another table not even a temporary one.
i hope u can help me.....
and thanx also for any help or hints u could give me!
P.S. ok well first the table is about a bank account. "state" (i forgot an exact english word for this) of an account on a particular date.....like on 04.02.2005 i had 120$ on my account.
my problem lies here that i have to calculate an average value of account (AVG(state)) for all days in a given date range. and i only have the values for days on which there was a transaction on that account. and for all other days i have to put the value of "state" before those days that r not mentioned in the table.
i cant create another table with dates and also with transactions, cause my task is to try to calculate an average value of state of an account in a given date range based on the values in the table.
i hope that this clears some things up.....
-
Hi,
You can create a sql statement :
select avg(state) from <table>
where date between <date1> and <date2>
--Kishore
-
Why can't you create another table? Is this an assigned homework to you?
It should be an easy task with an auxiliary date table. However, since you can't create such a table, please post sample data and your expected result so we can verify any suggestion first before posting it here.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I don't have SQL to hand to test this but
Use the DATEDIFF function to find the number of days, then your average will be SUM(STATE) / DATEDIFF(d, @startDate, @EndDate)
-
its sort of an excercise given to me. not sure if its a real example. but all the dates and values r fictional.
as this is a bank account it would be logical that an average value of column "state" for a given date range would be selected with:
select avg (state)
from mytable
where date between 'dmy' and 'dmy'
for me that would be logical. but there is a possibility that i need to calculate this average value by using all the days in a given date range. and yey it would be easier to have another table, but my boss told me that i cant create any kind of an additional table.
the table that i have written above is the table that i'm using.
-
Here's a shot in the dark:
USE NORTHWIND
SELECT
CustomerID
, COUNT(*) No_of_Orders
, DATEDIFF(d,MIN(OrderDate), MAX(OrderDate)) TimeSpan
, COUNT(*)*1.0 / DATEDIFF(d,MIN(OrderDate), MAX(OrderDate)) DailyOrder
FROM
Orders
WHERE CustomerID = 'ERNSH'
AND (OrderDate >='19960717' AND OrderDate <='19960723')
GROUP BY CustomerID
Btw, since this is an exercise given to you, don't you think you should find the solution yourself?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Another btw, did you tell your boss, that an auxiliary date table comes in handy in many situations? Curious on his reasoning here.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
well thanx for that.....the problem lies here......
ie.: on 4.2.2005 on the bank account after a transaction there was 120$........value on the acccount doesent change until 17.02.2005. the days in this date range take on the value of 4.02.2005....so it would be like this:
05.02.2005 120
06.02.2005 120
........
16.02.2005 120
so procedure should in a way recognize the gap in a date range, fill the values of those days with the value of the last day then there was a transaction prior to the date gap in the table, and then sum all this values and then devide it with the total number of days in a give date range.
-
Oops, my mistake. The time span in my example is 7! days. So:
USE NORTHWIND
SELECT
CustomerID
, COUNT(*) No_of_Orders
, DATEDIFF(d,MIN(OrderDate), MAX(OrderDate))+1 TimeSpan
, COUNT(*)*1.0 / (DATEDIFF(d,MIN(OrderDate), MAX(OrderDate))+1) DailyOrder
FROM
Orders
WHERE CustomerID = 'ERNSH'
AND (OrderDate >='19960717' AND OrderDate <='19960723')
GROUP BY CustomerID
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Okay, I now see what you mean. Keep thinking about it.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Between reading the original response and writing this answer, there have being several posts.
Now I am not sure whether I should post the solution since it was an exercise given to Strippy, rather than real world situation
I shall let the forum decide.
Using the data supplied by Strippy, and limiting to date range of 3rd Feb 2005 to 15th Mar 2005, my result set looks like
State NumberOfDaysForAvg AvgClosingBalance FirstCloseDate
110 40 120.000000 2005-02-03 00:00:00.000
120 39 140.000000 2005-02-04 00:00:00.000
130 12 177.500000 2005-03-03 00:00:00.000
140 19 171.578947 2005-02-24 00:00:00.000
150 13 162.307692 2005-03-02 00:00:00.000
160 26 150.000000 2005-02-17 00:00:00.000
(6 row(s) affected)
perhaps I need to include the 'AccountBalance' field values I used:
/* table with data for illustration */
create table BankAcctClosingBalance
( State Int
, CloseDate datetime
, CloseBal decimal(19,5)
)
go
set dateformat dmy
insert BankAcctClosingBalance
select 110, '2.2.2005', 120
union all select 150, '3.2.2003', 130
union all select 120, '4.2.2005', 140
union all select 160, '17.2.2005', 150
union all select 140, '24.2.2005', 160
union all select 130, '3.3.2005', 170
union all select 140, '4.3.2005', 180
union all select 150, '8.3.2005', 190
union all select 130, '12.3.2005 ', 200
union all select 160, '18.3.2005', 210
union all select 170, '27.3.2005', 220
go
/* end table and data */
-
the thing is i dont know if its an excercise, or not. just that the values in the table r fictional. i mean i made them up. maybe the problem that needs to be solved is real one.
the thing is i 'm learning ms sql...so i'm sort of a newbie in procedures, functions and advance sql scripts....i learnt before oracle sql, but the toughest thing that we have done we inner joins and joins of a few tables.
so i have read about procedures, but i dont know how to solve this problem.
so i woul appreciate any help that could be given to me so i could learn more thorough examples.
-
OK Strippy,
Have a good look at the following code:
Given that you cannot create any objects, not enough temp tables, will just have to utilise derived tables and subqueries.
Here is my sample table and using your sample data
/* table with data for illustration */
create table BankAcctClosingBalance
( State Int
, CloseDate datetime
, CloseBal decimal(19,5)
)
go
set dateformat dmy
insert BankAcctClosingBalance
select 110, '2.2.2005', 120
union all select 150, '3.2.2003', 130
union all select 120, '4.2.2005', 140
union all select 160, '17.2.2005', 150
union all select 140, '24.2.2005', 160
union all select 130, '3.3.2005', 170
union all select 140, '4.3.2005', 180
union all select 150, '8.3.2005', 190
union all select 130, '12.3.2005 ', 200
union all select 160, '18.3.2005', 210
union all select 170, '27.3.2005', 220
-- based on subsequent posts...
update BankAcctClosingBalance
set CloseBal = State
go
/* end table and data */
/*
Given that we need to materialise a record for each date in the range being considered, I use the master.dbo.spt_values table.
However, the way I use this there is a limit of 255 dates.
Again using your illustration we want dates from 3rd Feb 2005 to 15th Mar 2005.
*/
set dateformat ymd
declare @startdate datetime
declare @enddate datetime
declare @numofdays int
set @startdate = '2005.2.3'
set @enddate = '2005.3.15'
set @numofdays = datediff ( dd, @startdate, @enddate)
print @startdate
print @enddate
print @numofdays
if @numofdays 256
begin
print 'exceeded date range limit!. please reduce number of days covered'
end
/*
this select shows how it materialises a record for each date
*/
select BA.*
from BankAcctClosingBalance BA
join
(
select dateadd( dd, number, @startdate) as ThisDate
from master.dbo.spt_values
where type = 'P' and number < @numofdays
) DT
on BA.CloseDate = BB.CloseDate
where DT.ThisDate BA.CloseDate
)
and BB.CloseDate <=
(
select isnull( Min(CloseDate) , '2199.1.1' )
from BankAcctClosingBalance BC
where /* BC.State = BA.State
and */ BC.CloseDate <= BB.CloseDate
)
order by /* BA.State, */ BB.CloseDate, BA.CloseDate
/*
this select now calculates an average
based on the records available
The result set does not include State = 170
because the only record is outside the date range
*/
select
/* BA.State
, */ COUNT(BA.CloseDate) as NumberOfDaysForAvg
, AVG(BA.CloseBal) As AvgClosingBalance
, min(DT.ThisDate) As FirstCloseDate
from BankAcctClosingBalance BA
join
(
select dateadd( dd, number, @startdate) as ThisDate
from master.dbo.spt_values
where type = 'P' and number < @numofdays
) DT
on BA.CloseDate = BB.CloseDate
where DT.ThisDate BA.CloseDate
)
and BB.CloseDate <=
(
select isnull( Min(CloseDate) , '2199.1.1' )
from BankAcctClosingBalance BC
where /* BC.State = BA.State
and */ BC.CloseDate <= BB.CloseDate
)
/* group by BA.State
*/
/*
the results of the final select appears as:
40136.5000002005-02-03 00:00:00.000
*/
How does it work?
a) "on BA.CloseDate <= DT.ThisDate"
grab a date record for every date from the CloseDate onwards
b) "DT.ThisDate BA.CloseDate
)
"
continue to grab dates until the next CloseDate for the same state,
or if no more records (isnull) , until the end of the range
c) "
select dateadd( dd, number, @startdate) as ThisDate
from master.dbo.spt_values
where type = 'P' and number < @numofdays
"
Generate up to 256 dates, from the date start range, as a derived table
Regards
Robert
-
THANK U VERY MUCH!! i mean ofcourse i wont tell that this is my solution........just wanted to learn how to do it, but definitely this is way above my current knowledge of ms aql and sql in general....
so thank u very much again, i will definitelly study this example in thorough.
-
Sorry, I couldn't be of any help to you here.
May I add, that master.dbo.spt_values is an SQL Server internal helper table, which isn't really documented at all. You shouldn't rely on the existance of that table in some future version or service pack.
Now, what I really would like to know is, what solution your boss comes up with. If this is a test, he surely must have one to compare and benchmark.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply