Archive | SQL coding RSS feed for this section

SQL code to show top X percent of vendor data

March 21, 2016 by Victoria Yudin 1 Comment

I had an interesting request the other day – how do you show the top 10 (or 20) percent of vendor payments (or invoices) for a particular date range? This is something that has to be done in a several steps – first you have to get your data, then summarize of it, then calculate the top […]

Continue reading →
Dynamics GP, GP Reports code, GP SQL scripts, Payables SQL code, SQL coding, Uncategorized
, , ,

Changing part of a string in SQL Server using REPLACE

March 11, 2013 by Victoria Yudin 4 Comments

This latest SQL Server tip comes from the Feb/March 2013 GP Reports Viewer newsletter. Have you ever needed to change part of a string in SQL Server? I have seen this come up in a variety of situations, like needing to change account names in the General Ledger or needing to show something differently when […]

Continue reading →
Dynamics GP, GP Reports code, GP Reports Viewer, SQL coding, SQL Server
, ,

Concatenating strings in SQL Server

May 21, 2012 by Victoria Yudin 7 Comments

This SQL Server tip comes from the April 2012 edition of the GP Reports Viewer newsletter. A request that we have seen many times when creating SOP invoices for Dynamics GP is to concatenate serial numbers.  So if there is an item with serial numbers, instead of showing a list with one serial number per […]

Continue reading →
GP Reports code, GP Reports Viewer, SQL coding, SQL Server
, ,

Getting the name of a month from the month number in SQL Server

May 23, 2011 by Victoria Yudin 18 Comments

I was creating a SmartList for a customer recently and was using the GL11110 and GL11111 views in GP for monthly summaries of P&L accounts, but wanted to show the month names instead of period ID’s to make it a bit more user-friendly. I could have linked to the fiscal period setup table to get the period names, but […]

Continue reading →
GP Reports code, SQL coding, SQL Server
,

Coding specific dates in SQL Server

September 13, 2010 by Victoria Yudin 3 Comments

Quite often when writing reports I need to write a formula to calculate dates. Dates are not very straightforward to code in SQL Server, and I have started keeping a list of various formulas so that I do not have to re-write the code every time. I have previously posted some code on calculating date differences in SQL, […]

Continue reading →
GP Reports code, SQL coding
, ,

How to find all SQL tables with a column name

April 23, 2010 by Victoria Yudin 8 Comments

Often I need to find all the tables in Dynamics GP that have a particular column or search for all columns where I only have a partial column name. Over the years I have seen lots of different code to accomplish this, so this is nothing new. But I am asked about this enough that […]

Continue reading →
GP Reports code, SQL coding, SQL Server
,

Searching for strings in SQL Server using LIKE

March 2, 2009 by Victoria Yudin 7 Comments

Many times when searching for data in SQL Server we need to search for strings and use wild cards. One common wild card is %. As an example, I am going to use the Customer Master table (RM00101) in the Dynamics GP sample company, Fabrikam, Inc. If I want to all customers with ‘USA’ in the Class ID, […]

Continue reading →
GP Reports code, SQL coding, SQL Server
,

SQL Server – how to get date differences

October 27, 2008 by Victoria Yudin 8 Comments

Often, when writing a report, we need to calculate the difference between two dates or the difference between a date and today. For example – how old is an invoice? How many days overdue is an invoice? Below is some SQL code that will help. First, if you need to dynamically get the current date […]

Continue reading →
GP Reports code, GP SQL scripts, SQL coding, SQL Server
,