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

JessyRobinson1234Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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 your problem seen by more experts

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

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JessyRobinson1234Author Commented:
Thomas, I think you just did it! Let me double check the values one last time.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.