Showing posts with label Query. Show all posts
Showing posts with label Query. Show all posts

Saturday, August 22, 2015

Storing and Querying Age in Dynamics CRM

In the previous post, I discussed how we can use North52 Formula Manager to calculate age in Dynamics CRM.

In a recent implementation, we needed to store a running age of each contact in years and months in CRM. Users also needed an ability to query contacts based on the age including an ability to search contacts using the age range. For example, find all contacts with ages between 1 year and 9 months and 2 years and 9 months. Of course, they wanted to be able to do this intuitively. They wanted to be able to enter 1 year and 9 months using the numbers 1 (year) and 9 (months) rather than 1.75 because this would require users to calculate the fraction in their head before doing the search. Also, some of the fractions would be difficult to calculate mentally. For example, 7 months would be equal to 0.583333 (years).
We cannot store the age value in two different fields i.e. years and months because this would prevent users from using ranges i.e. > and < operators in the Advanced Find query or in the System View.
For example, the following query will return incorrect results:
Age_Years >= 1 AND
Age_Months >= 9 AND
Age_Years <= 2 AND
Age_Months <= 9
We addressed this problem by storing the age values as a decimal number with 2 decimals. The whole number represents years and the fraction represents months. For example, 1 year and 1 month is stored as 1.01, 1 year and 10 months is stored as 1.10 and 2 years is stored as 2.00. This is both intuitive to the users in terms of how they think (in years and months rather than fractions) and also enables users to create Advanced Find queries and views based on the age range. Now, they can easily use the following filter criteria to query all contacts between the ages of 1 year and 9 months and 2 years and 9 months:
Age >= 1.09 AND
Age <= 2.09

Friday, November 28, 2014

SQL to Create GL Account Balances Table for Dynamics GP

I searched Dynamics GP documentation and the web for a SQL query to retrieve GL Balances that include beginning balance, ending balance and net change but could not come up with anything that would help me with what I needed.


I created the following SQL which creates a table that contains all the information I need to create a PowerPivot data model (more on this in the future article). The query uses a Cartesian product (cross-join) to include all periods for a given GL account whether there is any activity or not.
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[GL_AccountBalances]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[GL_AccountBalances]
GO


SELECT
ACTNUMST AS [Account Number],
ACTDESCR AS [Description],
YEAR1 AS [Fiscal Year],
PERIODID,
SY40100.PERIODDT AS [Starting Date],
SY40100.PERDENDT AS [Ending Date],
CASE WHEN ACTIVE = 1 THEN 'Active' ELSE 'Inactive' END AS [Active],
CASE WHEN PSTNGTYP = 0 THEN 'Balance Sheet' ELSE 'Profit and Loss' END AS [Financial Statement],
CASE WHEN ACCTTYPE = 1 THEN 'Posting' ELSE 'Unit' END AS [Account Type],
ACCATDSC AS [Account Category],
CASE WHEN TPCLBLNC = 0 THEN 'Debit' ELSE 'Credit' END AS [Typical Balance],
CASE WHEN TPCLBLNC = 0 THEN 1 ELSE -1 END AS [TypBalMult],
CAST(0 AS numeric(19,5)) AS [Beginning Balance],
CAST(0 AS numeric(19,5)) AS [Net Change],
CAST(0 AS numeric(19,5)) AS [Ending Balance],
GL00105.*
INTO GL_AccountBalances
FROM GL00100, SY40100, GL00105, GL00102
WHERE GL00100.ACTINDX = GL00105.ACTINDX AND
SERIES = 0 AND
ACCTTYPE IN (1, 2) AND
GL00100.ACCATNUM = GL00102.ACCATNUM


-- Update Net Change


UPDATE GL_AccountBalances
SET [Net Change] = b.PERDBLNC
FROM GL_AccountBalances a, GL10110 b
WHERE a.ACTINDX = b.ACTINDX AND
a.[Fiscal Year] = b.YEAR1 AND
a.PERIODID = b.PERIODID


UPDATE GL_AccountBalances
SET [Net Change] = b.PERDBLNC
FROM GL_AccountBalances a, GL10111 b
WHERE a.ACTINDX = b.ACTINDX AND
a.[Fiscal Year] = b.YEAR1 AND
a.PERIODID = b.PERIODID


-- Update Ending Balance


UPDATE GL_AccountBalances SET [Ending Balance] =
(SELECT SUM(b.[Net Change]) FROM GL_AccountBalances b
WHERE b.ACTINDX = GL_AccountBalances.ACTINDX AND
 b.[Fiscal Year] = GL_AccountBalances.[Fiscal Year] AND
 b.PERIODID <= GL_AccountBalances.PERIODID)


-- Update Beginning Balance


UPDATE GL_AccountBalances SET [Beginning Balance] = [Ending Balance] - [Net Change]


When you execute the above SQL in TWO (sample company) database and query the GL_AccountBalances table, you see the following results (partial).


As you can see, these results match the GL Summary inquiry in Dynamics GP.


DISCLAIMER: I verified the results of the above SQL on the sample company database (TWO). If you see an issue or have a suggestion to improve this SQL, please let me know.