• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 272
  • Last Modified:

STDEV() error

Error: Column 'temp.Weekno' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
 
Can someone assist ?
SELECT Weekno, NbrOfRecords , Pull_Req_Compl_60,  Pull_Val_Compl_30 , Val_Man ,  
Man_Inv_Recvd_15 , Inv_SED_Recvd_10 ,   SED_Start_Compl_15 ,  SED_Compl_Driver_20 , Driver_Border_90 ,  
Border_Depart_15 , Trailer_Arriv , Cycle_Time_Duration, STDEV(Cycle_Time_Duration) as SD 
FROM ( 
SELECT  Weekno,   
                COUNT(CT_Key) AS NbrOfRecords ,  
                AVG(Pull_Req_Compl_60) AS Pull_Req_Compl_60,   
                AVG(Pull_Val_Compl_30 ) AS Pull_Val_Compl_30 ,   
                AVG(Val_Man) AS Val_Man ,  
                AVG(Man_Inv_Recvd_15 ) AS Man_Inv_Recvd_15 ,  
                AVG(Inv_SED_Recvd_10 ) AS Inv_SED_Recvd_10 ,    
                AVG(SED_Start_Compl_15 ) AS SED_Start_Compl_15 ,   
                AVG(SED_Compl_Driver_20 ) AS SED_Compl_Driver_20 ,    
                AVG(Driver_Border_90 ) AS Driver_Border_90 ,  
                AVG(Border_Depart_15 ) AS Border_Depart_15 ,  
                AVG(Trailer_Arriv ) AS Trailer_Arriv ,  
                AVG(Pull_Req_Compl_60) + AVG(Pull_Val_Compl_30) + AVG(Val_Man) +  AVG(Man_Inv_Recvd_15)+ AVG(Inv_SED_Recvd_10) + AVG(SED_Start_Compl_15 ) + AVG(SED_Compl_Driver_20 ) + AVG(Driver_Border_90) + AVG(Border_Depart_15 ) + AVG(Trailer_Arriv )  AS Cycle_Time_Duration 
FROM    
(    
        SELECT     
                        DATEDIFF(mi, Pull_Req_Time, Pull_Compl_Time)   
                        + CASE WHEN Pull_Req_Time > Pull_Compl_Time THEN 960 ELSE 0 END Pull_Req_Compl_60,    
                        DATEDIFF(mi,  Pull_Compl_Time, Val_Compl_Time)   
                        + CASE WHEN Pull_Compl_Time > Val_Compl_Time THEN 960 ELSE 0 END Pull_Val_Compl_30,   
                        DATEDIFF(mi, Val_Compl_Time, Manifest_Time)   
                        + CASE WHEN Val_Compl_Time > Manifest_Time THEN 960 ELSE 0 END Val_Man,   
                        DATEDIFF(mi, Manifest_Time, Invoice_Time)   
                        + CASE WHEN Manifest_Time > Invoice_Time THEN 960 ELSE 0 END Man_Inv_Recvd_15,  
            DATEDIFF(mi, Invoice_Time, SED_Start_Time)   
                        + CASE WHEN Invoice_Time > SED_Start_Time THEN 960 ELSE 0 END Inv_SED_Recvd_10,  
                        DATEDIFF(mi, SED_Start_Time, SED_Compl_Time)   
                        + CASE WHEN SED_Start_Time > SED_Compl_Time THEN 960 ELSE 0 END SED_Start_Compl_15,  
                        DATEDIFF(mi, SED_Compl_Time, Driver_Depart_Time)   
                        + CASE WHEN SED_Compl_Time > Driver_Depart_Time THEN 960 ELSE 0 END SED_Compl_Driver_20,  
                        DATEDIFF(mi, Driver_Depart_Time, Customs_Time)   
                        + CASE WHEN Driver_Depart_Time > Customs_Time THEN 960 ELSE 0 END Driver_Border_90,  
                        DATEDIFF(mi, Customs_Time, Border_Depart)   
                        + CASE WHEN Customs_Time > Border_Depart THEN 960 ELSE 0 END Border_Depart_15,  
                        DATEDIFF(mi, Border_Depart, Trailer_Arriv_Time)   
                        + CASE WHEN Border_Depart > Trailer_Arriv_Time THEN 960 ELSE 0 END Trailer_Arriv,  
        DATEPART(Week,CT_Date) AS Weekno,     
        CT_Key
	FROM Cycle_Time  where ct_key = 40  
        GROUP BY Pull_Compl_Time, Pull_Req_Time, Val_Compl_Time,Manifest_Time, Invoice_Time,SED_Start_Time, Trailer_Arriv_Time, SED_Compl_Time, Driver_Depart_Time, Customs_Time, Border_Depart, CT_Date, CT_Key, Cycle_Time  
) AA    
GROUP BY Weekno) temp

Open in new window

0
JessyRobinson1234
Asked:
JessyRobinson1234
  • 6
  • 3
  • 3
  • +2
1 Solution
 
käµfm³d 👽Commented:
I'm not sure why you have the outer select...  You're basically pulling the same columns your middle query is pulling.
It looks like line 41 does not have an aggregate function attached to it--not that I think that is your intent. You need to either aggregate this column, or more likely, add this to the inner group by clause.

Open in new window

0
 
käµfm³d 👽Commented:
Ok. that was not how I intended that post to look :\

It looks like line 41 does not have an aggregate function attached to it--not that I think that is your intent. You need to either aggregate this column, or more likely, add this to the inner group by clause.
0
 
JessyRobinson1234Author Commented:
Could you make the changes to the attached code ? I am not sure how to do it.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
käµfm³d 👽Commented:
You can try the following:
SELECT  Weekno,   
        COUNT(CT_Key) AS NbrOfRecords ,  
        AVG(Pull_Req_Compl_60) AS Pull_Req_Compl_60,   
        AVG(Pull_Val_Compl_30 ) AS Pull_Val_Compl_30 ,   
        AVG(Val_Man) AS Val_Man ,  
        AVG(Man_Inv_Recvd_15 ) AS Man_Inv_Recvd_15 ,  
        AVG(Inv_SED_Recvd_10 ) AS Inv_SED_Recvd_10 ,    
        AVG(SED_Start_Compl_15 ) AS SED_Start_Compl_15 ,   
        AVG(SED_Compl_Driver_20 ) AS SED_Compl_Driver_20 ,    
        AVG(Driver_Border_90 ) AS Driver_Border_90 ,  
        AVG(Border_Depart_15 ) AS Border_Depart_15 ,  
        AVG(Trailer_Arriv ) AS Trailer_Arriv ,  
        AVG(Pull_Req_Compl_60) + AVG(Pull_Val_Compl_30) + AVG(Val_Man) +  AVG(Man_Inv_Recvd_15)+ AVG(Inv_SED_Recvd_10) + AVG(SED_Start_Compl_15 ) + AVG(SED_Compl_Driver_20 ) + AVG(Driver_Border_90) + AVG(Border_Depart_15 ) + AVG(Trailer_Arriv )  AS Cycle_Time_Duration 
FROM (SELECT DATEDIFF(mi, Pull_Req_Time, Pull_Compl_Time)   
             + CASE WHEN Pull_Req_Time > Pull_Compl_Time THEN 960 ELSE 0 END Pull_Req_Compl_60,    
             DATEDIFF(mi,  Pull_Compl_Time, Val_Compl_Time)   
             + CASE WHEN Pull_Compl_Time > Val_Compl_Time THEN 960 ELSE 0 END Pull_Val_Compl_30,   
             DATEDIFF(mi, Val_Compl_Time, Manifest_Time)   
             + CASE WHEN Val_Compl_Time > Manifest_Time THEN 960 ELSE 0 END Val_Man,   
             DATEDIFF(mi, Manifest_Time, Invoice_Time)   
             + CASE WHEN Manifest_Time > Invoice_Time THEN 960 ELSE 0 END Man_Inv_Recvd_15,  
             DATEDIFF(mi, Invoice_Time, SED_Start_Time)   
             + CASE WHEN Invoice_Time > SED_Start_Time THEN 960 ELSE 0 END Inv_SED_Recvd_10,  
             DATEDIFF(mi, SED_Start_Time, SED_Compl_Time)   
             + CASE WHEN SED_Start_Time > SED_Compl_Time THEN 960 ELSE 0 END SED_Start_Compl_15,  
             DATEDIFF(mi, SED_Compl_Time, Driver_Depart_Time)   
             + CASE WHEN SED_Compl_Time > Driver_Depart_Time THEN 960 ELSE 0 END SED_Compl_Driver_20,  
             DATEDIFF(mi, Driver_Depart_Time, Customs_Time)   
             + CASE WHEN Driver_Depart_Time > Customs_Time THEN 960 ELSE 0 END Driver_Border_90,  
             DATEDIFF(mi, Customs_Time, Border_Depart)   
             + CASE WHEN Customs_Time > Border_Depart THEN 960 ELSE 0 END Border_Depart_15,  
             DATEDIFF(mi, Border_Depart, Trailer_Arriv_Time)   
             + CASE WHEN Border_Depart > Trailer_Arriv_Time THEN 960 ELSE 0 END Trailer_Arriv,  
             DATEPART(Week,CT_Date) AS Weekno,
             CT_Key
    FROM Cycle_Time  where ct_key = 40  
    GROUP BY Weekno, Pull_Compl_Time, Pull_Req_Time, Val_Compl_Time,Manifest_Time, Invoice_Time,SED_Start_Time, Trailer_Arriv_Time, SED_Compl_Time, Driver_Depart_Time, Customs_Time, Border_Depart, CT_Date, CT_Key, Cycle_Time  
    ) AA    
GROUP BY Weekno) temp

Open in new window

0
 
JessyRobinson1234Author Commented:
Incorrect syntax near ')'.
0
 
rushShahCommented:
hi try this..


SELECT Weekno, NbrOfRecords , Pull_Req_Compl_60,  Pull_Val_Compl_30 , Val_Man ,   
Man_Inv_Recvd_15 , Inv_SED_Recvd_10 ,   SED_Start_Compl_15 ,  SED_Compl_Driver_20 , Driver_Border_90 ,   
Border_Depart_15 , Trailer_Arriv , Cycle_Time_Duration, STDEV(Cycle_Time_Duration) as SD  
FROM (  
SELECT  Weekno,    
                COUNT(CT_Key) AS NbrOfRecords ,   
                AVG(Pull_Req_Compl_60) AS Pull_Req_Compl_60,    
                AVG(Pull_Val_Compl_30 ) AS Pull_Val_Compl_30 ,    
                AVG(Val_Man) AS Val_Man ,   
                AVG(Man_Inv_Recvd_15 ) AS Man_Inv_Recvd_15 ,   
                AVG(Inv_SED_Recvd_10 ) AS Inv_SED_Recvd_10 ,     
                AVG(SED_Start_Compl_15 ) AS SED_Start_Compl_15 ,    
                AVG(SED_Compl_Driver_20 ) AS SED_Compl_Driver_20 ,     
                AVG(Driver_Border_90 ) AS Driver_Border_90 ,   
                AVG(Border_Depart_15 ) AS Border_Depart_15 ,   
                AVG(Trailer_Arriv ) AS Trailer_Arriv ,   
                AVG(Pull_Req_Compl_60) + AVG(Pull_Val_Compl_30) + AVG(Val_Man) +  AVG(Man_Inv_Recvd_15)+ AVG(Inv_SED_Recvd_10) + AVG(SED_Start_Compl_15 ) + AVG(SED_Compl_Driver_20 ) + AVG(Driver_Border_90) + AVG(Border_Depart_15 ) + AVG(Trailer_Arriv )  AS Cycle_Time_Duration  
FROM     
(     
        SELECT      
                        DATEDIFF(mi, Pull_Req_Time, Pull_Compl_Time)    
                        + CASE WHEN Pull_Req_Time > Pull_Compl_Time THEN 960 ELSE 0 END Pull_Req_Compl_60,     
                        DATEDIFF(mi,  Pull_Compl_Time, Val_Compl_Time)    
                        + CASE WHEN Pull_Compl_Time > Val_Compl_Time THEN 960 ELSE 0 END Pull_Val_Compl_30,    
                        DATEDIFF(mi, Val_Compl_Time, Manifest_Time)    
                        + CASE WHEN Val_Compl_Time > Manifest_Time THEN 960 ELSE 0 END Val_Man,    
                        DATEDIFF(mi, Manifest_Time, Invoice_Time)    
                        + CASE WHEN Manifest_Time > Invoice_Time THEN 960 ELSE 0 END Man_Inv_Recvd_15,   
            DATEDIFF(mi, Invoice_Time, SED_Start_Time)    
                        + CASE WHEN Invoice_Time > SED_Start_Time THEN 960 ELSE 0 END Inv_SED_Recvd_10,   
                        DATEDIFF(mi, SED_Start_Time, SED_Compl_Time)    
                        + CASE WHEN SED_Start_Time > SED_Compl_Time THEN 960 ELSE 0 END SED_Start_Compl_15,   
                        DATEDIFF(mi, SED_Compl_Time, Driver_Depart_Time)    
                        + CASE WHEN SED_Compl_Time > Driver_Depart_Time THEN 960 ELSE 0 END SED_Compl_Driver_20,   
                        DATEDIFF(mi, Driver_Depart_Time, Customs_Time)    
                        + CASE WHEN Driver_Depart_Time > Customs_Time THEN 960 ELSE 0 END Driver_Border_90,   
                        DATEDIFF(mi, Customs_Time, Border_Depart)    
                        + CASE WHEN Customs_Time > Border_Depart THEN 960 ELSE 0 END Border_Depart_15,   
                        DATEDIFF(mi, Border_Depart, Trailer_Arriv_Time)    
                        + CASE WHEN Border_Depart > Trailer_Arriv_Time THEN 960 ELSE 0 END Trailer_Arriv,   
        DATEPART(Week,CT_Date) AS Weekno,      
        CT_Key 
        FROM Cycle_Time  where ct_key = 40   
        GROUP BY Pull_Compl_Time, Pull_Req_Time, Val_Compl_Time,Manifest_Time, Invoice_Time,SED_Start_Time, Trailer_Arriv_Time, SED_Compl_Time, Driver_Depart_Time, Customs_Time, Border_Depart, CT_Date, CT_Key, Cycle_Time   
) AA     
GROUP BY Weekno) temp
GROUP BY temp.Weekno, temp.NbrOfRecords , temp.Pull_Req_Compl_60,  temp.Pull_Val_Compl_30 , temp.Val_Man ,   
temp.Man_Inv_Recvd_15 , temp.Inv_SED_Recvd_10 ,   temp.SED_Start_Compl_15 ,  temp.SED_Compl_Driver_20 
, temp.Driver_Border_90 ,  temp.Border_Depart_15 , temp.Trailer_Arriv , temp.Cycle_Time_Duration

Open in new window

0
 
JessyRobinson1234Author Commented:
That gave me no error but the SD returns NULL for all of the records.
0
 
ThomasianCommented:
Try this
SELECT Weekno, NbrOfRecords , Pull_Req_Compl_60,  Pull_Val_Compl_30 , Val_Man ,  
Man_Inv_Recvd_15 , Inv_SED_Recvd_10 ,   SED_Start_Compl_15 ,  SED_Compl_Driver_20 , Driver_Border_90 ,  
Border_Depart_15 , Trailer_Arriv , Cycle_Time_Duration, STDEV(Cycle_Time_Duration) OVER () as SD 
FROM ( 
SELECT  Weekno,   
                COUNT(CT_Key) AS NbrOfRecords ,  
                AVG(Pull_Req_Compl_60) AS Pull_Req_Compl_60,   
                AVG(Pull_Val_Compl_30 ) AS Pull_Val_Compl_30 ,   
                AVG(Val_Man) AS Val_Man ,  
                AVG(Man_Inv_Recvd_15 ) AS Man_Inv_Recvd_15 ,  
                AVG(Inv_SED_Recvd_10 ) AS Inv_SED_Recvd_10 ,    
                AVG(SED_Start_Compl_15 ) AS SED_Start_Compl_15 ,   
                AVG(SED_Compl_Driver_20 ) AS SED_Compl_Driver_20 ,    
                AVG(Driver_Border_90 ) AS Driver_Border_90 ,  
                AVG(Border_Depart_15 ) AS Border_Depart_15 ,  
                AVG(Trailer_Arriv ) AS Trailer_Arriv ,  
                AVG(Pull_Req_Compl_60) + AVG(Pull_Val_Compl_30) + AVG(Val_Man) +  AVG(Man_Inv_Recvd_15)+ AVG(Inv_SED_Recvd_10) + AVG(SED_Start_Compl_15 ) + AVG(SED_Compl_Driver_20 ) + AVG(Driver_Border_90) + AVG(Border_Depart_15 ) + AVG(Trailer_Arriv )  AS Cycle_Time_Duration 
FROM    
(    
        SELECT     
                        DATEDIFF(mi, Pull_Req_Time, Pull_Compl_Time)   
                        + CASE WHEN Pull_Req_Time > Pull_Compl_Time THEN 960 ELSE 0 END Pull_Req_Compl_60,    
                        DATEDIFF(mi,  Pull_Compl_Time, Val_Compl_Time)   
                        + CASE WHEN Pull_Compl_Time > Val_Compl_Time THEN 960 ELSE 0 END Pull_Val_Compl_30,   
                        DATEDIFF(mi, Val_Compl_Time, Manifest_Time)   
                        + CASE WHEN Val_Compl_Time > Manifest_Time THEN 960 ELSE 0 END Val_Man,   
                        DATEDIFF(mi, Manifest_Time, Invoice_Time)   
                        + CASE WHEN Manifest_Time > Invoice_Time THEN 960 ELSE 0 END Man_Inv_Recvd_15,  
            DATEDIFF(mi, Invoice_Time, SED_Start_Time)   
                        + CASE WHEN Invoice_Time > SED_Start_Time THEN 960 ELSE 0 END Inv_SED_Recvd_10,  
                        DATEDIFF(mi, SED_Start_Time, SED_Compl_Time)   
                        + CASE WHEN SED_Start_Time > SED_Compl_Time THEN 960 ELSE 0 END SED_Start_Compl_15,  
                        DATEDIFF(mi, SED_Compl_Time, Driver_Depart_Time)   
                        + CASE WHEN SED_Compl_Time > Driver_Depart_Time THEN 960 ELSE 0 END SED_Compl_Driver_20,  
                        DATEDIFF(mi, Driver_Depart_Time, Customs_Time)   
                        + CASE WHEN Driver_Depart_Time > Customs_Time THEN 960 ELSE 0 END Driver_Border_90,  
                        DATEDIFF(mi, Customs_Time, Border_Depart)   
                        + CASE WHEN Customs_Time > Border_Depart THEN 960 ELSE 0 END Border_Depart_15,  
                        DATEDIFF(mi, Border_Depart, Trailer_Arriv_Time)   
                        + CASE WHEN Border_Depart > Trailer_Arriv_Time THEN 960 ELSE 0 END Trailer_Arriv,  
        DATEPART(Week,CT_Date) AS Weekno,     
        CT_Key
	FROM Cycle_Time  where ct_key = 40  
        GROUP BY Pull_Compl_Time, Pull_Req_Time, Val_Compl_Time,Manifest_Time, Invoice_Time,SED_Start_Time, Trailer_Arriv_Time, SED_Compl_Time, Driver_Depart_Time, Customs_Time, Border_Depart, CT_Date, CT_Key, Cycle_Time  
) AA    
GROUP BY Weekno) temp

Open in new window

0
 
JessyRobinson1234Author Commented:
when I remove ct_key = 40 I get this for SD.

They are all the same values:

283.668057184214
283.668057184214
283.668057184214
283.668057184214
283.668057184214
283.668057184214
283.668057184214
283.668057184214
283.668057184214
283.668057184214
0
 
ThomasianCommented:
Do you want to get the standard deviation for the same ct_key?


Try
SELECT Weekno, NbrOfRecords , Pull_Req_Compl_60,  Pull_Val_Compl_30 , Val_Man ,  
Man_Inv_Recvd_15 , Inv_SED_Recvd_10 ,   SED_Start_Compl_15 ,  SED_Compl_Driver_20 , Driver_Border_90 ,  
Border_Depart_15 , Trailer_Arriv , Cycle_Time_Duration, STDEV(Cycle_Time_Duration) OVER (PARTITION BY CT_Key) as SD
FROM ( 
SELECT  Weekno,   
                COUNT(CT_Key) AS NbrOfRecords ,  
                AVG(Pull_Req_Compl_60) AS Pull_Req_Compl_60,   
                AVG(Pull_Val_Compl_30 ) AS Pull_Val_Compl_30 ,   
                AVG(Val_Man) AS Val_Man ,  
                AVG(Man_Inv_Recvd_15 ) AS Man_Inv_Recvd_15 ,  
                AVG(Inv_SED_Recvd_10 ) AS Inv_SED_Recvd_10 ,    
                AVG(SED_Start_Compl_15 ) AS SED_Start_Compl_15 ,   
                AVG(SED_Compl_Driver_20 ) AS SED_Compl_Driver_20 ,    
                AVG(Driver_Border_90 ) AS Driver_Border_90 ,  
                AVG(Border_Depart_15 ) AS Border_Depart_15 ,  
                AVG(Trailer_Arriv ) AS Trailer_Arriv ,  
                AVG(Pull_Req_Compl_60) + AVG(Pull_Val_Compl_30) + AVG(Val_Man) +  AVG(Man_Inv_Recvd_15)+ AVG(Inv_SED_Recvd_10) + AVG(SED_Start_Compl_15 ) + AVG(SED_Compl_Driver_20 ) + AVG(Driver_Border_90) + AVG(Border_Depart_15 ) + AVG(Trailer_Arriv )  AS Cycle_Time_Duration 
FROM    
(    
        SELECT     
                        DATEDIFF(mi, Pull_Req_Time, Pull_Compl_Time)   
                        + CASE WHEN Pull_Req_Time > Pull_Compl_Time THEN 960 ELSE 0 END Pull_Req_Compl_60,    
                        DATEDIFF(mi,  Pull_Compl_Time, Val_Compl_Time)   
                        + CASE WHEN Pull_Compl_Time > Val_Compl_Time THEN 960 ELSE 0 END Pull_Val_Compl_30,   
                        DATEDIFF(mi, Val_Compl_Time, Manifest_Time)   
                        + CASE WHEN Val_Compl_Time > Manifest_Time THEN 960 ELSE 0 END Val_Man,   
                        DATEDIFF(mi, Manifest_Time, Invoice_Time)   
                        + CASE WHEN Manifest_Time > Invoice_Time THEN 960 ELSE 0 END Man_Inv_Recvd_15,  
            DATEDIFF(mi, Invoice_Time, SED_Start_Time)   
                        + CASE WHEN Invoice_Time > SED_Start_Time THEN 960 ELSE 0 END Inv_SED_Recvd_10,  
                        DATEDIFF(mi, SED_Start_Time, SED_Compl_Time)   
                        + CASE WHEN SED_Start_Time > SED_Compl_Time THEN 960 ELSE 0 END SED_Start_Compl_15,  
                        DATEDIFF(mi, SED_Compl_Time, Driver_Depart_Time)   
                        + CASE WHEN SED_Compl_Time > Driver_Depart_Time THEN 960 ELSE 0 END SED_Compl_Driver_20,  
                        DATEDIFF(mi, Driver_Depart_Time, Customs_Time)   
                        + CASE WHEN Driver_Depart_Time > Customs_Time THEN 960 ELSE 0 END Driver_Border_90,  
                        DATEDIFF(mi, Customs_Time, Border_Depart)   
                        + CASE WHEN Customs_Time > Border_Depart THEN 960 ELSE 0 END Border_Depart_15,  
                        DATEDIFF(mi, Border_Depart, Trailer_Arriv_Time)   
                        + CASE WHEN Border_Depart > Trailer_Arriv_Time THEN 960 ELSE 0 END Trailer_Arriv,  
        DATEPART(Week,CT_Date) AS Weekno,     
        CT_Key
	FROM Cycle_Time  where ct_key = 40  
        GROUP BY Pull_Compl_Time, Pull_Req_Time, Val_Compl_Time,Manifest_Time, Invoice_Time,SED_Start_Time, Trailer_Arriv_Time, SED_Compl_Time, Driver_Depart_Time, Customs_Time, Border_Depart, CT_Date, CT_Key, Cycle_Time  
) AA    
GROUP BY Weekno) temp

Open in new window

0
 
LowfatspreadCommented:
can you explain what you are trying to achieve, and give the background to the rows on the table....

e.g why are you grouping by all the dates to start off with?
    doesn't that reduce the variance (IF ANY) that exists at that level?

and why use 960... since you either have 960 or 0 wouldn't you expect the values to be the same?
0
 
JessyRobinson1234Author Commented:
Thomas, sorry about that. No, I was just trying it for one record. I want the SD for each week.
0
 
ThomasianCommented:
Do you want to get the SD of the sum of the fields used to compute Cycle_Time_Duration?

Try if this is what you want.
SELECT Weekno, NbrOfRecords , Pull_Req_Compl_60,  Pull_Val_Compl_30 , Val_Man ,  
Man_Inv_Recvd_15 , Inv_SED_Recvd_10 ,   SED_Start_Compl_15 ,  SED_Compl_Driver_20 , Driver_Border_90 ,  
Border_Depart_15 , Trailer_Arriv , Cycle_Time_Duration, SD
FROM ( 
SELECT  Weekno,   
                COUNT(CT_Key) AS NbrOfRecords ,  
                AVG(Pull_Req_Compl_60) AS Pull_Req_Compl_60,   
                AVG(Pull_Val_Compl_30 ) AS Pull_Val_Compl_30 ,   
                AVG(Val_Man) AS Val_Man ,  
                AVG(Man_Inv_Recvd_15 ) AS Man_Inv_Recvd_15 ,  
                AVG(Inv_SED_Recvd_10 ) AS Inv_SED_Recvd_10 ,    
                AVG(SED_Start_Compl_15 ) AS SED_Start_Compl_15 ,   
                AVG(SED_Compl_Driver_20 ) AS SED_Compl_Driver_20 ,    
                AVG(Driver_Border_90 ) AS Driver_Border_90 ,  
                AVG(Border_Depart_15 ) AS Border_Depart_15 ,  
                AVG(Trailer_Arriv ) AS Trailer_Arriv ,  
                AVG(Pull_Req_Compl_60) + AVG(Pull_Val_Compl_30) + AVG(Val_Man) +  AVG(Man_Inv_Recvd_15)+ AVG(Inv_SED_Recvd_10) + AVG(SED_Start_Compl_15 ) + AVG(SED_Compl_Driver_20 ) + AVG(Driver_Border_90) + AVG(Border_Depart_15 ) + AVG(Trailer_Arriv )  AS Cycle_Time_Duration,
                STDEV(Pull_Req_Compl_60+Pull_Val_Compl_30+Val_Man+Man_Inv_Recvd_15+Inv_SED_Recvd_10+SED_Start_Compl_15+SED_Compl_Driver_20+Driver_Border_90+Border_Depart_15+Trailer_Arriv)  AS SD
FROM    
(    
        SELECT     
                        DATEDIFF(mi, Pull_Req_Time, Pull_Compl_Time)   
                        + CASE WHEN Pull_Req_Time > Pull_Compl_Time THEN 960 ELSE 0 END Pull_Req_Compl_60,    
                        DATEDIFF(mi,  Pull_Compl_Time, Val_Compl_Time)   
                        + CASE WHEN Pull_Compl_Time > Val_Compl_Time THEN 960 ELSE 0 END Pull_Val_Compl_30,   
                        DATEDIFF(mi, Val_Compl_Time, Manifest_Time)   
                        + CASE WHEN Val_Compl_Time > Manifest_Time THEN 960 ELSE 0 END Val_Man,   
                        DATEDIFF(mi, Manifest_Time, Invoice_Time)   
                        + CASE WHEN Manifest_Time > Invoice_Time THEN 960 ELSE 0 END Man_Inv_Recvd_15,  
            DATEDIFF(mi, Invoice_Time, SED_Start_Time)   
                        + CASE WHEN Invoice_Time > SED_Start_Time THEN 960 ELSE 0 END Inv_SED_Recvd_10,  
                        DATEDIFF(mi, SED_Start_Time, SED_Compl_Time)   
                        + CASE WHEN SED_Start_Time > SED_Compl_Time THEN 960 ELSE 0 END SED_Start_Compl_15,  
                        DATEDIFF(mi, SED_Compl_Time, Driver_Depart_Time)   
                        + CASE WHEN SED_Compl_Time > Driver_Depart_Time THEN 960 ELSE 0 END SED_Compl_Driver_20,  
                        DATEDIFF(mi, Driver_Depart_Time, Customs_Time)   
                        + CASE WHEN Driver_Depart_Time > Customs_Time THEN 960 ELSE 0 END Driver_Border_90,  
                        DATEDIFF(mi, Customs_Time, Border_Depart)   
                        + CASE WHEN Customs_Time > Border_Depart THEN 960 ELSE 0 END Border_Depart_15,  
                        DATEDIFF(mi, Border_Depart, Trailer_Arriv_Time)   
                        + CASE WHEN Border_Depart > Trailer_Arriv_Time THEN 960 ELSE 0 END Trailer_Arriv,  
        DATEPART(Week,CT_Date) AS Weekno,     
        CT_Key
	FROM Cycle_Time  where ct_key = 40  
        GROUP BY Pull_Compl_Time, Pull_Req_Time, Val_Compl_Time,Manifest_Time, Invoice_Time,SED_Start_Time, Trailer_Arriv_Time, SED_Compl_Time, Driver_Depart_Time, Customs_Time, Border_Depart, CT_Date, CT_Key, Cycle_Time  
) AA    
GROUP BY Weekno) temp

Open in new window

0
 
JessyRobinson1234Author Commented:
Thomas, I think you just did it! Let me double check the values one last time.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 6
  • 3
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now