Database

DROP FUNCTION f_PendingDCDetails;
GO
CREATE FUNCTION f_PendingDCDetails 
(
    @SID Varchar(32), 
    @iFID varchar(32), 
    @iTID varchar(32), 
    @CID varchar(32), 
    @EnDate varchar(11), 
    @iWR tinyint, 
    @iLoc varchar(8), 
    @iScrVNo varchar(80), 
    @iVtp varchar(20), 
    @iSubType varchar(8), 
    @iFinancialYear varchar(8), 
    @iMnth varChar(8), 
    @ivYear varchar(8), 
    @iLocation varchar(8), 
    @iForLocation varchar(8), 
    @iAorB varchar(1), 
    @iVNo integer, 
    @iSrNo integer, 
    @ForWhichForm varchar(2)
)
Returns Table
As
Return
(
    Select 
        Max(T.QueryNo) QueryNo, 
        Max(T.ScrVoucher_No) ScrVoucher_No, 
        Max(T.Vtp) Vtp, 
        Max(T.SubType) SubType, 
        Max(T.FinancialYear) FinancialYear, 
        Max(T.Mnth) Mnth, 
        Max(T.vYear) vYear, 
        Max(T.Location) Location, 
        Max(T.ForLocation) ForLocation, 
        Max(T.AorB) AorB, 
        Max(T.vno) vno, 
        Max(T.srno) srno, 
        Max(T.dated) dated, 
        Max(T.Item_ID) Item_ID, 
        Sum(T.Delivered) Delivered, 
        Sum(T.Returned) Returned, 
        Sum(T.Invoiced) Invoiced, 
        Sum(T.Rate) Rate, 
        Max(T.Remarks) Remarks, 
        Max(T.Project_ID) Project_ID, 
        Max(T.Client_ID) Client_ID, 
        Max(T.Emp_ID) Emp_ID, 
        Max(T.DeliveryChallanNo) DeliveryChallanNo, 
        Sum(T.GDNDelivered) GDNDelivered, 
        Max(T.EngineNo) EngineNo, 
        Max(T.FrameNo) FrameNo, 
        Max(T.VRegNo) VRegNo, 
        Max(T.ClaimType) ClaimType, 
        Max(T.PrincipalCompany_ID) PrincipalCompany_ID, 
        Sum(T.STPercent) STPercent, 
        Sum(T.EDPercent) EDPercent, 
        Sum(T.OTPercent) OTPercent, 
        Sum(T.DiscP) DiscP, 
        Sum(T.DiscA) DiscA, 
        Sum(T.GrossRate) GrossRate, 
        Sum(T.MainDiscP) MainDiscP, 
        Max(T.Party_ID) Party_ID, 
        Max(T.ClientPONo) ClientPONo, 
        Max(T.ClientPODate) ClientPODate, 
        Max(T.PaymentTerms) PaymentTerms, 
        Max(T.Job_ID) Job_ID, 
        Max(T.CostCenter_ID) CostCenter_ID, 
        Max(T.BatchNo) BatchNo, 
        Max(T.ExpiryDate) ExpiryDate, 
        Max(T.Transporter_ID) Transporter_ID, 
        Sum(T.BQO) BQO, 
        Sum(T.BQD) BQD, 
        Max(T.Gift_ID) Gift_ID, 
        Sum(T.GQO) GQO, 
        Sum(T.GQD) GQD, 
        Max(T.BiltyNo) BiltyNo, 
        Max(T.BiltyDate) BiltyDate, 
        Max(T.OldSoftNo) OldSoftNo 
    From 
        (
            ...
        ) T
    Group By 
        T.VTP, 
        T.SubType, 
        T.FinancialYear, 
        T.Mnth, 
        T.vYear, 
        T.Location, 
        T.ForLocation, 
        T.AorB, 
        T.VNo, 
        T.SrNo, 
        T.Item_ID
    Having 
    (
        @iWR = 4 
    Or 
        (@iWR = 0 And Sum(T.Delivered) > Sum(T.Invoiced - T.Returned))
    Or 
        (@iWR = 1 And Aum(T.Delivered) > Sum(T.GDNDelivered)) 
    Or 
        (@iWR = 2 And Sum(T.Delivered) < Sum(T.Invoiced - T.Returned)) 
    Or 
        (@iWR = 3 And Sum(T.Delivered) < Sum(T.GDNDelivered))
    )
);

DROP FUNCTION f_PendingDC;
GO
CREATE FUNCTION f_PendingDC 
(
    @SID varchar(32), 
    @iFID varchar(32), 
    @iTID varchar(32), 
    @CID varchar(32), 
    @EnDate varchar(11), 
    @iWR tinyint, 
    @iLoc varchar(8), 
    @iScrVNo varchar(80), 
    @iVtp varchar(20), 
    @iSubType varchar(8), 
    @iFinancialYear varchar(8), 
    @iMnth varchar(8), 
    @ivYear varchar(8), 
    @iLocation varchar(8), 
    @iForLocation varchar(8), 
    @iAorB varchar(1), 
    @iVNo integer, 
    @iSrNo integer, 
    @ForWhichForm varchar(2)
)
Returns Table
As
Return
(
    Select 
        T.ScrVoucher_No ScrVoucher_NoWithSrNo, 
        T.Vtp VTP, 
        T.SubType SubType, 
        T.FinancialYear FinancialYear, 
        T.Mnth Mnth, 
        T.vYear vYear, 
        T.Location Location, 
        T.ForLocation ForLocation, 
        T.AorB AorB, 
        T.VNo VNo, 
        T.srno SrNo, 
        T.dated Dated, 
        T.Client_ID Client_ID, 
        C.Title Client_Title, 
        T.Item_ID Item_ID, 
        IsNull(I.Title, D.Title) Item_Title, 
        T.Delivered Delivered, 
        T.Returned Returned, 
        T.Invoiced Invoiced, 
        I.Unit Unit, 
        T.Rate Rate, 
        Left(T.Remarks, 1000) Remarks, 
        T.Emp_ID Emp_ID, 
        ED.Title Emp_Title, 
        T.DeliveryChallanNo DeliveryChallanNo,
        T.GDNDelivered GDNDelivered, 
        @iWR iWR, 
        T.EngineNo EngineNo,
        T.FrameNo FrameNo, 
        T.DeliveryChallanNo ScrVoucher_No, 
        Case When I.ID Is Null Then 'Services' Else 'Parts' End ServiceOrPart, 
        T.VRegNo VRegNo,
        T.ClaimType ClaimType, 
        CP.ID PrincipalCompany_ID, 
        CP.Title PrincipalCompany_Title,
        T.STPercent STPercent, 
        T.EDPercent EDPercent, 
        T.OTPercent OTPercent, 
        T.DiscP DiscP,
        T.DiscA DiscA, 
        T.GrossRate GrossRate, 
        T.MainDiscP MainDiscP,
        A.ID Party_ID, 
        A.Title Party_Title, 
        T.ClientPONo ClientPONo, 
        T.ClientPODate ClientPODate,
        V.ID1 Main_VTP, 
        T.GrossRate SaleRate, 
        T.PaymentTerms PaymentTerms, 
        CA.Terms PaymentTermsCoa, 
        T.Job_ID Job_ID, 
        DJ.Title Job_Title, 
        T.CostCenter_ID CostCenter_ID, 
        T.Project_ID Project_ID,
        CC.Title CostCenter_Title, 
        DP.Title Project_Title, 
        T.BatchNo BatchNo, 
        T.ExpiryDate ExpiryDate,
        TT.ID Transporter_ID, 
        TT.Title Transporter_Title, 
        Case When MR.VTP Is Null Then I.PackSize Else MR.PackSize End QtyPerCTN,
        T.BQO BQO, 
        T.BQD BQD, 
        T.Gift_ID Gift_ID, 
        G.Title Gift_Title, 
        T.GQO GQO, 
        T.GQD GQD,
        T.BiltyNo BiltyNo, 
        T.BiltyDate BiltyDate, 
        T.OldSoftNo OldSoftNo
    From
        f_PendingDCDetails(@SID, @iFID, @iTID, @CID, @EnDate, @iWR, @iLoc, @iScrVNo, @iVtp, @iSubType, @iFinancialYear, @iMnth, @ivYear, @iLocation, @iForLocation, @iAorB, @iVNo, @iSrNo, @ForWhichForm) T
        Left Join Coa31 I on I.id = T.Item_ID
        Left Join Coa31 G on G.id = T.Gift_ID
        Left Join Coa32 C On T.Client_ID = C.ID
        Left Join EmployeeDetails ED On T.Emp_ID = ED.ID
        Left Join DefJobs D On T.Item_ID = D.ID
        Left Join Coa32 CP On T.PrincipalCompany_ID = CP.ID
        Left Join AnalysisCodes A On T.Party_ID = A.ID
        Left Join Vtp2 V On T.VTP = V.ID2
        Left Join Coa321 CA on CA.ID = IsNull(CP.ID, C.ID)
        Left Join DefJobs DJ On DJ.ID = T.Job_ID
        Left Join CostCenters CC On CC.ID = T.CostCenter_ID
        Left Join DefProjects DP On DP.ID = T.Project_ID
        Left Join Coa32 TT On T.Transporter_ID = TT.ID
        Left Join ManufacturingOrder_Det M On T.BatchNo = M.BatchNo
        Left Join MiscItemRecepie MR On IsNull(M.RefVTP, '') = MR.VTP 
            And IsNull(M.RefSubType, '') = MR.SubType  
            And IsNull(M.RefFinancialYear, '') = MR.FinancialYear
            And IsNull(M.RefMnth, '') = MR.Mnth
            And IsNull(M.RefvYear, '') = MR.vYear
            And IsNull(M.RefLocation, '') = MR.Location
            And IsNull(M.RefForLocation, '') = MR.ForLocation
            And IsNull(M.RefAorB, '') = MR.AorB
            And IsNull(M.RefVNo, '') = MR.VNo
);

create or replace view Total_TABLE as
select 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,29,30,31,1+2+3+4+5+6+7+8+9+10+11+12+13+14+15+16+17+18+19+20+21+22+23+24+25+26+27+28+29+29+30+31 as Total
from Add_attend;

COALESCE(1, 0) + COALESCE(2, 0) + ... + COALESCE(31, 0)

Member 11840363 wrote:

It would be of great help if you can help me with this.

William Ivanski wrote:

We are looking forward to hear feedback

William Ivanski wrote:

OmniDB is cross-platform

Bastard Programmer from Hell
If you can't read my code, try converting it here[^]

Eddy Vluggen wrote:

There's a "Free Tools" section where you can point out free tools. It's in the "Product Lifecycle" category.

Eddy Vluggen wrote:

having tabs in an editor is not something to brag about

Eddy Vluggen wrote:

Where is the download for Ubuntu?

William Ivanski wrote:

It may sound crazy, but not all database management tool have tabs in their editors.

William Ivanski wrote:

Note that OmniDB is a web database management tool with tabs in its editor.

William Ivanski wrote:

OmniDB is a web application, so it needs to be served by a webserver.

Bastard Programmer from Hell
If you can't read my code, try converting it here[^]

MSDN[^] states:

By default, the maximum size that can be added to a captured column in a single INSERT, UPDATE, WRITETEXT, or UPDATETEXT statement is 65,536 bytes or 64 KB.

Bastard Programmer from Hell
If you can't read my code, try converting it here[^]