Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have two tables one is PaymentDetail and another is Ledger

enter image description here

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 enter image description here

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 enter image description here

share|improve this question
    
You'll need the primary key of Ledger. Please include it. And of PaymentDetail, too, if it isn't the (RecptNo, PayModeType) –  ypercube Sep 17 '12 at 10:31
    
@ypercube i have edited the second scenario please check –  Amritpal Singh Sep 17 '12 at 10:41
    
@ypercube I have edited question please check –  Amritpal Singh Sep 17 '12 at 10:49
    
Some questions: (1) Is the real problem on a much larger number of rows? If so, can you give some approximate sizes please. (2) Am I allowed to use a SQLCLR procedure? (3) Is the order of assignment of LedgerID and PaymentID values guaranteed to follow the hierarchies (e.g. R > A > I) you describe? (4) Is a payment guaranteed to be fully used up by one or more ledger entries, or might there be a bit left over for the next payment (perhaps of a different type?) –  Paul White Sep 18 '12 at 12:29
    
@SQLKiwi 1) Yes it is a realtime problem, The Paymodedetail has aprrox .5 million and Ledger has 1.1 Million rows.2) Can it be done using single query as i have posted my solution in sqlfiddle.3) Yes order of payment is guranteed to be R>A>I. 4)There Might be leftover but we will consider the amount which is realised means in paymentdetail satisfying Ledger. –  Amritpal Singh Sep 18 '12 at 12:35
add comment

3 Answers

up vote 7 down vote accepted

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:

Results

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() && 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 <= 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 > 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 < 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();
            }
        }
    }
}
share|improve this answer
    
Thanks for your solution, But when i ran this it give me timeout. I have .5 million and 1.1 million respectively in payment and ledger. –  Amritpal Singh Sep 19 '12 at 8:22
    
@AmritpalSingh How are you executing it to get a timeout? Not via SSMS? I would expect it to start returning rows almost immediately, assuming the clustered indexes are on LedgerID and PaymentID as in the demo code. If a full sort is needed for the SELECT queries in the code, that would account for the delay. –  Paul White Sep 19 '12 at 8:31
    
yes via SSMS, and clustered Index are on mentioned columns. –  Amritpal Singh Sep 19 '12 at 8:32
    
to test it for one recptno i passed parameter and it returns no row? –  Amritpal Singh Sep 19 '12 at 8:34
    
as mentioned in the above solution i made database trustworthy –  Amritpal Singh Sep 19 '12 at 8:37
show 1 more comment

Not exactly the output you want, but you'll get some ideas. test at SQL-Fiddle:

order the PaymentDetail:

; WITH LedgerSortOrder AS
    ( SELECT 1 AS SortOrder, 'R' AS FundType
    UNION ALL
      SELECT 2, 'A'
    UNION ALL
      SELECT 3, 'I'
    )     

, LedgerOrdered AS
    ( SELECT L.*
           , ROW_NUMBER() OVER ( PARTITION BY L.RecptNo 
                                 ORDER BY LSO.SortOrder, L.LedgerID )
               AS Rn
      FROM Ledger AS L
        JOIN LedgerSortOrder AS LSO
          ON LSO.FundType = L.FundType
    ) 

, LedgerCTE AS
    ( SELECT L1.RecptNo, L1.Amount, L1.FundType
           , SUM(L2.Amount) AS SumAmount
      FROM LedgerOrdered AS L1
        JOIN LedgerOrdered AS L2
          ON  L2.RecptNo = L1.RecptNo
          AND L2.Rn <= L1.Rn
      GROUP BY L1.RecptNo, L1.Rn, L1.Amount, L1.FundType
    )  

order the Ledger:

, PaymentDetailSortOrder AS
    ( SELECT 1 AS SortOrder, 'Cash' AS PayModeType
    UNION ALL
      SELECT 2, 'Credit Card'
    UNION ALL
      SELECT 3, 'DD'
    UNION ALL
      SELECT 4, 'Cheque'
    )

, PaymentDetailOrdered AS
    ( SELECT P.*
           , ROW_NUMBER() OVER ( PARTITION BY P.RecptNo 
                                 ORDER BY PSO.SortOrder, P.PaymentID )
               AS Rn
      FROM PaymentDetail AS P
        JOIN PaymentDetailSortOrder AS PSO
          ON PSO.PayModeType = P.PayModeType 
    ) 

, PaymentDetailCTE AS
    ( SELECT P1.RecptNo, P1.PayModeType, P1.Amount
           , SUM(P2.Amount) AS SumAmount
      FROM PaymentDetailOrdered AS P1
        JOIN PaymentDetailOrdered AS P2
          ON  P2.RecptNo = P1.RecptNo
          AND P2.Rn <= P1.Rn
      GROUP BY P1.RecptNo, P1.Rn, P1.PayModeType, P1.Amount
    )  

then join the two CTEs:

SELECT 
    COALESCE(P.RecptNo, L.RecptNo) AS RecptNo
  , L.FundType
  , L.Amount
  , P.PayModeType
  , COALESCE(L.SumAmount, P.SumAmount) AS SumAmount
FROM PaymentDetailCTE AS P
  FULL JOIN LedgerCTE AS L
    ON  L.RecptNo = P.RecptNo
    AND L.SumAmount = P.SumAmount
ORDER BY RecptNo, SumAmount ;
share|improve this answer
    
You have put a lot of effort, Its not exact but near by i have to work on this solution to get the desired result. –  Amritpal Singh Sep 17 '12 at 13:11
add comment

Based on my better understanding of your requirements, I have updated my answer. It is not perfect. Specifically, it does not gracefully handle a payment that is larger than the last available ledger amount. Feel free to modify to better suit your needs.

WITH PaymentCTE
(
    Recptno,
    LedgerAmount,
    FundType,
    PayModeType,
    RowNum,
    Amount,
    PaidAmount,
    AmountRemaining,
    Ordinal
)
AS
(
    SELECT
        MasterRecords.Recptno,
        MasterRecords.LedgerAmount,
        MasterRecords.FundType,
        MasterRecords.PayModeType,
        MasterRecords.RowNum,
        MasterRecords.Amount,
        CASE
            WHEN MasterRecords.LedgerAmount <= MasterRecords.Amount THEN MasterRecords.LedgerAmount
            ELSE MasterRecords.Amount
        END AS PaidAmount,
        MasterRecords.Amount -      
        CASE
            WHEN MasterRecords.LedgerAmount <= MasterRecords.Amount THEN MasterRecords.LedgerAmount
            ELSE MasterRecords.Amount
        END AS AmountRemaining,
        MasterRecords.Ordinal
    FROM
    (
        SELECT
            pd.RecptNo,
            AllRecords.LedgerAmount,
            AllRecords.FundType,
            pd.PayModeType,
            ROW_NUMBER()
            OVER
            (
                PARTITION BY
                    pd.RecptNo,
                    pd.PaymentId
                ORDER BY
                    pd.RecptNo ASC,
                    AllRecords.Ordinal ASC          
            ) AS RowNum,
            AllRecords.Ordinal,
            pd.Amount

        FROM
        (
            SELECT
                TotalAmount.Recptno,
                TotalAmount.amount AS LedgerAmount,
                PaymentPriority.FundType,
                PaymentPriority.PayModeType,
                PaymentPriority.Ordinal
            FROM 
            (
                SELECT
                    'R' AS FundType,
                    'Cash' AS PayModeType,
                    1 AS Ordinal
                UNION ALL
                SELECT
                    'A' AS FundType,
                    'Cash' AS PayModeType,
                    2 AS Ordinal
                UNION ALL
                SELECT
                    'I' AS FundType,
                    'Cash' AS PayModeType,
                    3 AS Ordinal
                UNION ALL
                SELECT
                    'I' AS FundType,
                    'CreditCard' AS PayModeType,
                    4 AS Ordinal
                UNION ALL
                SELECT
                    'I' AS FundType,
                    'DD' AS PayModeType,
                    5 AS Ordinal
                UNION ALL
                SELECT
                    'I' AS FundType,
                    'Cheque' AS PayModeType,
                    6 AS Ordinal
            ) AS PaymentPriority

                INNER JOIN dbo.Ledger AS TotalAmount
                    ON PaymentPriority.FundType = TotalAmount.FundType
        ) AS AllRecords

            INNER JOIN dbo.PaymentDetail AS pd
                ON AllRecords.Recptno = pd.RecptNo
                AND AllRecords.PayModeType = pd.PayModeType
    ) AS MasterRecords

    WHERE MasterRecords.RowNum = 1  

    UNION ALL
    SELECT
        MasterRecords.Recptno,
        MasterRecords.LedgerAmount,
        MasterRecords.FundType,
        MasterRecords.PayModeType,
        MasterRecords.RowNum,
        pcte.AmountRemaining AS Amount,
        CASE
            WHEN MasterRecords.LedgerAmount <= pcte.AmountRemaining THEN MasterRecords.LedgerAmount
            ELSE pcte.AmountRemaining
        END AS PaidAmount,
        pcte.AmountRemaining -      
        CASE
            WHEN MasterRecords.LedgerAmount <= pcte.AmountRemaining THEN MasterRecords.LedgerAmount
            ELSE pcte.AmountRemaining
        END AS AmountRemaining,
        MasterRecords.Ordinal
    FROM
    (
        SELECT
            pd.RecptNo,
            AllRecords.LedgerAmount,
            AllRecords.FundType,
            pd.PayModeType,
            ROW_NUMBER()
            OVER
            (
                PARTITION BY
                    pd.RecptNo,
                    pd.PaymentId
                ORDER BY
                    pd.RecptNo ASC,
                    AllRecords.Ordinal ASC          
            ) AS RowNum,
            AllRecords.Ordinal,
            pd.Amount

        FROM
        (
            SELECT
                TotalAmount.Recptno,
                TotalAmount.amount AS LedgerAmount,
                PaymentPriority.FundType,
                PaymentPriority.PayModeType,
                PaymentPriority.Ordinal
            FROM 
            (
                SELECT
                    'R' AS FundType,
                    'Cash' AS PayModeType,
                    1 AS Ordinal
                UNION ALL
                SELECT
                    'A' AS FundType,
                    'Cash' AS PayModeType,
                    2 AS Ordinal
                UNION ALL
                SELECT
                    'I' AS FundType,
                    'Cash' AS PayModeType,
                    3 AS Ordinal
                UNION ALL
                SELECT
                    'I' AS FundType,
                    'CreditCard' AS PayModeType,
                    4 AS Ordinal
                UNION ALL
                SELECT
                    'I' AS FundType,
                    'DD' AS PayModeType,
                    5 AS Ordinal
                UNION ALL
                SELECT
                    'I' AS FundType,
                    'Cheque' AS PayModeType,
                    6 AS Ordinal
            ) AS PaymentPriority

                INNER JOIN dbo.Ledger AS TotalAmount
                    ON PaymentPriority.FundType = TotalAmount.FundType
        ) AS AllRecords

            INNER JOIN dbo.PaymentDetail AS pd
                ON AllRecords.Recptno = pd.RecptNo
                AND AllRecords.PayModeType = pd.PayModeType

    ) AS MasterRecords

        INNER JOIN PaymentCTE AS pcte
            ON MasterRecords.RecptNo = pcte.Recptno
            AND MasterRecords.PayModeType = pcte.PayModeType

    WHERE MasterRecords.RowNum = pcte.RowNum + 1
)
SELECT
    PaymentCTE.Recptno,
    PaymentCTE.FundType,
    PaymentCTE.PayModeType, 
    PaymentCTE.PaidAmount
FROM PaymentCTE

WHERE PaymentCTE.PaidAmount > 0

ORDER BY
    PaymentCTE.Recptno ASC,
    PaymentCTE.RowNum ASC,
    PaymentCTE.Ordinal ASC;

Thanks for the interesting challenge. Sorry I couldn't make it suit your needs 100%. Hope this helps in some way.

share|improve this answer
    
It gives duplicate value,means same rows for cash and same for cheque for each row in ledger –  Amritpal Singh Sep 17 '12 at 13:08
    
@AmritpalSingh That's because the RecptNo column is not unique in the Ledger table. If you are only using RecptNo to join (as was the case in your SQLFiddle), you are going to get duplication. You might consider adding FundType to PaymentDetail and including it in the join. –  Matt M Sep 17 '12 at 13:18
    
I can not use there, Because I recieve amount irrespective of no of fundtype, If i had Fundtype there then i dont need the Ledger table, then i know which FeeTpye covered in which type of Paymode and how much –  Amritpal Singh Sep 17 '12 at 13:27
    
@AmritpalSingh Do I understand you correctly that you want only 1 record returned per PaymentDetail, based on your arbitrary sort order? –  Matt M Sep 17 '12 at 15:00
    
No, I want to spread Rows obtain from PaymentDetail to spread over Multiple Rows of Ledger, Sort Order will be Defined. –  Amritpal Singh Sep 17 '12 at 15:02
show 3 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.