SQL Stored Procedure help

Question

Post reply

SQL Stored Procedure help

npatel 17252

SSC-Addicted

Points: 475
More actions
May 13, 2014 at 5:16 pm

#291324

So my company had a report created by a consulting group before I came on board, and now I am basically the reporting guy at the company. The Stored Procedure the consulting group is good however It is not grabbing some data and I figured out how to modify it to fix it but when I try to run it it says Source ID and ItemNumber cannot store NULL values, however I edited the columns to allow NULLS but still not working, the only thing I am changing is an INNER Join to a LEFT Join on the 'Inventory Transfer' portion of the code. Any insight?

Here is the original Code ->

USE [FAST]

GO

/****** Object: StoredProcedure [dbo].[CCPAspProjectCostCatDetail] Script Date: 05/13/2014 15:46:48 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:

-- Create date: 06/01/2013

-- Description:

-- =============================================

ALTER PROCEDURE [dbo].[CCPAspProjectCostCatDetail]

-- Add the parameters for the stored procedure here

@ProjectID char(15) = ''

,@PACostCatGrp char(15) = ''

AS

DECLARE

@PA_TIMESHEET int = 1

,@PA_EQUIPMENTLOG int = 2

,@PA_MISCLOG int = 3

,@PA_PURCHASEMATERIAL int = 4

,@PA_INVENTORYTRANSFER int = 5

,@PA_EMPLOYEEEXPENSE int = 6

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

CREATE TABLE [dbo].[#PACostCatDtl](

[PAUsageTypeNameId] [smallint] NOT NULL,

[PACostCatGrp] [char](15) NOT NULL,

[PACostCatGrpName] [char](30) NOT NULL,

[PAUsageTypeName] [varchar](30) NOT NULL,

[PAUsageCodeName] [varchar](30) NOT NULL,

[PAPROJNUMBER] [char](15) NOT NULL,

[PACOSTCATID] [char](15) NOT NULL,

[PADT] [datetime] NOT NULL,

[Quantity] [numeric](19, 5) NOT NULL,

[UnitCost] [numeric](19, 5) NOT NULL,

[ExtCost] [numeric](19, 5) NOT NULL,

[SourceName] [varchar](60) NULL,

[SourceID] [char](15) NOT NULL,

[Comments] [char](80) NOT NULL,

[DocumentNumber] [char](21) NOT NULL,

[ItemNumber] [char](31) NOT NULL,

)

/* TimeSheet */

/*

select top 5 'TimeSheetHdr_PA10000', * from PA10000 TimeSheetHdr

select top 5 'TimeSheetDtl_PA10001', * from PA10001 TimeSheetDtl

select top 5 'TimeSheetHdrHist_PA30100', * from PA30100 TimeSheetHdrHist

select 'TimeSheetDtlHist_PA30101', * from PA30101 TimeSheetDtlHist

*/

INSERT INTO [#PACostCatDtl]

([PAUsageTypeNameId]

,[PACostCatGrp]

,[PACostCatGrpName]

,[PAUsageTypeName]

,[PAUsageCodeName]

,[PAPROJNUMBER]

,[PACOSTCATID]

,[PADT]

,[Quantity]

,[UnitCost]

,[ExtCost]

,[SourceName]

,[SourceID]

,[Comments]

,[DocumentNumber]

,[ItemNumber])

SELECT

TimeSheetDtl.PATSTYP PAUsageTypeNameId

,TimeSheetDtl.PACOSTCATID PACostCatGrp

,'' PACostCatGrpName

,'Timesheet' PAUsageTypeName

,'Labor' PAUsageCodeName

,TimeSheetDtl.PAPROJNUMBER

,TimeSheetDtl.PACOSTCATID

,TimeSheetDtl.PADT

,(TimeSheetDtl.PAQtyQ) Quantity

,(TimeSheetDtl.PAUNITCOST) UnitCost

,(TimeSheetDtl.PAEXTCOST) + (TimeSheetDtl.PAEXTCOST * 1.81) ExtCost

,RTRIM(PayrollMstr.FRSTNAME) + ' ' + PayrollMstr.LASTNAME SourceName

,PayrollMstr.EMPLOYID SourceID

,PACOMM Comments

,TimeSheetHdr.PATSNO DocumentNumber

,''

FROM

PA10000 TimeSheetHdr WITH (NOLOCK)

INNER JOIN PA10001 TimeSheetDtl WITH (NOLOCK)

ON TimeSheetHdr.PATSNO = TimeSheetDtl.PATSNO

INNER JOIN UPR00100 PayrollMstr WITH (NOLOCK)

ON TimeSheetHdr.EMPLOYID = PayrollMstr.EMPLOYID

WHERE

TimeSheetDtl.PAPROJNUMBER LIKE RTRIM(@ProjectID) + '%'

UNION ALL

SELECT

TimeSheetDtlHist.PATSTYP PAUsageTypeNameId

,TimeSheetDtlHist.PACOSTCATID PACostCatGrp

,'' PACostCatGrpName

,'Timesheet' PAUsageTypeName

,'Labor' PAUsageCodeName

,TimeSheetDtlHist.PAPROJNUMBER

,TimeSheetDtlHist.PACOSTCATID

,TimeSheetDtlHist.PADT

,(TimeSheetDtlHist.PAQtyQ) Quantity

,(TimeSheetDtlHist.PAUNITCOST) UnitCost

,(TimeSheetDtlHist.PAEXTCOST) + (TimeSheetDtlHist.PAEXTCOST * 1.81) ExtCost

,RTRIM(PayrollMstr.FRSTNAME) + ' ' + PayrollMstr.LASTNAME SourceName

,PayrollMstr.EMPLOYID SourceID

,PACOMM Comments

,TimeSheetHdrHist.PATSNO DocumentNumber

,''

FROM

PA30100 TimeSheetHdrHist WITH (NOLOCK)

INNER JOIN PA30101 TimeSheetDtlHist WITH (NOLOCK)

ON TimeSheetHdrHist.PATSNO = TimeSheetDtlHist.PATSNO

INNER JOIN UPR00100 PayrollMstr WITH (NOLOCK)

ON TimeSheetHdrHist.EMPLOYID = PayrollMstr.EMPLOYID

WHERE

TimeSheetDtlHist.PAPROJNUMBER LIKE RTRIM(@ProjectID) + '%'

/* Equip Log */

/*

select top 5 'PAEquipLogHdrWork', * from PA10100 PAEquipLogHdrWork WITH (NOLOCK)

select top 5 'PAEquipLogLineWork', * from PA10101 PAEquipLogLineWork WITH (NOLOCK)

select top 5 'PAEquipLogHdrHist', * from PA30200 PAEquipLogHdrHist WITH (NOLOCK)

select top 5 'PAEquipLogLineHist', * from PA30201 PAEquipLogLineHist WITH (NOLOCK)

*/

INSERT INTO [#PACostCatDtl]

([PAUsageTypeNameId]

,[PACostCatGrp]

,[PACostCatGrpName]

,[PAUsageTypeName]

,[PAUsageCodeName]

,[PAPROJNUMBER]

,[PACOSTCATID]

,[PADT]

,[Quantity]

,[UnitCost]

,[ExtCost]

,[SourceName]

,[SourceID]

,[Comments]

,[DocumentNumber]

,[ItemNumber])

SELECT

@PA_EQUIPMENTLOG PAUsageTypeNameId

,PAEquipLogLineWork.PACOSTCATID PACostCatGrp

,'' PACostCatGrpName

,'Equip Log' PAUsageTypeName

,'Equipment' PAUsageCodeName

,PAEquipLogLineWork.PAPROJNUMBER

,PAEquipLogLineWork.PACOSTCATID

,PAEquipLogLineWork.PADT

,PAEquipLogLineWork.PAQTYQ Quantity

,PAEquipLogLineWork.PAUNITCOST UnitCost

,PAEquipLogLineWork.PAEXTCOST ExtCost

,PAEquipMstr.PAEQNME SourceName

,PAEquipMstr.PAEQUIPTID SourceID

,PAEquipLogHdrWork.PACOMM Comment

,PAEquipLogHdrWork.PAEQLOGNO DocumentNumber

,''

FROM

PA10100 PAEquipLogHdrWork WITH (NOLOCK)

INNER JOIN PA10101 PAEquipLogLineWork WITH (NOLOCK)

ON PAEquipLogHdrWork.PAEQLOGNO = PAEquipLogLineWork.PAEQLOGNO

INNER JOIN PA00701 PAEquipMstr WITH (NOLOCK)

ON PAEquipLogLineWork.PAEQUIPTID = PAEquipMstr.PAEQUIPTID

WHERE

PAEquipLogLineWork.PAPROJNUMBER LIKE RTRIM(@ProjectID) + '%'

UNION

SELECT

@PA_EQUIPMENTLOG PAUsageTypeNameId

,PAEquipLogLineHist.PACOSTCATID PACostCatGrp

,'' PACostCatGrpName

,'Equip Log' PAUsageTypeName

,'Equipment' PAUsageCodeName

,PAEquipLogLineHist.PAPROJNUMBER

,PAEquipLogLineHist.PACOSTCATID

,PAEquipLogLineHist.PADT

,PAEquipLogLineHist.PAQTYQ Quantity

,PAEquipLogLineHist.PAUNITCOST UnitCost

,PAEquipLogLineHist.PAEXTCOST ExtCost

,PAEquipMstr.PAEQNME SourceName

,PAEquipMstr.PAEQUIPTID SourceID

,PAEquipLogHdrHist.PACOMM Comment

,PAEquipLogHdrHist.PAEQLOGNO DocumentNumber

,''

FROM

PA30200 PAEquipLogHdrHist WITH (NOLOCK)

INNER JOIN PA30201 PAEquipLogLineHist WITH (NOLOCK)

ON PAEquipLogHdrHist.PAEQLOGNO = PAEquipLogLineHist.PAEQLOGNO

INNER JOIN PA00701 PAEquipMstr WITH (NOLOCK)

ON PAEquipLogLineHist.PAEQUIPTID = PAEquipMstr.PAEQUIPTID

WHERE

PAEquipLogLineHist.PAPROJNUMBER LIKE RTRIM(@ProjectID) + '%'

/* Misc Log */

/*

select top 5 'PAMiscLogHdrWork', * from PA10200 PAMiscLogHdrWork WITH (NOLOCK)

select top 5 'PAMiscLogLineWork', * from PA10201 PAMiscLogLineWork WITH (NOLOCK)

select top 5 'PAMiscLogHdrHist', * from PA30300 PAMiscLogHdrHist WITH (NOLOCK)

select top 5 'PAMiscLogLineHist', * from PA30301 PAMiscLogLineHist WITH (NOLOCK)

*/

INSERT INTO [#PACostCatDtl]

([PAUsageTypeNameId]

,[PACostCatGrp]

,[PACostCatGrpName]

,[PAUsageTypeName]

,[PAUsageCodeName]

,[PAPROJNUMBER]

,[PACOSTCATID]

,[PADT]

,[Quantity]

,[UnitCost]

,[ExtCost]

,[SourceName]

,[SourceID]

,[Comments]

,[DocumentNumber]

,[ItemNumber])

SELECT

@PA_MISCLOG PAUsageTypeNameId

,PAMiscLogLineWork.PACOSTCATID PACostCatGrp

,'' PACostCatGrpName

,'Misc Log' PAUsageTypeName

,'Miscellaneous' PAUsageCodeName

,PAMiscLogLineWork.PAPROJNUMBER

,PAMiscLogLineWork.PACOSTCATID

,PAMiscLogLineWork.PADT

,PAMiscLogLineWork.PAQtyQ Quantity

,PAMiscLogLineWork.PAUNITCOST UnitCost

,PAMiscLogLineWork.PAEXTCOST ExtCost

,PAMiscMSTR.PAMISCEN SourceName

,PAMiscMSTR.PSMISCID SourceID

,PAMiscLogHdrWork.PACOMM Comment

,PAMiscLogHdrWork.PAMISCLDOCNO DocumentNumber

,''

FROM

PA10200 PAMiscLogHdrWork WITH (NOLOCK)

INNER JOIN PA10201 PAMiscLogLineWork WITH (NOLOCK)

ON PAMiscLogHdrWork.PAMISCLDOCNO = PAMiscLogLineWork.PAMISCLDOCNO

INNER JOIN PA00801 PAMiscMSTR WITH (NOLOCK)

ON PAMiscLogHdrWork.PSMISCID = PAMiscMSTR.PSMISCID

WHERE

PAMiscLogLineWork.PAPROJNUMBER LIKE RTRIM(@ProjectID) + '%'

UNION

SELECT

@PA_MISCLOG PAUsageTypeNameId

,PAMiscLogLineHist.PACOSTCATID PACostCatGrp

,'' PACostCatGrpName

,'Misc Log' PAUsageTypeName

,'Miscellaneous' PAUsageCodeName

,PAMiscLogLineHist.PAPROJNUMBER

,PAMiscLogLineHist.PACOSTCATID

,PAMiscLogLineHist.PADT

,PAMiscLogLineHist.PAQtyQ Quantity

,PAMiscLogLineHist.PAUNITCOST UnitCost

,PAMiscLogLineHist.PAEXTCOST ExtCost

,PAMiscMSTR.PAMISCEN SourceName

,PAMiscMSTR.PSMISCID SourceID

,PAMiscLogHdrHist.PACOMM Comment

,PAMiscLogHdrHist.PAMISCLDOCNO DocumentNumber

,''

FROM

PA30300 PAMiscLogHdrHist WITH (NOLOCK)

INNER JOIN PA30301 PAMiscLogLineHist WITH (NOLOCK)

ON PAMiscLogHdrHist.PAMISCLDOCNO = PAMiscLogLineHist.PAMISCLDOCNO

INNER JOIN PA00801 PAMiscMSTR WITH (NOLOCK)

ON PAMiscLogHdrHist.PSMISCID = PAMiscMSTR.PSMISCID

WHERE

PAMiscLogLineHist.PAPROJNUMBER LIKE RTRIM(@ProjectID) + '%'

/* Purchase Material */

/*

select top 5 * from PA10600 PAPOWorkHdr WITH (NOLOCK)

select top 5 * from PA10601 PAPOWorkLine WITH (NOLOCK)

select top 5 * from PA10701 PAPOReceiptHdr WITH (NOLOCK)

select top 5 * from PA10702 PAPOReceiptLine WITH (NOLOCK)

select top 5 * from PA10721 PAPOReceiptApply WITH (NOLOCK)

select top 5 * from PA30600 PAPOWorkHdrHist WITH (NOLOCK)

select top 5 * from PA30601 PAPOWorkLineHist WITH (NOLOCK)

select top 5 * from PA31101 PAPOReceiptHdrHist WITH (NOLOCK)

select top 5 * from PA31102 PAPOReceiptLineHist WITH (NOLOCK)

*/

INSERT INTO [#PACostCatDtl]

([PAUsageTypeNameId]

,[PACostCatGrp]

,[PACostCatGrpName]

,[PAUsageTypeName]

,[PAUsageCodeName]

,[PAPROJNUMBER]

,[PACOSTCATID]

,[PADT]

,[Quantity]

,[UnitCost]

,[ExtCost]

,[SourceName]

,[SourceID]

,[Comments]

,[DocumentNumber]

,ItemNumber)

SELECT

@PA_PURCHASEMATERIAL PAUsageTypeNameId

,PAPOReceiptLine.PACOSTCATID PACostCatGrp

,'' PACostCatGrpName

,'Purchase' PAUsageTypeName

,'Purchase' PAUsageCodeName

,PAPROJNUMBER

,PACOSTCATID

,PAPOReceiptLine.receiptdate PADT

,CASE WHEN PAPOReceiptLine.POPTYPE = 5 THEN -1 ELSE 1 END * PABase_Qty Quantity

,PABase_Unit_Cost UnitCost

,CASE WHEN PAPOReceiptLine.POPTYPE = 5 THEN -1 ELSE 1 END * PAShipmentExtCost ExtCost

,ISNULL(POP10300.VENDNAME, '') SourceName

,ISNULL(POP10300.VENDORID, '') SourceId

,'' Comment

,PAVIDN DocumentNumber

,ITEMNMBR

FROM -- PA10600 PAPOWorkHdr WITH (NOLOCK)

PA10702 PAPOReceiptLine WITH (NOLOCK)

LEFT OUTER JOIN POP10300

on PAPOReceiptLine.PAVIDN = POP10300.POPRCTNM

WHERE

PAPROJNUMBER LIKE RTRIM(@ProjectID) + '%'

AND PAIV_Item_Checkbox = 0

UNION

SELECT

@PA_PURCHASEMATERIAL PAUsageTypeNameId

,PAPOReceiptLineHist.PACOSTCATID PACostCatGrp

,'' PACostCatGrpName

,'Purchase' PAUsageTypeName

,'Purchase' PAUsageCodeName

,PAPROJNUMBER

,PACOSTCATID

,PAPOReceiptLineHist.receiptdate PADT

,CASE WHEN PAPOReceiptLineHist.POPTYPE = 5 THEN -1 ELSE 1 END * PABase_Qty Quantity

,PABase_Unit_Cost UnitCost

,CASE WHEN PAPOReceiptLineHist.POPTYPE = 5 THEN -1 ELSE 1 END * PAShipmentExtCost ExtCost

,ISNULL(POP30300.VENDNAME, '') SourceName

,ISNULL(POP30300.VENDORID, '') SourceId ,

'' Comment

,PAVIDN DocumentNumber

,ITEMNMBR

FROM -- PA10600 PAPOWorkHdr WITH (NOLOCK)

PA31102 PAPOReceiptLineHist WITH (NOLOCK)

LEFT OUTER JOIN POP30300

on PAPOReceiptLineHist.PAVIDN = POP30300.POPRCTNM

WHERE

PAPROJNUMBER LIKE RTRIM(@ProjectID) + '%'

AND PAIV_Item_Checkbox = 0

/* Inventory Transfer */

/*

select top 5 * from PA10900 PAInvTransferHdrWork WITH (NOLOCK)

select top 5 * from PA10901 PAInvTransferLineWork WITH (NOLOCK)

select top 5 * from PA30900 PAInvTransferHdrHist WITH (NOLOCK)

select top 5 * from PA30901 PAInvTransferLineHist WITH (NOLOCK)

*/

INSERT INTO [#PACostCatDtl]

([PAUsageTypeNameId]

,[PACostCatGrp]

,[PACostCatGrpName]

,[PAUsageTypeName]

,[PAUsageCodeName]

,[PAPROJNUMBER]

,[PACOSTCATID]

,[PADT]

,[Quantity]

,[UnitCost]

,[ExtCost]

,[SourceName]

,[SourceID]

,[Comments]

,[DocumentNumber]

,[ItemNumber])

SELECT

@PA_INVENTORYTRANSFER PAUsageTypeNameId

,PAInvTransferLineWork.PACOSTCATID PACostCatGrp

,'' PACostCatGrpName

,'Inventory Transfer' PAUsageTypeName

,'Inventory' PAUsageCodeName

,PAInvTransferLineWork.PAPROJNUMBER

,PAInvTransferLineWork.PACOSTCATID

,PAInvTransferLineWork.PADT

,CASE WHEN PAInvTransferLineWork.PAIV_Transfer_Type = 2 THEN -1 WHEN PAInvTransferLineWork.PAIV_Transfer_Type = 3 THEN -1 ELSE 1 END * PAInvTransferLineWork.Quantity

,PAInvTransferLineWork.PAUNITCOST UnitCost

,CASE WHEN PAInvTransferLineWork.PAIV_Transfer_Type = 2 THEN -1 WHEN PAInvTransferLineWork.PAIV_Transfer_Type = 3 THEN -1 ELSE 1 END * PAInvTransferLineWork.PAEXTCOST ExtCost

,ItemMstr.ITEMDESC SourceName

,ItemMstr.ITEMNMBR SourceID

,PAInvTransferHdrWork.PACOMM Comment

,PAInvTransferHdrWork.PAIV_Document_No DocumentNumber

,ItemMstr.ITEMNMBR

FROM

PA10900 PAInvTransferHdrWork WITH (NOLOCK)

INNER JOIN PA10901 PAInvTransferLineWork WITH (NOLOCK)

ON PAInvTransferHdrWork.PAIV_Document_No = PAInvTransferLineWork.PAIV_Document_No

INNER JOIN IV00101 ItemMstr WITH (NOLOCK)

ON PAInvTransferLineWork.ITEMNMBR = ItemMstr.ITEMNMBR

WHERE

PAInvTransferLineWork.PAPROJNUMBER LIKE RTRIM(@ProjectID) + '%'

UNION

SELECT

@PA_INVENTORYTRANSFER PAUsageTypeNameId

,PAInvTransferLineHist.PACOSTCATID PACostCatGrp

,'' PACostCatGrpName

,'Inventory Transfer' PAUsageTypeName

,'Inventory' PAUsageCodeName

,PAInvTransferLineHist.PAPROJNUMBER

,PAInvTransferLineHist.PACOSTCATID

,PAInvTransferLineHist.PADT

,CASE WHEN PAInvTransferLineHist.PAIV_Transfer_Type = 2 THEN -1 WHEN PAInvTransferLineHist.PAIV_Transfer_Type = 3 THEN -1 ELSE 1 END * PAInvTransferLineHist.PAQtyQ Quantity

,PAInvTransferLineHist.PAUNITCOST UnitCost

,CASE WHEN PAInvTransferLineHist.PAIV_Transfer_Type = 2 THEN -1 WHEN PAInvTransferLineHist.PAIV_Transfer_Type = 3 THEN -1 ELSE 1 END * PAInvTransferLineHist.PAEXTCOST ExtCost

,ItemMstr.ITEMDESC SourceName

,ItemMstr.ITEMNMBR SourceID

,PAInvTransferHdrHist.PACOMM Comment

,PAInvTransferHdrHist.PAIV_Document_No DocumentNumber

,ItemMstr.ITEMNMBR

FROM

PA30900 PAInvTransferHdrHist WITH (NOLOCK)

INNER JOIN PA30901 PAInvTransferLineHist WITH (NOLOCK)

ON PAInvTransferHdrHist.PAIV_Document_No = PAInvTransferLineHist.PAIV_Document_No

INNER JOIN IV00101 ItemMstr WITH (NOLOCK)

ON PAInvTransferLineHist.ITEMNMBR = ItemMstr.ITEMNMBR

WHERE

PAInvTransferLineHist.PAPROJNUMBER LIKE RTRIM(@ProjectID) + '%'

/* Employee Expense */

/*

select top 5 'PAEmpExpHdrWork', * from PA10500 PAEmpExpHdrWork WITH (NOLOCK)

select top 5 'PAEmpExpLineWork', * from PA10501 PAEmpExpLineWork WITH (NOLOCK)

select top 5 'PAEmpExpHdrHist', * from PA30500 PAEmpExpHdrHist WITH (NOLOCK)

select top 5 'PAEmpExpLineHist', * from PA30501 PAEmpExpLineHist WITH (NOLOCK)

*/

INSERT INTO [#PACostCatDtl]

([PAUsageTypeNameId]

,[PACostCatGrp]

,[PACostCatGrpName]

,[PAUsageTypeName]

,[PAUsageCodeName]

,[PAPROJNUMBER]

,[PACOSTCATID]

,[PADT]

,[Quantity]

,[UnitCost]

,[ExtCost]

,[SourceName]

,[SourceID]

,[Comments]

,[DocumentNumber]

,[ItemNumber])

SELECT

@PA_EMPLOYEEEXPENSE PAUsageTypeNameId

,PAEmpExpLineWork.PACOSTCATID PACostCatGrp

,'' PACostCatGrpName

,'Employee Expense' PAUsageTypeName

,'Employee Expense' PAUsageCodeName

,PAEmpExpLineWork.PAPROJNUMBER

,PAEmpExpLineWork.PACOSTCATID

,PAEmpExpLineWork.PADT

,PAEmpExpLineWork.PAQtyQ Quantity

,PAEmpExpLineWork.PAUNITCOST UnitCost

,PAEmpExpLineWork.PAEXTCOST ExtCost

,RTRIM(PayrollMstr.FRSTNAME) + ' ' + PayrollMstr.LASTNAME SourceName

,PayrollMstr.EMPLOYID SourceID

,PAEmpExpHdrWork.PACOMM Comment

,PAEmpExpHdrWork.PAerdocnumber DocumentNumber

,''

FROM

PA10500 PAEmpExpHdrWork WITH (NOLOCK)

INNER JOIN PA10501 PAEmpExpLineWork WITH (NOLOCK)

ON PAEmpExpHdrWork.PAerdocnumber = PAEmpExpLineWork.PAerdocnumber

INNER JOIN UPR00100 PayrollMstr WITH (NOLOCK)

ON PAEmpExpHdrWork.EMPLOYID = PayrollMstr.EMPLOYID

WHERE

PAEmpExpLineWork.PAPROJNUMBER LIKE RTRIM(@ProjectID) + '%'

UNION

SELECT

@PA_EMPLOYEEEXPENSE PAUsageTypeNameId

,PAEmpExpLineHist.PACOSTCATID PACostCatGrp

,'' PACostCatGrpName

,'Employee Expense' PAUsageTypeName

,'Employee Expense' PAUsageCodeName

,PAEmpExpLineHist.PAPROJNUMBER

,PAEmpExpLineHist.PACOSTCATID

,PAEmpExpLineHist.PADT

,PAEmpExpLineHist.PAQtyQ Quantity

,PAEmpExpLineHist.PAUNITCOST UnitCost

,PAEmpExpLineHist.PAEXTCOST ExtCost

,RTRIM(PayrollMstr.FRSTNAME) + ' ' + PayrollMstr.LASTNAME SourceName

,PayrollMstr.EMPLOYID SourceID

,PAEmpExpHdrHist.PACOMM Comment

,PAEmpExpHdrHist.PAerdocnumber DocumentNumber

,''

FROM

PA30500 PAEmpExpHdrHist WITH (NOLOCK)

INNER JOIN PA30501 PAEmpExpLineHist WITH (NOLOCK)

ON PAEmpExpHdrHist.PAerdocnumber = PAEmpExpLineHist.PAerdocnumber

INNER JOIN UPR00100 PayrollMstr WITH (NOLOCK)

ON PAEmpExpHdrHist.EMPLOYID = PayrollMstr.EMPLOYID

WHERE

PAEmpExpLineHist.PAPROJNUMBER LIKE RTRIM(@ProjectID) + '%'

UPDATE #PACostCatDtl

SET

PACostCatGrpName = CostCatMstr.PAUD1

,PAUsageCodeName = CostCatMstr.PACOSTCATNME

FROM #PACostCatDtl

INNER JOIN PA01001 CostCatMstr WITH (NOLOCK)

ON CostCatMstr.PACOSTCATID = #PACostCatDtl.PACostCatGrp

UPDATE #PACostCatDtl

SET

[PACostCatGrp] = SUBSTRING(PACostCatGrp, 1, LEN(RTRIM(PACostCatGrp))-2)

SELECT * FROM #PACostCatDtl

WHERE (PACostCatGrp = @PACostCatGrp or @PACostCatGrp = '')

END

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183686 More actions · Answer 1

Wow, that's a lot of code to even try to imagine what's going on. Remember that we don't know how do your tables and data look like.

The only tip that I can give you is to remove all the NOLOCK hints unless you're fine with getting inaccurate data (missing or duplicate rows). That hint is not a magical "run fast" option.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

npatel 17252 SSC-Addicted Points: 475 More actions · Answer 2

npatel 17252

SSC-Addicted

Points: 475

May 13, 2014 at 5:40 pm

#1713284

Hmm, how would removing NOLOCK give inaccurate data?

And ya I just graduated last May and my ive been really lucking in bringing up mt T-SQL, however I wish I was as talented as the guy that wrote this at the consulting company.(Id ask him but unfortunately the company is no longer using them)

Luis Cazares SSC Guru Points: 183686 More actions · Answer 3

Removing NOLOCK won't give inaccurate results. Adding it might. You can read more in this article http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx and you could google for other examples on why it's a bad idea.

I have to run but I'll come back as soon as I can to help you a little bit more (unless someone else does).

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ocean3300 SSChasing Mays Points: 621 More actions · Answer 4

ocean3300

SSChasing Mays

Points: 621

May 13, 2014 at 8:01 pm

#1713297

however I edited the columns to allow NULLS but still not working

Can we see how you edited the column to allow NULL value? Because I don't see it in your code. It's still NOT NULL.

Sean Lange SSC Guru Points: 286562 More actions · Answer 5

Let's see what this monster looks like with some formatting so we can read it...



/****** Object: StoredProcedure [dbo].[CCPAspProjectCostCatDetail] Script Date: 05/13/2014 15:46:48 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:

-- Create date: 06/01/2013

-- Description:

-- =============================================

ALTER PROCEDURE [dbo].[CCPAspProjectCostCatDetail]

	-- Add the parameters for the stored procedure here

	@ProjectID CHAR(15) = ''

	,@PACostCatGrp CHAR(15) = ''

AS

DECLARE @PA_TIMESHEET INT = 1

	,@PA_EQUIPMENTLOG INT = 2

	,@PA_MISCLOG INT = 3

	,@PA_PURCHASEMATERIAL INT = 4

	,@PA_INVENTORYTRANSFER INT = 5

	,@PA_EMPLOYEEEXPENSE INT = 6

BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from

	-- interfering with SELECT statements.

	SET NOCOUNT ON;

	CREATE TABLE [dbo].[#PACostCatDtl] (

		[PAUsageTypeNameId] [smallint] NOT NULL

		,[PACostCatGrp] [char](15) NOT NULL

		,[PACostCatGrpName] [char](30) NOT NULL

		,[PAUsageTypeName] [varchar](30) NOT NULL

		,[PAUsageCodeName] [varchar](30) NOT NULL

		,[PAPROJNUMBER] [char](15) NOT NULL

		,[PACOSTCATID] [char](15) NOT NULL

		,[PADT] [datetime] NOT NULL

		,[Quantity] [numeric](19, 5) NOT NULL

		,[UnitCost] [numeric](19, 5) NOT NULL

		,[ExtCost] [numeric](19, 5) NOT NULL

		,[SourceName] [varchar](60) NULL

		,[SourceID] [char](15) NOT NULL

		,[Comments] [char](80) NOT NULL

		,[DocumentNumber] [char](21) NOT NULL

		,[ItemNumber] [char](31) NOT NULL

		,

		)

	/* TimeSheet */

	/*

select top 5 'TimeSheetHdr_PA10000', * from PA10000 TimeSheetHdr

select top 5 'TimeSheetDtl_PA10001', * from PA10001 TimeSheetDtl

select top 5 'TimeSheetHdrHist_PA30100', * from PA30100 TimeSheetHdrHist

select 'TimeSheetDtlHist_PA30101', * from PA30101 TimeSheetDtlHist

*/

	INSERT INTO [#PACostCatDtl] (

		[PAUsageTypeNameId]

		,[PACostCatGrp]

		,[PACostCatGrpName]

		,[PAUsageTypeName]

		,[PAUsageCodeName]

		,[PAPROJNUMBER]

		,[PACOSTCATID]

		,[PADT]

		,[Quantity]

		,[UnitCost]

		,[ExtCost]

		,[SourceName]

		,[SourceID]

		,[Comments]

		,[DocumentNumber]

		,[ItemNumber]

		)

	SELECT TimeSheetDtl.PATSTYP PAUsageTypeNameId

		,TimeSheetDtl.PACOSTCATID PACostCatGrp

		,'' PACostCatGrpName

		,'Timesheet' PAUsageTypeName

		,'Labor' PAUsageCodeName

		,TimeSheetDtl.PAPROJNUMBER

		,TimeSheetDtl.PACOSTCATID

		,TimeSheetDtl.PADT

		,(TimeSheetDtl.PAQtyQ) Quantity

		,(TimeSheetDtl.PAUNITCOST) UnitCost

		,(TimeSheetDtl.PAEXTCOST) + (TimeSheetDtl.PAEXTCOST * 1.81) ExtCost

		,RTRIM(PayrollMstr.FRSTNAME) + ' ' + PayrollMstr.LASTNAME SourceName

		,PayrollMstr.EMPLOYID SourceID

		,PACOMM Comments

		,TimeSheetHdr.PATSNO DocumentNumber

		,''

	FROM PA10000 TimeSheetHdr WITH (NOLOCK)

	INNER JOIN PA10001 TimeSheetDtl WITH (NOLOCK) ON TimeSheetHdr.PATSNO = TimeSheetDtl.PATSNO

	INNER JOIN UPR00100 PayrollMstr WITH (NOLOCK) ON TimeSheetHdr.EMPLOYID = PayrollMstr.EMPLOYID

	WHERE TimeSheetDtl.PAPROJNUMBER LIKE RTRIM(@ProjectID) + '%'

	UNION ALL

	SELECT TimeSheetDtlHist.PATSTYP PAUsageTypeNameId

		,TimeSheetDtlHist.PACOSTCATID PACostCatGrp

		,'' PACostCatGrpName

		,'Timesheet' PAUsageTypeName

		,'Labor' PAUsageCodeName

		,TimeSheetDtlHist.PAPROJNUMBER

		,TimeSheetDtlHist.PACOSTCATID

		,TimeSheetDtlHist.PADT

		,(TimeSheetDtlHist.PAQtyQ) Quantity

		,(TimeSheetDtlHist.PAUNITCOST) UnitCost

		,(TimeSheetDtlHist.PAEXTCOST) + (TimeSheetDtlHist.PAEXTCOST * 1.81) ExtCost

		,RTRIM(PayrollMstr.FRSTNAME) + ' ' + PayrollMstr.LASTNAME SourceName

		,PayrollMstr.EMPLOYID SourceID

		,PACOMM Comments

		,TimeSheetHdrHist.PATSNO DocumentNumber

		,''

	FROM PA30100 TimeSheetHdrHist WITH (NOLOCK)

	INNER JOIN PA30101 TimeSheetDtlHist WITH (NOLOCK) ON TimeSheetHdrHist.PATSNO = TimeSheetDtlHist.PATSNO

	INNER JOIN UPR00100 PayrollMstr WITH (NOLOCK) ON TimeSheetHdrHist.EMPLOYID = PayrollMstr.EMPLOYID

	WHERE TimeSheetDtlHist.PAPROJNUMBER LIKE RTRIM(@ProjectID) + '%'

	/* Equip Log */

	/*

select top 5 'PAEquipLogHdrWork', * from PA10100 PAEquipLogHdrWork WITH (NOLOCK)

select top 5 'PAEquipLogLineWork', * from PA10101 PAEquipLogLineWork WITH (NOLOCK)

select top 5 'PAEquipLogHdrHist', * from PA30200 PAEquipLogHdrHist WITH (NOLOCK)

select top 5 'PAEquipLogLineHist', * from PA30201 PAEquipLogLineHist WITH (NOLOCK)

*/

	INSERT INTO [#PACostCatDtl] (

		[PAUsageTypeNameId]

		,[PACostCatGrp]

		,[PACostCatGrpName]

		,[PAUsageTypeName]

		,[PAUsageCodeName]

		,[PAPROJNUMBER]

		,[PACOSTCATID]

		,[PADT]

		,[Quantity]

		,[UnitCost]

		,[ExtCost]

		,[SourceName]

		,[SourceID]

		,[Comments]

		,[DocumentNumber]

		,[ItemNumber]

		)

	SELECT @PA_EQUIPMENTLOG PAUsageTypeNameId

		,PAEquipLogLineWork.PACOSTCATID PACostCatGrp

		,'' PACostCatGrpName

		,'Equip Log' PAUsageTypeName

		,'Equipment' PAUsageCodeName

		,PAEquipLogLineWork.PAPROJNUMBER

		,PAEquipLogLineWork.PACOSTCATID

		,PAEquipLogLineWork.PADT

		,PAEquipLogLineWork.PAQTYQ Quantity

		,PAEquipLogLineWork.PAUNITCOST UnitCost

		,PAEquipLogLineWork.PAEXTCOST ExtCost

		,PAEquipMstr.PAEQNME SourceName

		,PAEquipMstr.PAEQUIPTID SourceID

		,PAEquipLogHdrWork.PACOMM Comment

		,PAEquipLogHdrWork.PAEQLOGNO DocumentNumber

		,''

	FROM PA10100 PAEquipLogHdrWork WITH (NOLOCK)

	INNER JOIN PA10101 PAEquipLogLineWork WITH (NOLOCK) ON PAEquipLogHdrWork.PAEQLOGNO = PAEquipLogLineWork.PAEQLOGNO

	INNER JOIN PA00701 PAEquipMstr WITH (NOLOCK) ON PAEquipLogLineWork.PAEQUIPTID = PAEquipMstr.PAEQUIPTID

	WHERE PAEquipLogLineWork.PAPROJNUMBER LIKE RTRIM(@ProjectID) + '%'

	UNION

	SELECT @PA_EQUIPMENTLOG PAUsageTypeNameId

		,PAEquipLogLineHist.PACOSTCATID PACostCatGrp

		,'' PACostCatGrpName

		,'Equip Log' PAUsageTypeName

		,'Equipment' PAUsageCodeName

		,PAEquipLogLineHist.PAPROJNUMBER

		,PAEquipLogLineHist.PACOSTCATID

		,PAEquipLogLineHist.PADT

		,PAEquipLogLineHist.PAQTYQ Quantity

		,PAEquipLogLineHist.PAUNITCOST UnitCost

		,PAEquipLogLineHist.PAEXTCOST ExtCost

		,PAEquipMstr.PAEQNME SourceName

		,PAEquipMstr.PAEQUIPTID SourceID

		,PAEquipLogHdrHist.PACOMM Comment

		,PAEquipLogHdrHist.PAEQLOGNO DocumentNumber

		,''

	FROM PA30200 PAEquipLogHdrHist WITH (NOLOCK)

	INNER JOIN PA30201 PAEquipLogLineHist WITH (NOLOCK) ON PAEquipLogHdrHist.PAEQLOGNO = PAEquipLogLineHist.PAEQLOGNO

	INNER JOIN PA00701 PAEquipMstr WITH (NOLOCK) ON PAEquipLogLineHist.PAEQUIPTID = PAEquipMstr.PAEQUIPTID

	WHERE PAEquipLogLineHist.PAPROJNUMBER LIKE RTRIM(@ProjectID) + '%'

	/* Misc Log */

	/*

select top 5 'PAMiscLogHdrWork', * from PA10200 PAMiscLogHdrWork WITH (NOLOCK)

select top 5 'PAMiscLogLineWork', * from PA10201 PAMiscLogLineWork WITH (NOLOCK)

select top 5 'PAMiscLogHdrHist', * from PA30300 PAMiscLogHdrHist WITH (NOLOCK)

select top 5 'PAMiscLogLineHist', * from PA30301 PAMiscLogLineHist WITH (NOLOCK)

*/

	INSERT INTO [#PACostCatDtl] (

		[PAUsageTypeNameId]

		,[PACostCatGrp]

		,[PACostCatGrpName]

		,[PAUsageTypeName]

		,[PAUsageCodeName]

		,[PAPROJNUMBER]

		,[PACOSTCATID]

		,[PADT]

		,[Quantity]

		,[UnitCost]

		,[ExtCost]

		,[SourceName]

		,[SourceID]

		,[Comments]

		,[DocumentNumber]

		,[ItemNumber]

		)

	SELECT @PA_MISCLOG PAUsageTypeNameId

		,PAMiscLogLineWork.PACOSTCATID PACostCatGrp

		,'' PACostCatGrpName

		,'Misc Log' PAUsageTypeName

		,'Miscellaneous' PAUsageCodeName

		,PAMiscLogLineWork.PAPROJNUMBER

		,PAMiscLogLineWork.PACOSTCATID

		,PAMiscLogLineWork.PADT

		,PAMiscLogLineWork.PAQtyQ Quantity

		,PAMiscLogLineWork.PAUNITCOST UnitCost

		,PAMiscLogLineWork.PAEXTCOST ExtCost

		,PAMiscMSTR.PAMISCEN SourceName

		,PAMiscMSTR.PSMISCID SourceID

		,PAMiscLogHdrWork.PACOMM Comment

		,PAMiscLogHdrWork.PAMISCLDOCNO DocumentNumber

		,''

	FROM PA10200 PAMiscLogHdrWork WITH (NOLOCK)

	INNER JOIN PA10201 PAMiscLogLineWork WITH (NOLOCK) ON PAMiscLogHdrWork.PAMISCLDOCNO = PAMiscLogLineWork.PAMISCLDOCNO

	INNER JOIN PA00801 PAMiscMSTR WITH (NOLOCK) ON PAMiscLogHdrWork.PSMISCID = PAMiscMSTR.PSMISCID

	WHERE PAMiscLogLineWork.PAPROJNUMBER LIKE RTRIM(@ProjectID) + '%'

	UNION

	SELECT @PA_MISCLOG PAUsageTypeNameId

		,PAMiscLogLineHist.PACOSTCATID PACostCatGrp

		,'' PACostCatGrpName

		,'Misc Log' PAUsageTypeName

		,'Miscellaneous' PAUsageCodeName

		,PAMiscLogLineHist.PAPROJNUMBER

		,PAMiscLogLineHist.PACOSTCATID

		,PAMiscLogLineHist.PADT

		,PAMiscLogLineHist.PAQtyQ Quantity

		,PAMiscLogLineHist.PAUNITCOST UnitCost

		,PAMiscLogLineHist.PAEXTCOST ExtCost

		,PAMiscMSTR.PAMISCEN SourceName

		,PAMiscMSTR.PSMISCID SourceID

		,PAMiscLogHdrHist.PACOMM Comment

		,PAMiscLogHdrHist.PAMISCLDOCNO DocumentNumber

		,''

	FROM PA30300 PAMiscLogHdrHist WITH (NOLOCK)

	INNER JOIN PA30301 PAMiscLogLineHist WITH (NOLOCK) ON PAMiscLogHdrHist.PAMISCLDOCNO = PAMiscLogLineHist.PAMISCLDOCNO

	INNER JOIN PA00801 PAMiscMSTR WITH (NOLOCK) ON PAMiscLogHdrHist.PSMISCID = PAMiscMSTR.PSMISCID

	WHERE PAMiscLogLineHist.PAPROJNUMBER LIKE RTRIM(@ProjectID) + '%'

	/* Purchase Material */

	/*

select top 5 * from PA10600 PAPOWorkHdr WITH (NOLOCK)

select top 5 * from PA10601 PAPOWorkLine WITH (NOLOCK)

select top 5 * from PA10701 PAPOReceiptHdr WITH (NOLOCK)

select top 5 * from PA10702 PAPOReceiptLine WITH (NOLOCK)

select top 5 * from PA10721 PAPOReceiptApply WITH (NOLOCK)

select top 5 * from PA30600 PAPOWorkHdrHist WITH (NOLOCK)

select top 5 * from PA30601 PAPOWorkLineHist WITH (NOLOCK)

select top 5 * from PA31101 PAPOReceiptHdrHist WITH (NOLOCK)

select top 5 * from PA31102 PAPOReceiptLineHist WITH (NOLOCK)

*/

	INSERT INTO [#PACostCatDtl] (

		[PAUsageTypeNameId]

		,[PACostCatGrp]

		,[PACostCatGrpName]

		,[PAUsageTypeName]

		,[PAUsageCodeName]

		,[PAPROJNUMBER]

		,[PACOSTCATID]

		,[PADT]

		,[Quantity]

		,[UnitCost]

		,[ExtCost]

		,[SourceName]

		,[SourceID]

		,[Comments]

		,[DocumentNumber]

		,ItemNumber

		)

	SELECT @PA_PURCHASEMATERIAL PAUsageTypeNameId

		,PAPOReceiptLine.PACOSTCATID PACostCatGrp

		,'' PACostCatGrpName

		,'Purchase' PAUsageTypeName

		,'Purchase' PAUsageCodeName

		,PAPROJNUMBER

		,PACOSTCATID

		,PAPOReceiptLine.receiptdate PADT

		,CASE

			WHEN PAPOReceiptLine.POPTYPE = 5

				THEN - 1

			ELSE 1

			END * PABase_Qty Quantity

		,PABase_Unit_Cost UnitCost

		,CASE

			WHEN PAPOReceiptLine.POPTYPE = 5

				THEN - 1

			ELSE 1

			END * PAShipmentExtCost ExtCost

		,ISNULL(POP10300.VENDNAME, '') SourceName

		,ISNULL(POP10300.VENDORID, '') SourceId

		,'' Comment

		,PAVIDN DocumentNumber

		,ITEMNMBR

	FROM -- PA10600 PAPOWorkHdr WITH (NOLOCK)

		PA10702 PAPOReceiptLine WITH (NOLOCK)

	LEFT JOIN POP10300 ON PAPOReceiptLine.PAVIDN = POP10300.POPRCTNM

	WHERE PAPROJNUMBER LIKE RTRIM(@ProjectID) + '%'

		AND PAIV_Item_Checkbox = 0

	UNION

	SELECT @PA_PURCHASEMATERIAL PAUsageTypeNameId

		,PAPOReceiptLineHist.PACOSTCATID PACostCatGrp

		,'' PACostCatGrpName

		,'Purchase' PAUsageTypeName

		,'Purchase' PAUsageCodeName

		,PAPROJNUMBER

		,PACOSTCATID

		,PAPOReceiptLineHist.receiptdate PADT

		,CASE

			WHEN PAPOReceiptLineHist.POPTYPE = 5

				THEN - 1

			ELSE 1

			END * PABase_Qty Quantity

		,PABase_Unit_Cost UnitCost

		,CASE

			WHEN PAPOReceiptLineHist.POPTYPE = 5

				THEN - 1

			ELSE 1

			END * PAShipmentExtCost ExtCost

		,ISNULL(POP30300.VENDNAME, '') SourceName

		,ISNULL(POP30300.VENDORID, '') SourceId

		,'' Comment

		,PAVIDN DocumentNumber

		,ITEMNMBR

	FROM -- PA10600 PAPOWorkHdr WITH (NOLOCK)

		PA31102 PAPOReceiptLineHist WITH (NOLOCK)

	LEFT JOIN POP30300 ON PAPOReceiptLineHist.PAVIDN = POP30300.POPRCTNM

	WHERE PAPROJNUMBER LIKE RTRIM(@ProjectID) + '%'

		AND PAIV_Item_Checkbox = 0

	/* Inventory Transfer */

	/*

select top 5 * from PA10900 PAInvTransferHdrWork WITH (NOLOCK)

select top 5 * from PA10901 PAInvTransferLineWork WITH (NOLOCK)

select top 5 * from PA30900 PAInvTransferHdrHist WITH (NOLOCK)

select top 5 * from PA30901 PAInvTransferLineHist WITH (NOLOCK)

*/

	INSERT INTO [#PACostCatDtl] (

		[PAUsageTypeNameId]

		,[PACostCatGrp]

		,[PACostCatGrpName]

		,[PAUsageTypeName]

		,[PAUsageCodeName]

		,[PAPROJNUMBER]

		,[PACOSTCATID]

		,[PADT]

		,[Quantity]

		,[UnitCost]

		,[ExtCost]

		,[SourceName]

		,[SourceID]

		,[Comments]

		,[DocumentNumber]

		,[ItemNumber]

		)

	SELECT @PA_INVENTORYTRANSFER PAUsageTypeNameId

		,PAInvTransferLineWork.PACOSTCATID PACostCatGrp

		,'' PACostCatGrpName

		,'Inventory Transfer' PAUsageTypeName

		,'Inventory' PAUsageCodeName

		,PAInvTransferLineWork.PAPROJNUMBER

		,PAInvTransferLineWork.PACOSTCATID

		,PAInvTransferLineWork.PADT

		,CASE

			WHEN PAInvTransferLineWork.PAIV_Transfer_Type = 2

				THEN - 1

			WHEN PAInvTransferLineWork.PAIV_Transfer_Type = 3

				THEN - 1

			ELSE 1

			END * PAInvTransferLineWork.Quantity

		,PAInvTransferLineWork.PAUNITCOST UnitCost

		,CASE

			WHEN PAInvTransferLineWork.PAIV_Transfer_Type = 2

				THEN - 1

			WHEN PAInvTransferLineWork.PAIV_Transfer_Type = 3

				THEN - 1

			ELSE 1

			END * PAInvTransferLineWork.PAEXTCOST ExtCost

		,ItemMstr.ITEMDESC SourceName

		,ItemMstr.ITEMNMBR SourceID

		,PAInvTransferHdrWork.PACOMM Comment

		,PAInvTransferHdrWork.PAIV_Document_No DocumentNumber

		,ItemMstr.ITEMNMBR

	FROM PA10900 PAInvTransferHdrWork WITH (NOLOCK)

	INNER JOIN PA10901 PAInvTransferLineWork WITH (NOLOCK) ON PAInvTransferHdrWork.PAIV_Document_No = PAInvTransferLineWork.PAIV_Document_No

	INNER JOIN IV00101 ItemMstr WITH (NOLOCK) ON PAInvTransferLineWork.ITEMNMBR = ItemMstr.ITEMNMBR

	WHERE PAInvTransferLineWork.PAPROJNUMBER LIKE RTRIM(@ProjectID) + '%'

	UNION

	SELECT @PA_INVENTORYTRANSFER PAUsageTypeNameId

		,PAInvTransferLineHist.PACOSTCATID PACostCatGrp

		,'' PACostCatGrpName

		,'Inventory Transfer' PAUsageTypeName

		,'Inventory' PAUsageCodeName

		,PAInvTransferLineHist.PAPROJNUMBER

		,PAInvTransferLineHist.PACOSTCATID

		,PAInvTransferLineHist.PADT

		,CASE

			WHEN PAInvTransferLineHist.PAIV_Transfer_Type = 2

				THEN - 1

			WHEN PAInvTransferLineHist.PAIV_Transfer_Type = 3

				THEN - 1

			ELSE 1

			END * PAInvTransferLineHist.PAQtyQ Quantity

		,PAInvTransferLineHist.PAUNITCOST UnitCost

		,CASE

			WHEN PAInvTransferLineHist.PAIV_Transfer_Type = 2

				THEN - 1

			WHEN PAInvTransferLineHist.PAIV_Transfer_Type = 3

				THEN - 1

			ELSE 1

			END * PAInvTransferLineHist.PAEXTCOST ExtCost

		,ItemMstr.ITEMDESC SourceName

		,ItemMstr.ITEMNMBR SourceID

		,PAInvTransferHdrHist.PACOMM Comment

		,PAInvTransferHdrHist.PAIV_Document_No DocumentNumber

		,ItemMstr.ITEMNMBR

	FROM PA30900 PAInvTransferHdrHist WITH (NOLOCK)

	INNER JOIN PA30901 PAInvTransferLineHist WITH (NOLOCK) ON PAInvTransferHdrHist.PAIV_Document_No = PAInvTransferLineHist.PAIV_Document_No

	INNER JOIN IV00101 ItemMstr WITH (NOLOCK) ON PAInvTransferLineHist.ITEMNMBR = ItemMstr.ITEMNMBR

	WHERE PAInvTransferLineHist.PAPROJNUMBER LIKE RTRIM(@ProjectID) + '%'

	/* Employee Expense */

	/*

select top 5 'PAEmpExpHdrWork', * from PA10500 PAEmpExpHdrWork WITH (NOLOCK)

select top 5 'PAEmpExpLineWork', * from PA10501 PAEmpExpLineWork WITH (NOLOCK)

select top 5 'PAEmpExpHdrHist', * from PA30500 PAEmpExpHdrHist WITH (NOLOCK)

select top 5 'PAEmpExpLineHist', * from PA30501 PAEmpExpLineHist WITH (NOLOCK)

*/

	INSERT INTO [#PACostCatDtl] (

		[PAUsageTypeNameId]

		,[PACostCatGrp]

		,[PACostCatGrpName]

		,[PAUsageTypeName]

		,[PAUsageCodeName]

		,[PAPROJNUMBER]

		,[PACOSTCATID]

		,[PADT]

		,[Quantity]

		,[UnitCost]

		,[ExtCost]

		,[SourceName]

		,[SourceID]

		,[Comments]

		,[DocumentNumber]

		,[ItemNumber]

		)

	SELECT @PA_EMPLOYEEEXPENSE PAUsageTypeNameId

		,PAEmpExpLineWork.PACOSTCATID PACostCatGrp

		,'' PACostCatGrpName

		,'Employee Expense' PAUsageTypeName

		,'Employee Expense' PAUsageCodeName

		,PAEmpExpLineWork.PAPROJNUMBER

		,PAEmpExpLineWork.PACOSTCATID

		,PAEmpExpLineWork.PADT

		,PAEmpExpLineWork.PAQtyQ Quantity

		,PAEmpExpLineWork.PAUNITCOST UnitCost

		,PAEmpExpLineWork.PAEXTCOST ExtCost

		,RTRIM(PayrollMstr.FRSTNAME) + ' ' + PayrollMstr.LASTNAME SourceName

		,PayrollMstr.EMPLOYID SourceID

		,PAEmpExpHdrWork.PACOMM Comment

		,PAEmpExpHdrWork.PAerdocnumber DocumentNumber

		,''

	FROM PA10500 PAEmpExpHdrWork WITH (NOLOCK)

	INNER JOIN PA10501 PAEmpExpLineWork WITH (NOLOCK) ON PAEmpExpHdrWork.PAerdocnumber = PAEmpExpLineWork.PAerdocnumber

	INNER JOIN UPR00100 PayrollMstr WITH (NOLOCK) ON PAEmpExpHdrWork.EMPLOYID = PayrollMstr.EMPLOYID

	WHERE PAEmpExpLineWork.PAPROJNUMBER LIKE RTRIM(@ProjectID) + '%'

	UNION

	SELECT @PA_EMPLOYEEEXPENSE PAUsageTypeNameId

		,PAEmpExpLineHist.PACOSTCATID PACostCatGrp

		,'' PACostCatGrpName

		,'Employee Expense' PAUsageTypeName

		,'Employee Expense' PAUsageCodeName

		,PAEmpExpLineHist.PAPROJNUMBER

		,PAEmpExpLineHist.PACOSTCATID

		,PAEmpExpLineHist.PADT

		,PAEmpExpLineHist.PAQtyQ Quantity

		,PAEmpExpLineHist.PAUNITCOST UnitCost

		,PAEmpExpLineHist.PAEXTCOST ExtCost

		,RTRIM(PayrollMstr.FRSTNAME) + ' ' + PayrollMstr.LASTNAME SourceName

		,PayrollMstr.EMPLOYID SourceID

		,PAEmpExpHdrHist.PACOMM Comment

		,PAEmpExpHdrHist.PAerdocnumber DocumentNumber

		,''

	FROM PA30500 PAEmpExpHdrHist WITH (NOLOCK)

	INNER JOIN PA30501 PAEmpExpLineHist WITH (NOLOCK) ON PAEmpExpHdrHist.PAerdocnumber = PAEmpExpLineHist.PAerdocnumber

	INNER JOIN UPR00100 PayrollMstr WITH (NOLOCK) ON PAEmpExpHdrHist.EMPLOYID = PayrollMstr.EMPLOYID

	WHERE PAEmpExpLineHist.PAPROJNUMBER LIKE RTRIM(@ProjectID) + '%'

	UPDATE #PACostCatDtl

	SET PACostCatGrpName = CostCatMstr.PAUD1

		,PAUsageCodeName = CostCatMstr.PACOSTCATNME

	FROM #PACostCatDtl

	INNER JOIN PA01001 CostCatMstr WITH (NOLOCK) ON CostCatMstr.PACOSTCATID = #PACostCatDtl.PACostCatGrp

	UPDATE #PACostCatDtl

	SET [PACostCatGrp] = SUBSTRING(PACostCatGrp, 1, LEN(RTRIM(PACostCatGrp)) - 2)

	SELECT *

	FROM #PACostCatDtl

	WHERE (

			PACostCatGrp = @PACostCatGrp

			OR @PACostCatGrp = ''

			)

END

What I don't understand is why each insert is two queries with a UNION. That doesn't make sense to me at all. Either UNION all of them together or do each one as its own insert statement.

And I agree with Luis 10000% about getting rid of NOLOCK unless accuracy is not important.

Now what was the question again? My head is spinning looking through this overly complicated procedure.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Luis Cazares SSC Guru Points: 183686 More actions · Answer 6

You seem to have "Work" tables and "History" tables and you use UNION between them. You must realize that UNION and UNION ALL work slightly different. UNION will return a result set of distinct values (which will force SQL Server to sort them to find any duplicates) and UNION ALL will return any possible duplicates (it'll work faster because it won't need to sort the values unless there's another operator to force that). Usually "work" and "history" tables don't share information, so you shouldn't use UNION and you should use UNION ALL or an INSERT for every SELECT. You need to find out if that's true.

The reason of why your LEFT JOIN might not be working is because you have WHERE conditions that use columns from your "right" table. That would convert them into inner joins. Check the following article to read about this with examples: http://qa.sqlservercentral.com/articles/T-SQL/93039/

The procedure is not complicated (it's just long). It's concentrating everything on a temp table to give a result. The problem is that we can't be sure where are you missing the rows. You should go step by step to determine the problem.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

npatel 17252 SSC-Addicted Points: 475 More actions · Answer 7

npatel 17252

SSC-Addicted

Points: 475

May 14, 2014 at 9:26 am

#1713523

I did it when I was messing around with it, I posted the original code.