March 14, 2014 at 2:49 pm
Here's a good puzzle that I need some help figuring out.
I'm trying to calculate DSL bandwidth data per session. The problem that I'm running into is the session records are somewhat inconsistent. In some cases the [Acct_Input_Oct] and [Acct_Output_Oct] records only aggregate to a certain point and then reset. The expected behavior would be that all bandwidth related records would aggregate during a single session and the final record would have total amounts.
Here's a prime example of a session where the [Acct_Session_Time] is properly aggregating with each subsequent record. However...the [Acct_Input_Oct] and [Acct_Output_Oct] records seem to reset when they hit a threshold around 4200000000.
How can I isolate the max value for each of these occurrences and tally them up? For instance...the following example I would need to tally up 4136265117 + 4130418311 + 2145151703 for the [Acct_Input_Oct] records and 3864574247 + 3840090147 + 4281523421 + 636351437 for the [Acct_Output_Oct] records.
CREATE TABLE #Bandwidth
(
Acct_Session_ID varchar(20),
SessionDate datetime,
Acct_Session_Time varchar(20),
Acct_Status_Type varchar(10),
Acct_Input_Oct varchar(20),
Acct_Output_Oct varchar(20)
)
INSERT INTO #Bandwidth (Acct_Session_ID, SessionDate, Acct_Session_Time, Acct_Status_Type, Acct_Input_Oct, Acct_Output_Oct)
SELECT '00364AEB','2014-02-01 15:49:53.990','0','Start','','' UNION ALL
SELECT '00364AEB','2014-02-01 16:50:00.530','3600','Alive','246582360','12043318' UNION ALL
SELECT '00364AEB','2014-02-01 17:50:40.037','7238','Alive','493476555','24316713' UNION ALL
SELECT '00364AEB','2014-02-01 18:53:48.447','11027','Alive','775263844','40007889' UNION ALL
SELECT '00364AEB','2014-02-01 19:52:58.490','14577','Alive','1024798960','182299006' UNION ALL
SELECT '00364AEB','2014-02-01 20:54:26.020','18264','Alive','1285039654','389103574' UNION ALL
SELECT '00364AEB','2014-02-01 21:51:35.793','21694','Alive','1531287548','411767495' UNION ALL
SELECT '00364AEB','2014-02-01 22:54:26.187','25464','Alive','1797704746','432573219' UNION ALL
SELECT '00364AEB','2014-02-01 23:52:03.617','28922','Alive','2044452422','449089961' UNION ALL
SELECT '00364AEB','2014-02-02 00:53:24.557','32603','Alive','2311032980','468360795' UNION ALL
SELECT '00364AEB','2014-02-02 01:50:31.920','36030','Alive','2558596665','486050303' UNION ALL
SELECT '00364AEB','2014-02-02 02:53:08.017','39787','Alive','2831874036','506707689' UNION ALL
SELECT '00364AEB','2014-02-02 03:50:01.140','43200','Alive','3032526762','520861091' UNION ALL
SELECT '00364AEB','2014-02-02 04:53:59.420','47039','Alive','3189466664','533979246' UNION ALL
SELECT '00364AEB','2014-02-02 05:52:02.547','50522','Alive','3412517118','548608225' UNION ALL
SELECT '00364AEB','2014-02-02 06:52:09.770','54129','Alive','3652798353','563799852' UNION ALL
SELECT '00364AEB','2014-02-02 07:51:59.980','57719','Alive','3908768488','1237165485' UNION ALL
SELECT '00364AEB','2014-02-02 08:52:09.160','61329','Alive','4136265117','3864574247' UNION ALL
SELECT '00364AEB','2014-02-02 09:50:05.770','64805','Alive','80838955','875229508' UNION ALL
SELECT '00364AEB','2014-02-02 10:51:34.217','68494','Alive','334481122','2457619572' UNION ALL
SELECT '00364AEB','2014-02-02 11:52:39.020','72159','Alive','567991200','3790107317' UNION ALL
SELECT '00364AEB','2014-02-02 12:53:23.230','75804','Alive','829746846','3810858779' UNION ALL
SELECT '00364AEB','2014-02-02 13:54:21.707','79462','Alive','1095530954','3840090147' UNION ALL
SELECT '00364AEB','2014-02-02 14:49:59.830','82800','Alive','1310512408','1805067065' UNION ALL
SELECT '00364AEB','2014-02-02 15:50:00.990','86402','Alive','1552045482','4281523421' UNION ALL
SELECT '00364AEB','2014-02-02 16:51:22.467','90083','Alive','1822168456','13319806' UNION ALL
SELECT '00364AEB','2014-02-02 17:53:25.870','93807','Alive','2077382240','38096642' UNION ALL
SELECT '00364AEB','2014-02-02 18:52:36.467','97357','Alive','2337106571','73646832' UNION ALL
SELECT '00364AEB','2014-02-02 19:52:59.643','100980','Alive','2581339461','108499106' UNION ALL
SELECT '00364AEB','2014-02-02 20:51:06.797','104468','Alive','2838516130','124914192' UNION ALL
SELECT '00364AEB','2014-02-02 21:50:43.320','108045','Alive','3106493632','143725906' UNION ALL
SELECT '00364AEB','2014-02-02 22:52:28.690','111750','Alive','3379480582','161388021' UNION ALL
SELECT '00364AEB','2014-02-02 23:51:33.790','115296','Alive','3620703406','177123103' UNION ALL
SELECT '00364AEB','2014-02-03 00:51:16.217','118878','Alive','3872972943','194814492' UNION ALL
SELECT '00364AEB','2014-02-03 01:51:37.770','122500','Alive','4130418311','212369682' UNION ALL
SELECT '00364AEB','2014-02-03 02:49:57.867','126000','Alive','71187710','228117122' UNION ALL
SELECT '00364AEB','2014-02-03 03:52:15.687','129738','Alive','344274918','247671359' UNION ALL
SELECT '00364AEB','2014-02-03 04:52:52.600','133375','Alive','616305838','269365385' UNION ALL
SELECT '00364AEB','2014-02-03 05:51:03.633','136866','Alive','871903082','361028360' UNION ALL
SELECT '00364AEB','2014-02-03 06:52:03.703','140527','Alive','1137257420','389758900' UNION ALL
SELECT '00364AEB','2014-02-03 07:52:31.587','144154','Alive','1375808644','473274835' UNION ALL
SELECT '00364AEB','2014-02-03 08:52:48.717','147772','Alive','1599611164','538380124' UNION ALL
SELECT '00364AEB','2014-02-03 09:53:41.960','151425','Alive','1841909429','604883634' UNION ALL
SELECT '00364AEB','2014-02-03 10:52:08.867','154932','Alive','2085905197','622415547' UNION ALL
SELECT '00364AEB','2014-02-03 11:08:49.727','155933','Stop','2145151703','636351437'
SELECT * FROM #Bandwidth
DROP TABLE #Bandwidth
March 14, 2014 at 10:12 pm
Nice job on posting readily consumable data!
I believe the following is one way of doing it for you. It keeps things down to just two scans of the table and prevents the "aggregate ignored null" warnings.
WITH
cteEnumerate AS
(
SELECT RowNum = ROW_NUMBER() OVER (PARTITION BY Acct_Session_ID ORDER BY SessionDate)
,Acct_Session_ID
,Acct_Status_Type
,Acct_Input_Oct = CAST(Acct_Input_Oct AS BIGINT)
,Acct_Output_Oct = CAST(Acct_Output_Oct AS BIGINT)
FROM #Bandwidth
)
SELECT lo.Acct_Session_ID
,Acct_Input_Oct =
SUM(
CASE
WHEN lo.Acct_Status_Type = 'Alive' AND lo.Acct_Input_Oct > hi.Acct_Input_Oct
THEN lo.Acct_Input_Oct
WHEN hi.Acct_Status_Type = 'Stop'
THEN hi.Acct_Input_Oct
ELSE 0
END
)
,Acct_Output_Oct =
SUM(
CASE
WHEN lo.Acct_Status_Type = 'Alive' AND lo.Acct_Output_Oct > hi.Acct_Output_Oct
THEN lo.Acct_Output_Oct
WHEN hi.Acct_Status_Type = 'Stop'
THEN hi.Acct_Output_Oct
ELSE 0
END
)
FROM cteEnumerate lo
JOIN cteEnumerate hi
ON lo.Acct_Session_ID = hi.Acct_Session_ID AND lo.RowNum = hi.RowNum - 1
GROUP BY lo.Acct_Session_ID
;
--Jeff Moden
Change is inevitable... Change for the better is not.
March 17, 2014 at 3:46 pm
Thanks Jeff...! I think this might work.
March 21, 2014 at 12:36 pm
I ran into another issue. Here's an example of a session that started in the previous month and continues all the way through to the end of the month without interruption.
The first issue is that the [Acct_Input_Oct] never reaches the ~4200000000 threshold...so the counter never resets. The second problem would be that the grand total of [Acct_Input_Oct] and [Acct_Output_Oct] will be skewed since the first record has data from the the previous month.
So...In addition to my previous example...I would need to report on the last occurrence of [Acct_Input_Oct] and also subtract the incoming [Acct_Input_Oct] and [Acct_Output_Oct] amounts that are included in the first record.
CREATE TABLE #Bandwidth
(
Acct_Session_ID VARCHAR(20),
SessionDate DATETIME,
Acct_Session_Time VARCHAR(20),
Acct_Status_Type VARCHAR(10),
Acct_Input_Oct VARCHAR(20),
Acct_Output_Oct VARCHAR(20)
)
INSERT INTO #Bandwidth (Acct_Session_ID, SessionDate, Acct_Session_Time, Acct_Status_Type, Acct_Input_Oct, Acct_Output_Oct)
SELECT '00343EC9','2014-01-31 23:59:38.593','255794','Alive','317147055','2260844624' UNION ALL
SELECT '00343EC9','2014-02-02 07:59:51.591','371002','Alive','388875711','3999842493' UNION ALL
SELECT '00343EC9','2014-02-02 09:59:20.520','378172','Alive','405216918','416039040' UNION ALL
SELECT '00343EC9','2014-02-02 11:58:48.848','385340','Alive','423116393','1127804674' UNION ALL
SELECT '00343EC9','2014-02-02 16:01:03.013','399876','Alive','468201276','2912366101' UNION ALL
SELECT '00343EC9','2014-02-02 18:00:43.043','407055','Alive','489398876','3793062725' UNION ALL
SELECT '00343EC9','2014-02-02 18:59:24.924','410577','Alive','508728505','286642515' UNION ALL
SELECT '00343EC9','2014-02-02 19:58:02.582','414094','Alive','511311917','337869203' UNION ALL
SELECT '00343EC9','2014-02-03 13:58:36.836','478931','Alive','561380520','1465932174' UNION ALL
SELECT '00343EC9','2014-02-04 13:58:34.834','565325','Alive','642268863','4259989966' UNION ALL
SELECT '00343EC9','2014-02-04 14:56:54.654','568825','Alive','648784030','168966866' UNION ALL
SELECT '00343EC9','2014-02-05 16:57:55.755','662489','Alive','691083226','1175366530' UNION ALL
SELECT '00343EC9','2014-02-06 14:57:08.578','741639','Alive','736069755','2553561828' UNION ALL
SELECT '00343EC9','2014-02-07 14:57:04.574','828039','Alive','779710701','4023387931' UNION ALL
SELECT '00343EC9','2014-02-07 15:56:26.626','831600','Alive','787432958','53921613' UNION ALL
SELECT '00343EC9','2014-02-07 17:00:52.052','835467','Alive','797213591','270525049' UNION ALL
SELECT '00343EC9','2014-02-08 10:59:33.933','900190','Alive','830638327','1069384576' UNION ALL
SELECT '00343EC9','2014-02-09 08:57:10.710','979242','Alive','901731751','3223283582' UNION ALL
SELECT '00343EC9','2014-02-09 13:57:02.572','997234','Alive','935414481','4198051399' UNION ALL
SELECT '00343EC9','2014-02-09 14:56:55.655','1000827','Alive','941901460','164096334' UNION ALL
SELECT '00343EC9','2014-02-09 19:56:31.631','1018805','Alive','957171897','797372669' UNION ALL
SELECT '00343EC9','2014-02-09 20:58:27.827','1022521','Alive','967591979','1265093113' UNION ALL
SELECT '00343EC9','2014-02-10 14:59:48.948','1087404','Alive','998596034','2336086337' UNION ALL
SELECT '00343EC9','2014-02-10 18:59:55.955','1101811','Alive','1044150670','4200578866' UNION ALL
SELECT '00343EC9','2014-02-10 19:59:14.914','1105370','Alive','1048802641','104773419' UNION ALL
SELECT '00343EC9','2014-02-11 11:57:51.751','1162882','Alive','1069944053','342188767' UNION ALL
SELECT '00343EC9','2014-02-12 11:58:53.853','1249348','Alive','1145428920','2974647990' UNION ALL
SELECT '00343EC9','2014-02-17 13:56:42.642','1256417','Alive','1163177164','3835697764' UNION ALL
SELECT '00343EC9','2014-02-22 14:56:25.625','1260000','Alive','1174657040','140106339' UNION ALL
SELECT '00343EC9','2014-02-28 23:58:46.846','1274542','Alive','1200578220','1156001317'
GO
WITH
cteEnumerate AS
(
SELECT RowNum = ROW_NUMBER() OVER (PARTITION BY Acct_Session_ID ORDER BY SessionDate)
,Acct_Session_ID
,Acct_Status_Type
,Acct_Input_Oct = CAST(Acct_Input_Oct AS BIGINT) --/1048576.00
,Acct_Output_Oct = CAST(Acct_Output_Oct AS BIGINT) --/1048576.00
FROM #Bandwidth
)
SELECT low.Acct_Session_ID
,Acct_Input_Oct =
SUM(
CASE
WHEN low.Acct_Status_Type = 'Alive' AND low.Acct_Input_Oct > high.Acct_Input_Oct
THEN low.Acct_Input_Oct
WHEN high.Acct_Status_Type = 'Stop'
THEN high.Acct_Input_Oct
ELSE 0
END
)
,Acct_Output_Oct =
SUM(
CASE
WHEN low.Acct_Status_Type = 'Alive' AND low.Acct_Output_Oct > high.Acct_Output_Oct
THEN low.Acct_Output_Oct
WHEN high.Acct_Status_Type = 'Stop'
THEN high.Acct_Output_Oct
ELSE 0
END
)
FROM cteEnumerate low
JOIN cteEnumerate high
ON low.Acct_Session_ID = high.Acct_Session_ID AND low.RowNum = high.RowNum - 1
GROUP BY low.Acct_Session_ID
DROP TABLE #Bandwidth
March 24, 2014 at 1:15 pm
I still can't figure this one out. Anyone else wanna take a stab at it?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply