perhaps something strikes you with this sql

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

perhaps something strikes you with this sql

Author

Topic

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2011-01-19 : 14:51:23

HI this is long but it is creating some duplicate rows. perhaps something jumps out at you?

USE [SpendA]
GO
/****** Object: StoredProcedure [dbo].[GPImport] Script Date: 01/19/2011 14:45:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--DROP PROCEDURE GPImport
--EXEC GPImport 'PBS'
--EXEC GPImport 'SUPPL'
--DROP table #budget_staging
--DROP table ##staging
ALTER PROCEDURE [dbo].[GPImport]
@database VARCHAR(50)

AS
BEGIN

SET NOCOUNT ON;

DECLARE @datasource VARCHAR(10)
DECLARE @customertype VARCHAR(20)
DECLARE @sql VARCHAR(200)
DECLARE @importid INT
DECLARE @custcode VARCHAR(25)
DECLARE @custname VARCHAR(150)
DECLARE @customerid INT
DECLARE @department VARCHAR(250)
DECLARE @departmentid INT
DECLARE @itemnum VARCHAR(50)
DECLARE @itemname VARCHAR(200)
DECLARE @itemid INT

SET @datasource = @database
SET @customertype = CASE UPPER(@datasource)
WHEN 'PBS' THEN 'Dietary'
WHEN 'SUPPL' THEN 'Medical'
END

-- create synonym in order to have variable database name
SET @sql = 'CREATE SYNONYM SOP10200 FOR GPLAINS.' + @database + '.dbo.SOP10200'
EXEC (@sql)
SET @sql = 'CREATE SYNONYM SOP10100 FOR GPLAINS.' + @database + '.dbo.SOP10100'
EXEC (@sql)
SET @sql = 'CREATE SYNONYM RM00101 FOR GPLAINS.' + @database + '.dbo.RM00101'
EXEC (@sql)
SET @sql = 'CREATE SYNONYM IV00101 FOR GPLAINS.' + @database + '.dbo.IV00101'
EXEC (@sql)
SET @sql = 'CREATE SYNONYM SOP10106 FOR GPLAINS.' + @database + '.dbo.SOP10106'
EXEC (@sql)
SET @sql = 'CREATE SYNONYM IV40600 FOR GPLAINS.' + @database + '.dbo.IV40600'
EXEC (@sql)
SET @sql = 'CREATE SYNONYM CustomerBudget FOR GPLAINS.' + @database + '.dbo.CustomerBudget'
EXEC (@sql)

INSERT INTO Imports(DataSource, ImportStart)
VALUES(@datasource, CURRENT_TIMESTAMP)

SET @importid = (
SELECT @@IDENTITY
)

CREATE TABLE #staging (
CustomerName CHAR(65) NULL
,Department CHAR(65) NULL
,CustomerNumber CHAR(15) NULL
,ItemNumber CHAR(31) NULL
,ItemDescription CHAR(101) NULL
,Price NUMERIC(19,5) NULL
,Quantity NUMERIC(19,5) NULL
,InvoiceNum CHAR(21) NULL
,DateCreated DATETIME NULL
,CategoryName CHAR(11) NULL
,CategoryLongName CHAR(255) NULL
,Budget FLOAT NULL
,DexRowId INT NULL
,CustomerId INT NULL
,DepartmentId INT NULL
,ItemId INT NULL
,CategoryId INT NULL
)

INSERT INTO #staging(CustomerName, Department, CustomerNumber, ItemNumber, ItemDescription,
Price, Quantity, DateCreated, InvoiceNum, CategoryName,
CategoryLongName, DexRowId)
SELECT LTRIM(RTRIM(cm.CUSTNAME)) AS CustomerName
,CASE WHEN LTRIM(RTRIM(sud.USRDEF05)) != '' THEN LTRIM(RTRIM(sud.USRDEF05)) ELSE LTRIM(RTRIM(cm.CUSTNAME)) END AS Department
,LTRIM(RTRIM(soh.CUSTNMBR)) AS CustomerNumber
,LTRIM(RTRIM(sol.ITEMNMBR)) AS ItemNumber
,LTRIM(RTRIM(sol.ITEMDESC)) AS ItemDescription
,sol.XTNDPRCE AS Price
,sol.QUANTITY AS Quantity
,soh.DOCDATE AS DateCreated
,LTRIM(RTRIM(sol.SOPNUMBE)) AS InvoiceNum
,LTRIM(RTRIM(im.ITMGEDSC)) AS CategoryName
,COALESCE(LTRIM(RTRIM(iuc.UserCatLongDescr)), LTRIM(RTRIM(im.ITMGEDSC))) AS CategoryLongName
--,(SELECT TOP 1 ISNULL(BudgetAmount,0)
-- FROM CustomerBudget
-- WHERE soh.DOCDATE BETWEEN BudgetDateFrom AND BudgetDateTo
-- AND soh.CUSTNMBR = CUSTNMBR
-- AND im.ITMGEDSC = USCATVAL
-- AND (sud.USRDEF05 IS NULL OR LTRIM(RTRIM(sud.USRDEF05))='' OR sud.USRDEF05 = Dept) -- original _FnGetBudgetAmount_Spend function did not search by dept
-- AND BudgetAmount > 0
-- ORDER BY BudgetDateFrom DESC) AS Budget
--,dbo._FnGetBudgetAmount_Spend(soh.DOCDATE, soh.CUSTNMBR, iuc.USCATVAL, sud.USRDEF05) AS Budget
,sol.DEX_ROW_ID
FROM SOP10200 sol -- sales order lines
INNER JOIN SOP10100 soh ON soh.SOPTYPE = sol.SOPTYPE AND soh.SOPNUMBE = sol.SOPNUMBE -- sales order header
INNER JOIN RM00101 cm ON cm.CUSTNMBR = soh.CUSTNMBR -- customer master
INNER JOIN IV00101 im ON im.ITEMNMBR = sol.ITEMNMBR -- item master
LEFT JOIN SOP10106 sud ON sud.SOPTYPE = sol.SOPTYPE AND sud.SOPNUMBE = sol.SOPNUMBE -- sales user defined
LEFT JOIN IV40600 iuc ON iuc.USCATVAL = im.ITMGEDSC -- inventory user category master
WHERE soh.SOPTYPE = '1'
AND soh.DOCDATE >= DATEADD(YYYY, -2, GETDATE())
AND soh.DOCDATE <= GETDATE()

-- Set existing customer ids
UPDATE #staging
SET CustomerId = c.CustomerId
FROM CustomerMappings c
WHERE #staging.CustomerNumber = c.DataSourceCustomerId
AND c.DataSource = @datasource

-- If necessary, add some code here
-- to check if names of existing customers
-- need to be updated.

-- New customers
DECLARE c CURSOR
FOR SELECT s.CustomerNumber,
(SELECT TOP 1 CustomerName
FROM #staging
WHERE CustomerNumber = s.CustomerNumber
ORDER BY DateCreated DESC) AS CustomerName
FROM #staging s
WHERE CustomerId IS NULL
GROUP BY CustomerNumber

OPEN c

FETCH NEXT FROM c INTO @custcode, @custname

WHILE (@@FETCH_STATUS = 0)
BEGIN

INSERT INTO Customers(CustomerName, CustomerType)
VALUES(@custname, @customertype)

SET @customerid = (
SELECT @@IDENTITY
)

INSERT INTO CustomerMappings(DataSource, DataSourceCustomerId, CustomerId)
VALUES(@datasource, @custcode, @customerid)

UPDATE #staging
SET CustomerId = @customerid
WHERE CustomerNumber = @custcode
AND CustomerName = @custname

FETCH NEXT FROM c INTO @custcode, @custname
END

CLOSE c
DEALLOCATE c

---- Set department ids
--UPDATE #staging
--SET DepartmentId = d.DepartmentId
--FROM Departments d
--WHERE #staging.Department = d.DepartmentName
--AND #staging.CustomerId = d.CustomerId

---- New departments
--INSERT INTO Departments(CustomerId, DepartmentName)
--SELECT CustomerId
-- ,Department
--FROM #staging
--WHERE DepartmentId IS NULL
--GROUP BY CustomerId, Department

--UPDATE #staging
--SET DepartmentId = d.DepartmentId
--FROM Departments d
--WHERE d.CustomerId = #staging.CustomerId
--AND d.DepartmentName = #staging.Department
--AND #staging.DepartmentId IS NULL

-- existing departments
UPDATE #staging
SET DepartmentId = m.DepartmentId
FROM DepartmentMappings m
WHERE #staging.Department = m.DataSourceDepartmentId
AND #staging.CustomerId = m.CustomerId
AND m.DataSource = @datasource

-- New departments
DECLARE c CURSOR
FOR SELECT CustomerId
,Department
FROM #staging s
WHERE DepartmentId IS NULL
GROUP BY CustomerId, Department

OPEN c

FETCH NEXT FROM c INTO @customerid, @department

WHILE (@@FETCH_STATUS = 0)
BEGIN

INSERT INTO Departments(CustomerId, DepartmentName)
VALUES(@customerid, @department)

SET @departmentid = (
SELECT @@IDENTITY
)

INSERT INTO DepartmentMappings(DataSource, DataSourceDepartmentId, CustomerId, DepartmentId)
VALUES(@datasource, @department, @customerid, @departmentid)

UPDATE #staging
SET DepartmentId = @departmentid
WHERE Department = @department
AND CustomerId = @customerid

FETCH NEXT FROM c INTO @customerid, @department

END

CLOSE c
DEALLOCATE c

-- Set category ids
-- Categories are matched by
-- DepartmentId and CategoryName
UPDATE #staging
SET CategoryId = c.CategoryId
FROM Categories c
WHERE #staging.CategoryName = c.CategoryName
AND #staging.DepartmentId = c.DepartmentId

-- Add code to update CategoryLongNames
-- in case they changed?

-- New categories
INSERT INTO Categories(DepartmentId, CategoryName, CategoryLongName)
SELECT DepartmentId
,CategoryName
,(SELECT TOP 1 CategoryLongName
FROM #staging
WHERE s.DepartmentId = DepartmentId
AND s.CategoryName = CategoryName) AS CategoryLongName
FROM #staging s
WHERE CategoryId IS NULL
GROUP BY DepartmentId, CategoryName

UPDATE #staging
SET CategoryId = c.CategoryId
FROM Categories c
WHERE #staging.CategoryName = c.CategoryName
AND #staging.DepartmentId = c.DepartmentId
AND #staging.CategoryId IS NULL

-- Existing items
UPDATE #staging
SET ItemId = m.ItemId
FROM ItemMappings m
WHERE #staging.ItemNumber = m.DataSourceItemId

-- If necessary, add some code
-- to update existing item names
-- in case they changed

-- New items
DECLARE c CURSOR
FOR SELECT s.ItemNumber
,(SELECT TOP 1 ItemDescription
FROM #staging
WHERE ItemNumber = s.ItemNumber
ORDER BY DateCreated DESC) AS ItemDescription
FROM #staging s
WHERE ItemId IS NULL
GROUP BY s.ItemNumber

OPEN c

FETCH NEXT FROM c INTO @itemnum, @itemname

WHILE (@@FETCH_STATUS = 0)
BEGIN

INSERT INTO Items(ItemName)
VALUES(@itemname)

SET @itemid = (
SELECT @@IDENTITY
)

INSERT INTO ItemMappings(DataSource, DataSourceItemId, ItemId)
VALUES(@datasource, @itemnum, @itemid)

UPDATE #staging
SET ItemId = @itemid
WHERE ItemNumber = @itemnum
--AND ItemDescription = @itemname

FETCH NEXT FROM c into @itemnum, @itemname

END

CLOSE c
DEALLOCATE c

DELETE FROM Transactions
WHERE DataSource = @datasource

INSERT INTO Transactions(CustomerId, DepartmentId, ItemId, CategoryId, Quantity,
Cost, DateCreated, InvoiceNumber, DataSource, DataSourceId, ImportId,
LastUpdate)
SELECT CustomerId
,DepartmentId
,ItemId
,CategoryId
,Quantity
,Price
,DateCreated
,InvoiceNum
,@datasource
,DexRowId
,@importid
,CURRENT_TIMESTAMP
FROM #staging

-- Budget info
CREATE TABLE #budget_staging (
CustomerNumber CHAR(15) NULL
,CategoryName CHAR(20) NULL
,BudgetDateFrom DATETIME NULL
,BudgetDateTo DATETIME NULL
,BudgetAmount MONEY NULL
,Department CHAR(100) NULL
,CustomerId INT NULL
,CategoryId INT NULL
,DepartmentId INT NULL
)

INSERT INTO #budget_staging(CustomerNumber, CategoryName, BudgetDateFrom,
BudgetDateTo, BudgetAmount, Department)
SELECT LTRIM(RTRIM(CUSTNMBR))
,LTRIM(RTRIM(USCATVAL))
,BudgetDateFrom
,BudgetDateTo
,BudgetAmount
,NULLIF(LTRIM(RTRIM(Dept)),'') AS Dept
FROM CustomerBudget
WHERE BudgetDateTo >= DATEADD(YYYY, -2, GETDATE())
AND BudgetAmount > 0

-- customers
UPDATE #budget_staging
SET CustomerId = m.CustomerId
FROM CustomerMappings m
WHERE m.DataSourceCustomerId = #budget_staging.CustomerNumber
AND m.DataSource = @datasource

-- add new customers here?
-- are there really customers w/o any transactions
-- who will be using the SpendAnalyzer?

-- departments
-- if dept is not defined, use customer name as department
UPDATE #budget_staging
SET Department = c.CustomerName
FROM Customers c
WHERE #budget_staging.CustomerId = c.CustomerId
AND #budget_staging.Department IS NULL
AND #budget_staging.CustomerId IS NOT NULL

UPDATE #budget_staging
SET DepartmentId = d.DepartmentId
FROM Departments d
WHERE d.DepartmentName = #budget_staging.Department
AND d.CustomerId = #budget_staging.CustomerId

-- check for new departments
-- Should we ignore these?
INSERT INTO Departments(CustomerId, DepartmentName)
SELECT CustomerId
,Department
FROM #budget_staging
WHERE DepartmentId IS NULL
AND CustomerId IS NOT NULL
AND Department IS NOT NULL
GROUP BY CustomerId, Department

-- update #budget_staging for depts just created
UPDATE #budget_staging
SET DepartmentId = d.DepartmentId
FROM Departments d
WHERE d.DepartmentName = #budget_staging.Department
AND d.CustomerId = #budget_staging.CustomerId
AND #budget_staging.DepartmentId IS NULL

-- categories
UPDATE #budget_staging
SET CategoryId = c.CategoryId
FROM Categories c
WHERE c.CategoryName = #budget_staging.CategoryName
AND c.DepartmentId = #budget_staging.DepartmentId

-- new categories
INSERT INTO Categories(DepartmentId, CategoryName, CategoryLongName)
SELECT DepartmentId
,CategoryName
,COALESCE(CategoryLongName, CategoryName) -- use category name if there's no long name
FROM (
SELECT DepartmentId
,CategoryName
-- get first long category name from IV40600 table
-- treat empty strings as NULLs
,(SELECT TOP 1 NULLIF(LTRIM(RTRIM(iuc.UserCatLongDescr)),'')
FROM IV40600 iuc
WHERE LTRIM(RTRIM(iuc.USCATVAL)) = CategoryName) as CategoryLongName
FROM #budget_staging
WHERE CategoryId IS NULL
AND CategoryName IS NOT NULL
AND DepartmentId IS NOT NULL
GROUP BY DepartmentId, CategoryName
)q

-- update #budget_staging with categories just created
UPDATE #budget_staging
SET CategoryId = c.CategoryId
FROM Categories c
WHERE c.CategoryName = #budget_staging.CategoryName
AND c.DepartmentId = #budget_staging.DepartmentId
AND #budget_staging.CategoryId IS NULL

DELETE FROM Budget

INSERT INTO Budget(CustomerId, DepartmentId, CategoryId, BudgetAmount,
BudgetDateFrom, BudgetDateTo)
SELECT CustomerId
,DepartmentId
,CategoryId
,BudgetAmount
,BudgetDateFrom
,BudgetDateTo
FROM #budget_staging
WHERE CustomerId IS NOT NULL

DROP SYNONYM SOP10200
DROP SYNONYM SOP10100
DROP SYNONYM RM00101
DROP SYNONYM IV00101
DROP SYNONYM SOP10106
DROP SYNONYM IV40600
DROP SYNONYM CustomerBudget

UPDATE Imports
SET ImportEnd = CURRENT_TIMESTAMP
WHERE ImportId = @importid

END

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2011-01-19 : 15:08:06

It was discovered that certain items are being imported twice into the Spenda database. It turns out that if a category is listed more than once in the IV40600 table, the join from IV00101 to IV40600 (on ITMGEDSC = USCATVAL) yields multiple rows instead of one. The IV40600 table is queried in order to get the category name (UserCatLongDescr).

In order to resolve this issue, I'm thinking of changing the left join from

LEFT JOIN IV40600 iuc ON iuc.USCATVAL = im.ITMGEDSC

to

LEFT JOIN IV40600 iuc ON iuc.DEX_ROW_ID = (SELECT MAX(DEX_ROW_ID) FROM IV40600 WHERE USCATVAL=im.ITMGEDSC AND UserCatLongDescr!='')

Does this make sense to you?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-19 : 15:36:03

Replace this ugly piece of code

SET @importid = (
SELECT @@IDENTITY
)

with

SET @importid = scope_identity()

Likewise for the @ItemID variable.

N 56°04'39.26"
E 12°55'05.63"

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2011-01-19 : 15:48:58

OK thanks Pedro this is in, do you feel it is causing this error?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-19 : 15:51:53

It can be, because you are not guaranteed @@IDENTITY is returning "your" value. It can be another other identity value created in the database such as trigger inserting into an auxiliary table.

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-19 : 15:52:54

And give some love to @DepartmentID too...

N 56°04'39.26"
E 12°55'05.63"

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2011-01-19 : 15:53:58

Yup, plenty of love all around, Peaced, AW

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources