Click here to Skip to main content
16,021,226 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,
In the below query I need a output as Quantity-
SQL
Sum(T3.U_SST_Nr_Act_AirQTY+T3.U_SST_Nr_Act_BAQTY+T3.U_SSt_Nr_Act_CourQTY+T3.U_SST_Nr_Act_SeaQTY) as 'ACTQTY'

and also In where condition have to remove if quantity-
SQL
sum(Sum(T3.U_SST_Nr_Act_AirQTY+T3.U_SST_Nr_Act_BAQTY+T3.U_SSt_Nr_Act_CourQTY+T3.U_SST_Nr_Act_SeaQTY) as 'ACTQTY'

If Qty-Sum(Actqty)=zero we have to remove that zero row entries how to acheive that query
SQL
SELECT T3.[DocNum] AS 'Document Number', T3.[CardCode] AS 'Customer/Supplier No.', T3.[CardName] AS 'Customer/Supplier Name', T2.[ItemCode] AS 'Item No.', 

T2.[Quantity] AS 'Quantity',
(T3.U_SST_Nr_Act_AirQTY+T3.U_SST_Nr_Act_BAQTY+T3.U_SSt_Nr_Act_CourQTY+T3.U_SST_Nr_Act_SeaQTY) as 'SumQty',
((T2.Quantity)-sum(T3.U_SST_Nr_Act_AirQTY+T3.U_SST_Nr_Act_BAQTY+T3.U_SSt_Nr_Act_CourQTY+T3.U_SST_Nr_Act_SeaQTY)) AS 'ACTQTY',
 
 T2.[U_ALP_DateETA] AS 'ETA', T3.[DocDueDate] AS 'Value Date', T3.[DocDate] AS 'Posting Date', T3.[U_DateArtwkappCUST] AS 'DtArtwCusAppr', T3.[U_DateProdctionApp] AS 'DateAppProd', T3.[U_ETA_UK_arrival] AS 'ETA_UK_arrival', T3.[U_Cutting_start] AS 'Cutting_start', T3.[U_CuttingStartACT] AS 'CuttingStartACT', T3.[U_CuttingEndACT] AS 'CuttingEndACT', T3.[U_Print_start] AS 'Print_start', T3.[U_PrintStartACT] AS 'PrintStartACT', T3.[U_PrintEndACT] AS 'PrintEndACT', 

T3.[U_Ex_factory] AS 'Predicted Factory Exit', T3.[U_ExFactoryACT] AS 'ExFactoryACT', T2.[U_ALP_DespatchMethod] AS 'Despatch Method', T3.[U_ETAUKArrivalACT] AS 'ETAUKArrivalACT',
 T3.[U_ALP_TrackRef] AS 'Tracking Reference',  T3.[U_SST_Nr_ACT] AS 'SST_Nr_ACT', T3.[U_SST_Nr_Act_Air] AS 'SST_Nr_Act_Air',
   T3.[U_SST_Nr_Act_AirQTY] AS 'SST_Nr_Act_AirQTY',
 T3.[U_SST_Nr_Act_BA] AS 'SST_Nr_Act_BA', T3.[U_SST_Nr_Act_BAQTY] AS 'SST_Nr_Act_BAQTY', 
 T3.[U_SST_Nr_Act_Sea] AS 'SST_Nr_Act_Sea', T3.[U_SST_Nr_Act_SeaQTY] AS 'SST_Nr_Act_SeaQTY',
 T3.[U_SSt_Nr_Act_Cour] AS 'SSt_Nr_Act_Cour', T3.[U_SSt_Nr_Act_CourQTY] AS 'SSt_Nr_Act_CourQTY',
  T3.[U_Fusing_foil] AS 'Fusing_foil', T3.[U_NO_Fabric_1] AS 'NoFabric1',
 T3.[U_NoSwing_tag] AS 'NoSwing_tag', T3.[U_No_Closure] AS 'No_Closure', T3.[U_No_GENERALPRINT] AS 'No_GENERALPRINT', T3.[U_No_LiningTrimming] AS 'No_LiningTrimming', T3.[U_No_Thread] AS 'No Thread', T3.[U_No_buckle] AS 'No_buckle', T3.[U_No_button] AS 'No_button', T3.[U_No_carton_pkg] AS 'No_carton_pkg', T3.[U_No_exposing1] AS 'No_exposing1', T3.[U_No_label] AS 'No_label', T3.[U_No_pigment1] AS 'No_pigment1', T3.[U_No_plasticol1] AS 'No_plasticol1', T3.[U_No_polybag] AS 'No_polybag', T3.[U_No_zip] AS 'No_zip', T3.[SlpCode] AS 'Sales Employee', T3.[DocStatus] AS 'Document Status', T3.[U_Red_clock] AS 'Red Clock', T3.[U_Green_Flag] AS 'Green Flag', T2.[U_PPS_photo_datereq] AS 'Sample Photo Req', T3.[U_PPSPhotoETA] AS 'Sample Photo ETA', T3.[U_PP_submit] AS 'Photo Sample Submit', T3. [U_PP_approved] AS 'Photo Sample Approved', 
 T2.[U_PPSsample_reqdate] AS 'Physical Sample Req', T3.[U_PPSSampleETA] AS 'Physical Sample ETA', 
 T3.[U_PPPhysical_submit] AS 'Physical Sample Submit', T3. [U_PPPhysical_apprvd] AS 'Physical Sample Approved',
  T2.[Dscription] AS 'Description' 
 FROM  [dbo].[OITB] T0  INNER  JOIN [dbo].[OITM] T1  ON  T1.[ItmsGrpCod] = T0.[ItmsGrpCod] 
   INNER  JOIN [dbo].[RDR1] T2  ON  T2.[ItemCode] = T1.[ItemCode]  AND  T2.[ItemCode] = T1.[ItemCode] 
     INNER  JOIN [dbo].[ORDR] T3  ON  T3.[DocEntry] = T2.[DocEntry]
      WHERE T3.[DocStatus] = (N'O' )  

 group by T3.[DocNum] , T3.[CardCode] , T3.[CardName] , T2.[ItemCode],  
T2.[Quantity] , T2.[U_ALP_DateETA] , T3.[DocDueDate] , T3.[DocDate], T3.[U_DateArtwkappCUST] ,
 T3.[U_DateProdctionApp] , T3.[U_ETA_UK_arrival], T3.[U_Cutting_start] , T3.[U_CuttingStartACT],
  T3.[U_CuttingEndACT] , T3.[U_Print_start] , T3.[U_PrintStartACT], T3.[U_PrintEndACT] , 
T3.[U_Ex_factory], T3.[U_ExFactoryACT] ,
 T2.[U_ALP_DespatchMethod] , T3.[U_ETAUKArrivalACT] ,
 T3.[U_ALP_TrackRef] ,t3.U_sst_NR_ACT,t3.u_SST_Nr_Act_Air,t3.u_sst_Nr_ACT_BA,t3.u_sst_Nr_Act_sea,
 t3.u_sst_Nr_Act_cour,T3.[U_Fusing_foil] , T3.[U_NO_Fabric_1] ,
 T3.[U_NoSwing_tag] , T3.[U_No_Closure] , T3.[U_No_GENERALPRINT] , T3.[U_No_LiningTrimming] , 
 T3.[U_No_Thread] , T3.[U_No_buckle] , T3.[U_No_button] ,
  T3.[U_No_carton_pkg] , T3.[U_No_exposing1] ,
   T3.[U_No_label] , T3.[U_No_pigment1] ,
    T3.[U_No_plasticol1] , T3.[U_No_polybag] ,
     T3.[U_No_zip] , T3.[SlpCode] , T3.[DocStatus] ,
      T3.[U_Red_clock] , T3.[U_Green_Flag] , 
      T2.[U_PPS_photo_datereq] , T3.[U_PPSPhotoETA] , 
      T3.[U_PP_submit] , T3. [U_PP_approved] , 
 T2.[U_PPSsample_reqdate] , T3.[U_PPSSampleETA] , 
 T3.[U_PPPhysical_submit] , T3. [U_PPPhysical_apprvd] ,
  T2.[Dscription],T3.[U_SST_Nr_Act_AirQTY],T3.U_SST_Nr_Act_BAQTY,T3.U_SSt_Nr_Act_CourQTY,T3.U_SST_Nr_Act_SeaQTY
Posted
Updated 10-Dec-15 20:31pm
v2
Comments
Suvendu Shekhar Giri 11-Dec-15 4:21am    
Hard to read your question.
Herman<T>.Instance 11-Dec-15 8:20am    
You have a group by, so the HAVING statement could be used for your Qty-Sum(Actqty)=zero
John C Rayan 11-Dec-15 11:32am    
I would suggest be more clearer by improving your question and more importantly format your query nicely making it more readable if you need our help.
CHill60 12-Dec-15 11:57am    
That is an awful group by clause. If you are only including all those items in the Group By so that you can have them in the final results, then use a CTE to get the results you need then join the CTE to the original table(s) to get all the other columns

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900