We help IT Professionals succeed at work.

Stored Procedure Logic

I cannot read T-SQL. But I am tasked with figuring out what the logic is for determining what is called a "Placement" at my workplace. The rough definition is when we "place" a new service with a new or inactive customer (not sure how long inactivity has to be, but I am guessing 30 days). I have attached the stored procedure code that IT uses to calculate this metric. I am only concerned with the OIL part, so please do not spend time with the other 3 products. I do not need  a line by line translation, but some business logic translation. I do not have sql server available to even try to replicate this stored procedure. Is there anyone that can give me the meaning of "placement"?
USE [EntReporting]
GO
/****** Object:  StoredProcedure [dbo].[usp_CustFirst_TerrPlacementsAndPulls_Branch_PLCS]    Script Date: 12/07/2011 01:06:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO





/*
exec [dbo].[usp_CustFirst_TerrPlacementsAndPulls_Branch_PLCS] '2011','3','MSS/TAM','Terr-MSS-1-1'
*/
ALTER Procedure [dbo].[usp_CustFirst_TerrPlacementsAndPulls_Branch_PLCS]
(
@ReportType varchar(18),
@Year varchar(8),
@Period varchar(8),
@Values varchar(max)
)
as


IF(@ReportType='BRANCH')
BEGIN
DECLARE @i_sDate DATETIME
DECLARE @i_eDate DATETIME
DECLARE @i_esDate DATETIME
DECLARE @inPeriod VARCHAR(8)

SET @inPeriod = @Year + '0' + @Period

SELECT @i_sDate = PeriodStartDate
FROM   dbo.Fn_periodcalendar(NULL, @inPeriod)

SELECT @i_edate = MAX(RunDate)
FROM   CustFirstDailyOutput
WHERE  PeriodYear = @Year
       AND Period = @Period

SET @i_esDate = @i_eDate + ' 23:59:59:997'


	SELECT DISTINCT k.MATERIAL, k.PROD_HIER AS PROD_HIE
INTO   #PROD_HIER1
FROM EntReporting.dbo.KpiMaterialAttr AS k 
CROSS JOIN EntReporting.dbo.CustFirstRules AS cs
WHERE	(cs.Metric = 'LOB') AND 
		(cs.TypeOfRule = 'CWS') AND
		(@i_eDate BETWEEN cs.FromDate AND cs.ToDate) AND
		(PATINDEX(cs.SearchString, k.PROD_HIER) > 0) 


	SELECT s.CustomerId
		,CASE WHEN r.TypeOfRule = 'OIL' and s.PlacementDate <= @i_sDate 
				and s.PlacementDate is not null
				and (s.inactivationdate is null or s.inactivationdate > @i_sDate - 1)
				and patindex(r.SearchString,h.Prod_Hier) > 0 
			  THEN 'OIL' 
			  WHEN r.TypeOfRule = 'VAC' and s.PlacementDate <= @i_sDate 
				and s.PlacementDate is not null
				and (s.inactivationdate is null or s.inactivationdate > @i_sDate - 1)
				and patindex(r.SearchString,h.Prod_Hier) > 0 
			  THEN 'VAC' 
			  WHEN r.TypeOfRule = 'CWS' and s.PlacementDate <= @i_sDate 
				and s.PlacementDate is not null
				and (s.inactivationdate is null or s.inactivationdate > @i_sDate - 1)
				and patindex(r.SearchString,h.Prod_Hier) > 0 
			 THEN 'CWS' end as Old_LOB
		,CASE WHEN r.TypeOfRule = 'OIL' and s.PlacementDate <= @i_sDate 
				and s.PlacementDate is not null
				and (s.inactivationdate is null or s.inactivationdate > @i_sDate - 1)
				and patindex(r.SearchString,h.Prod_Hier) > 0 
			  THEN ServiceID 
			  WHEN r.TypeOfRule = 'VAC' and s.PlacementDate <= @i_sDate 
				and s.PlacementDate is not null
				and (s.inactivationdate is null or s.inactivationdate > @i_sDate - 1)
				and patindex(r.SearchString,h.Prod_Hier) > 0 
			  THEN ServiceID
			  WHEN r.TypeOfRule = 'CWS' and s.PlacementDate <= @i_sDate 
				and s.PlacementDate is not null
				and (s.inactivationdate is null or s.inactivationdate > @i_sDate - 1)
				and patindex(r.SearchString,h.Prod_Hier) > 0 
			 THEN ServiceID end as Old_ServiceID
		,CASE WHEN r.TypeOfRule = 'OIL' and s.PlacementDate <= @i_eDate
				and s.PlacementDate is not null
				and (s.inactivationdate is null or s.inactivationdate > @i_eDate)
				and patindex(r.SearchString,h.Prod_Hier) > 0 
			  THEN 'OIL'
			  WHEN r.TypeOfRule = 'VAC' and s.PlacementDate <= @i_eDate
				and s.PlacementDate is not null
				and (s.inactivationdate is null or s.inactivationdate > @i_eDate)
				and patindex(r.SearchString,h.Prod_Hier) > 0 
			  THEN 'VAC' 
			  WHEN r.TypeOfRule = 'CWS' and s.PlacementDate <= @i_eDate
				and s.PlacementDate is not null
				and (s.inactivationdate is null or s.inactivationdate > @i_eDate)
				and patindex(r.SearchString,h.Prod_Hier) > 0 
			  THEN 'CWS' end as New_LOB
		,CASE WHEN r.TypeOfRule = 'OIL' and s.PlacementDate <= @i_eDate
				and s.PlacementDate is not null
				and (s.inactivationdate is null or s.inactivationdate > @i_eDate)
				and patindex(r.SearchString,h.Prod_Hier) > 0 
			  THEN ServiceID
			  WHEN r.TypeOfRule = 'VAC' and s.PlacementDate <= @i_eDate
				and s.PlacementDate is not null
				and (s.inactivationdate is null or s.inactivationdate > @i_eDate)
				and patindex(r.SearchString,h.Prod_Hier) > 0 
			  THEN ServiceID 
			  WHEN r.TypeOfRule = 'CWS' and s.PlacementDate <= @i_eDate
				and s.PlacementDate is not null
				and (s.inactivationdate is null or s.inactivationdate > @i_eDate)
				and patindex(r.SearchString,h.Prod_Hier) > 0 
			  THEN ServiceID end as New_ServiceID,
			s.AssociatedBranchId,s.PlacementDate
	INTO #Tmp_Terr
	FROM KpiCustSvcMstrSla s LEFT OUTER JOIN KpiMaterialAttr h
			ON RIGHT(REPLICATE('0', 18) + LTRIM(RTRIM(s.ServiceID)), 18) = h.Material 
		join [dbo].[fn_CustFirstParseInString](@Values) f
			on coalesce(f.Value,s.AssociatedBranchId) = s.AssociatedBranchId
		,[dbo].[CustFirstRules] r
	WHERE r.Metric = 'LOB' and s.FreeTrial=0

    delete 
	from #Tmp_Terr
	where Old_LOB is null and New_LOB is null
--*************
	select AssociatedBranchId,CustomerID,PlacementDate,Old_LOB,Old_ServiceID
	into #Terr_Start
	from #Tmp_Terr
	where Old_LOB is not null

	select AssociatedBranchId,CustomerID,PlacementDate,New_LOB,New_ServiceID
	into #Terr_End
	from #Tmp_Terr
	where New_LOB is not null

	select b.CustomerID, 'OIL' as LOB, 
			b.AssociatedBranchId,b.PlacementDate,
			b.New_ServiceID as ServiceID
	into #Terr_OIL
	from  #Terr_End b left join #Terr_Start a 
		on a.AssociatedBranchId = b.AssociatedBranchId and 
			a.CustomerID = b.CustomerID and b.New_LOB = a.Old_LOB 
	where a.AssociatedBranchId is null and 
			b.New_LOB is not null and b.New_LOB = 'OIL'
	
	select b.CustomerID, 'CWS' as LOB, 
			b.AssociatedBranchId,b.PlacementDate,
			b.New_ServiceID as ServiceID
	into #Terr_CWS1
	from  #Terr_End b left join #Terr_Start a  
		on a.AssociatedBranchId = b.AssociatedBranchId and 
			a.CustomerID = b.CustomerID and b.New_LOB = a.Old_LOB 
	where a.AssociatedBranchId is null and 
			b.New_LOB is not null and b.New_LOB = 'CWS'
			
				
	SELECT	CAST(SOLD_TO AS INT)CustomerID,
						SUM(so.ZSUBTOT_1)  US INTO #CUST 
				FROM	EntReporting.dbo.CF_kpisalesorder so
						INNER JOIN #PROD_HIER1 ph ON so.Material = ph.MATERIAL
				WHERE  ( so.ITEM_CATEG NOT IN (SELECT SearchString
											   FROM   EntReporting.dbo.CustFirstRules
											   WHERE  Metric = 'CWS Revenue'
													  AND TypeOfRule = 'Item Category Exclude'
													  AND @i_eDate BETWEEN FromDate AND ToDate)
						 OR so.ITEM_CATEG IS NULL )
						 AND ( so.DOC_TYPE <> 'ZMBP' OR so.DOC_TYPE IS NULL )
						 AND ( so.SERV_DATE>=@i_sDate AND so.SERV_DATE<=@i_eDate )
						 AND SOLD_TO in (Select DISTINCT CustomerID FROM  #Terr_CWS1)
						 GROUP  BY SO.SOLD_TO
						 HAVING SUM(so.ZSUBTOT_1) >0
    
    SELECT *into #Terr_CWS  FROM #Terr_CWS1
    WHERE CustomerID in (select DISTINCT CustomerID from #CUST)
	
	select b.CustomerID, 'VAC' as LOB, 
			b.AssociatedBranchId,b.PlacementDate,
			b.New_ServiceID as ServiceID
	into #Terr_VAC
	from  #Terr_End b left join #Terr_Start a 
		on a.AssociatedBranchId = b.AssociatedBranchId and 
			a.CustomerID = b.CustomerID and  b.New_LOB = a.Old_LOB 
	where a.AssociatedBranchId is null and 
		b.New_LOB is not null and b.New_LOB = 'VAC'
	
	create table #CustTerr(CustomerID varchar(10), 
				LOB varchar(5),AssociatedBranchId varchar(10),
				PlacementDate datetime, ServiceID varchar(20))

	insert into #CustTerr
	select CustomerID, LOB,AssociatedBranchId,PlacementDate,ServiceID
	from #Terr_OIL
	union all
	select CustomerID, LOB,AssociatedBranchId,PlacementDate,ServiceID
	from #Terr_VAC
	union all
	select CustomerID, LOB,AssociatedBranchId,PlacementDate,ServiceID
	from #Terr_CWS

	drop table #Terr_OIL
	drop table #Terr_CWS
	drop table #Terr_VAC
	
CREATE TABLE #Placements
  (
     BranchID        VARCHAR(10),
     CustomerID    VARCHAR(10),
     CustomerName  VARCHAR(100),
     LOB           VARCHAR(10),
     SerialNum     VARCHAR(10),
     ServiceID     VARCHAR(20),
     PlacementDATE DATETIME
  )

INSERT INTO #Placements
            (BranchID,
             CustomerID,
             CustomerName,
             LOB,
             SerialNum,
             ServiceID,
             PlacementDate)
select t.AssociatedBranchId,t.CustomerID,c.[Name],LOB,
	'',t.ServiceID,t.PlacementDate
from #CustTerr t join CustomerDetail c
	on right('0000000000'+t.CustomerID,10) = c.CustomerID

--select *
--from #Placements

SELECT DISTINCT k.Material,
                k.PROD_HIER PROD_HIE
INTO   #PROD_HIER
FROM   kpimaterialAttr k
       INNER JOIN CustFirstRules cs
         ON k.PROD_HIER = cs.SearchString
WHERE  cs.Metric = 'NetUnits'
       AND cs.TypeOfRule = 'Prod Hier'
       AND @i_eDate BETWEEN CS.FromDate AND CS.ToDate --remember to put back @Rundate

INSERT INTO #Placements
            (BranchID,
             CustomerID,
             CustomerName,
             LOB,
             SerialNum,
             ServiceID,
             PlacementDate)
SELECT SO.ZSALESOFF,
       SO.SOLD_TO,
       C.NAME,
       'PW' LOB,ZSERIALNO,
       SO.MATERIAL,
       SO.ST_UP_DTE
FROM   CF_KpiSalesOrder so
       INNER JOIN #PROD_HIER PH
         ON PH.Material = so.Material
       INNER JOIN dbo.Fn_custfirstparseinstring(@Values) s
         ON Coalesce(s.[Value], SO.ZSALESOFF) = SO.ZSALESOFF
       INNER JOIN CUSTOMERDETAIL C
         ON SO.SOLD_TO = C.CUSTOMERID
WHERE  ( so.ST_UP_DTE BETWEEN @i_sDate AND @i_eDate )
       AND ( So.Item_Categ <> 'ZPBP'
              OR So.Item_Categ <> 'ZGIM'
              OR So.Item_Categ IS NULL )
       AND So.ZPPSFlag IN ( 'A', 'D', 'C' )

select RIGHT(BranchID, 4) + '/' + B.TXTSH AS Branch,
	CustomerID,CustomerName,LOB, SerialNum,
	cast(ServiceID as Int) ServiceID,
    PlacementDate
from #Placements A
       INNER JOIN KPISALESOFFTEXT B
         ON RIGHT('0000000000' + A.BranchID, 10) = B.ZSALESOFF
order by LOB,CustomerID

	--DROP TABLE  #Placements_01
	DROP TABLE  #Placements
	--DROP TABLE  #FINAL1
	drop table #Terr_End
	drop table #Terr_Start
	drop table #Tmp_Terr
	--drop table #Tmp_Terr_01
	drop table #PROD_HIER
	drop table #CustTerr
	
END

Open in new window

Comment
Watch Question

QPR

Commented:
Nothing that I would bet my house on considering that placement meant whatever the designer had in his mind at the time but if I were to guess (based on your code) I would say that placement was the placement of an order and this SP was extracting order information between a start and end date and that this criteria was used to produce a report to measure the sales for given products

Author

Commented:
Ok. Can you tell me any of the criteria for these dates? Because we have a table that tells the last service date, but I do not know they are comparing the current date to the last one. Anything helps! Thanks for your response.
Commented:
The start date is the returned value from the function dbo.Fn_periodcalendar, without seeing the code within this I can't say what the start date represents.
The end date is maximum (most recent) date where the year and period matches those values passed to the stored procedure. These values are held in a table called CustFirstDailyOutput

So a user may run this code (maybe from a report or spreadsheet or application) and the code would be "passed" values they had chosen, among these values maybe 2011 and Q1 (for year and period... remember this is guess work). The start date would be ? and the end date would be the most recent date within 1/1/2011 and 4/1/2011
The data returned may include all sales within this period and other info such as who sold, what sold and who bought

Author

Commented:
Great! Thats a start at least. Thanks for your time!