Rows to columns for price breaks

No doubt your sales team want to go on the road with a human friendly version of your prices for the customers to read. It is possible to do this with a SQL table function to extract the prices from GP with price breaks. The following example assumes you know how many price breaks you have in your price lists and will result in output something like the following screen shot. These results may be squirted into excel with more columns as described by your business requirements.

Two key SQL server functions that many people I find are not familiar with but are vital for this kind of data manipulation are; “ROW_NUMBER()” and “PARTITION BY” , one way to learn is to dive in with an example.

GP Price Table

Natively the prices are held in the table IV00108 of your company database.

There is a row per “price point”. Each row contains, the item sku, Currency of the price list, price list name, unit of measure, quantity break range and price.

This is unreadable to humans once you get, say 15,000 products, five currencies and ten or so price levels. From experience, one company this solution is used with has 1,623,586 rows in the price table IV00108.

Table Partitioning

Firstly the rows are grouped together by the common factor each output row should be sharing. Each row in this example should have the same Item, Currency, Price Level and unit of measure. A row number is added for each successive row within this grouping;

SELECT 
ITEMNMBR,CURNCYID, PRCLEVEL, UOMPRICE, FROMQTY, UOFM, TOQTY, ROW_NUMBER() 
        OVER(PARTITION BY 
            ITEMNMBR,PRCLEVEL, CURNCYID, UOFM 
            ORDER BY toqty ASC) AS 'RowNumber'
FROM iv00108 (NOLOCK) WHERE itemnmbr='WIRE100'

The above TSQL partitions the returned rows from IV00108 by ITEMNMBR,PRCLEVEL, CURNCYID, UOFM, for each row in the group a row number is generated by ROW_NUMBER() see the following output example. For this example, there are two quanity break columns for the prices of the “foot” unit of measure.
These are breaks of; 0.01+ and 100+, resulting in row numbers one and two for this unit of measure.

Now that we have the RowNumber, this can act as the anchor field to crosstab the data with. It makes sense to wrap this query in a common table expression (CTE) to clean it up. The output from the below should be identical to that above.

WITH PriceTable 
(ITEMNMBR, CURNCYID, PRCLEVEL, UOMPRICE, FROMQTY,UOFM, TOQTY,[RowNumber]) AS 
(SELECT ITEMNMBR,CURNCYID, PRCLEVEL, UOMPRICE, FROMQTY, UOFM, toqty, ROW_NUMBER() 
        OVER(PARTITION BY 
        ITEMNMBR,PRCLEVEL, CURNCYID,UOFM 
        ORDER BY TOQTY ASC) AS 'RowNumber'
    FROM iv00108 (NOLOCK) where itemnmbr='WIRE100' 
)
SELECT * FROM PriceTable

Crosstabbing the Common Table Expression

Now building on the select statement from the CTE, it is crosstabbed by using CASE statements as shown below. All that has changed between these two scripts is the select out of the CTE. The select is also add “+” to the price from column results as well as some NULL handling to make the presentation cleaner for Excel should it end up there. This is optional, it might be more appropriate for other uses to keep the results as numeric values and do that kind of processing in the reporting tool.

WITH PriceTable 
(ITEMNMBR, CURNCYID, PRCLEVEL, UOMPRICE, FROMQTY,UOFM, TOQTY,[RowNumber]) AS 
(SELECT ITEMNMBR,CURNCYID, PRCLEVEL, UOMPRICE, FROMQTY, UOFM, toqty, ROW_NUMBER() 
        OVER(PARTITION BY 
        ITEMNMBR,PRCLEVEL, CURNCYID,UOFM 
        ORDER BY TOQTY ASC) AS 'RowNumber'
    FROM iv00108 (NOLOCK) where itemnmbr='WIRE100' 
)

select itemnmbr,  
 isnull(max(case when PriceTable.[RowNumber] = 1 then
    LTRIM( STR(FROMQTY,6,0)) + '+' end),'') as Break1
, max(case when PriceTable.[RowNumber] = 1 then 
    uomprice end) as Price1
, isnull(max(case when PriceTable.[RowNumber] = 2 then
    LTRIM( STR(FROMQTY,6,0)) + '+' end),'') as Break2
, max(case when PriceTable.[RowNumber] = 2 then 
    uomprice end) as Price2
,isnull( max(case when PriceTable.[RowNumber] = 3 then
    LTRIM( STR(FROMQTY,6,0)) + '+' end),'') as Break3
, max(case when PriceTable.[RowNumber] = 3 then 
    uomprice end) as Price3
,isnull( max(case when PriceTable.[RowNumber] = 4 then
    LTRIM( STR(FROMQTY,6,0)) + '+' end),'') as Break4
, max(case when PriceTable.[RowNumber] = 4 then
    uomprice end) as Price4
, isnull(max(case when PriceTable.[RowNumber] = 5 then
    LTRIM( STR(FROMQTY,6,0)) + '+' end),'') as Break5
, max(case when PriceTable.[RowNumber] = 5 then
    uomprice end) as Price5
, isnull(max(case when PriceTable.[RowNumber] = 6 then 
    LTRIM( STR(FROMQTY,6,0)) + '+' end),'') as Break6
, max(case when PriceTable.[RowNumber] = 6 then 
    uomprice end) as Price6
from PriceTable
group by itemnmbr, curncyid, prclevel, UOFM;

Table valued function

Great there we have it, price table partitioned and crosstabbed. Lets not stop there as this is much more useful as a table valued function. This is achieved by wrapping the above SQL as shown below. Here we have decided that the calling script should provide the currency, pricelist, item pattern and unit of measure to export. Your application may differ and not require the expensive type conversions.

CREATE function [dbo].[Extract_PricesCrosstabTable] (
 @CURNCYID varchar(15),
 @PRCLEVEL varchar(11),
 @ITEMPATTERN  nvarchar(31) = '%',
 @UOFM varchar(9) = '%'
)
RETURNS @retTable TABLE 
(
    [ITEMNMBR] [varchar](31) primary key NOT NULL,
    [BREAK1] [varchar](255) NOT NULL,
    [PRICE1] [numeric](19, 5) NULL,
    [BREAK2] [varchar](255) NOT NULL,
    [PRICE2] [numeric](19, 5) NULL,
    [BREAK3] [varchar](255) NOT NULL,
    [PRICE3] [numeric](19, 5) NULL,
    [BREAK4] [varchar](255) NOT NULL,
    [PRICE4] [numeric](19, 5) NULL,
    [BREAK5] [varchar](255) NOT NULL,
    [PRICE5] [numeric](19, 5) NULL,
    [BREAK6] [varchar](255) NOT NULL,
    [PRICE6] [numeric](19, 5) NULL
)
AS
BEGIN

WITH PriceTable 
(ITEMNMBR, CURNCYID, PRCLEVEL, UOMPRICE, FROMQTY,UOFM, TOQTY,[RowNumber]) AS 
(SELECT ITEMNMBR,CURNCYID, PRCLEVEL, UOMPRICE, FROMQTY, UOFM, toqty, ROW_NUMBER() 
        OVER(PARTITION BY 
        ITEMNMBR,PRCLEVEL, CURNCYID,UOFM 
        ORDER BY TOQTY ASC) AS 'RowNumber'
    FROM iv00108 (NOLOCK) where itemnmbr like @ITEMPATTERN and PRCLEVEL= @PRCLEVEL 
    AND CURNCYID=@CURNCYID AND UOFM LIKE @UOFM
)
INSERT @retTable
select itemnmbr,  
 isnull(max(case when PriceTable.[RowNumber] = 1 then
    LTRIM( STR(FROMQTY,6,0)) + '+' end),'') as Break1
, max(case when PriceTable.[RowNumber] = 1 then 
    uomprice end) as Price1
, isnull(max(case when PriceTable.[RowNumber] = 2 then
    LTRIM( STR(FROMQTY,6,0)) + '+' end),'') as Break2
, max(case when PriceTable.[RowNumber] = 2 then 
    uomprice end) as Price2
,isnull( max(case when PriceTable.[RowNumber] = 3 then
    LTRIM( STR(FROMQTY,6,0)) + '+' end),'') as Break3
, max(case when PriceTable.[RowNumber] = 3 then 
    uomprice end) as Price3
,isnull( max(case when PriceTable.[RowNumber] = 4 then
    LTRIM( STR(FROMQTY,6,0)) + '+' end),'') as Break4
, max(case when PriceTable.[RowNumber] = 4 then
    uomprice end) as Price4
, isnull(max(case when PriceTable.[RowNumber] = 5 then
    LTRIM( STR(FROMQTY,6,0)) + '+' end),'') as Break5
, max(case when PriceTable.[RowNumber] = 5 then
    uomprice end) as Price5
, isnull(max(case when PriceTable.[RowNumber] = 6 then 
    LTRIM( STR(FROMQTY,6,0)) + '+' end),'') as Break6
, max(case when PriceTable.[RowNumber] = 6 then 
    uomprice end) as Price6
from PriceTable
group by itemnmbr, curncyid, prclevel, UOFM;

RETURN

END;

Putting it to work

Now it is a table valued function, this allows a crosstabbed price table to be used as if it were a table. For example to add in the item description from the item master table IV00101, the following is used;

SELECT 
IV00101.ITEMDESC, 
PricesCrossTab.* 
From Extract_PricesCrosstabTable('Z-US$','RETAIL','WIRE%','Foot') PricesCrossTab
JOIN IV00101
ON PricesCrossTab.ITEMNMBR= IV00101.ITEMNMBR

The unit of measure has been used as a parameter here for selection, however by changing the schema of the table valued function returned table type to include unit of measure as part of the primary key, all units of measure can be returned. This is the foundations of some scripts that can be amended to produce the results that you require for your particular circumstances.

We upgraded our SQL server from 2000 to 2008 early this year and although I have been using 2005 for a while for Microsoft Dynamics GP, only the website really stretches my TSQL skills to the limit. I have a library of scripts that solve most problems or can be adapted to address most issues that occur in Dynamics GP developed in SQL server 2000 days.

This year I have been wallowing in

1. Common Table Expressions (CTE)
2. Availability of the TOP command in INSERT DELETES
3. Ranking expressions
4. VARCHAR(MAX) data types, and the exciting date types in SQL 2008
5. Outputting affected rows (INSERT/UPDATE/DELETE)
6. Enhancements in TOP to allow use to filter results in SProcs
7. Intelisense in Management Studio

These new friends almost eradicate cursors and triggers for all but the most complex scenarios.

I found that Programming Microsoft® SQL Server® 2008 (PRO-Developer) was great step up book to get from SQL 2000 experienced DBA to SQL 2008. No wasting time going over basics, very factual expecting you to pick up from where you left with SQL 2000.

Another book I still enjoy today is the The Guru's Guide to Transact SQL (Paperback), that is technically a little outdated but the way this book makes you think about SQL really gives you a leg up to a higher  level of TSQL programming that  you can still apply to the newer versions of SQL server.

Deduplication

I had a duplication problem to solve today. The usual issue where there is a plain text imported file that has issues. The import table had no primary key and many thousands of duplicate rows that needed removing. This could be done with SQL server 2000, it was a pain and never very pretty. Now  the solution is beautiful.

Create the table, fill it with some duplicate lines.

CREATE TABLE [ItemImages_Import](

    [ItemCode] [varchar](31) NULL,

    [Graphic] [varchar](255) NULL,

    [Position] [smallint] NULL

) ON [PRIMARY]

GO

then goodness me all you have to do is run this script and your duplicates are no more!

WITH VirtTable 

as (select

 itemcode,graphic,ROW_NUMBER() 

    OVER (PARTITION BY itemcode  order by graphic

     as GroupCounter)

from ItemImages_Import)

Delete from VirtTable where GroupCounter>1

This uses the ranking expression ROW_NUMBER() to create a temporary “key” that is then used to delete the items after the “1”st instance in the table of the duplicate. This is great stuff.

Create Row number in a Grouping using TSQL

My next problem is that I wanted to renumber the position of the images in the table using the sort column, overwriting the existing values;

288-288      graphic1.jpg     1
288-288      graphic2.jpg     2
288-288      graphic3.jpg     3
288-999      graphic4.jpg     1
288-999      graphic5.jpg     2
289-100      graphic6.jpg     1
289-100      graphic7.jpg     2
289-100      graphic8.jpg     3
etc

Again its a doddle once you get your head around rankings.

UPDATE ItemImages_Import
set position=0;
WITH VirtTable 
as 
(select *,
 ROW_NUMBER() 
    OVER (PARTITION BY itemcode 
     order by graphic)as GroupCounter
from ItemImages_Import)
UPDATE  VirtTable
 SET position=GroupCounter;
select * from ItemImages_Import order by 
    ItemCode, Position

Just to prove it works I blank the column first with the update.

Happy TSQLing!

I’ve just spent a few hours this morning working on a drop down menu for the website. It works a little like the Microsoft Vista file browser address bar. Any part of the breadcrumb type trail can be clicked on to drop down the other categories at that level.

Since I’ve upgraded the website SQL server earlier in the year from 2000 to 2008 version of SQL server I now can utilise Common Table Expressions to get the solution (CTE). Used in a recursive manner you can do all sorts that needed cursors before – great stuff!

Here was my resulting script before packaging into a  stored procedure:

-- Script to get all the categories from a given category up over 

-- includes a mark against the path we are on and other categories at that level.

-- T.WAPPAT 20th July 2009

---

DECLARE @CatID Integer

SET @CatID=22141

WITH CategoryParents(CatID, ParentCatID, SortInLevel, 

                            CatLangCode, CatDesc, InPath,  Depth)

AS

(

--Anchor member

SELECT    CategoriesSibs.CatID as CatID, CategoriesSibs.ParentCatID,

        CategoriesSibs.SortInLevel, CategoriesDesc.CatLangCode, 

        CategoriesDesc.CatDesc, 

        CASE CategoriesSibs.CatID WHEN @CatID 

            THEN 1 ELSE 0 END as InPath,

        0 as Depth

FROM  MA_Categories AS Categories WITH (NOLOCK)   

      JOIN 

      MA_Categories as CategoriesChild WITH (NOLOCK) 

        ON CategoriesChild.ParentCatID=Categories.CatID

      JOIN 

      MA_Categories as CategoriesSibs WITH (NOLOCK) 

        ON CategoriesSibs.ParentCatID=Categories.CatID

      JOIN

      MA_CategoryDescs AS CategoriesDesc WITH (NOLOCK) 

        ON CategoriesDesc.CatID = CategoriesSibs.CatID 

WHERE CategoriesChild.CatID=@CatID

UNION ALL

--Recursive member

select  Categories.CatID, Categories.ParentCatID, 

        CategoriesSibs.SortInLevel, CategoriesDesc.CatLangCode,

        CategoriesDesc.CatDesc, 

        CASE CategoriesSibs.CatID WHEN CategoryPArents.ParentCatID 

            THEN 1 ELSE 0 END as InPath,

        Depth+1 as Depth

FROM         CategoryParents AS CategoryParents 

             JOIN 

             MA_Categories AS Categories WITH (NOLOCK) 

                ON CategoryParents.ParentCatID=Categories.CatID  

             JOIN

             MA_Categories as CategoriesSibs WITH (NOLOCK) 

                ON CategoriesSibs.ParentCatID=Categories.ParentCatID

                JOIN

             MA_CategoryDescs AS CategoriesDesc WITH (NOLOCK) 

                ON CategoriesDesc.CatID = CategoriesSibs.CatID  

)

-- execute CTE

select  distinct * from CategoryParents  ORDER BY Depth, SortinLevel

You may find this useful to hack around for your own purposes. The following are table definitions.

CREATE TABLE [dbo].[MA_Categories](

    [CatID] [int] NULL,

    [ParentCatID] [int] NULL,

    [SortInLevel] [int] NULL

) ON [PRIMARY]

CREATE TABLE [dbo].[MA_CategoryDescs](

    [CatID] [int] NULL,

    [CatLangCode] [varchar](3) NULL,

    [CatDesc] [varchar](200) NULL

) ON [PRIMARY]