Have Geek Brain

Using a group clause in a stored procedure

I have a stored procedure where iam gets InvoiceValue as one field. I have different account values ​​corresponding to 1 file. I want to get the sum of the account values ​​corresponding to each file. How can I use the group here? here is my stored procedure

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[rptGGM]
(
    @FromDate varchar(50),
    @ToDate varchar(50),
    @PartyID int,
    @WID int

)

As
DECLARE @WhereStr VarChar(800)

SET @FromDate = LTRIM(RTRIM(@FromDate)) 
SET @ToDate = LTRIM(RTRIM(@ToDate))

SET @WhereStr = 
    CASE @WID
        WHEN 23 THEN 'WHERE G.PartyID='+LTRIM(str(@PartyID)) +' AND ((D.ATASea >='''+@FromDate+''' And D.ATASea<='''+@ToDate+'''))'
        WHEN 7 THEN 'WHERE G.PartyID='+LTRIM(str(@PartyID)) +' AND ((D.ATASea >='''+@FromDate+''' And D.ATASea<='''+@ToDate+''')) AND  A.WorkID='+LTRIM(str(@WID))
        WHEN 6 THEN 'WHERE G.PartyID='+LTRIM(str(@PartyID)) +' AND ((D.ATASea >='''+@FromDate+''' And D.ATASea<='''+@ToDate+''')) AND  A.WorkID='+LTRIM(str(@WID))
        WHEN 11 THEN 'WHERE G.PartyID='+LTRIM(str(@PartyID)) +' AND ((D.ATASea >='''+@FromDate+''' And D.ATASea<='''+@ToDate+''')) AND  A.WorkID='+LTRIM(str(@WID))
        WHEN 12 THEN 'WHERE G.PartyID='+LTRIM(str(@PartyID)) +' AND ((D.ATASea >='''+@FromDate+''' And D.ATASea<='''+@ToDate+'''))  AND  A.WorkID='+LTRIM(str(@WID))

    END


BEGIN
exec

('SELECT 
         A.FileNumber As [File_No],
         H.IDFNumber as [IDF No],
         H.IDFRegNo as [TZDAR],
         F.POString As [PO_Numbers],
         B.PartyName As [Exporter Name],
         F.SIString As [Supplier Invoices],
         C.CargoDesc As [Cargo_Description],
         dbo.PackCntDetails(A.FileID) As [Pk/Cnt_Details],
         D.VesselName As [Vessel Name],
         D.VoyageNo As [Voyage No],
         L.Amount As [Invoice Value],
         convert(varchar, Dbo.ActualDate(A.ETA), 103) + '' '' + convert(varchar, Dbo.ActualDate(A.ETA), 108) As ETA,
         convert(varchar,Dbo.ActualDate(D.ATASea),103) + '' '' + convert(varchar, Dbo.ActualDate(D.ATASea), 108) As ATA,
         convert(varchar,Dbo.ActualDate(H.PCVRIssuedDate),103) + '' '' + convert(varchar, Dbo.ActualDate(H.PCVRIssuedDate), 108) as [PCVR Issued Date],
         convert(varchar,Dbo.ActualDate(V.VATRelDate),103) + '' '' + convert(varchar, Dbo.ActualDate(V.VATRelDate), 108) as [VR Requested],
         convert(varchar,Dbo.ActualDate(V.OriginalRecdDate),103) + '' '' + convert(varchar, Dbo.ActualDate(V.OriginalRecdDate), 108) as [VR Granted],
         convert(varchar,Dbo.ActualDate(H.SBECVRAppReturnedDate),103) + '' '' + convert(varchar, Dbo.ActualDate(H.SBECVRAppReturnedDate), 108) as [SBE _VR App. ReturnedDate],
         convert(varchar,Dbo.ActualDate(H.SBECVRIssuedDate),103) + '' '' + convert(varchar, Dbo.ActualDate(H.SBECVRIssuedDate), 108) as [SBE_CVR IssuedDate],
         convert(varchar,Dbo.ActualDate(I.CDFLodgedDate),103) + '' '' + convert(varchar, Dbo.ActualDate(I.CDFLodgedDate), 108) as [CDF LodgedDate],
         convert(varchar,dbo.ActualDate(I.AssessmentPaidDate), 103) + '' '' + convert(varchar,dbo.ActualDate(I.AssessmentPaidDate),108) As [DutyPaidDate], 
         convert(varchar,Dbo.ActualDate(I.EntryLodgedDate),103) + '' '' + convert(varchar, Dbo.ActualDate(I.EntryLodgedDate), 108) as [Entry LodgedDate],
         convert(varchar,Dbo.ActualDate(I.EntryPassedDate),103) + '' '' + convert(varchar, Dbo.ActualDate(I.EntryPassedDate), 108) as [Custom Released],
         [Actual Days from ATA to CR]=DATEDIFF(dd, Dbo.ActualDate(D.ATASea),Dbo.ActualDate(I.EntryPassedDate)),
         convert(varchar,Dbo.ActualDate(K.ShippingLineReleaseDate),103) + '' '' + convert(varchar, Dbo.ActualDate(K.ShippingLineReleaseDate),108) as [ShippingLine ReleaseDate],
         convert(varchar,Dbo.ActualDate(K.PortInvoicePaidDate),103) + '' '' + convert(varchar, Dbo.ActualDate(K.PortInvoicePaidDate),108) as [Port_Charges Paid_Date],
         convert(varchar,Dbo.ActualDate(K.RemPortYardDateSea),103) + '' '' + convert(varchar, Dbo.ActualDate(K.RemPortYardDateSea),108) as [Rem_Sea_Port to Yard_Date],
         [Actual Days From CR to RFD Dar]=DATEDIFF(dd,Dbo.ActualDate(I.EntryPassedDate), Dbo.ActualDate(K.RemPortYardDateSea)),
         [Total No: of Days(ATA-RFD)]=DATEDIFF(dd,Dbo.ActualDate(D.ATASea), Dbo.ActualDate(K.RemPortYardDateSea)),
         A.Remarks As [File Status]
FROM
    FileMain A
INNER JOIN Party G ON G.PartyID = A.PartyID
LEFT JOIN Party B ON B.PartyID = A.ExporterID
LEFT JOIN Cargo C ON C.FileID = A.FileID
LEFT JOIN FileSea D ON D.FileID = A.FileID
LEFT JOIN SIPOString F ON F.FileID=A.FileID
LEFT JOIN IDFMain H ON H.FileID=A.FileID
LEFT JOIN Customs I ON I.FileID=A.FileID
LEFT JOIN VATRelief V ON V.FileID=A.FileID
LEFT JOIN PortAirPortOperation K ON K.FileID=A.FileID
LEFT JOIN Invoice L ON L.FileID=A.FileID and L.Incoterm=''CIF''' +@WhereStr)

END
0


a source to share


1 answer


The fastest implementation here would be to not use GROUP BY. Instead, I used SubQuery in your SELECT to execute the SUM of the invoices.

Remove the LEFT JOIN for the invoice and then return the L.Amount row in the SELECT using SubQuery as follows



(SELECT SUM(Amount) FROM Invoice WHERE Invoice.FileID = A.FileID and Invoice.Incoterm = 'CIF') [Invoice Value]

If you want to use GROUP BY to do SUM, you need to either GROUP for all of your other columns that you selected, or do GROUP within the VIEW (possibly nested).

0


a source






More articles:


All Articles