SQL Stored Procedure help

Question

SQL Stored Procedure help

npatel 17252

Mr or Mrs. 500

Points: 525
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: 183706 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 Mr or Mrs. 500 Points: 525 More actions · Answer 2

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: 183706 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

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: 286590 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: 183706 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://www.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 Mr or Mrs. 500 Points: 525 More actions · Answer 7

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