Meta tags:
description= Posts about SQL Queries written by Vaidhy Mohan;
Headings (most frequently used words):
share, if, you, liked, it, sql, gp, to, in, dynamics, archives, post, navigation, history, is, date, learn, discuss, menu, category, queries, average, days, pay, calculation, open, script, simulate, dex_row_id, view, using, row_number, msdyngp, delete, company, microsoft, compatible, with, 2013, move, expired, sop, quotes, leslie, vail, infamous, sales, distribution, amount, incorrect, error, seven, sins, against, performance, grant, fritchey, on, simple, talk, implicit, conversions, causing, deadlocks, server, function, for, proper, case, format, formatting, procedures, fiscal, year, start, end, query, subscribe, blog, visits, search, categories, victory, directly, proportional, hardwork, vaidhy, mohan, verified, services,
Text of the page (most frequently used words):
sql (96), new (76), opens (70), window (70), share (60), the (43), this (33), that (29), and (27), you (24), #dynamics (23), for (23), email (22), microsoft (21), posted (21), linkedin (21), 2013 (20), pinterest (20), tumblr (20), print (20), facebook (20), server (19), 2012 (19), 2010 (18), not (18), year (17), have (15), script (15), are (15), but (15), queries (14), from (14), like (13), 2015 (13), april (13), 2009 (12), more (12), would (12), which (12), loading (11), view (11), 2008 (11), programming (11), administration (11), 2011 (11), january (11), vaidhy (11), mohan (11), post (11), link (11), comments (10), sales (10), 2014 (10), friend (10), liked (10), vaidy (10), invoices (10), comment (9), windows (9), 2016 (9), dexterity (9), december (9), february (9), may (9), fiscal (9), line (9), now (8), update (8), sop (8), document (8), open (8), all (8), one (8), october (8), there (8), services (7), performance (7), table (7), august (7), november (7), march (7), july (7), some (7), was (7), markdown (7), with (6), learn (6), end (6), tools (6), stored (6), error (6), management (6), extender (6), customer (6), september (6), june (6), can (6), time (6), when (6), amount (6), history (6), get (5), discuss (5), create (5), mac (5), studio (5), company (5), system (5), support (5), ssms (5), reporting (5), source (5), order (5), dex_row_id (5), days (5), msdyngp (5), query (5), case (5), had (5), take (5), wordpress (4), com (4), report (4), blog (4), word (4), visual (4), troubleshooting (4), procedures (4), reports (4), code (4), processing (4), office (4), nav (4), multi (4), posts (4), 2018 (4), gp2013 (4), google (4), month (4), net (4), dex (4), crm (4), business (4), average (4), pay (4), adtp (4), 2017 (4), select (4), dates (4), then (4), know (4), how (4), custom (4), date (4), happy (4), has (4), procedure (4), solution (4), function (4), any (4), simple (4), even (4), important (4), here (4), zero (4), issue (4), below (4), items (4), distribution (4), users (4), voided (4), quote (4), quotes (4), row_number (4), fully (4), applied (4), website (3), name (3), reader (3), log (3), subscribe (3), account (3), close (3), web (3), client (3), conference (3), views (3), user (3), test (3), debugging (3), ssrs (3), smartlist (3), builder (3), sharepoint (3), service (3), based (3), architecture (3), copy (3), information (3), receivables (3), power (3), database (3), exchange (3), sp1 (3), field (3), feature (3), list (3), dictionary (3), david (3), accounts (3), bit (3), archives (3), also (3), work (3), above (3), start (3), getdate (3), current (3), working (3), does (3), out (3), what (3), found (3), read (3), very (3), could (3), provided (3), those (3), implicit (3), quite (3), against (3), article (3), till (3), record (3), invoice (3), don (3), available (3), down (3), given (3), lookup (3), started (2), site (2), required (2), content (2), sign (2), subscribed (2), free (2), virtual (2), payroll (2), control (2), upgrade (2), rollup (2), tips (2), technology (2), tech (2), tool (2), statement (2), native (2), messages (2), designer (2), packs (2), sdk (2), roadmap (2), resources (2), resource (2), references (2), project (2), professional (2), mavericks (2), accounting (2), 365 (2), named (2), tenant (2), events (2), technical (2), mb3 (2), macbook (2), knowledge (2), base (2), jet (2), integration (2), manager (2), functionality (2), cookbook (2), general (2), file (2), excel (2), eone (2), solutions (2), profile (2), outstanding (2), data (2), summary (2), convergence (2), intelligence (2), concepts (2), cloud (2), chunk (2), cashbook (2), bank (2), apture (2), apple (2), viewer (2), receivable (2), 2020 (2), search (2), rss (2), verified (2), older (2), navigation (2), calendar (2), dashboards (2), first (2), sy40101 (2), following (2), same (2), since (2), dynamically (2), currently (2), aligning (2), within (2), always (2), understand (2), useful (2), proper (2), format (2), deadlock (2), conversions (2), actually (2), leave (2), seven (2), sins (2), long (2), really (2), got (2), find (2), reconcile (2), again (2), see (2), imbalance (2), sop10100 (2), obvious (2), yes (2), didn (2), seriously (2), records (2), header (2), after (2), realized (2), something (2), must (2), while (2), message (2), reuse (2), whether (2), wrong (2), much (2), possible (2), materialised (2), closing (2), leslie (2), vail (2), thousands (2), about (2), expired (2), download (2), clearcompanies (2), partner (2), never (2), particularly (2), wanted (2), referring (2), definition (2), simply (2), adding (2), looks (2), perfect (2), refer (2), object (2), rm30101 (2), rm20101 (2), both (2), transaction (2), calculates (2), point (2), calculation (2), design, write, collapse, bar, manage, subscriptions, privacy, already, join, other, subscribers, workflow, wishes, vista, white, paper, wendy, neal, webinar, vss, vpc, visualisation, basic, application, machine, victoria, yudin, vba, vacation, manuals, interface, union, uncategorized, uae, uac, top, 100, tfs, environment, adoption, program, team, foundation, tap, requirements, syexcelreports, direction, ssas, scripts, profiler, operations, sod, smartphones, smartconnect, silverlight, sharepointwendy, sdt, profiling, debugger, sba, sanscript, saas, functions, rtm, reviews, writer, reminders, receivings, entry, reblogged, rdp, ram, purchase, pstl, crescent, library, advantage, product, releases, powerpivot, pop, poll, philosophy, personal, penny, pdf, payables, parallels, packt, operating, online, premise, ole, off, topic, odbc, nokia, symbian, newsgroups, features, navision, picture, mvp, currency, msdynamicsworld, modifier, modified, forms, mobile, migration, middle, east, specialist, door, airlift, connect, micr, metrics, memory, mekorma, 701, 700, mark, polino, reporter, maintenance, high, sierra, macintosh, pro, lync, linux, lightswitch, kubuntu, kpi, express, intercompany, implementation, iis, ian, grieve, human, html5, hotfix, guest, grn, utilities, metadata, home, page, bug, gp2016, gp2015r2, gp2015, sp2, sp3, gp2010, drive, chrome, ftm, frx, frank, hamelly, fixed, assets, financials, financial, transfer, level, security, day, rate, erp, econnect, dynamicsworld, dynamic, future, dynamicaccounting, dso, dsn, doug, awesome, attachment, documentation, assembly, generator, ini, developer, insights, decisions, dba, musgrave, science, dag, customizations, credit, cumulative, ctree, crystal, crossdomainerror, adapter, creator, count, consolidated, collections, coa, computing, creation, checklinks, charts, chart, certification, cbm, cbi, capslock, canada, portal, budget, browsers, boxplot, books, book, review, blogs, reconciliation, audit, trails, highlights, safari, android, analytical, aio, adobe, addons, framework, conv12, categories, 2019, 271, 736, visits, full, consultant, doting, husband, father, coffee, music, lover, travel, photography, hobbyist, good, rest, assured, automatically, refreshed, year1, else, your, where, lstfscdy, fiscal_end_date, fstfscdy, fiscal_start_date, anyone, retrieved, simplest, way, periods, setup, hard, values, somewhere, dec, jan, tuning, related, exercises, task, among, automate, plugin, called, pack, standing, his, learned, usage, poor, anymore, seconds, give, aligned, shared, portals, least, weekly, basis, impossible, logic, ever, opened, standard, formatting, many, noteworthy, pinal, dave, jeff, smith, wiseman, thought, highlight, links, programmers, our, community, built, convert, string, feel, missing, latest, version, known, title, believe, who, extensively, explains, definitions, each, term, such, conversion, etc, example, trigger, interesting, crucial, sans, causing, deadlocks, want, anything, realize, reading, original, came, across, gem, lists, things, affect, drafted, ago, couldn, might, old, relevant, extremely, informative, grant, fritchey, talk, caused, fields, backup, abnormal, situation, follows, most, baffling, thing, tried, major, mishap, cleared, decided, sop10200, ran, thru, none, contained, problem, minutes, thinking, stranded, requires, contain, creating, strange, seemed, entered, item, stop, upon, running, edit, due, corresponding, received, seeking, help, clearing, posting, popped, infamous, incorrect, answer, positive, because, master, just, unless, edited, come, mins, inquire, text, paste, added, specific, copying, transferred, won, able, gotta, kidding, age, years, still, fundamentally, lame, reasons, use, hear, failed, moves, move, implementers, developers, consultants, been, updated, cater, used, realised, today, multiple, instance, removes, companies, pretty, exist, delete, compatible, yet, powerful, charm, transact, going, resolve, number, created, achieved, every, single, backend, cannot, afford, everything, runtime, suppose, allows, mention, physical, requirement, access, customisation, value, form, easiest, option, turn, creates, simulate, using, rm_averagedaystopay, feedbacks, welcome, sample, customers, whom, were, either, meaning, non, whose, default, please, tables, satisfy, criteria, better, late, than, isn, readers, requested, amend, consider, remains, only, run, paid, removal, ptr, soon, somehow, steve, pena, tim, previous, between, two, include, category, contact, vaidymohan, blogroll, skip, menu, victory, directly, proportional, hardwork,
Text of the page (random words):
sql queries dynamics gp learn discuss dynamics gp learn discuss victory is directly proportional to hardwork menu skip to content about me blogroll vaidymohan linkedin contact category archives sql queries post navigation older posts average days to pay calculation history open sql script posted on december 11 2014 by vaidhy mohan in my previous post average days to pay calculation sql code i had provided a sql stored procedure that calculates a customer s adtp for a given point of time between two dates while this was perfect it does not include fully applied but open invoices some of the readers particularly tim and steve pena requested to amend the script to consider open invoices that are fully applied an invoice remains open even after fully applied only when we do not run paid transaction removal ptr i wanted to work on this script as soon as possible but somehow i could not better late than never isn t it please find the link below to download the sql procedure that calculates a customer s adtp for a given point of time but looks at both history rm30101 and open rm20101 tables take invoices that satisfy following criteria invoices that are fully applied if invoices are in history table by default current transaction amount would be zero if invoices are in open table then take those invoices whose receivable outstanding amount is zero invoices that are not voided invoices that have a document amount meaning non zero i have verified this script against some sample customers for whom invoices were either in history rm30101 or in open rm20101 or in both as always feedbacks are welcome rm_averagedaystopay sql vaidy share if you liked it share on x opens in new window x share on linkedin opens in new window linkedin share on facebook opens in new window facebook more print opens in new window print email a link to a friend opens in new window email share on tumblr opens in new window tumblr share on pinterest opens in new window pinterest like loading posted in msdyngp adtp average days to pay gp microsoft dynamics gp receivables rm sql programming sql queries t sql 1 comment simulate dex_row_id in a sql view using row_number msdyngp posted on april 25 2014 by vaidhy mohan i have a requirement in which i have to access a sql view from within my customisation dictionary in order to create a custom lookup for users to select a value based on an extender form and an extender lookup easiest option is to create an extender view which in turn creates a sql view for us now this is the view that i am suppose to refer to from my custom dictionary dexterity allows us to refer to any sql object by simply create a table definition and mention the sql object table or view name as the physical name everything looks perfect till you actually see below error messages at runtime error message is quite obvious you do not have dex_row_id in that sql view that you are referring to every single dexterity table must have dex_row_id at the backend it cannot afford to not have one so how am i going to resolve this by simply adding a record number dynamically to the sql view created by extender how to do that by adding the t sql function row_number this is how i achieved it definition of row_number can be found here row_number transact sql a simple yet powerful sql function has given me the power to do what i wanted in no time oh and my custom lookup referring to this view is working like a charm users are happy and so am i vaidy share if you liked it share on x opens in new window x share on linkedin opens in new window linkedin share on facebook opens in new window facebook more print opens in new window print email a link to a friend opens in new window email share on tumblr opens in new window tumblr share on pinterest opens in new window pinterest like loading posted in msdyngp dex dexterity dexterity programming dex_row_id gp microsoft dynamics gp sql sql programming sql queries sql server sql views t sql 1 comment delete a company in microsoft dynamics gp compatible with gp 2013 posted on january 9 2014 by vaidhy mohan we have a sql script named clearcompanies sql which is available on customer source or partner source this script removes all references to those companies that are not available in sql server but pretty much exist in gp records it s an all important script for all implementers developers and consultants now this script has been updated to cater for also gp 2013 i had not used this script for a long time so never realised it till today this is particularly important as gp 2013 now support multi tenant architecture multiple gp system db on same sql instance you can download this script from here provided you have a customer source partner source account clearcompanies sql vaidy share if you liked it share on x opens in new window x share on linkedin opens in new window linkedin share on facebook opens in new window facebook more print opens in new window print email a link to a friend opens in new window email share on tumblr opens in new window tumblr share on pinterest opens in new window pinterest like loading posted in msdyngp gp gp 2013 gp administration gp2013 microsoft dynamics gp 2013 resources sql sql administration sql queries test company 4 comments move expired sop quotes to history leslie vail posted on january 12 2013 by vaidhy mohan leslie vail has posted an article at a time when i am currently working on closing down thousands and thousands of sop quotes which users failed to close down it s about a sql script which moves all expired sop quotes to history some really lame reasons i use to hear for not closing down quotes 1 we don t know when that quote would be materialised seriously gotta be kidding me if it s a quote of age 2 years and you still don t know whether it would get materialised or not then something is wrong fundamentally 2 if it s voided we won t be able to copy the line items of that quote wrong you can copying line items from an sop document is very much possible even if it s transferred or voided 3 we don t know whether the comments that we had added specific to that quote would be available for us to reuse it answer is a positive yes you can reuse the comments because comments are not stored on that document but on comments master just select the id and there it is unless you have edited that comment on the document but again come on it d take 5 mins for you to inquire the voided one copy the comment text and paste it on new one and so on vaidy share if you liked it share on x opens in new window x share on linkedin opens in new window linkedin share on facebook opens in new window facebook more print opens in new window print email a link to a friend opens in new window email share on tumblr opens in new window tumblr share on pinterest opens in new window pinterest like loading posted in gp administration sales order processing sop sql sql programming sql queries stored procedures t sql leave a comment infamous sales distribution amount is incorrect error posted on december 31 2012 by vaidhy mohan i received an email from one of my users seeking my help in clearing this issue while posting an invoice this error message popped up upon running the edit list for this invoice i realized that it was due to an imbalance in markdown amount and corresponding distribution line below was the error report that i got there seemed to be markdown entered on one or more line item s but there was no distribution line for that amount but the issue was not that simple and didn t stop there when i ran thru all line items none contained a markdown now that s the problem after some minutes of thinking i realized something must be stranded on header record s markdown field for which gp requires a markdown distribution line but since line items do not contain any markdown it s not creating one strange i decided to query the records from sop10100 sop header and sop10200 sop line to understand the issue below is what i found seriously but yes that was the issue and most baffling thing is when i tried to reconcile this sales document this major mishap didn t get cleared at all obvious solution for this abnormal situation is as follows 1 take backup of this sop10100 record 2 update markdown fields with zero 3 reconcile this sales document again to see if the above update had caused any imbalance happy new year and happy troubleshooting vaidy share if you liked it share on x opens in new window x share on linkedin opens in new window linkedin share on facebook opens in new window facebook more print opens in new window print email a link to a friend opens in new window email share on tumblr opens in new window tumblr share on pinterest opens in new window pinterest like loading posted in database db debugging gp gp administration kb knowledge base microsoft dynamics gp sales sales order processing sql sql queries tips troubleshooting 1 comment seven sins against t sql performance grant fritchey on simple talk posted on december 27 2012 by vaidhy mohan update this was drafted long ago but couldn t really got to post it till now you might find this post a bit old but it s quite relevant even now and extremely informative post i came across this gem of an article which lists out 7 things that affect t sql performance i do not want to take anything out from that article and post it here as you would realize by reading the original post you would learn some very important concepts read it here the seven sins against t sql performance vaidy share if you liked it share on x opens in new window x share on linkedin opens in new window linkedin share on facebook opens in new window facebook more print opens in new window print email a link to a friend opens in new window email share on tumblr opens in new window tumblr share on pinterest opens in new window pinterest like loading posted in performance sql sql administration sql programming sql queries t sql leave a comment implicit conversions causing deadlocks in sql server posted on june 27 2012 by vaidhy mohan quite an interesting but crucial post up there on sans sql blog the post explains that implicit conversions in sql server could actually trigger a deadlock and there are definitions for each important term such as deadlock implicit conversion etc with an example i believe this post would be useful for those who work extensively on sql server vaidy share if you liked it share on x opens in new window x share on linkedin opens in new window linkedin share on facebook opens in new window facebook more print opens in new window print email a link to a friend opens in new window email share on tumblr opens in new window tumblr share on pinterest opens in new window pinterest like loading posted in sql sql administration sql programming sql queries sql server 1 comment t sql function for proper case format posted on may 27 2012 by vaidhy mohan we do not have a built in t sql function to convert any string or a statement to proper case format also known as title case this i feel is a very simple feature that sql server could have provided us but missing even in it s latest version sql server 2012 i thought i would highlight some links which would be useful for t sql programmers in our community david wiseman s solution jeff smith s solution pinal dave s solution there are many more solutions but above are noteworthy vaidy share if you liked it share on x opens in new window x share on linkedin opens in new window linkedin share on facebook opens in new window facebook more print opens in new window print email a link to a friend opens in new window email share on tumblr opens in new window tumblr share on pinterest opens in new window pinterest like loading posted in sql sql queries sql server sql server 2012 t sql 4 comments formatting sql procedures posted on april 24 2012 by vaidhy mohan have you ever opened a standard gp stored procedure i do it at least on a weekly basis and have always found it impossible to read as it is so i end up aligning the procedure first and then read it to understand the logic not anymore david has shared information on some portals which does this in seconds and give us an aligned code standing out from his list is poor sql from what i learned from my usage and there is a plugin for ssms which does this from within ssms this tool is called ssms tools pack happy aligning vaidy share if you liked it share on x opens in new window x share on linkedin opens in new window linkedin share on facebook opens in new window facebook more print opens in new window print email a link to a friend opens in new window email share on tumblr opens in new window tumblr share on pinterest opens in new window pinterest like loading posted in sql sql programming sql queries sql server ssms stored procedures t sql 4 comments fiscal year start date end date sql query posted on april 1 2012 by vaidhy mohan i am currently working on custom ssrs dashboards performance tuning and related exercises one task among all is to automate the fiscal year start date and fiscal year end date based on which fiscal year we are in if the fiscal year is the same as calendar year we can hard code the values to 1 jan current year and 31 dec current year since it s not in my case i had to dynamically get the dates from somewhere the simplest way for me is to query this from gp fiscal periods setup table which is sy40101 following is the query if anyone would like to know how the dates are retrieved select fstfscdy fiscal_start_date lstfscdy fiscal_end_date from sy40101 where year1 case when month getdate first month of your company fiscal year then year getdate else year getdate 1 end with above i can now be rest assured that by the time a new fiscal year is started my dashboards would automatically get refreshed with new start end dates this query would also work if the fiscal year is as good as the calendar year vaidy share if you liked it share on x opens in new window x share on linkedin opens in new window linkedin share on facebook opens in new window facebook more print opens in new window print email a link to a friend opens in new window email share on tumblr opens in new window tumblr share on pinterest opens in new window pinterest like loading posted in gp gp administration gp functionality performance reporting sql sql queries ssrs t sql 2 comments post navigation older posts vaidhy mohan business consultant doting husband father coffee music lover travel photography hobbyist verified services view full profile subscribe rss posts rss comments blog visits 271 736 search search archives archives select month february 2020 1 january 2020 1 november 2019 3 may 2018 3 april 2018 1 may 2017 1 april 2017 2 march 2017 2 february 2017 1 july 2016 1 may 2016 6 april 2016 18 march 2016 6 february 2016 3 january 2016 1 november 2015 1 october 2015 2 september 2015 4 august 2015 1 july 2015 2 june 2015 1 april 2015 2 february 2015 2 january 2015 4 december 2014 6 november ...
|
