How to find duplicate Value in SQL

Identify Duplicate Criteria

The first step is to define your criteria for a duplicate row. Do you need a combination of two columns to be unique together, or are you simply searching for duplicates in a single column? In this example, we are searching for duplicates across two columns in our Users table: username and email.

Write Query to Verify Duplicates Exist

The first query we’re going to write is a simple query to verify whether duplicates do indeed exist in the table. For our example, my query looks like this:

SELECT username, email, COUNT(*)
FROM users
GROUP BY username, email

HAVING is important here because unlike WHEREHAVING filters on aggregate functions.

If any rows are returned, that means we have duplicates. In this example, our results look like this:


List All Rows Containing Duplicates

In the previous step, our query returned a list of duplicates. Now, we want to return the entire record for each duplicate row.

To accomplish this, we’ll need to select the entire table and join that to our duplicate rows. Our query looks like this:

FROM users a
JOIN (SELECT username, email, COUNT(*)
FROM users 
GROUP BY username, email
HAVING count(*) > 1 ) b
ON a.username = b.username

If you look closely, you’ll see that this query is not so complicated. The initial SELECT simply selects every column in the users table, and then inner joins it with the duplicated data table from our initial query. Because we’re joining the table to itself, it’s necessary to use aliases (here, we’re using a and b) to label the two versions.

Here is what our results look like for this query:


Because this result set includes all of the row ids, we can use it to help us deduplicate the rows later.

Sage Business Cloud Enterprise Management HR update 12 patch 15 is out

Sage Business cloud enterprise management HR (formerly known as Sage X3 People) has a new release V12 2018 R8 (V12.0.15).

The following are the some of the featues in this release

  • Global Leave Management Priority List
  • Global Leave Management – Multiple contracts (with the same CRONO) linked to the same Nature of event
  • Additional scenarios have been added to the Global Leave Management functionality to validate a transaction against a leave subtype the employee is no longer linked to for Accruals.
  • An additional grid has been added to the Global Leave Management functionality so the balance screen correctly displays the number of leave transactions, and the balance, per leave subtype. This is useful for scenarios where leave is captured over a cycle.
  • Clarified payslip model

Self Service Command Centre

The Employee Self Service Personal information lets an employee view their own personal details. This includes Personal information, Persons to contact, Spouse information, Child/dependent information, Addresses and Passport information. They can also edit or update their details on Personal information, Child/dependent information, Persons to contact and Passport information. The workflow sends the change request to Manager Self Service.
This is an internal delivery because the Self Service Command Center is planned for the next release.

Manager Self Serivce
Employee Self Service

Understanding the new user interface download the following document

How to efficiently manage a project

I will be discussing some tip to that has been helpful to me in the course of managing various project across Africa.

1:   Get clear about your role and your responsibility.
2:   Make an assessment of whether the framework is realistic, both in time and budget, prior to accepting the project.
3:   Find out as much as possible at the first meeting with the client or project promoter. Do you understand the purpose? What’s the status of the project inside the organization?
4:   Ensure that project documentation is well-founded, and that it has management support and sufficient resources.
5:   Gain knowledge of the subject and the client. It is necessary because you, as a project manager, have responsibility for the results and conclusions of the group.
6:   Do not enter the project without proper planning.
7:   Make a rough schedule together with the project team. This will get them involved and accept the time you planned. It’s also a more efficient way of working.
8:   Do a risk analysis before the project starts. What can go wrong? Which backup can you have?
9:   Make a project plan. A detailed requirements specification of the project, with the purpose, background, objectives and targets, milestones, constraints, resources and timetable. Include project organization, information, support and follow-up.
10:  Ensure targets are completed before you start working, and that they are thorough and clearly formulated.
11:  The goals should be consistent. See therefore through each milestone both separately and as a part of the cycle. See the total picture!
12:  Break down the project overall goals into specific targets that can be coordinated and monitored.
13:  Describe the objectives of the project, so everyone is clear on where you are going.
14:  What is a priority for the project to achieve its goal? Is high quality the most imortant aspect or that it should be quick and not cost a lot? Determine expectations for quality, time and cost.
15:  Do not be impatient in the beginning, the foundation must be completed before you get going.
16:  Know your own strengths and weaknesses, your own needs and motivations.
17:  Limit the project team’s size so that it does not become too cumbersome.
18:  Get the expertise you need. Find out what knowledge and what experience the project needs to go in goal.
19:  Make sure that you don’t select the most pleasant people.. those who are most like you. Search instead for people who complement each other.
20:  Remind your people that everyone need to share his/her knowledge and experience.
21:  Identify the various roles. Who is responsible for what?
22:  Establish decision-making and information flows. Who takes decisions and how are decisions made? Who informs whom?
23:  Make sure that the project team is talking to you, and not others, if they have opinions about the way you manage the project.
24:  Remember that we all want to be seen. How will you see your people’s work in the project? Show clearly that you see your people.. with constructive criticism, praise and appreciation.
25:  Focus on the good side of every employee, and reinforce it.
26:  Think about the stages a team goes through and adapt your role to it. At various stages in the project, you need different leadership styles.
27:  Do not waste your most important resource: your employees!
28:  Prepare well for your first meeting with your project team. It is here you’ll start creating a common platform for your work. And this is also where you will create a team spirit and motivate the group to give your project a flying start.
29:  In the beginning you need to clarify your expectations for the group, and you should give the group an opportunity to come to grips with their expectations of you.
30:  Discuss at one of your first meetings the rules of the game you should have. Make sure you all agree on them and that they are so clear that you can actually use them.
31:  Revisit your rules during the journey.
32:  Do not let the project group take over. It is you who must lead the work.
33:  Manage conflicts in time.
34:  Make sure that your employees get the opportunity to use their resources as much as possible – it is the best way to make a project successful.
35:  Hold stimulating, interesting and exciting project meetings. Skip the rest.
36:  Start documenting early.
37:  Focus on the right things.
38:  Focus on opportunities, innovate, solve problems.
39:  Create and re-create time and activity plans.
40:  Do a proper SWOT analysis.
41:  Keep all parties informed.
42:  Always follow up on deadlines, by yourself and with the group.
43:  Anchor the end of each phase with the steering group or project promoter.
44:  Dare to criticize when necessary, but do it in a constructive way.
45:  Say “no” when you have to! Set clear limits in your project management role.
46:  Your project reporting meetings are important. Prepare well. Figure out what you want and what kind of questions you’ll get asked.
47:  Make sure all anomalies are reported back to you fast, so you’ll get enough time steering the project.
48:  Relax and enjoy the journey! Project management is fun.


ERPs Pitfall

Implementing  ERP system and the likes is a major investment decision for any company.  ERPs impacts everyone within the organization, from front line staff to the senior executives.  ERPS implementations can be complex and if not managed properly can quickly lead to failure. Here are 5 common pitfalls, which can easily be avoided during implementation:

  1. Making a Choice Based on  ERPs Features Only

ERPS features are not the only thing to consider when deciding on which system to use. During  ERPs selection process, some people get dazzled by these features and make a choice based on that. Of importance to consider is the success history, flexibility, ease of customization and the ability to integrate the system with existing processes.

  1. Ignoring Departmental End-users in The Decision-Making Process

The implementation of  ERPS system is too important and delicate to be left on the hands of the leadership executives only. As a result, key employees actively involved in its use should be part of the process to ensure the search and implementation of a system that works for all. It’s not just for the IT department but also for operations, warehouse, and finance and so on.

  1. Inadequate requirement gathering

Managers make the mistake of implementing  ERPs in their existing business processes just as they are. It is important to conduct an analysis of the processes forming part of the gathering for  ERP requirements.  ERP systems should aim at improving the existing business processes. A bad process doesn’t get better just because it is automated. Therefore, it is important to highlight the critical issues prior to migration.

  1. Doing Everything at Once

Trying to implement  ERPS systems at once is not possible due to their complex nature. For that reason, it is difficult to gather all requirements upfront, implement the system, train user and go live. Instead, implementation should occur in smaller chunks with the involvement of all departments to establish what is required, test, find gaps and then repeat the process.

  1. Failure to Weigh the Benefits of Cloud-based and On-Premises  ERPs

Before settling on any  ERPS solution, the pros and cons of a cloud-based and on-premises  ERP systems must be analyzed. Ideally, a cloud system needs good internet connection and is beneficial especially for those working remotely. For on-premises option, there must be a dedicated team to support the  ERP solution. SaaS is quickly becoming the platform of choice for many companies due to its ease of implementation and post go live support. However, SaaS software do not have high flexibility for customizations, and can force business to adapt to the software rather than the other way around. This can especially be problematic for companies with unique business processes.

No one wants to find themselves in a scenario where their  ERPS is not working as expected. The above 5 mistakes can easily be avoided and allow organizations to have a higher return on their  ERPS investment.


Import, Export in Sage X3

Sage X3 Import/Export utility function is used to define the file formats used by the import and export functions for an object in order to integrate or extract data from the software database.

A file that can be used by the import module and generated by the export module is based on a file with one of the following structures :

  • flat file with fixed length fields.
  • flat file with separator between the fields (and records).
  • XML file

You can download my Volume 1


Manipulate time and date: Sage X3

I will be discussing how to manipulate time and date in Sage X3.

To get the current date in a Date variable , use the date function $ .
In the same way, to have the current date and time in a Datetime variable , use the datetime function $ (only from U9).
The canonical form to represent date and time with a string is:

YYYY-MM-DDThh: mm: ssZ #attention to the "extra" letters T and Z

Generally, date and time have “YYYY-MM-DDThh: mm: ss” format

num $ (date $) #To get today's date string
num $ (time $) #To get the current time as a string
num $ (datetime $) #To get today's date and time as a canonical string
gdat $ (DAY, MONTH, YEAR) #To get a date, day, month and year data
gdatetime $ (str) #To get a Datetime variable from a canonical string
year (date) #To get the year of a date
month (data) #To get the month number of a date
month $ (date) #To get the month name of a date in the current language
day (data) #To get the day number of the month (1-31)
day $ (date) #To get the name of the day of the week
dayn (data) #To get the day of the week number (1 = Monday, 7 = Sunday)
week (date) #To get the week number (1-53)
nday ​​(data) #To get the number of days passed since January 1, 1600
nday ​​$ (NUM) #To get a later date of NUM days from January 1, 1600
eomonth (data) #To get the last day of a given month


You can also perform simple operations:

Local Date DATA
DATE = [1/2/2015] +5 #DATA will contain [06/02/2015]
DATA = [1/2/2015] -1 #DATA will contain [31/01/2015]

For example:

Local date LASTDATE
LASTDATE = eomonth ([1/2/2015])
Infbox num $ (LASTDATE) # will display the date [28/02/2015]

To obtain a date as a string with the desired format:

format $ ( "D: YYYYMMDD [_] hhmmss", $ date)   

December 31, 2018 at 12.30 and 56 seconds will correspond to “20181231_123056”.

Note that square brackets are used to insert a space or other constant elements:

To get the date and time as a formatted string, you can also use AFNC.FDH.
With this call, use the formatting contained in the global variable GFMDAT.
Suppose today is January 19, 2018, and GFMDAT contains “DD [/] MM [/] YY”:

func AFNC.FDH (dates $, "121531") # returns "01/19/18 12:15:31"
func AFNC.FDH (dates $, "") # returns "19/01/18 17:08:27"
func AFNC.FDH (dates $, "15:32") # returns "19/01/18 15:32:00"
func AFNC.FDH (dates $, "15") # returns "19/01/18 15:00:00"
func AFNC.FDH (dates $, "15:") # returns "19/01/18 15:00:00"
func AFNC.FDH (date $, "string-errata") # returns "19/01/18"
func AFNC.FDH (dates $ + 1, "x") # returns "01/20/18"

It must be remembered that there are several global variables containing formatting strings,
all preset according to the various predefined parameters.
They are useful for maintaining a standard that is adapted to different users, sites, etc.

The order day-month-year of these formats depends precisely on the location and user settings.

The variables are these:

GFMDAT = standard date, with 2 characters per day, 2 for the month, 2 for the year
GFMDAT4 = date with the 4-digit year
GFMDAT3 = date with the month expressed in three letters
GFMDAT9 = date with the month written in letters
GFMMOI = month-year
GFMMOI4 = month-year with 4-digit year
GFMJOU = day-month


HR Processes

The modern Human Resources Organization and the effective HR Management is based on strong, simple and effective HR Processes. The HR Processes are always divided into the functional areas and the HR Managers are responsible for the design, development and re-design of the HR Processes as the HR Strategy can be implemented.


The HR Processes are the basis for the management of the client satisfaction with Human Resources. When the clients understand the HR Processes and how they are connected and how the HR Processes interact, they feel comfort and the satisfaction level increases.

The modern HR Processes are measured and constantly monitored as HR identifies the gaps in the processes and it brings the real improvements for the benefit of the organization.

Main HR Process Areas

The modern HR Processes are grouped into logical HR areas as they provide the value added to the internal and external clients. The main HR Process Areas are:

  • Recruitment and Staffing
  • Compensation and Benefits
  • Training and Development
  • Career Development
  • Talent Management
  • Leadership Development
  • HR Controlling

The real art of Human Resources is in the right connection of all processes as they provide the right support to managers and employees in the organization. The role of Human Resources is about keeping the human capital competitive on the market and the logical grouping of HR Processes helps to connect and manage the individual processes.

Each organization divides the HR Processes differently, but the main HR Processes are always in the same group in each company as it is the market HR Best Practice.

Sage x3, X3 People – Crystal Reports error – NGPAY.rpt : Table does not exist

Sage X3


Sage X3 , Sage X3 People, Crystal report


When running a crystal report either from Sage X3 ERP or Sage X3 People and you encounter an error like “XXXX.rpt: Table does not exist”

Possible cause:

  1. Physical report does not exist in the folder
  2. Missing default values


  1. A copy of the report not only needs to be in the report dictionary but also needs to exist in the folder on the server.
  2. Create/copy default values  for your crystal report

Crystal Report: Convert Amount to Words

Report Application: Crystal Report

Version: 2013


I have a field {ARIBH.AMTNETTOT} that has amount in figures and the client needs the amounts in words


//extract Naira  only

Propercase(ToWords(truncate({ARIBH.AMTNETTOT}),0) + ‘ Naira and ‘ +

//extract the Kobo and convert to words Another formula for rounding up.
ToWords(remainder({ARIBH.AMTNETTOT}*100,100),0) + ‘ Kobo Only.’)


Do have a lovely day

How to setup a workflow to email payslip to employees: Sage X3 People

Application: Sage X3 People

Version: U9 patch 5


  1. Open Setup, General parameters, Parameter values.
  2. Expand SUP chapter and click on Action button for the WRK Group.Following are some of the important parameters and their descriptions:SERMES – We can specify the SMTP server Hostname or IP address here. This server will then be used to send workflow email notifications. This field can be left blank if local email client is to be used to send workflow emails.

    TRAMEL – This parameter determines whether workflow logs are to be enabled or disabled.

    TYPMES – This parameter determines the default email sending mechanism. The possible options are:

    Server: Choose this option if SMTP Server is to be used to send workflow mails. SMTP server Hostname or the IP address must be specified the in the SERMES parameter.

    Client: Choose this option if the local email client (e.g. Outlook mail client) credentials is to be used to send the workflow emails.

    WRKRMTDIR – We need to specify the WORKFLOW directory path that on the webserver. This is the path where workflow replies will be stored for batch execution.

    WRKRMTHTTP – This is the X3 webserver machine name or IP address, with the relevant port number (e.g. 28880) and the X3 workflow process name AdxWfC (this name is case sensitive).

    WRKRMTMAC – This is the X3 webserver Hostname or the IP address along with the X3 Runtime service port number (e.g. 1802).

    WRKSINTER – This is the X3 webserver URL which can be accessed over the internet

    WRKSINTRA – This is the X3 webserver URL that can be accessed within the LAN (intranet)

    Hope this blog on Workflow parameters would help you in tuning your workflows as desired.

  3. Verify that you have an email server entered for parameter SERMES.
  4. Click OK.
  5. Click on Action button for RPT Group.
  6. Verify that ASAIDEST parameter is set to No.
  7. Open Setup, Workflow, Workflow rules.
  8. Select EMLPAY workflow and create a new workflow called EMLPAY2 , by coping EMLPAY.
  9. Set the condition on the workflow to only run when your Employee Payslip report is printing(in this example it’s ALLPAYSLIP)
  10. Click on Recipients tab and add Employee to the recipients, similar to below,( note this will email to the employee).
  11. On the Message tab, verify that GFILPATH is set under attached documents.
  12. Save and Validate.
  13. Open Personnel Management, Employee, Address Tab, Detail Section and Email ensure the employee has a valid email address
  14. Go to Report, Payroll Management
  15. Select ALLPAYSLIP report( this is the report we set as part of the workflow)
  16. Choose output of Message.
  17. Click Print.
  18. Your Employee would receive an email with the attached payslip