Sample Code (SQL)

                    
SELECT 
    dism.LetterNo, 
    dism.LetterDate, 
    lc.Country, 
    dism.InvoiceNo, 
    dism.InvoiceDate, 
    dia.CountingUnit, 
    dia.UnitPrice, 
    dia.Quantity, 
    br.BrandName, 
    br.GenericIndex, 
    imp.CompanyName AS Importer, 
    comp.CompanyName AS Manufacturer, 
    YEAR(dism.UpdatedOn) AS Year, 
    dism.UpdatedOn, 
    di.Border, 
    MONTH(dism.UpdatedOn) AS Month, 
    YEAR(dism.InvoiceDate) AS InvoiceYear, 
    ROUND(dia.UnitPrice * dia.Quantity, 2) AS TotalAmount
FROM 
    DrugImportations di
    INNER JOIN drugImportation_SubMain dism ON di.ImportationID = dism.ImportationID
    INNER JOIN 
    (
        lkpCountries lc 
        RIGHT JOIN 
        (
            (
                (
                    dism 
                    INNER JOIN 
                    (
                        Importer imp
                        INNER JOIN 
                        (
                            drugImportations_Sub dis 
                            INNER JOIN drugImportations_Actual dia 
                            ON dis.RegistrationID = dia.RegistrationID
                        ) 
                        ON imp.ImporterID = dis.ImporterID
                    ) 
                    ON 
                        dism.CommercialDrugImportsID = dia.CommercialDrugImportsID 
                        AND dism.ImportationID = dis.ImportationID
                ) 
                LEFT JOIN BrandRegistration br
                ON 
                    dis.CompanyID = br.CompanyID 
                    AND dis.RegistrationID = br.RegistrationID
            ) 
            LEFT JOIN Company comp 
            ON br.CompanyID = comp.CompanyID
        ) 
        ON lc.CountryId = comp.CountryId
    )
GROUP BY 
    dism.LetterNo, 
    dism.LetterDate, 
    lc.Country, 
    dism.InvoiceNo, 
    dia.CountingUnit, 
    dia.UnitPrice, 
    dia.Quantity, 
    br.BrandName, 
    br.GenericIndex, 
    imp.CompanyName, 
    comp.CompanyName, 
    dism.UpdatedOn, 
    di.Border, 
    dism.InvoiceDate, 
    YEAR(dism.InvoiceDate), 
    ROUND(dia.UnitPrice * dia.Quantity, 2);

                

                    
SELECT 
    cwnp.id,
    cwnp.Country, 
    cwnp.Importer, 
    cwnp.CompanyName, 
    cwnp.Qty, 
    cwnp.UnitPrice, 
    cwnp.TotalAmount, 
    cwnp.Border, 
    "No Pro" AS Stat,
    cwnp.GenericIndex,
    cwnp.BrandName,
    cwnp.RegisteredWithLDL,
    cwnp.RegistrationStatus, 
    cwnp.Year,
    cwnp.MonthG,
    cwnp.InvoiceNo,
    cwnp.ProformaNo,
    cwnp.ProformaLetterNo,
    cwnp.CommercialLetterNo,
    cwnp.CommercialLetterDate, 
    cwnp.ProformaInvoiceDate,
    cwnp.ProformaLetterDate,
    cwnp.UpdatedBy,
    cwnp.UpdatedOn,
    cwnp.ATCUniqueCode,
    cwnp.CommercialNote,
    cwnp.ProformaNote,
    cwnp.CountingUnit,
    cwnp.CommercialInvoiceDate
FROM 
    Commerical_WithNoProforma cwnp

UNION ALL 

SELECT 
    cfa.iD,
    cfa.Country, 
    cfa.Importer, 
    cfa.CompanyName, 
    cfa.Qty, 
    cfa.UnitPrice, 
    cfa.TotalAmount, 
    cfa.Border, 
    "Yes Pro" AS Stat,
    cfa.GenericIndex,
    cfa.BrandName,
    cfa.RegisteredWithLDL,
    cfa.RegistrationStatus, 
    cfa.Year,
    cfa.MonthG,
    cfa.InvoiceNo,
    cfa.ProformaNo,
    cfa.ProformaLetterNo,
    cfa.CommercialLetterNo,
    cfa.CommercialLetterDate, 
    cfa.ProformaInvoiceDate,
    cfa.ProformaLetterDate,
    cfa.UpdatedBy,
    cfa.UpdatedOn,
    cfa.ATCUniqueCode,
    cfa.CommercialNote,
    cfa.ProformaNote,
    cfa.CountingUnit,
    cfa.CommercialInvoiceDate
FROM 
    Common_for_Actual cfa;

                

                    
SELECT 
    c.CompanyName, 
    i.CompanyName AS Importer, 
    dis.InviceNo, 
    dis.InviceDate, 
    dis.LetterNo AS C_LetterNo, 
    dis.LetterDate AS C_LetterDate, 
    dis.UpdatedOn, 
    dis.UpdatedBy, 
    dis.InviceNo & "-" & dis.InviceDate & "-" & dis.LetterNo & "-" & dis.LetterDate & "-" & c.CompanyID & "-" & i.ImporterID AS InvoiceUniqueID, 
    YEAR(dis.UpdatedOn) AS [Year], 
    IIF([Month] IN (1, 2, 3), "Q1", 
        IIF([Month] IN (4, 5, 6), "Q2", 
        IIF([Month] IN (7, 8, 9), "Q3", "Q4"))) AS Q, 
    MONTH(dis.UpdatedOn) AS [Month], 
    DAY(dis.UpdatedOn) AS [Day], 
    "With Pro-forma" AS Status
FROM 
    Importer i
    INNER JOIN (DrugImportations di 
        INNER JOIN Company c 
        ON di.CompanyID = c.CompanyID) 
    ON i.ImporterID = di.ImporterID
    INNER JOIN drugImportation_SubMain dis 
    ON di.ImportationID = dis.ImportationID;