Getting the first day of the next six months – using DATEFROMPARTS

It is always tempting just to MONTH(GETDATE())+1 – but then there’s the end of the year to consider.

DECLARE @ThisMonth DATE = (SELECT DATEFROMPARTS(YEAR(GETDATE()),MONTH(GETDATE()),1));

DECLARE @NextMonth DATE = (SELECT
CASE
WHEN MONTH(@ThisMonth) = 12 THEN (SELECT DATEFROMPARTS(YEAR(@ThisMonth)+1,1,1))
ELSE (SELECT DATEFROMPARTS(YEAR(@ThisMonth),MONTH(@ThisMonth)+1,1))
END);

DECLARE @NextMonth1 DATE = (SELECT
CASE
WHEN MONTH(@NextMonth) = 12 THEN (SELECT DATEFROMPARTS(YEAR(@NextMonth)+1,1,1))
ELSE (SELECT DATEFROMPARTS(YEAR(@NextMonth),MONTH(@NextMonth)+1,1))
END);

DECLARE @NextMonth2 DATE = (SELECT
CASE
WHEN MONTH(@NextMonth1) = 12 THEN (SELECT DATEFROMPARTS(YEAR(@NextMonth1)+1,1,1))
ELSE (SELECT DATEFROMPARTS(YEAR(@NextMonth1),MONTH(@NextMonth1)+1,1))
END);

DECLARE @NextMonth3 DATE = (SELECT
CASE
WHEN MONTH(@NextMonth2) = 12 THEN (SELECT DATEFROMPARTS(YEAR(@NextMonth2)+1,1,1))
ELSE (SELECT DATEFROMPARTS(YEAR(@NextMonth2),MONTH(@NextMonth2)+1,1))
END);

DECLARE @NextMonth4 DATE = (SELECT
CASE
WHEN MONTH(@NextMonth3) = 12 THEN (SELECT DATEFROMPARTS(YEAR(@NextMonth3)+1,1,1))
ELSE (SELECT DATEFROMPARTS(YEAR(@NextMonth3),MONTH(@NextMonth3)+1,1))
END);

SELECT @ThisMonth
UNION
SELECT @NextMonth
UNION
SELECT @NextMonth1
UNION
SELECT @NextMonth2
UNION
SELECT @NextMonth3
UNION
SELECT @NextMonth4

Consolidating several Syspro (V6) companies into one Syspro (V7) company.

I was tasked to project manage the consolidation of 13 x Syspro (V6) companies into one Syspro (V7) company in 2015.

The first phase was a master file cleanup, standardisation and setup check.  The following areas were covered:

Inventory Master  (InvMaster)

I had to ensure each (unique) StockCode had the same description, Unit of Measure, Alternate Unit Of Measure and conversion factor, etc. – and that it represented the same item in reality – for all companies.  I found the easiest way to do this, was to create a single ‘consolidated’ InvMaster – and compare each companies’ data to it.

Customer and Supplier Master (ArCustomer and ApSupplier)

Each companies’ Customer and Supplier accounts remained unique – but Area, Class, Branch, Terms Codes, etc. had to be standardised across all companies.  Price lists and Contracts were also made unique.

General Ledger (GenMaster)

There was no time for a complete re-code of all GlCodes – so I prefixed each companies’ GlCodes to make it unique.

Other data and setup

Banks, Tax Codes, Gl Integration, Document Numbering, additional fields*, were just some of the list of areas that were covered.

*V6 uses AdmFormData, whereas V7 makes use of the (MUCH more convenient) ‘plus’ tables.  e.g. [InvMaster+], [ArCustomer+], etc.

 

A staggered approach was used for implementation (as opposed to a ‘big bang’) – and this proved to be very wise.  Learnings from each go-live were incorporated for the next consolidation run.

I wrote SQL Server 2014 scripts to handle the extraction, transformation and loading (ETL) of each companies’ Syspro data into the Consolidated environment.  For the very first implementation, I ran the (list of 70!) manually – but the rest was done via SQL Server Jobs.

How to make a string out of a list (or a set of rows) MS SQL

You’ve got table #t1:

InvoiceNumber StockCode
Inv001 Stk001
Inv001 Stk002
Inv001 Stk003
Inv002 Stk001
Inv002 Stk003

But you want:

InvoiceNumber StockList
Inv001 Stk001,Stk002,Stk003
Inv002 Stk001,Stk003

 

Then use:

SELECT

DISTINCT t2.InvoiceNumber,

SUBSTRING((Select‘,’+RTRIM(t1.StockCode)AS[text()]

FROM #t1 t1

WHERE t1.InvoiceNumber = t2.InvoiceNumber

ORDER BY t1.InvoiceNumber,StockCode

For XML PATH ()),2, 1000)[StockList]

FROM  #t1 t2;

 

Most handy MS SQL query – ever

Find any field name in a MS SQL db – matching your search string (e.g. ‘perator’, as in ‘Operator’ or ‘operator’):

SELECT table_name=sysobjects.name,
column_name=syscolumns.name,
datatype=systypes.name,
length=syscolumns.length
FROM sysobjects
JOIN syscolumns ON sysobjects.id = syscolumns.id
JOIN systypes ON syscolumns.xtype=systypes.xtype
WHERE sysobjects.xtype=’U’ and syscolumns.name like ‘%perator%’

ORDER BY sysobjects.name,syscolumns.colid

Change the ‘syscolumns.name’ to ‘sysobjects.name’ to search on table names.

 

Handy when learning a new system!