Can someone help me with a SQL script?

General

Can someone help me with a SQL script?

I posted before and did not provide enough detail and am not able to edit the post so though that I would try again.  I am only showing relavent columns of data that are in the Teradata table.

 

I am wondering if there is a way to write a script that would look at individual rows of data to caculate services levels for me.  I can do this in Excel with a formula (TAT column is a formula which is Ship_Dt-Recd_Dt) * Shp_cards ) for each row of data and then sum up that result and divide it by the sum of Shp_cards to come up with a weighted service level.  I hope that I am providing enough detail this time.  Maybe there isn't a way but thought that I would ask the professionals :-)

 

Recd_DtShip_DtShp_cardsTAT
9/13/20169/17/20161352
9/13/20169/14/20165252
9/13/20169/13/2016100
9/13/20169/18/201631155
9/13/20169/15/2016229458
9/13/20169/15/2016141282
9/13/20169/14/20167474
9/13/20169/17/20161456
9/13/20169/20/201616112
9/13/20169/19/201632192
9/13/20169/15/20163978
   2.32

Accepted Solutions

Re: Can someone help me with a SQL script?

Is this really what you want?

SELECT SUM(CAST((ship_dt - recd_dt ) * shp_cards AS FLOAT)) / SUM(shp_cards ) AS weighted_avg2
FROM vt1;

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
1 ACCEPTED SOLUTION
4 REPLIES

Re: Can someone help me with a SQL script?

Hi,

 

Try this:

SELECT a.*
   ,(ship_dt - recd_dt ) * shp_cards AS tat
   ,SUM(CAST(tat AS FLOAT)) OVER() / SUM(shp_cards ) OVER() AS weighted_avg2
FROM vt1 AS a;

Note that this will put the weighted average value on each row of the output (but that is SQL).

 

Cheers,

Dave

 

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com

Re: Can someone help me with a SQL script?

Thanks Dave.  I wish there was someway to have just the result of 1.43 and not all of the rows that the calculations were based off of.  Thanks for your help.

Re: Can someone help me with a SQL script?

Is this really what you want?

SELECT SUM(CAST((ship_dt - recd_dt ) * shp_cards AS FLOAT)) / SUM(shp_cards ) AS weighted_avg2
FROM vt1;

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com

Re: Can someone help me with a SQL script?

That is!  Thank you so much!!!