Compare two tables amount with respect to priortization of FundType I have two tables one is PaymentDetail and another is Ledger



Here i want to prioritize paymodetype in manner that cash should be considered first for 
R,A and then I FundType, then Credit Card, then DD and then Cheque.

I want that amount received in cash should first be considered for R then A and lastly for I FundType.Another example


Here is sqlfiddle as well. Can it be solved by SQL only. I don't want to use Cursor.
Edit:
I have updated the sqlfiddle with what i have tried. I am trying to use join due to which multiple rows are obtained in my result with respect to Payment Detail. I want to span amount of one row of payment detail over Ledger then second row should be used.
How can i achieve that?
Result of current sqlfiddle is
I cannot think of a reasonable way to meet the requirement with a T-SQL non-cursor solution, so the following is a SQLCLR procedure that returns a result set.  The solution performs a single scan of each table (at most).  In a test with 500,000 Payment rows and 1,000,000 Ledger rows, it completed in 4.8 seconds with a warm buffer pool and 5.2 seconds from a cold start - including the time to display the 120,000 rows of results in SSMS.

The procedure uses the EXTERNAL_ACCESS permission set, so ALTER DATABASE [name] SET TRUSTWORTHY ON; is the easiest way for testing (the database must also have a valid owner).  For deployment, you might prefer to sign it and grant the permission explicitly.  I was unable to upload this to SQL Fiddle as it has a limit of 8000 characters for DDL statements.

CREATE ASSEMBLY [dbase]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300A1AE59500000000000000000E00002210B0108000014000000060000000000000E320000002000000040000000004000002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000BC3100004F000000004000009802000000000000000000000000000000000000006000000C000000303100001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E7465787400000014120000002000000014000000020000000000000000000000000000200000602E7273726300000098020000004000000004000000160000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001A00000000000000000000000000004000004200000000000000000000000000000000F0310000000000004800000002000500A8240000880C000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001B300600C603000001000011730500000A0A7201000070730600000A0B076F0700000A723500007007730800000A0C086F0900000A72AF0000701F0C20800000006F0A00000A186F0B00000A086F0900000A72C70000701F0C20800000006F0A00000A186F0B00000A086F0C00000A2606166F0D00000A06176F0E00000A06086F0900000A166F0F00000A6F1000000A74110000016F1100000A06086F0900000A176F0F00000A6F1000000A74110000016F1200000ADE0A072C06076F1300000ADC066F1400000A730600000A0D066F1400000A730600000A1304096F0700000A11046F0700000A72E300007009730800000A130572DE0200701104730800000A13061105202C0100006F1500000A1106202C0100006F1500000A1B8D14000001131311131672C10400701E731600000AA211131772D50400701E731600000AA211131872E704007019176A731700000AA211131972F90400701C731600000AA211131A72070500701F161F146A731700000AA2111313071107731800000A1308281900000A11086F1A00000A11056F1B00000A130911066F1B00000A130A110A6F1C00000A39E601000011096F1C00000A39DA010000110A166F1D00000A130B110A176F1D00000A130C110A186F1E00000A130D110A196F1F00000A130E1109166F1D00000A130F1109176F1D00000A13101109186F1F00000A13111109196F1E00000A1312110C1110400F010000110E1111357E110816110F6F2000000A110817110B6F2000000A110818110D6F2100000A110819110E6F2200000A11081A11126F2100000A281900000A11086F2300000A1111110E591311110A6F1C00000A392A010000110A166F1D00000A130B110A176F1D00000A130C110A186F1E00000A130D110A196F1F00000A130E3873FFFFFF11112300000000000000003645110816110F6F2000000A110817110B6F2000000A110818110D6F2100000A11081911116F2200000A11081A11126F2100000A281900000A11086F2300000A110E111159130E11096F1C00000A399F0000001109166F1D00000A130F1109176F1D00000A13101109186F1F00000A13111109196F1E00000A131238E8FEFFFF1110110C2F3611096F1C00000A2C631109166F1D00000A130F1109176F1D00000A13101109186F1F00000A13111109196F1E00000A131238ACFEFFFF110A6F1C00000A2C2D110A166F1D00000A130B110A176F1D00000A130C110A186F1E00000A130D110A196F1F00000A130E3876FEFFFF281900000A6F2400000ADE0C110A2C07110A6F1300000ADCDE0C11092C0711096F1300000ADCDE0C11042C0711046F1300000ADCDE0A092C06096F1300000ADC2A0000417C000002000000110000009B000000AC0000000A000000000000000200000093010000FE010000910300000C00000000000000020000008A010000150200009F0300000C0000000000000002000000CF000000DE020000AD0300000C0000000000000002000000C2000000F9020000BB0300000A000000000000001E02282500000A2A42534A4201000100000000000C00000076322E302E35303732370000000005006C0000004C020000237E0000B8020000A403000023537472696E6773000000005C06000020050000235553007C0B00001000000023475549440000008C0B0000FC00000023426C6F620000000000000002000001471402000900000000FA25330016000001000000190000000200000002000000250000000400000001000000010000000200000000000A00010000000000060035002E000600680055000B007C0000000600AB008B000600CB008B000A001601FB000A0042012C010A005D012C010A007E016B010A0090012C010A009B012C010A00C1012C010A00CE01EF000A00DC016B010A00E801EF000A0009026B01060058022E00060081022E000A0095026B010A00D702FB000A00E302FB000A00F102FB000A00FC02FB000A001E032C010A003A036B010000000001000000000001000100010010001400000005000100010050200000000096003C000A000100A0240000000086184F000E00010011004F00120021004F00180029004F000E0031004F000E0039004F000E0041004F00220049008B010E0051004F0027005100B2012E005900D80133007100FB013C0081001302420039002302460039002E024600590045024B0071004E02510039005F02220039006E02220091008D020E009900AF0255008100C4021800A1004F005900A1004F006000A9004F006800B10004036F00B9000D03740051002C037A00C90047037F00C9004C038300C90055038800C9005F038D00A90069039200A90072039800A9007C039E00B90086037400B90095030E0009004F000E00200023001D002E000B00C9002E001300D2002E001B00DB00A400048000000000000000000000000000000000E900000002000000000000000000000001002500000000000200000000000000000000000100EF00000000000000003C4D6F64756C653E0064626173652E646C6C0053746F72656450726F63656475726573006D73636F726C69620053797374656D004F626A656374004D617463684C65646765725265636F726473002E63746F720053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650064626173650053797374656D2E44617461004D6963726F736F66742E53716C5365727665722E5365727665720053716C50726F6365647572654174747269627574650053797374656D2E446174612E53716C436C69656E740053716C436F6E6E656374696F6E537472696E674275696C6465720053716C436F6E6E656374696F6E0053797374656D2E446174612E436F6D6D6F6E004462436F6E6E656374696F6E004F70656E0053716C436F6D6D616E640053716C506172616D65746572436F6C6C656374696F6E006765745F506172616D65746572730053716C506172616D657465720053716C44625479706500416464004462506172616D6574657200506172616D65746572446972656374696F6E007365745F446972656374696F6E004462436F6D6D616E6400457865637574654E6F6E5175657279007365745F456E6C697374007365745F496E74656772617465645365637572697479006765745F4974656D006765745F56616C756500537472696E67007365745F44617461536F75726365007365745F496E697469616C436174616C6F670049446973706F7361626C6500446973706F7365004462436F6E6E656374696F6E537472696E674275696C646572006765745F436F6E6E656374696F6E537472696E67007365745F436F6D6D616E6454696D656F75740053716C4D657461446174610053716C446174615265636F72640053716C436F6E746578740053716C50697065006765745F506970650053656E64526573756C747353746172740053716C44617461526561646572004578656375746552656164657200446244617461526561646572005265616400476574496E74333200476574537472696E6700476574446F75626C6500536574496E74333200536574537472696E6700536574446F75626C650053656E64526573756C7473526F770053656E64526573756C7473456E6400003363006F006E007400650078007400200063006F006E006E0065006300740069006F006E0020003D00200074007200750065000079530045004C00450043005400200040005300650072007600650072004E0061006D00650020003D002000400040005300450052005600450052004E0041004D0045002C0020004000440061007400610062006100730065004E0061006D00650020003D002000440042005F004E0041004D00450028002900001740005300650072007600650072004E0061006D006500001B4000440061007400610062006100730065004E0061006D0065000081F90D000A002000200020002000200020002000200020002000200020002000200020002000530045004C0045004300540020000D000A00200020002000200020002000200020002000200020002000200020002000200020002000200020005000610079006D0065006E007400490044002C0020000D000A0020002000200020002000200020002000200020002000200020002000200020002000200020002000520065006300700074004E006F002C000D000A002000200020002000200020002000200020002000200020002000200020002000200020002000200041006D006F0075006E0074002C0020000D000A00200020002000200020002000200020002000200020002000200020002000200020002000200020005000610079004D006F0064006500540079007000650020000D000A002000200020002000200020002000200020002000200020002000200020002000460052004F004D002000640062006F002E005000610079006D0065006E007400440065007400610069006C0020000D000A0020002000200020002000200020002000200020002000200020002000200020004F00520044004500520020004200590020000D000A00200020002000200020002000200020002000200020002000200020002000200020002000200020005000610079006D0065006E007400490044003B000081E10D000A002000200020002000200020002000200020002000200020002000200020002000530045004C004500430054000D000A00200020002000200020002000200020002000200020002000200020002000200020002000200020004C0065006400670065007200490044002C0020000D000A0020002000200020002000200020002000200020002000200020002000200020002000200020002000520065006300700074004E006F002C0020000D000A0020002000200020002000200020002000200020002000200020002000200020002000200020002000460075006E00640054007900700065002C0020000D000A002000200020002000200020002000200020002000200020002000200020002000200020002000200041006D006F0075006E00740020000D000A002000200020002000200020002000200020002000200020002000200020002000460052004F004D002000640062006F002E004C006500640067006500720020000D000A0020002000200020002000200020002000200020002000200020002000200020004F00520044004500520020004200590020000D000A00200020002000200020002000200020002000200020002000200020002000200020002000200020004C0065006400670065007200490044003B0000135000610079006D0065006E0074004900440000114C0065006400670065007200490044000011460075006E0064005400790070006500000D41006D006F0075006E00740000175000610079004D006F006400650054007900700065000000FF7A4814813C7D4BABD129FD442C46230008B77A5C561934E089030000010320000105200101110D04200101080401000000042001010E062002010E1221042000122D08200312310E11350805200101113D0320000804200101020520011231080320001C0320000E062002010E1135072003010E11350A062001011D1251040000125D05200101125504200012610320000204200108080420010E080420010D0805200201080805200201080E05200201080D240714121D1221122912211221122912291D125112551261126108080E0D08080D0E1D12510801000200000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F777301000000000000A1AE59500000000002000000700000004C3100004C13000052534453FCB7D3C1C0B8134E9BE2C8123748E8CD09000000633A5C55736572735C5061756C2057686974655C446F63756D656E74735C56697375616C2053747564696F20323031305C50726F6A656374735C53616E647069745C6F626A5C52656C656173655C64626173652E70646200E43100000000000000000000FE310000002000000000000000000000000000000000000000000000F0310000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500204000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001001000000018000080000000000000000000000000000001000100000030000080000000000000000000000000000001000000000048000000584000003C02000000000000000000003C0234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000000000000000000000000000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B0049C010000010053007400720069006E006700460069006C00650049006E0066006F0000007801000001003000300030003000300034006200300000002C0002000100460069006C0065004400650073006300720069007000740069006F006E000000000020000000300008000100460069006C006500560065007200730069006F006E000000000030002E0030002E0030002E003000000034000A00010049006E007400650072006E0061006C004E0061006D0065000000640062006100730065002E0064006C006C0000002800020001004C006500670061006C0043006F0070007900720069006700680074000000200000003C000A0001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000640062006100730065002E0064006C006C000000340008000100500072006F006400750063007400560065007200730069006F006E00000030002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000030002E0030002E0030002E003000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000003000000C000000103200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO
CREATE PROCEDURE [dbo].[MatchLedgerRecords]
WITH EXECUTE AS CALLER AS 
EXTERNAL NAME [dbase].[StoredProcedures].[MatchLedgerRecords];


Sample data reproduced from your SQL Fiddle:

CREATE TABLE dbo.PaymentDetail
(
    PaymentId   integer PRIMARY KEY,
    RecptNo     integer NOT NULL,
    Amount      float NOT NULL,
    PayModeType varchar(20) NOT NULL
);

CREATE TABLE dbo.Ledger
(
    LedgerId    integer PRIMARY KEY,
    Recptno     integer NOT NULL,
    FundType    char(1) NOT NULL,
    amount      float NOT NULL
);

INSERT INTO dbo.PaymentDetail
    (PaymentId, RecptNo, Amount, PayModeType)
VALUES 
    (1, 1, 1000, 'Cash'),
    (2, 1, 3000, 'Cheque'),
    (3, 2, 4000, 'Cheque'),
    (4, 2, 1000, 'Cheque');

INSERT INTO dbo.Ledger
    (LedgerId, Recptno, FundType, amount)
VALUES
    (1, 1, 'R', 500),
    (2, 1, 'A', 500),
    (3, 1, 'I', 1000),
    (4, 1, 'I', 1000),
    (5, 1, 'I', 1000),
    (6, 2, 'R', 2000),
    (7, 2, 'A', 1000),
    (8, 2, 'I', 2000);


Usage:

EXECUTE [dbo].[MatchLedgerRecords];


Output:



Source code:

using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
    [SqlProcedure]
    public static void MatchLedgerRecords()
    {
        SqlConnectionStringBuilder scb = new SqlConnectionStringBuilder();

        // Use a context connection to find the name of the server and current database
        using (SqlConnection conn = new SqlConnection("context connection = true"))
        {
            conn.Open();

            SqlCommand cmd = new SqlCommand(@"SELECT @ServerName = @@SERVERNAME, @DatabaseName = DB_NAME()", conn);
            cmd.Parameters.Add("@ServerName", SqlDbType.NVarChar, 128).Direction = ParameterDirection.Output;
            cmd.Parameters.Add("@DatabaseName", SqlDbType.NVarChar, 128).Direction = ParameterDirection.Output;

            cmd.ExecuteNonQuery();

            // Build a connection string
            scb.Enlist = false;
            scb.IntegratedSecurity = true;
            scb.DataSource = (string)cmd.Parameters[0].Value;
            scb.InitialCatalog = (string)cmd.Parameters[1].Value;
        }

        // Non-context-connections
        using (SqlConnection conn1 = new SqlConnection(scb.ConnectionString), conn2 = new SqlConnection(scb.ConnectionString))
        {
            conn1.Open();
            conn2.Open();

            // Commands to read the two tables in the required order
            SqlCommand paymentCommand = new SqlCommand(
                @"
                SELECT 
                    PaymentID, 
                    RecptNo,
                    Amount, 
                    PayModeType 
                FROM dbo.PaymentDetail 
                ORDER BY 
                    PaymentID;", conn1);

            SqlCommand ledgerCommand = new SqlCommand(
                @"
                SELECT
                    LedgerID, 
                    RecptNo, 
                    FundType, 
                    Amount 
                FROM dbo.Ledger 
                ORDER BY 
                    LedgerID;", conn2);

            // Avoid default 30 second timeout
            paymentCommand.CommandTimeout = 300;
            ledgerCommand.CommandTimeout = 300;

            // Shape of the results
            SqlMetaData[] smd = new SqlMetaData[] 
            {
                    new SqlMetaData("PaymentID", SqlDbType.Int),
                    new SqlMetaData("LedgerID", SqlDbType.Int),
                    new SqlMetaData("FundType", SqlDbType.Char, 1),
                    new SqlMetaData("Amount", SqlDbType.Float),
                    new SqlMetaData("PayModeType", SqlDbType.VarChar, 20)
            };

            SqlDataRecord sdr = new SqlDataRecord(smd);

            // Indicate start-of-results to SQL Server
            SqlContext.Pipe.SendResultsStart(sdr);

            // Main algorithm
            using (SqlDataReader paymentReader = paymentCommand.ExecuteReader(), ledgerReader = ledgerCommand.ExecuteReader())
            {
                if (ledgerReader.Read() &amp;&amp; paymentReader.Read())
                {
                    // Read the first ledger row column values
                    // (ordinal numbers are the column position from the original SELECT)
                    int LedgerID = ledgerReader.GetInt32(0);
                    int LedgerReceiptNo = ledgerReader.GetInt32(1);
                    string FundType = ledgerReader.GetString(2);
                    double LedgerAmount = ledgerReader.GetDouble(3);

                    // Read the first payment row column values
                    int PaymentID = paymentReader.GetInt32(0);
                    int PaymentReceiptNo = paymentReader.GetInt32(1);
                    double PaymentAmount = paymentReader.GetDouble(2);
                    string PayModeType = paymentReader.GetString(3);

                    // A modified merge join
                    while (true)
                    {
                        // Join on receipt number
                        if (LedgerReceiptNo == PaymentReceiptNo)
                        {
                            // If the current ledger amount can be allocated from the current payment
                            if (LedgerAmount &lt;= PaymentAmount)
                            {
                                // Set result column values
                                sdr.SetInt32(0, PaymentID);
                                sdr.SetInt32(1, LedgerID);
                                sdr.SetString(2, FundType);
                                sdr.SetDouble(3, LedgerAmount);
                                sdr.SetString(4, PayModeType);

                                // Send a result row
                                SqlContext.Pipe.SendResultsRow(sdr);

                                // Subtract the allocated amount from the payment amount available
                                PaymentAmount -= LedgerAmount;

                                // Next ledger record
                                if (!ledgerReader.Read()) break;

                                // Read the ledger record's columns
                                LedgerID = ledgerReader.GetInt32(0);
                                LedgerReceiptNo = ledgerReader.GetInt32(1);
                                FundType = ledgerReader.GetString(2);
                                LedgerAmount = ledgerReader.GetDouble(3);
                            }
                            // Ledger amount is greater than the payment amount remaining
                            else
                            {
                                // Return a row for the remaining payment amount (if non-zero)
                                if (PaymentAmount &gt; 0)
                                {
                                    sdr.SetInt32(0, PaymentID);
                                    sdr.SetInt32(1, LedgerID);
                                    sdr.SetString(2, FundType);
                                    sdr.SetDouble(3, PaymentAmount);
                                    sdr.SetString(4, PayModeType);

                                    SqlContext.Pipe.SendResultsRow(sdr);

                                    // Adjust the ledger amount
                                    LedgerAmount -= PaymentAmount;
                                }

                                // Read the next payment record
                                if (!paymentReader.Read()) break;

                                // Read the payment record columns
                                PaymentID = paymentReader.GetInt32(0);
                                PaymentReceiptNo = paymentReader.GetInt32(1);
                                PaymentAmount = paymentReader.GetDouble(2);
                                PayModeType = paymentReader.GetString(3);
                            }
                        }
                        // Receipt numbers do not currently match, payment behind ledger
                        else if (PaymentReceiptNo &lt; LedgerReceiptNo)
                        {
                            // Read next payment record
                            if (!paymentReader.Read()) break;

                            // Read column values
                            PaymentID = paymentReader.GetInt32(0);
                            PaymentReceiptNo = paymentReader.GetInt32(1);
                            PaymentAmount = paymentReader.GetDouble(2);
                            PayModeType = paymentReader.GetString(3);
                        }
                        // Receipt numbers do not currently match, ledger behind payment
                        else
                        {
                            // Read next ledger record
                            if (!ledgerReader.Read()) break;

                            // Read column values
                            LedgerID = ledgerReader.GetInt32(0);
                            LedgerReceiptNo = ledgerReader.GetInt32(1);
                            FundType = ledgerReader.GetString(2);
                            LedgerAmount = ledgerReader.GetDouble(3);
                        }
                    // end while: Repeat until ledger or payment runs out of rows
                    }
                }
                // End of result set
                SqlContext.Pipe.SendResultsEnd();
            }
        }
    }
}