Here is my entire proceedure. What I am trying to do is create a dataset that will have the age of a tool that has been returned. Age = Date_Returned - Date Built. The date the tool is built is in the serial number, but in two different formats, so I need to select the correct format. Actually I knwo very little about T-SQL (but, you already know that) I am taking other code from a consultant that I brought in and trying to reconstruct something.
CREATE procedure [dbo].[SELAgingDataPr] (
-- possible filters on product
@Prod_Line [nvarchar] (50) = '*',
@Brand [nvarchar] (50) = '*',
@Tool_Family [nvarchar] (50) = '*',
@Tool_Type [nvarchar] (50) = '*',
@SKU [nvarchar] (50) = '*',
@Model_No [nvarchar] (50) = '*',
@Obsolete [int] = 0,
-- possible filters on error
@Defect_Cat [nvarchar] (50) = '*',
@Defect_Type [nvarchar] (50) = '*',
@Defect_Detail [nvarchar] (65) = '*',
-- possible date ranges
@StartDate [datetime] = '01/01/1899',
@StopDate [datetime] = '01/01/1899',
-- (added 10/8/2004) possible manufacturing location
@Mfg_Location [nvarchar] (50) = '*',
-- possible prefix for "period" label (for reportnet sorting)
@PeriodPrefix [nvarchar] (50) = '*',
-- added to determine which return table date to use
@UseDateField_DateOfReturn [int] = 1,
@UseDateField_Date_Stamp [int] = 0,
@UseDateField_DateBuilt [int] = 0,
-- if the data is to be broken around the start date... as before and after
@BreakTimePeriodsAroundStartDate [int] = 0
)
as
declare
@obs bit,
@SerNo [nvarchar],
@DateBuilt datetime
begin
-- don't display row counters
set nocount on
-- holder table for analysis data
create table #tblCatalogTable (
[Prod_Line] [nvarchar] (50) NULL ,
[Brand] [nvarchar] (50) NULL ,
[Tool_Family] [nvarchar] (50) NULL ,
[Tool_Type] [nvarchar] (50) NULL ,
[Model_No] [nvarchar] (50) NULL ,
[SKU] [nvarchar] (50) NULL,
[Mfg_Location] [nvarchar] (50) NULL
)
-- get the product information
insert into #tblCatalogTable (
[Prod_Line],
[Brand],
[Tool_Family],
[Tool_Type],
[Model_No],
[SKU],
[Mfg_Location]
)
select
[Prod_Line],
[Brand],
[Tool_Family],
[Tool_Type],
[Model_No],
[SKU],
isnull([Mfg_Location],'')
from
Catalog
where
((Prod_Line = @Prod_Line) or (@Prod_Line = '*')) and
((Brand = @Brand) or (@Brand = '*')) and
((Tool_Family = @Tool_Family) or (@Tool_Family = '*')) and
((Tool_Type = @Tool_Type) or (@Tool_Type = '*')) and
((SKU = @SKU) or (@SKU = '*')) and
((Model_No = @Model_No) or (@Model_No = '*')) and
((Obsolete = @obs) or (@obs is null)) and
((Mfg_Location = @Mfg_Location) or (@Mfg_Location = '*')) and
(((system_user=N'cgsosuser') and (Manufactured=0)) or (system_user<>N'cgsosuser'))
-- holder table for analysis data
create table #RETURNS (
[Model_No] [nvarchar] (50) NULL ,
[Serial_No] [nvarchar] (50) NULL,
[Return_Date] [datetime] NOT NULL
)
-- get the filtered analysis data
insert into #RETURNS (
Model_No,
Serial_No,
Return_Date
)
select
Model_No,
Serial_No,
Return_Date
From
RETURNS_WITH_SERIAL
where
Return_Date >= @StartDate and
Return_Date <= @StopDate
-- holder for final sales analysis detail
create table #ReturnsDetail (
[Prod_Line] [nvarchar] (50) NULL ,
[Brand] [nvarchar] (50) NULL ,
[Tool_Family] [nvarchar] (50) NULL ,
[Tool_Type] [nvarchar] (50) NULL ,
[SKU] [nvarchar] (50) NULL,
[Model_No] [nvarchar] (50) NULL ,
[Serial_No] [nvarchar] (50) NULL ,
[Return_Date] [datetime],
[Build_Date] [datetime]
)
-- combine all of the records
insert into #ReturnsDetail (
[Prod_Line],
[Brand],
[Tool_Family],
[Tool_Type],
[SKU],
[Model_No],
[Serial_No],
[Return_Date]
)
select
b.[Prod_Line],
b.[Brand],
b.[Tool_Family],
b.[Tool_Type],
b.[SKU],
a.[Model_No],
a.[Serial_No],
a.[Return_Date]
from
#RETURNS a
join
#tblCatalogTable b
on
a.Model_No = b.Model_No
insert into #ReturnsDetail (
[Build_Date]
)
select
case [Prod_Line]
when 'PT' then
'2006-01-01'
-- set @SerNo = a.[Serial_No]
-- exec sp_PT_Date_Built @SerNo, @DateBuilt OUTPUT
when 'OP' then
-- set @SerNo = a.[Serial_No]
-- exec sp_OP_Date_Built @SerNo, @DateBuilt OUTPUT
'2007-01-01'
end
from
#ReturnsDetail
--Update #ReturnsDetail
-- set Build_Date =
create table #ReturnsSummary (
[Prod_Line] [nvarchar] (50) NULL ,
[Brand] [nvarchar] (50) NULL ,
[Tool_Family] [nvarchar] (50) NULL ,
[Tool_Type] [nvarchar] (50) NULL ,
[SKU] [nvarchar] (50) NULL,
[Model_No] [nvarchar] (50) NULL ,
[Serial_No] [nvarchar] (50) NULL ,
[Return_Date] [datetime],
[Build_Date] [datetime],
[Age] [int]
)
insert into #ReturnsSummary (
[Prod_Line],
[Brand],
[Tool_Family],
[Tool_Type],
[SKU],
[Model_No],
[Serial_No],
[Return_Date],
[Build_Date],
[Age]
)
select
[Prod_Line],
[Brand],
[Tool_Family],
[Tool_Type],
[SKU],
[Model_No],
[Serial_No],
[Return_Date],
[Build_Date],
datediff(mm,build_Date,Return_Date) as Age
from
#ReturnsDetail
-- return the results
select
[Prod_Line],
[Brand],
[Tool_Family],
[Tool_Type],
[SKU],
[Model_No],
[Serial_No],
[Return_Date],
[Build_Date],
[Age]
from
#ReturnsSummary
order by
[Prod_Line],
[Brand],
[Tool_Family],
[Tool_Type],
[SKU],
[Model_No]
-- cleanup temp tables
drop table #tblCatalogTable
drop table #RETURNS
drop table #ReturnsDetail
drop table #ReturnsSummary
end
GO
![[smile]](/data/assets/smilies/smile.gif "[smile] [smile]")
