July 27, 2011 at 1:44 pm
I have a table that contains customer IDs, interval, and total balance at that interval. Customer report any change in their balances at different times of the day between 8am-5pm. I need to calculate average balance of all customers at each minutes of the day. To make things simpler, lets say we have 5 intervals from 1-5.
Our original data looks like this:
declare @data table
(custId varchar,
interval int,
bal int);
insert into @data (custId, interval, bal) values ('A', 1, 50);
insert into @data (custId, interval, bal) values ('A', 3, 70);
insert into @data (custId, interval, bal) values ('B', 2, 5);
insert into @data (custId, interval, bal) values ('C', 1, 10);
insert into @data (custId, interval, bal) values ('C', 5, 2);
custIdintervalbal
A 1 50
A 3 70
B 2 5
C 1 10
C 5 2
And the average the above dataset produces is:
select interval, AVG(bal) avgBal from @data group by interval order by interval;
intervalavgBal
1 30
2 5
3 70
5 2
But I want to make this data look like this:
custIdintervalbal
A150
A250
A370
A470
A570
B15
B25
B35
B45
B55
C110
C210
C310
C410
C52
And average must look like this:
intervalavgBal
121
221
328
428
525
I'm using SQL Server 2008. I really appreciate any help I can get!!!
July 27, 2011 at 1:50 pm
The closest thing I can find in Oracle is by using partitioned outer join, but nothing in SQL Server.
July 31, 2011 at 1:50 pm
A bit cludgy, but it works
;WITH cteInterval(CustID, Interval, Bal)
AS (
SELECTc.CustID,
i.Interval,
COALESCE(p.Bal, n.Bal) AS Bal
FROM(
SELECTCustID
FROM@Data
GROUP BYCustID
) AS c
CROSS JOIN(
SELECTNumber AS Interval
FROMmaster..spt_values
WHEREType = 'P'
AND Number BETWEEN (SELECT MIN(Interval) FROM @Data) AND (SELECT MAX(Interval) FROM @Data)
) AS i
OUTER APPLY(
SELECT TOP(1)p.Bal
FROM@Data AS p
WHEREp.CustID = c.CustID
AND p.Interval <= i.Interval
ORDER BYp.Interval DESC
) AS p
OUTER APPLY(
SELECT TOP(1)n.Bal
FROM@Data AS n
WHEREn.CustID = c.CustID
AND n.Interval > i.Interval
ORDER BYn.Interval
) AS n
)
SELECTInterval,
AVG(Bal) AS avgBal
FROMcteInterval
GROUP BYInterval
ORDER BYInterval
N 56°04'39.16"
E 12°55'05.25"
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply