Skip to Content

Sales order query help

Hi experts,

In the below query I need a output as 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'

and also In where condition have to remove if 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'=zero have to remove that zero entries.

If Qty-Sum(Actqty)=zero we have to remove that zero row entries how to acheive that query

Query

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • author's profile photo Former Member
    Former Member
    Posted on Dec 11, 2015 at 09:59 AM

    way too many udf's and parameters man!!!!!

    Can you check this one to see if this gives results??

    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.number+T3.U_SST_Nr_Act_BAQTY.number+T3.U_SSt_Nr_Act_CourQTY.number+T3.U_SST_Nr_Act_SeaQTY.number) as 'SumQty',

    ((T2.Quantity)-sum(T3.U_SST_Nr_Act_AirQTY.number+T3.U_SST_Nr_Act_BAQTY.number+T3.U_SSt_Nr_Act_CourQTY.number+T3.U_SST_Nr_Act_SeaQTY.number)) 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' ) and

    ((T2.Quantity)-sum(T3.U_SST_Nr_Act_AirQTY.number+T3.U_SST_Nr_Act_BAQTY.number+T3.U_SSt_Nr_Act_CourQTY.number+T3.U_SST_Nr_Act_SeaQTY.number)) <> '0.00'

    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

    P.S.

    You may have to add isnull condition..

    Thanks,

    Joseph

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.