{"id":13577,"date":"2022-10-04T13:51:23","date_gmt":"2022-10-04T08:21:23","guid":{"rendered":"https:\/\/jassweb.com\/solved\/solved-sql-payment-distribution\/"},"modified":"2022-10-04T13:51:23","modified_gmt":"2022-10-04T08:21:23","slug":"solved-sql-payment-distribution","status":"publish","type":"post","link":"https:\/\/jassweb.com\/solved\/solved-sql-payment-distribution\/","title":{"rendered":"[Solved] sql payment distribution"},"content":{"rendered":"<p> [ad_1]<br \/>\n<\/p>\n<div id=\"answer-42125045\" class=\"answer js-answer accepted-answer js-accepted-answer\" data-answerid=\"42125045\" data-parentid=\"42124684\" data-score=\"1\" data-position-on-page=\"1\" data-highest-scored=\"1\" data-question-has-accepted-highest-score=\"1\" itemprop=\"acceptedAnswer\" itemscope itemtype=\"https:\/\/schema.org\/Answer\">\n<div class=\"post-layout\">\n<div class=\"votecell post-layout--left\"><\/div>\n<div class=\"answercell post-layout--right\">\n<div class=\"s-prose js-post-body\" itemprop=\"text\">\n<p>Extending the answer to this question:<br \/>\npayment distrubution oracle sql query<\/p>\n<p>You can still use the SQL <code>MODEL<\/code> clause.  In this version, you need separate calculations for each distinct <code>account_num<\/code>.  You can achieve this using the <code>PARTITION<\/code> keyword of the SQL <code>MODEL<\/code> clause to partition by <code>account_num<\/code>.<\/p>\n<p>Like this (see SQL comments for step-by-step explanation):<\/p>\n<pre><code>-- Set up test data (since I don't have your table)\nWITH inv_raw (item_order, inv_amount, partial_pmt_allowed, account_num, cr_amt) AS (\nSELECT 1, 1256, 'Y', 12, 1000 FROM DUAL UNION ALL\nSELECT 2, 1134, 'Y', 12, 1000 FROM DUAL UNION ALL\nSELECT 3, 800, 'Y', 13, 1200 FROM DUAL UNION ALL\nSELECT 4, 200, 'N',13, 1200 FROM DUAL UNION ALL\nSELECT 5, 156, 'N',13, 1200 FROM DUAL),\n-- Ensure that the column we are ordering by is densely populated\ninv_dense (dense_item_order, item_order, inv_amount, partial_pmt_allowed, account_num, cr_amt) AS\n( SELECT DENSE_RANK() OVER ( PARTITION BY account_num ORDER BY item_order ), item_order, inv_amount, partial_pmt_allowed, account_num, cr_amt FROM inv_raw )\n-- Give us a way to input the payment amount\n--param AS ( SELECT 1100 p_payment_amount FROM DUAL )\n-- The actual query starts here\nSELECT \n   account_num,\n   item_order,\n   inv_amount,\n   partial_pmt_allowed,\n   applied dist_amount,\n   remaining_out balance_amt,\n   cr_amt\nFROM inv_dense\nMODEL\n-- We want a completely separate calculation for each distinct account_num\nPARTITION BY ( account_num )\n-- We'll output one row for each value of dense_item_order.\n-- We made item_order \"dense\" so we can do things like CV()-1 to get the \n--   previous row's values.\nDIMENSION BY ( dense_item_order )\nMEASURES ( cr_amt, item_order, inv_amount, \n           partial_pmt_allowed, 0 applied, \n           0 remaining_in, 0 remaining_out )\nRULES AUTOMATIC ORDER (\n-- The amount carried into the first row is the payment amount\nremaining_in[1] = cr_amt[1],\n-- The amount carried into subsequent rows is the amount we carried out of the prior row\nremaining_in[dense_item_order &gt; 1] = remaining_out[CV()-1],\n-- The amount applied depends on whether the amount remaining can cover the invoice\n-- and whether partial payments are allowed\napplied[ANY] = CASE WHEN remaining_in[CV()] &gt;= inv_amount[CV()] OR partial_pmt_allowed[CV()] = 'Y' THEN LEAST(inv_amount[CV()], remaining_in[CV()]) ELSE 0 END,\n-- The amount we carry out is the amount we brought in minus what we applied\nremaining_out[ANY] = remaining_in[CV()] - applied[CV()]\n)\nORDER BY account_num, item_order;\n<\/code><\/pre>\n<\/p><\/div>\n<div class=\"mt24\"><\/div>\n<\/div>\n<p>            <span class=\"d-none\" itemprop=\"commentCount\">0<\/span> <\/p><\/div>\n<\/div>\n<p>[ad_2]<\/p>\n<p>solved sql payment distribution <\/p>\n","protected":false},"excerpt":{"rendered":"<p>[ad_1] Extending the answer to this question: payment distrubution oracle sql query You can still use the SQL MODEL clause. In this version, you need separate calculations for each distinct account_num. You can achieve this using the PARTITION keyword of the SQL MODEL clause to partition by account_num. Like this (see SQL comments for step-by-step &#8230; <a title=\"[Solved] sql payment distribution\" class=\"read-more\" href=\"https:\/\/jassweb.com\/solved\/solved-sql-payment-distribution\/\" aria-label=\"More on [Solved] sql payment distribution\">Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[320],"tags":[838,341],"class_list":["post-13577","post","type-post","status-publish","format-standard","hentry","category-solved","tag-oracle","tag-sql"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>[Solved] sql payment distribution - JassWeb<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/jassweb.com\/solved\/solved-sql-payment-distribution\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"[Solved] sql payment distribution - JassWeb\" \/>\n<meta property=\"og:description\" content=\"[ad_1] Extending the answer to this question: payment distrubution oracle sql query You can still use the SQL MODEL clause. In this version, you need separate calculations for each distinct account_num. You can achieve this using the PARTITION keyword of the SQL MODEL clause to partition by account_num. Like this (see SQL comments for step-by-step ... Read more\" \/>\n<meta property=\"og:url\" content=\"https:\/\/jassweb.com\/solved\/solved-sql-payment-distribution\/\" \/>\n<meta property=\"og:site_name\" content=\"JassWeb\" \/>\n<meta property=\"article:published_time\" content=\"2022-10-04T08:21:23+00:00\" \/>\n<meta name=\"author\" content=\"Kirat\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Kirat\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-payment-distribution\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-payment-distribution\/\"},\"author\":{\"name\":\"Kirat\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\"},\"headline\":\"[Solved] sql payment distribution\",\"datePublished\":\"2022-10-04T08:21:23+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-payment-distribution\/\"},\"wordCount\":60,\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"keywords\":[\"oracle\",\"sql\"],\"articleSection\":[\"Solved\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-payment-distribution\/\",\"url\":\"https:\/\/jassweb.com\/solved\/solved-sql-payment-distribution\/\",\"name\":\"[Solved] sql payment distribution - JassWeb\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#website\"},\"datePublished\":\"2022-10-04T08:21:23+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-payment-distribution\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/jassweb.com\/solved\/solved-sql-payment-distribution\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-payment-distribution\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/jassweb.com\/solved\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"[Solved] sql payment distribution\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/jassweb.com\/solved\/#website\",\"url\":\"https:\/\/jassweb.com\/solved\/\",\"name\":\"JassWeb\",\"description\":\"Build High-quality Websites\",\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/jassweb.com\/solved\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\",\"name\":\"Jass Web\",\"url\":\"https:\/\/jassweb.com\/solved\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png\",\"contentUrl\":\"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png\",\"width\":693,\"height\":132,\"caption\":\"Jass Web\"},\"image\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\",\"name\":\"Kirat\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1776403586\",\"contentUrl\":\"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1776403586\",\"caption\":\"Kirat\"},\"sameAs\":[\"http:\/\/jassweb.com\"],\"url\":\"https:\/\/jassweb.com\/solved\/author\/jaspritsinghghumangmail-com\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"[Solved] sql payment distribution - JassWeb","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/jassweb.com\/solved\/solved-sql-payment-distribution\/","og_locale":"en_US","og_type":"article","og_title":"[Solved] sql payment distribution - JassWeb","og_description":"[ad_1] Extending the answer to this question: payment distrubution oracle sql query You can still use the SQL MODEL clause. In this version, you need separate calculations for each distinct account_num. You can achieve this using the PARTITION keyword of the SQL MODEL clause to partition by account_num. Like this (see SQL comments for step-by-step ... Read more","og_url":"https:\/\/jassweb.com\/solved\/solved-sql-payment-distribution\/","og_site_name":"JassWeb","article_published_time":"2022-10-04T08:21:23+00:00","author":"Kirat","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Kirat","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/jassweb.com\/solved\/solved-sql-payment-distribution\/#article","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/solved-sql-payment-distribution\/"},"author":{"name":"Kirat","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31"},"headline":"[Solved] sql payment distribution","datePublished":"2022-10-04T08:21:23+00:00","mainEntityOfPage":{"@id":"https:\/\/jassweb.com\/solved\/solved-sql-payment-distribution\/"},"wordCount":60,"publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"keywords":["oracle","sql"],"articleSection":["Solved"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/jassweb.com\/solved\/solved-sql-payment-distribution\/","url":"https:\/\/jassweb.com\/solved\/solved-sql-payment-distribution\/","name":"[Solved] sql payment distribution - JassWeb","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/#website"},"datePublished":"2022-10-04T08:21:23+00:00","breadcrumb":{"@id":"https:\/\/jassweb.com\/solved\/solved-sql-payment-distribution\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/jassweb.com\/solved\/solved-sql-payment-distribution\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/jassweb.com\/solved\/solved-sql-payment-distribution\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/jassweb.com\/solved\/"},{"@type":"ListItem","position":2,"name":"[Solved] sql payment distribution"}]},{"@type":"WebSite","@id":"https:\/\/jassweb.com\/solved\/#website","url":"https:\/\/jassweb.com\/solved\/","name":"JassWeb","description":"Build High-quality Websites","publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/jassweb.com\/solved\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/jassweb.com\/solved\/#organization","name":"Jass Web","url":"https:\/\/jassweb.com\/solved\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/","url":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","contentUrl":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","width":693,"height":132,"caption":"Jass Web"},"image":{"@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31","name":"Kirat","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/image\/","url":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1776403586","contentUrl":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1776403586","caption":"Kirat"},"sameAs":["http:\/\/jassweb.com"],"url":"https:\/\/jassweb.com\/solved\/author\/jaspritsinghghumangmail-com\/"}]}},"_links":{"self":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/13577","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/comments?post=13577"}],"version-history":[{"count":0,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/13577\/revisions"}],"wp:attachment":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/media?parent=13577"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/categories?post=13577"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/tags?post=13577"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}