Need Help - Running Totals and Standarad Deviation

Analytics
Enthusiast

Need Help - Running Totals and Standarad Deviation

Need help in calculating Cummilative(last column) for the dataset in the below table and Stanadard deviation on PICK_TIME.

 

Prod_IdLOAD_DATEOrder_ReadyOrder_PickedPICK_TIMERANK_PRD_IDDIFF_IN_SECSDaysCUMMULATIVE
1237/30/20178/2/2017 04:35:32.4709158/2/2017 04:42:09.0380005:00:001396.56708500
1247/30/20178/2/2017 04:26:00.3883038/2/2017 04:31:22.8930005:00:001322.50469700
1258/1/20178/1/2017 10:33:25.11175412/31/9999 00:00:00.0000000:00:001-467990.540.54
1257/31/20178/1/2017 10:33:25.11175412/31/9999 00:00:00.0000000:00:0028640011.54
1257/30/20178/1/2017 10:33:25.11175412/31/9999 00:00:00.0000000:00:0038640012.54
1267/31/20178/1/2017 10:38:05.36442312/31/9999 00:00:00.0000000:00:001-467990.540.54
1267/30/20178/1/2017 10:38:05.36442312/31/9999 00:00:00.0000000:00:0028640011.54
1277/30/201712/31/9999 00:00:00.00000012/31/9999 00:00:00.0000000:00:001-467990.540.54
1287/30/20178/2/2017 03:18:32.17077112/31/9999 00:00:00.0000000:00:001-467990.540.54

 

 

Thanks in advance


Accepted Solutions
Supporter

Re: Need Help - Running Totals and Standarad Deviation

Hi vPrashanth99,

 

Below query should solve your requirement. You didnt specify how do you want to get your std deviation so I took over Prod_id. But you can modify it accordingly by adding a window based on your requirement.

SELECT
  PROD_ID
, LOAD_DATE
, ORDER_READY
, ORDER_PICKED
, PICK_TIME
, RANK_PRD_ID
, DIFF_IN_SECS
, SUM(DAYS) OVER (PARTITION BY PROD_ID ORDER BY RANK_PRD_ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CUMULATIVE
, STDDEV_SAMP(DAYS) OVER (PARTITION BY PROD_ID) AS STD_DEV
FROM
  YOUR_TABLE
ORDER BY PROD_ID, RANK_PRD_ID;

Hope it helps.

 

Thanks,

Rohan Sawant

1 ACCEPTED SOLUTION
1 REPLY
Supporter

Re: Need Help - Running Totals and Standarad Deviation

Hi vPrashanth99,

 

Below query should solve your requirement. You didnt specify how do you want to get your std deviation so I took over Prod_id. But you can modify it accordingly by adding a window based on your requirement.

SELECT
  PROD_ID
, LOAD_DATE
, ORDER_READY
, ORDER_PICKED
, PICK_TIME
, RANK_PRD_ID
, DIFF_IN_SECS
, SUM(DAYS) OVER (PARTITION BY PROD_ID ORDER BY RANK_PRD_ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CUMULATIVE
, STDDEV_SAMP(DAYS) OVER (PARTITION BY PROD_ID) AS STD_DEV
FROM
  YOUR_TABLE
ORDER BY PROD_ID, RANK_PRD_ID;

Hope it helps.

 

Thanks,

Rohan Sawant