Can someone help me with a SQL script?

General
Enthusiast

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
Senior Apprentice

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
Senior Apprentice

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
Enthusiast

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.

Senior Apprentice

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
Enthusiast

Re: Can someone help me with a SQL script?

That is!  Thank you so much!!!